수천만 건 공공데이터 ETL 적재 시간을 90% 단축하는 MySQL LOAD DATA와 무중단 테이블 스왑 아키텍처

전국 사업자 DB, 도로명 주소, 요양기관 데이터 등 수천만 건에 달하는 대용량 공공데이터를 주기적으로 수집하여 서비스 DB에 갱신하는 ETL 파이프라인에서 가장 큰 기술적 도전은 대량 적재 시 발생하는 데이터베이스 I/O 병목과 서비스 가용성 저하입니다.

일반적인 ORM 기반 단건 INSERT나 수천 건 단위의 INSERT INTO ... ON DUPLICATE KEY UPDATE 문은 매 레코드마다 B+Tree 인덱스 재정렬, Undo 로그 생성, Redo 로그 플러시, 잠금 경합을 유발합니다. 이로 인해 운영 중인 서비스의 버퍼 풀이 오염되어 사용자 조회 쿼리가 지연되거나, 장시간 트랜잭션으로 인한 락 타임아웃(Lock Wait Timeout)이 발생합니다. 본 글에서는 수천만 건의 공공데이터를 단 몇 분 만에 적재하면서도 실시간 조회 서비스의 다운타임(Downtime)을 0초로 유지하는 MySQL LOAD DATA INFILE 벌크 인서트와 원자적 테이블 스왑(Atomic Table Swap) 아키텍처를 분석합니다.

1. 일반 INSERT 배치와 LOAD DATA의 엔진 레벨 차이

MySQL InnoDB 스토리지 엔진에서 INSERT 문은 SQL 파싱, 실행 계획 수립, 클라이언트-서버 프로토콜 왕복 오버헤드를 동반합니다. 반면 LOAD DATA INFILE은 파일 시스템에서 바이너리 스트림을 직접 읽어 서버 내부 파서로 처리하므로 다음과 같은 압도적인 성능 이점을 가집니다.

• SQL 파싱 바이패스: 수천만 줄의 SQL 토큰화 과정을 생략하고 고정된 구분자(Delimiter)를 기준으로 버퍼 블록 단위로 고속 직렬화합니다.
• 인덱스 트리 벌크 빌드(Bulk Index Building): 데이터를 적재하는 동안 보조 인덱스(Secondary Index) 정렬을 일시 지연시키고 메모리에서 정렬 후 일괄 삽입(Sorted Index Insertion)하여 B+Tree 페이지 분할(Page Split)을 최소화합니다.
• 네트워크 오버헤드 제거: 쿼리 패킷당 수십 바이트씩 낭비되는 프로토콜 헤더 없이 순수 데이터 페이로드만 전송합니다.

2. 무중단 적재를 위한 임시 스테이징 및 원자적 RENAME 스키마

실제 운영 중인 서비스 테이블(clinics)에 직접 벌크 인서트를 실행하면 적재가 끝날 때까지 심각한 읽기 지연이 발생합니다. 따라서 동일한 DDL을 가진 임시 스테이징 테이블(clinics_staging)에 먼저 데이터를 고속 적재한 뒤, MySQL의 원자적(Atomic) 다중 테이블 RENAME 문으로 순간 스왑합니다.

-- 1. 스테이징 테이블 생성 (운영 테이블과 동일한 DDL)
DROP TABLE IF EXISTS clinics_staging;
CREATE TABLE clinics_staging LIKE clinics;

-- 2. 고속 적재를 위한 세션 최적화 변수 설정
SET SESSION unique_checks = 0;
SET SESSION foreign_key_checks = 0;
SET SESSION sql_log_bin = 0; -- 복제 지연 방지 필요 시 슬레이브 별도 고려

-- 3. LOAD DATA INFILE 벌크 스트리밍 적재
LOAD DATA LOCAL INFILE '/tmp/public_data_clinics_20261005.csv'
INTO TABLE clinics_staging
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(ykiho_id, clinic_name, medical_type, post_no, address, tel_no, created_at);

