MySQL 대량 INSERT에서 보조 인덱스가 늘면 생기는 비용: 10만 건 입력 비교

“이 화면 조회가 느린데, 인덱스 하나만 추가해 주세요.” DBA가 가장 자주 받는 요청 중 하나입니다. 조회 쪽에서 보면 인덱스는 거의 공짜처럼 보입니다. 하지만 인덱스에는 청구서가 따라옵니다. 그 테이블에 행이 들어올 때마다, 바뀔 때마다, 지워질 때마다 인덱스도 함께 고쳐야 하기 때문입니다.

이 글은 그 청구서를 숫자로 뽑아 본 기록입니다. MySQL 8.4.11에서 같은 데이터 10만 건을 보조 인덱스 0개, 1개, 4개인 테이블에 각각 넣고 입력 시간과 공간을 비교했습니다. 그다음 인덱스를 줄이면 비용이 실제로 돌아오는지도 같은 테이블에서 확인했습니다.

청구 항목	보조 인덱스 0개	1개	4개
10만 건 평균 입력 시간	1,920.4ms	2,559.8ms	4,161.0ms
보조 인덱스 공간	0.00MiB	2.52MiB	11.06MiB

측정 조건: 같은 데이터, 같은 입력 방식, 다른 인덱스

항목	값
MySQL 버전	8.4.11
테스트 스키마	lab_insert37 (InnoDB)
원본 데이터	src 테이블 100,000행
버퍼 풀 / Redo 용량	128MiB / 100MiB
innodb_flush_log_at_trx_commit	1
log_bin / sync_binlog / binlog_format	1 / 1 / ROW
innodb_change_buffering	none
autocommit / 격리 수준	1 / READ-COMMITTED

로그 설정은 커밋마다 redo와 바이너리 로그를 모두 디스크에 동기화하는, 운영에서 흔히 쓰는 안전한 조합입니다. 마지막의 innodb_change_buffering = none은 뒤에서 결과를 해석할 때 다시 등장합니다.

세 테이블의 컬럼은 id, customer_id, order_time, status_code, amount, memo로 같고, 모두 PRIMARY KEY(id)를 가집니다. 다른 것은 보조 인덱스뿐입니다.

테이블	보조 인덱스	정의
t0	0개	없음 (기본 키만)
t1	1개	ix_customer(customer_id)
t4	4개	ix_customer(customer_id), ix_time(order_time), ix_status_time(status_code, order_time), ix_amount(amount)

원본 데이터는 id가 증가해도 customer_id와 order_time은 같은 순서로 증가하지 않도록 만들었습니다. 기본 키에는 순서대로 들어가지만, 보조 인덱스에는 값이 여기저기 흩어져 들어가는 조건입니다. 실제 주문 데이터에서도 고객 번호나 금액은 입력 순서와 무관하게 들어옵니다.

입력은 단일 세션에서 INSERT … SELECT로 10만 건을 넣고 한 번 커밋하는 방식이며, 대상 테이블 이름만 바꿔 실행했습니다.

START TRANSACTION;

INSERT INTO lab_insert37.t4
    (id, customer_id, order_time, status_code, amount, memo)
SELECT
    id, customer_id, order_time, status_code, amount, memo
FROM lab_insert37.src
ORDER BY id;

COMMIT;

측정 규칙은 다음과 같습니다.

• 테이블을 비운 뒤 입력하고, INSERT부터 COMMIT까지의 경과 시간만 기록했습니다. 데이터 생성, TRUNCATE, 구문 준비 시간은 제외했습니다.
• 테이블마다 예열 1회 후 본 측정 3회를 했습니다.
• 실행 순서를 0→1→4, 1→4→0, 4→0→1로 돌려 특정 테이블이 늘 먼저 실행되지 않게 했습니다. 다만 캐시와 백그라운드 작업의 영향까지 완전히 없앤 것은 아닙니다.

청구 항목 ① 입력 시간: 인덱스 하나가 수백 ms

테이블	보조 인덱스	최소	평균	최대	초당 처리 행 수
t0	0개	1,831.9ms	1,920.4ms	1,976.5ms	52,071
t1	1개	2,424.2ms	2,559.8ms	2,704.5ms	39,065
t4	4개	4,011.1ms	4,161.0ms	4,255.1ms	24,033

