인덱스를 걸었는데 왜 더 느려진 이유—읽기를 빠르게 하려다 쓰기를 느리게 만든 경험에서 인덱스의 양면을 정리한 글입니다.
인덱스는 어떻게 조회를 빠르게 하는가
인덱스를 처음 배울 때 가장 와닿았던 비유는 두꺼운 전공 책 맨 뒤의 색인이었습니다. 500쪽짜리 책에서 "트랜잭션"이라는 단어를 찾는다고 해 보죠. 1쪽부터 한 장씩 넘기며 눈으로 훑으면 언젠가는 찾겠지만, 그 사이 시간은 하염없이 흘러갑니다. 반면 책 뒤 색인에는 단어가 가나다순으로 정렬돼 있고, "트랜잭션 … 210, 233쪽"처럼 위치가 적혀 있습니다. 우리는 색인에서 단어를 빠르게 짚은 뒤 곧장 해당 쪽으로 넘어가죠. 데이터베이스 인덱스가 하는 일이 정확히 이것입니다.
인덱스 없이 조회하면 데이터베이스는 테이블의 모든 행을 처음부터 끝까지 읽습니다. 이걸 full table scan이라고 부르죠. 행이 100만 개면 100만 번을 훑어야 원하는 조건에 맞는 행을 골라낼 수 있습니다. 색인 없는 책을 1쪽부터 넘기는 것과 같습니다.
대부분의 관계형 데이터베이스는 인덱스를 B-tree라는 자료구조로 저장합니다. 핵심은 정렬입니다. 인덱스를 건 컬럼의 값이 트리 안에 정렬된 상태로 유지되기 때문에, 특정 값을 찾을 때 전체를 훑지 않고 가지를 몇 번 타고 내려가면 됩니다. 100만 건이라도 뿌리에서 잎까지의 깊이는 서너 단계에 불과하죠. 비교 횟수가 100만 번에서 수십 번 수준으로 줄어드는 셈입니다.
조금 더 구체적으로 그려 보면, WHERE user_id = 42 같은 조건이 들어왔을 때 데이터베이스는 트리의 뿌리에서 시작해 "42가 이 값보다 큰가 작은가"를 물으며 아래로 내려갑니다. 한 단계 내려갈 때마다 후보 범위가 절반 아래로 뚝뚝 줄어들죠. 그래서 데이터가 아무리 많아져도 탐색 비용은 완만하게만 늘어납니다. 반면 full table scan은 데이터가 두 배가 되면 시간도 정직하게 두 배가 됩니다. 이 차이가 수백만 건 규모에서는 800ms와 30ms처럼 체감으로 확 벌어지죠.
정렬돼 있다는 성질 덕분에 인덱스는 단순 일치 검색뿐 아니라 범위 검색(created_at > '2026-01-01')이나 정렬(ORDER BY)에도 힘을 발휘합니다. 이미 순서대로 놓여 있으니 데이터베이스가 따로 줄 세울 필요가 없으니까요. 여기까지만 보면 인덱스는 조회를 빠르게 해 주는 마법처럼 느껴집니다. 저도 한동안 "느리면 인덱스"라고 반사적으로 외웠죠. 문제는 이 마법에 청구서가 딸려 온다는 사실을 그때는 몰랐다는 데 있었습니다.
공짜가 아니다, 쓰기라는 비용
인덱스의 정렬은 저절로 유지되지 않습니다. 누군가 계속 정리해 줘야 하죠. 그 정리 작업이 바로 쓰기 시점에 일어납니다. 앞의 색인 비유로 돌아가면, 책에 새 문단을 끼워 넣을 때마다 뒤쪽 색인의 페이지 번호도 전부 손봐야 하는 것과 같습니다.
- INSERT — 새 행을 넣으면 그 행의 값을 인덱스의 정렬된 자리에 함께 꽂아야 합니다.
- UPDATE — 인덱스가 걸린 컬럼 값을 바꾸면, 인덱스에서 옛 자리를 지우고 새 자리에 다시 넣어야 하죠.
- DELETE — 행을 지우면 인덱스에서도 해당 항목을 걷어내야 합니다.
즉, 인덱스는 읽기를 위해 미리 만들어 두는 부가 자료구조이고, 원본 데이터가 바뀔 때마다 그 부가 자료구조도 같이 갱신돼야 합니다. 인덱스가 하나면 갱신도 한 벌이지만, 한 테이블에 인덱스를 다섯 개 걸어 두면 행 하나를 넣을 때 본체 한 번에 더해 인덱스 다섯 벌을 함께 손봐야 합니다. 쓰기 비용이 인덱스 개수에 비례해 늘어나는 구조죠.
저장 공간도 마찬가지입니다. 인덱스는 컬럼 값과 행 위치를 따로 복제해 들고 있으므로, 인덱스를 많이 걸수록 디스크 사용량이 눈에 띄게 불어납니다. 조회 몇 개를 빠르게 하려고 걸어 둔 인덱스가 테이블 본체만 한 용량을 차지하는 경우도 드물지 않습니다.
한 가지 더 짚고 싶은 점은, 이 쓰기 비용이 평소에는 잘 드러나지 않는다는 사실입니다. 행을 한 번에 하나씩 넣는 일반적인 트래픽에서는 인덱스 갱신 비용이 워낙 작아서 눈에 띄지 않으니까요. 그러다 수백만 건을 한꺼번에 밀어 넣는 배치나 대량 마이그레이션이 돌 때, 그동안 조용히 쌓여 있던 청구서가 한꺼번에 날아옵니다. 저 역시 이 지점을 놓쳐서 사고를 겪었죠.
결국 인덱스는 읽기 이득과 쓰기 손해를 맞바꾸는 트레이드오프입니다. 읽기가 압도적으로 많고 쓰기가 드문 테이블이라면 인덱스를 넉넉히 걸어도 남는 장사죠. 반대로 쓰기가 잦은 테이블에 인덱스를 무분별하게 걸면, 조회 몇 번 아끼려다 훨씬 잦은 쓰기 전부를 무겁게 만드는 손해를 볼 수 있습니다. 저는 이 저울의 반대쪽 접시를 제대로 보지 못한 채, 조회 속도만 보고 인덱스를 늘렸다가 크게 데었습니다.
인덱스가 오히려 독이 되는 순간
사고는 재작년 정산 시스템에서 터졌습니다. 거래 내역을 담는 transactions 테이블이 있었는데, 특정 사용자의 내역을 조회하는 화면이 눈에 띄게 느렸습니다. user_id로 거르는 쿼리가 full table scan을 타고 있었죠. 저는 별생각 없이 user_id에 인덱스를 걸었고, 조회 응답은 800ms에서 30ms로 떨어졌습니다. 여기까지는 성공이었죠.
문제는 그날 밤에 드러났습니다. 매일 새벽 2시에 하루치 거래 수백만 건을 적재하는 야간 배치가 있었는데, 인덱스를 건 다음 날 이 배치의 실행 시간이 평소 12분에서 40분 넘게로 늘어난 겁니다. 처음엔 데이터가 갑자기 늘었나 의심했지만, 건수는 그대로였습니다. 범인은 제가 낮에 추가한 인덱스였죠. 대량 배치 INSERT는 행 하나하나마다 인덱스를 정렬된 자리에 끼워 넣어야 했고, 그 비용이 수백만 번 누적된 것이었습니다.
원인을 좁힌 결정적 도구는 EXPLAIN이었습니다. 실행 계획을 열어 보니, 제가 좋다고 믿었던 인덱스 중 상당수가 실제 조회에서는 쓰이지도 않았습니다.
EXPLAIN SELECT * FROM transactions WHERE status = 'DONE';
-- type: ALL (인덱스가 있는데도 풀스캔)
-- possible_keys: idx_status / key: NULLstatus 컬럼에는 PENDING, DONE 정도의 몇 가지 값밖에 없었습니다. 이렇게 카디널리티(고유값 종류)가 낮은 컬럼은 인덱스로 걸러 봐야 여전히 테이블의 절반이 남습니다. 옵티마이저는 "어차피 대부분을 읽을 거면 인덱스로 돌아가느니 그냥 전체를 훑는 게 낫다"고 판단해 인덱스를 버렸죠. 상태 플래그 같은 컬럼의 인덱스는 자리만 차지하고 쓰기만 느리게 하는, 딱 무용지물이었습니다.
여기서 한 가지 짚어 둘 점은, 옵티마이저가 인덱스를 버리는 판단 자체는 대체로 옳다는 사실입니다. 전체의 절반을 읽어야 하는 상황이라면 인덱스를 거쳐 본체를 오가는 비용이 오히려 더 크니까요. 그래서 문제는 옵티마이저가 아니라, 그런 컬럼에 인덱스를 걸어 둔 저였습니다. 읽기에는 안 쓰이면서 쓰기만 무겁게 하는 인덱스가 테이블에 몇 개나 남아 있었던 셈이죠.
복합 인덱스의 컬럼 순서도 발목을 잡았습니다. (status, user_id) 순서로 묶은 인덱스는 user_id 단독 조회에는 거의 쓸모가 없었습니다. B-tree 복합 인덱스는 앞 컬럼부터 순서대로 타는 성질이 있어서, 앞의 status를 건너뛰고 뒤의 user_id만으로는 정렬의 이점을 살리지 못하니까요. 순서를 (user_id, status)로 뒤집고 나서야 조회가 인덱스를 제대로 탔습니다.
정리한 방향은 이랬습니다.
| 조치 | 결과 |
|---|---|
카디널리티 낮은 status 단독 인덱스 제거 |
배치 INSERT 부담 감소 |
| 안 쓰이는 중복 인덱스 정리 | 저장 공간·쓰기 비용 절감 |
| 복합 인덱스 컬럼 순서 재배치 | 조회는 그대로 빠르게 유지 |
불필요한 인덱스를 걷어내자 야간 배치는 다시 15분 안쪽으로 돌아왔고, 정작 필요했던 user_id 조회 속도는 그대로였습니다. 개인적으로 이 사고가 남긴 교훈은 분명합니다. 인덱스는 "걸면 빨라지는 것"이 아니라 읽기와 쓰기 사이의 저울이라는 것이죠. 지금의 저는 인덱스를 추가하기 전에 반드시 EXPLAIN으로 실제로 타는지 확인하고, 그 컬럼의 카디널리티와 그 테이블의 쓰기 빈도를 함께 저울에 올려 봅니다. 읽기만 보고 인덱스를 늘리던 예전 습관은, 그 새벽의 40분이 확실히 고쳐 주었습니다.