-- 4. 무중단 원자적 테이블 스왑 (Downtime 0초 전환)
-- MySQL은 단일 RENAME 문 내의 복수 테이블 이름 변경을 원자적으로 처리합니다.
DROP TABLE IF EXISTS clinics_old;
RENAME TABLE
    clinics TO clinics_old,
    clinics_staging TO clinics;

-- 5. 백그라운드 구버전 테이블 안전 삭제
DROP TABLE clinics_old;

3. Python ETL 파이프라인 구현체 및 연결 풀 최적화

Python 환경에서 PyMySQL 또는 mysql-connector-python 라이브러리를 통해 로컬 파일을 MySQL 서버로 직접 스트리밍 적재하는 자동화 워커 패턴입니다.

import os
import pymysql
import logging

logger = logging.getLogger("PublicDataBulkLoader")

class BulkDataETLLoader:
    def __init__(self, db_config: dict):
        self.config = db_config
        # LOCAL INFILE 옵션 필수 활성화
        self.config["local_infile"] = True

    def execute_bulk_swap(self, csv_filepath: str, target_table: str):
        staging_table = f"{target_table}_staging"
        old_table = f"{target_table}_old"

        connection = pymysql.connect(**self.config)
        try:
            with connection.cursor() as cursor:
                # 1. 스테이징 테이블 초기화
                cursor.execute(f"DROP TABLE IF EXISTS {staging_table};")
                cursor.execute(f"CREATE TABLE {staging_table} LIKE {target_table};")

                # 2. 성능 최적화 세션 변수 적용
                cursor.execute("SET SESSION unique_checks = 0;")
                cursor.execute("SET SESSION foreign_key_checks = 0;")

                # 3. 벌크 적재 실행
                logger.info("LOAD DATA INFILE 스트리밍 시작: %s", csv_filepath)
                load_sql = f"""
                    LOAD DATA LOCAL INFILE '{csv_filepath}'
                    INTO TABLE {staging_table}
                    CHARACTER SET utf8mb4
                    FIELDS TERMINATED BY ',' ENCLOSED BY '"'
                    LINES TERMINATED BY '\n'
                    IGNORE 1 LINES;
                """
                cursor.execute(load_sql)
                connection.commit()

                # 4. 원자적 다중 테이블 RENAME 스왑 실행
                logger.info("원자적 테이블 스왑(Atomic Swap) 수행 중...")
                cursor.execute(f"DROP TABLE IF EXISTS {old_table};")
                rename_sql = f"""
                    RENAME TABLE
                        {target_table} TO {old_table},
                        {staging_table} TO {target_table};
                """
                cursor.execute(rename_sql)
                connection.commit()

                # 5. 이전 버전 백업 테이블 정리
                cursor.execute(f"DROP TABLE IF EXISTS {old_table};")
                connection.commit()
                logger.info("ETL 벌크 적재 및 테이블 스왑 완료!")
        except Exception as exc:
            connection.rollback()
            logger.error("벌크 적재 실패 (롤백 완료): %s", exc)
            raise
        finally:
            connection.close()

4. 결론 및 도입 성과

수천만 건의 공공데이터를 갱신할 때 단건 INSERT나 청크 배치 방식 대신 LOAD DATA LOCAL INFILE과 원자적 RENAME TABLE 스왑 패턴을 도입하면, 적재 소요 시간을 90% 이상 단축하면서도 락 경합 없는 완전 무중단 서비스를 실현할 수 있습니다. 배치 파이프라인의 물리적 한계를 극복하는 가장 신뢰성 높은 엔지니어링 표준입니다.

출처 및 1차 공식 기술 문서

• MySQL 8.0 Reference Manual — LOAD DATA Statement Syntax and LOCAL Infile Handling
• MySQL 8.0 Reference Manual — Optimizing InnoDB Bulk Data Loading
• MySQL 8.0 Reference Manual — RENAME TABLE Statement and Atomicity Guarantees