평균만 보면 4개짜리 테이블은 인덱스가 없는 테이블보다 약 2.17배 오래 걸렸습니다. 하지만 청구서로 읽으려면 “인덱스 하나를 더할 때마다 얼마가 붙는가”로 나눠 봐야 합니다.

구간	늘어난 평균 시간	인덱스 1개당
0개 → 1개	+639.4ms	약 639ms
1개 → 4개	+1,601.2ms	약 534ms

이 테이블에서는 보조 인덱스 하나가 10만 건 적재마다 대략 0.5~0.6초를 더했습니다. 인덱스 4개가 1개의 네 배 비용이 되지 않는 것은 원본 읽기, 기본 키 입력, 로그 기록과 커밋 같은 고정 비용이 전체 시간에 함께 들어 있기 때문입니다. 또 인덱스마다 컬럼 폭과 값 분포가 달라 1개당 비용도 같지 않습니다.

세 번의 측정 범위도 겹치지 않았습니다. t0의 가장 느린 값(1,976.5ms)이 t1의 가장 빠른 값(2,424.2ms)보다 빨랐고, t1의 가장 느린 값도 t4의 가장 빠른 값보다 빨랐습니다. 한 번의 우연한 지연이 평균을 끌어올린 결과는 아니라는 뜻입니다.

초당 처리 행 수는 10만 건을 경과 시간으로 나눈 값입니다. 10만 건을 한 트랜잭션으로 넣었으므로 이 숫자를 초당 트랜잭션 수(TPS)로 읽으면 안 됩니다.

청구 항목 ② 공간: 데이터는 같고 인덱스만 늘었습니다

시간 측정 뒤 ANALYZE TABLE을 실행하고, 실제 행 수와 공간 지표를 함께 조회했습니다.

테이블	실제 행 수	DATA_LENGTH	INDEX_LENGTH	데이터 대비 인덱스
t0	100,000	14.52MiB	0.00MiB	0%
t1	100,000	14.52MiB	2.52MiB	약 17%
t4	100,000	14.52MiB	11.06MiB	약 76%

InnoDB의 DATA_LENGTH는 기본 키 순서로 행 전체를 담은 클러스터드 인덱스의 크기이고, INDEX_LENGTH는 보조 인덱스의 크기입니다. 그래서 t0의 INDEX_LENGTH가 0이라도 기본 키가 없다는 뜻은 아닙니다. 두 값 모두 할당 공간의 근사치입니다.

t4에서는 보조 인덱스가 데이터의 4분의 3 가까운 공간을 차지했습니다. 이 공간은 디스크뿐 아니라 버퍼 풀에서도 자리를 차지하므로, 인덱스가 늘면 같은 메모리에 올릴 수 있는 데이터 페이지가 그만큼 줄어듭니다. 다만 이 값은 유지 공간을 보여 줄 뿐, 이번 INSERT가 만든 디스크 쓰기량이나 redo 생성량을 뜻하지는 않습니다.

청구서가 이렇게 나온 이유

InnoDB에서 행 하나를 넣으면 기본 키 순서의 클러스터드 인덱스에 행이 들어가고, 보조 인덱스마다 “그 인덱스 컬럼 값 + 기본 키” 엔트리가 하나씩 추가됩니다. 인덱스가 4개면 행 하나에 B-Tree 다섯 곳을 고치는 셈입니다.

여기에 이번 데이터는 보조 인덱스 쪽 값이 입력 순서와 무관했습니다. 기본 키는 끝에 차곡차곡 붙지만, 보조 인덱스 엔트리는 B-Tree의 여러 페이지에 흩어져 들어가야 합니다. 이런 무작위 삽입은 더 많은 페이지를 버퍼 풀로 읽어 오고 고치게 만듭니다.

그리고 innodb_change_buffering = none입니다. 체인지 버퍼는 보조 인덱스 페이지가 메모리에 없을 때 변경을 잠시 모아 두었다가 나중에 반영하는 최적화입니다. MySQL 8.4부터는 이 설정의 기본값이 none이라, 이번 측정에서는 보조 인덱스 변경이 그때그때 바로 처리되었습니다. 이전 버전의 기본 설정이나 다른 서버 설정에서는 결과의 폭이 달라질 수 있습니다.

이번에는 페이지 분할 횟수, CPU, 물리 I/O, 대기 이벤트를 따로 측정하지 않았습니다. 따라서 위 설명은 늘어난 시간을 해석하는 근거이며, 각 요인이 몇 ms씩 차지했는지까지 나눈 것은 아닙니다.

