[ Mysql ] 대량의 데이터를 넣을 때 속도를 개선하는 법

5tr1ker·2024년 8월 17일

Database

목록 보기

7/7

개요

CREATE PROCEDURE createPartnerRequestTestData()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE y INT DEFAULT 1;
    
    WHILE (i <= 1000) DO
		INSERT INTO `partner_request` (`description`, `detail_address`, `locationx`, `locationy`, `market_address`, `phone_number`, `request_market_name`, `title`, `write_time`, `request_user_account_id`) VALUES ('description', 'addrtess', '10.145351', '36.4131235', 'market_address', 'phone_number', 'market_name', 'title', '2000-03-02 00:00:00', 1);
        set y = 0;
        set @partner_request_index = (select max(partner_request_id) from partner_request) - 1;
        
        while (y <= 20) DO
			INSERT INTO `partner_comment` (`message`, `write_time`, `partner_request_partner_request_id`, `writer_account_id`) VALUES ('message', '2000-03-02 00:00:00', (i + y) % @partner_request_index + 1 , 2);
            
            set y = y + 1;
        END while;
        
        SET i = i + 1;
    END WHILE;
END$$

위의 코드는 테스트데이터 20000개 ( 부모 데이터 1개당 자식 데이터 2개 ) 를 삽입하는 로직입니다.
반복문을 통해 간단하게 20000개의 데이터를 넣는 로직인데 문제는 이 로직을 실행하면 72초의 시간이 걸리는 문제가 발생합니다.

따라서 이번 포스팅에서는 대량의 데이터를 빠르게 넣는 방법을 알아보고자 작성하게 되었습니다.

하나의 트랜잭션으로 처리하기

MySQL에서는 기본적으로 autocommit 가 되어있습니다. 즉 insert 문은 독립적인 트랜잭션으로 처리됩니다. 이는 하나의 insert문이 실행될 경우 트랜잭션이 start되고 commit 되는 것으로 20000번의 insert시 트랜잭션이 20000개가 실행됩니다.

이는 트랜잭션이 열고 닫힐 때 많은 비용이 발생합니다. 이는 insert 도중에 문제가 발생하면 최근 insert한 데이터에 roleback 을 하여 성능상의 이점을 챙길 수 있습니다. 하지만 안정성이 높은 대신에 속도가 느리다는 단점이 발생합니다.

따라서 트랜잭션이 start 하면 20000개의 데이터를 넣고 commit 을 하여 단 1개의 트랜잭션이 실행되게 구현할 수 있습니다. 다만 속도가 빠른 장점이 있지만 1개의 데이터가 오류가 생길경우 모두 롤백될 수 있습니다.

방법 1. Bulk Insert

insert into tb_name (a, b, c)
values (1, 2, 3), (4, 5, 6), (7, 8, 9);

value를 여러개 명시하여 insert를 하는 방식입니다. ( Mysql에서는 Bulk insert라고 부릅니다. ) 3개의 row가 하나의 트랜잭션에서 처리됩니다.
하지만 모두 명시해주어야하기 때문에 Insert 할 데이터가 많다면 좋은 방법은 되지 못할 것 같습니다.

방법 2. one Transaction 명시

start transaction;
insert into tb_name (a, b, c) values (1, 2, 3);
insert into tb_name (a, b, c) values (4, 5, 6);
insert into tb_name (a, b, c) values (7, 8, 9);
commit;

이 또한 방법 1 과 동일한 결과를 얻을 수 있습니다. start transaction 으로 트랜잭션의 시작을 직접 명시하고, 여러개의 insert문을 거쳐 commit 코드를 통해 트랜잭션을 닫을 수 있습니다. 이는 위의 코드의 앞 뒤에 명시하여 하나의 트랜잭션을 처리할 수 있습니다.

방법 2로 문제 해결하기

위에서 정의한 프로시져를 방법 2 의 방법으로 성능을 최적화 해봅니다.

소요시간은 1.06second로 하나의 트랜잭션을 처리했을 때 70배 이상의 빠른 속도를 내는 것을 알 수 있습니다.

인덱스 비활성화

두번째로 생각해볼 방법은 인덱스를 비활성화 하는 것 입니다. MySQL은 인덱스를 사용할 시 B-Tree 자료구조 를 사용하는데, 매번 데이터가 추가될 때 마다 tree를 재 배열하는 과정이 진행되는데 이때 비용을 최소화하기 위해 인덱스를 일시적으로 비활성화 하는 것 입니다.

# 인덱스 비활성화
alter table partner_request disable keys;
alter table partner_comment disable keys;

# 인덱스 활성화
alter table partner_request enable keys;
alter table partner_comment enable keys;