MySQL 더미 데이터 적재 속도 비교

인트로

Offset 페이징과 No-Offset 페이징의 성능을 비교하기 위해 더미 데이터를 추가하던 중,
호기심이 생겨 다양한 방식으로 적재 속도를 비교해 보았습니다.

실행 환경

• PC : Apple M2 Pro (저전력모드)
• Tool : IntelliJ Datagrip
• Docker : MySQL 8.0.31

 

더미 데이터

• 1,000만 건의 Article 레코드
• 외래키 제약조건 1개, Check 제약조건 2개가 존재

---

성능 측정

1.  개별 Insert

from datetime import datetime

#SQL 파일 생성
sql_file_path = 'individual_insert.sql'
with open(sql_file_path, 'w') as sql_file:
    # 1부터 10000000까지의 데이터 삽입
    for i in range(1, 10000001):
        dt = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        insert_statement = f"INSERT INTO article (article_id, contents, created_term, created_year, published, user_id, created_date, modified_date) VALUES ({i}, 'contents', 1, 2024, 1, 537307139847203855, '{dt}', '{dt}');\n"

        sql_file.write(insert_statement)

print(f"SQL 파일이 생성되었습니다: {sql_file_path}")

• INSERT문 1,000만개로 구성된 sql 파일을 실행

 

소요 시간

• 3시간 23분 경과

---

2. 프로시저 호출

create
    definer = root@`%` procedure loopInsert()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE dt datetime DEFAULT NOW();
    WHILE i <= 10000000 DO
            INSERT INTO article
            VALUES(i, 'contents', 1, 2024, 1, 537307139847203855, dt, dt);
            SET i = i + 1;
        END WHILE;
END;

• 반복문으로 INSERT를 1000만 번 실행하는 프로시저를 호출

 

소요 시간

• 2분 12초 경과

---

3. CSV Import

• 더미 데이터가 저장된 csv 파일을 생성 후 Import

 

소요 시간

 

• 12분 59초 경과

---

4. Bulk Insert

# SQL 파일 생성
sql_file_path = 'bulk_insert.sql'
with open(sql_file_path, 'w') as sql_file:
    # INSERT INTO ... VALUES 문 작성
    sql_file.write("INSERT INTO article (article_id, contents, created_term, created_year, published, user_id, created_date, modified_date) VALUES\n")

    # 1부터 10000000까지의 데이터 삽입
    for i in range(1, 10000001):
        dt = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        values = f"({i}, 'contents', 1, 2024, 1, 537307139847203855, '{dt}', '{dt}')"

        # 마지막 행이 아니라면 쉼표를 추가
        if i < 10000000:
            values += ","

        sql_file.write(values)

    sql_file.write(";\n")

print(f"SQL 파일이 생성되었습니다: {sql_file_path}")

• Bulk Insert문이 담긴 sql 파일을 실행

 

소요 시간

• 9분 24초 경과

---

5. Bulk Insert + 자동 커밋 비활성

SET autocommit=0; [BULK INSERT문] commit;

• 자동 커밋을 비활성화 한 후, Bulk Insert를 수행

 

소요 시간

• 9분 15초 경과 (9초 개선)

---

6. Bulk Insert + 자동 커밋 비활성 + 외래키 검사 비활성

SET autocommit=0;
SET foreign_key_checks=0;
[BULK INSERT문]
SET foreign_key_checks=1;
commit;

• 자동 커밋과 외래키 검사를 비활성화 후, Bulk Insert를 수행

 

소요 시간

• 9분 3초 경과 (21초 개선)

---

결론

요약

• 성능 : 프로시저 > Bulk Insert > CSV Import >>>>> 개별 Insert
• Bulk Insert는 개별 Insert 방식보다 21배, CSV 방식보다 1.38배 빠르다
• 프로시저는 Bulk Insert 방식보다 4.27배 빠르다

 

정리

1. 데이터 구성이 단순한 경우 프로시저를 활용하자
2. 프로시저 활용이 불가능하다면 Bulk Insert를 활용하자
3. Bulk Insert시, 자동 커밋에 의한 영향은 미미하다
4. 외래키 검사를 비활성화하면 좀 더 빨라진다 (외래키 1개 기준, 2% 개선)

---

참고한 글

• [SQL] 쿼리 튜닝을 위한 준비 : 더미 데이터 적재
• BULK INSERTING - MYSQL 다량의 데이터 넣기