MySQL Bulk Update (feat: temporal table)

서론

---

 

DB에 직접 bulk update 쿼리를 실행시켜야 했는데 어떻게 하면 조금이라도 최적화 시킬 수 있을까를 검색을 하였더니
temporal table을 만드는 방식이 있었다.

 

 

해당 방식을 이번에 처음 알았고 성능이 얼마나 달라질까 궁금하여 직접 일반 업데이트와 temporal table을 생성하여 업데이트 하는 방식을 비교해보았다.

 

 

이 글에서 어떤 식으로 쿼리를 작성했고, 결과는 어땠는지를 정리할 예정이다.

 

 

본론

---

실험 조건

데이터는 대략 10만개를 넣어주었다.

 

많은 필드를 업데이트 하지는 않았고, 단 한 필드만 업데이트 하면 되는 상황이었기에 테스트를 할 때도 한 필드만 업데이트를 했다. (만간에 여러개 업데이트 할 때도 테스트를 할 예정이다.)

 

일반 update query

update item
set item_rank = 5
where item_rank >= 6; 

 

 

변수를 최대한 통제하기 위해 모든 데이터들이 업데이트 되게 셋팅해 놓았다. (item_rank가 모두 6이상으로 초기화)

 

 

 

사진에서 볼 수 있듯이 업데이트를 하는데 대략 0.4s 가 걸림을 볼 수 있다.

 

 

temporal table을 사용한 update

 

DB에서 직접 쿼리를 날려야 하기에 프로시저를 만들어서 실행시켰다.

그리고 데이터의 양이 많다보니 해당 방식에서도 1번에 전부 업데이트, 10번에 나누어서 업데이트 2가지 케이스를 모두 테스트 해보았다.

 

1번에 전부 업데이트

BEGIN
    DECLARE chunk_size INT DEFAULT 200000; -- 청크 크기 설정
    DECLARE start_row INT DEFAULT 0;
    DECLARE total_rows INT;
    -- 총 레코드 수 확인
    SELECT COUNT(*) INTO total_rows FROM item WHERE item_rank >= 6;
    -- 청크 단위로 데이터 업데이트
    WHILE start_row < total_rows DO
        -- 임시 테이블에 청크 단위로 데이터 복사
        CREATE TEMPORARY TABLE temp_item AS (
            SELECT item_id
            FROM item
            WHERE item_rank >= 6
            LIMIT start_row, chunk_size
        );
        -- 임시 테이블을 사용하여 업데이트
        UPDATE item
        JOIN temp_item ON item.itemn_id = temp_item.item_id
        SET item.item_rank = 5;
        -- 임시 테이블 삭제
        DROP TEMPORARY TABLE IF EXISTS temp_item;
        -- 다음 청크 시작 위치 설정
        SET start_row = start_row + chunk_size;
    END WHILE;

 

 

 

 

사진에서 볼 수 있듯이 update에만 대략 0.8s가 걸리고 teporal table을 생성하고 삭제하는데 0.1~0.2s가 걸린다.

그냥 Update 보다 느리다...

 

 

10번에 나눠서 업데이트

BEGIN
    DECLARE chunk_size INT DEFAULT 10000; -- 청크 크기 설정
    DECLARE start_row INT DEFAULT 0;
    DECLARE total_rows INT;
    -- 총 레코드 수 확인
    SELECT COUNT(*) INTO total_rows FROM item WHERE item_rank >= 6;
    -- 청크 단위로 데이터 업데이트
    WHILE start_row < total_rows DO
        -- 임시 테이블에 청크 단위로 데이터 복사
        CREATE TEMPORARY TABLE temp_item AS (
            SELECT item_id
            FROM item
            WHERE item_rank >= 6
            LIMIT start_row, chunk_size
        );
        -- 임시 테이블을 사용하여 업데이트
        UPDATE item
        JOIN temp_item ON item.itemn_id = temp_item.item_id
        SET item.item_rank = 5;
        -- 임시 테이블 삭제
        DROP TEMPORARY TABLE IF EXISTS temp_item;
        -- 다음 청크 시작 위치 설정
        SET start_row = start_row + chunk_size;
    END WHILE;

 

 

 

이 방식은 결과가 너무 길어서 일부만 짤랐는데 대략 1번의 작업(테이블 생성 + 업데이트 + 삭제)이 (0.02s + 0.25s + 0.0002s)가 나왔다. 평균적으로 0.25 ~ 0.3s 가 걸리고 10번을 하므로 총 2s 가 넘는다...

 

 

결론

---

 

효율적인 업데이트 방법을 찾으면서 해당 방식은 굉장히 여러번 언급되었고 실제로 결과를 봤을 때 빠른 케이스도 있었다.

 

 

하지만 적어도 내 상황에서는 일반 업데이트가 더 빨랐다, 이는 데이터가 적어서 그럴 수도 있고 업데이트 필드가 1개만 있어서 그럴 수도 있다고 생각한다.

 

 

다음 번에는 업데이트 필드가 좀 더 많을 경우에는 어떤 결과가 나타나는지 테스트 해 볼 예정이다.