환불 검증: 같은 테이블에서 인덱스를 줄이면 시간이 돌아오는가

서로 다른 테이블끼리 비교하면 “테이블이 달라서 생긴 차이 아니냐”는 의문이 남습니다. 그래서 t4에서 ix_customer만 남기고 보조 인덱스 3개를 삭제한 뒤 같은 방식으로 다시 측정했습니다.

ALTER TABLE lab_insert37.t4
    DROP INDEX ix_time,
    DROP INDEX ix_status_time,
    DROP INDEX ix_amount;

t4 상태	보조 인덱스	최소	평균	최대	초당 처리 행 수
삭제 전	4개	4,011.1ms	4,161.0ms	4,255.1ms	24,033
삭제 후	1개	2,281.9ms	2,477.2ms	2,598.7ms	40,368
참고: t1	1개	2,424.2ms	2,559.8ms	2,704.5ms	39,065

평균 입력 시간은 약 40.5% 줄었고, 초당 처리 행 수는 약 68.0% 늘었습니다. 두 비율은 계산 기준이 달라서 같은 숫자가 나오지 않습니다. 더 눈여겨볼 점은 삭제 후의 t4가 처음부터 인덱스가 1개였던 t1(평균 2,559.8ms)과 약 3.2% 차이로 거의 같은 수준이 되었다는 것입니다. 같은 인덱스 구성이면 비슷한 비용이 나온다는 뜻이므로, 앞의 차이가 테이블이 달라서가 아니라 인덱스 구성 때문이었다는 근거가 됩니다.

이 비교에는 인덱스 삭제 시간이 포함되지 않았습니다. 또 3.2%라는 작은 차이를 근거로 “나중에 삭제한 쪽이 더 빠르다”고 말할 수는 없습니다. 실행 시점과 캐시 상태의 영향이 남아 있기 때문입니다.

운영에서 이 청구서를 줄여 온 방법

대용량 이관: 데이터 먼저, 인덱스는 나중에

실제 운영에서 대용량 테이블을 이관할 때는 인덱스를 모두 갖춘 테이블에 데이터를 밀어 넣지 않았습니다. 데이터를 먼저 신규 테이블로 옮기고, 이관이 끝난 뒤 인덱스를 하나씩 생성했습니다. 이렇게 진행했을 때 이관 성능이 높게 나왔습니다.

이 실험의 결과가 그 이유를 보여 줍니다. 인덱스가 있는 상태로 넣으면 행마다 보조 인덱스 여러 곳을 흩어진 위치에 고쳐야 합니다. 반대로 데이터가 다 들어간 뒤 인덱스를 만들면, InnoDB는 인덱스 값을 한꺼번에 정렬해 B-Tree를 아래에서부터 차곡차곡 쌓는 방식으로 만들 수 있습니다. 같은 엔트리를 만드는 일이라도 무작위 삽입보다 훨씬 효율적인 경로입니다.

다만 이번 실험은 인덱스 생성 시간을 측정하지 않았으므로, “적재 + 인덱스 생성” 전체 시간 비교는 이 글의 수치로 증명한 것이 아닙니다. 인덱스를 나중에 만들면 그동안 그 인덱스가 필요한 조회는 쓸 수 없다는 점도 이관 계획에 넣어야 합니다.

안 쓰는 인덱스 정리: 바로 삭제하지 않고 Invisible로 며칠

이미 만들어진 인덱스의 비용을 줄이는 방법은 쓰지 않는 인덱스를 찾아 없애는 것입니다. 운영에서는 sys.schema_unused_indexes로 사용 기록이 없는 인덱스를 찾아 정리했습니다.

SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes
WHERE object_schema = 'lab_insert37';

이 뷰는 서버가 시작된 이후 performance_schema에 쌓인 사용 기록을 기준으로 합니다. 재시작한 지 얼마 안 됐거나, 월말 정산처럼 가끔 도는 작업이 아직 실행되지 않았다면 실제로 필요한 인덱스도 “미사용”으로 나올 수 있습니다.

그래서 찾은 인덱스를 곧바로 삭제하지 않았습니다. 먼저 Invisible 상태로 바꿔 옵티마이저가 쓰지 않게 하고, 며칠 동안 특이사항이 없는 것을 확인한 뒤에 최종 삭제를 진행했습니다.

-- 1단계: 옵티마이저에서만 숨김 (즉시 되돌릴 수 있음)
ALTER TABLE lab_insert37.t4 ALTER INDEX ix_amount INVISIBLE;

-- 문제가 생기면 바로 복구
ALTER TABLE lab_insert37.t4 ALTER INDEX ix_amount VISIBLE;

-- 2단계: 며칠 관찰 후 특이사항이 없으면 삭제
ALTER TABLE lab_insert37.t4 DROP INDEX ix_amount;

이 순서가 안전한 이유는 되돌리는 비용이 다르기 때문입니다. 삭제한 인덱스를 다시 만들려면 테이블 전체를 읽어 새로 생성해야 하지만, Invisible을 Visible로 돌리는 것은 메타데이터만 바꾸는 작업이라 즉시 끝납니다.

주의할 점은 Invisible 단계에서는 청구서가 줄지 않는다는 것입니다. Invisible 인덱스는 조회에만 쓰이지 않을 뿐, INSERT·UPDATE·DELETE 때는 여전히 함께 갱신됩니다. 이번 실험에서 본 입력 비용과 공간은 마지막 DROP 단계에서야 돌아옵니다. Invisible은 비용을 줄이는 단계가 아니라, 삭제해도 괜찮은지 확인하는 단계입니다.

이 숫자를 그대로 옮기면 안 되는 경우

• 빈 테이블 vs 큰 테이블: 이번 측정은 빈 테이블에 넣은 결과입니다. 이미 수억 건이 있는 테이블에 추가하면 보조 인덱스 페이지가 버퍼 풀에 없을 확률이 커져 비용이 더 커질 수 있습니다.
• 커밋 단위: 10만 건을 한 번에 커밋했습니다. 한 행씩 커밋하면 커밋마다 로그 동기화 비용이 붙어 인덱스 비용의 비중이 달라집니다.
• 동시 입력: 단일 세션 결과입니다. 여러 세션이 동시에 넣으면 같은 인덱스 페이지를 두고 경합이 생길 수 있습니다.
• 인덱스 종류: 단일 컬럼과 복합 인덱스가 섞여 있습니다. 인덱스 개수만으로 다른 테이블의 비용을 예측할 수는 없습니다.

그리고 보조 인덱스가 없는 t0이 가장 빨랐다고 해서 운영 테이블의 인덱스를 없애자는 뜻은 아닙니다. 입력이 빨라진 만큼 주요 조회가 느려진다면 전체로는 손해입니다. 인덱스는 비용과 효과를 같이 놓고 판단해야 합니다.

인덱스 추가 요청을 받을 때 쓰는 확인표

이번 측정으로 인덱스 하나가 적재 시간과 공간에 얼마를 청구하는지 감을 잡았다면, 다음부터는 요청을 받을 때 그 청구서와 효과를 함께 적어 보는 것이 좋습니다. 아래 표는 요청자와 DBA가 함께 채우는 형식으로 쓸 수 있습니다.

확인 항목	적을 내용	왜 필요한가
대상 쿼리	인덱스가 필요한 SQL 원문과 현재 실행계획	인덱스 없이 쿼리 수정으로 해결되는지 먼저 판단
호출 빈도	초당 / 일일 실행 횟수, 실행 시간대	하루 한 번 배치와 초당 수십 번 화면 쿼리는 다른 결정
테이블 쓰기량	일일 INSERT·UPDATE·DELETE 건수, 대량 적재 여부	이번 실험의 입력 비용이 매일 얼마나 반복되는지
기존 인덱스와의 관계	같은 선두 컬럼을 가진 인덱스가 있는지	기존 인덱스 확장이나 대체로 개수를 늘리지 않을 수 있는지
예상 크기	인덱스 컬럼 폭 × 행 수	디스크와 버퍼 풀 공간 비용
효과 측정 방법	추가 전후 EXPLAIN ANALYZE, 적재 배치 시간	읽기 이득과 쓰기 비용을 같은 기준으로 비교
정리 계획	기능 종료 시 삭제 여부	안 쓰는 인덱스가 쌓이지 않게 하기 위함

대량 INSERT가 갑자기 느려졌다면 입력 SQL만 보지 말고, 최근에 그 테이블에 어떤 인덱스가 늘었는지부터 확인해 보시기 바랍니다. 이번 실험에서 같은 10만 건의 적재 시간을 두 배 넘게 바꾼 것은 SQL이 아니라 보조 인덱스 네 개였습니다.