데이터베이스 인덱싱의 모든 것: 실무에서 겪은 성능 최적화 경험담

데이터베이스 인덱싱의 모든 것: 실무에서 겪은 성능 최적화 경험담

내가 처음 데이터베이스를 제대로 마주한 건, 한 중견 기업의 백엔드 개발자로 입사한 첫날이었다. 면접에서는 "MySQL 기본기 정도는 있습니다"라고 자신 있게 말했지만, 막상 실무에 들어가니 내가 알고 있던 건 빙산의 일각에 불과했다. 그 회사의 핵심 서비스는 하루에 수백만 건의 트랜잭션을 처리하는 주문 시스템이었고, 나는 이 시스템의 성능 개선 프로젝트에 투입됐다. 당시만 해도 "인덱스 걸면 빨라진다"는 막연한 수준의 지식しか 없었는데, 이 프로젝트를 통해 인덱싱의 세계에 제대로 빠져들게 됐다.

이 글에서는 내가 실제 프로젝트에서 겪었던 인덱스 관련 삽질과 깨달음, 그리고 성능 최적화를 위해 적용했던 방법론을 솔직하게 풀어보려고 한다. 인덱스의 개념부터 시작해서 실전 튜닝, 그리고 흔히 하는 실수까지 다룰 예정이다. 이론서에서 보던 딱딱한 내용보다는, 실제 현장에서 부딪히며 배운 내용 위주로 써보겠다.

인덱스의 기본 개념과 내가 처음 겪은 충격

학교 다닐 때 배운 인덱스의 개념은 간단했다. "책 뒷부분에 있는 찾아보기(index)처럼, 데이터를 빠르게 찾기 위한 자료구조"라는 설명이 전부였다. B-Tree라는 용어도 배웠지만, "아, 그냥 이진 트리 비슷한 거구나" 정도로 넘어갔던 기억이 난다. 막상 실무에서 마주친 현실은 달랐다.

내가 처음 맡은 작업은 주문 목록 조회 API의 응답 속도를 개선하는 거였다. 기존 API는 단순히 SELECT * FROM orders ORDER BY created_at DESC LIMIT 50 이라는 쿼리였는데, 주문 테이블에 데이터가 500만 건 정도 쌓이면서 응답 시간이 3초 이상으로 늘어난 상태였다. 당시 나는 "아, created_at에 인덱스 걸면 되겠네"라고 생각하고 바로 인덱스를 생성했다. 그런데 결과는? 3초에서 2.5초로 약간 빨라졌을 뿐, 기대했던 드라마틱한 개선은 없었다.

여기서 내가 처음 알게 된 사실은 "인덱스를 걸었다고 무조건 빨라지는 게 아니다"라는 점이었다. explain 명령어로 실행 계획을 확인해보니, 옵티마이저가 인덱스를 타긴 하는데, 여전히 많은 양의 데이터를 읽고 있었다. 이유는 ORDER BY에 사용된 created_at 인덱스로 정렬은 빨라졌지만, SELECT * 때문에 인덱스에 없는 컬럼들을 읽기 위해 추가적인 테이블 액세스(Random I/O)가 발생하고 있었기 때문이다. 즉, 인덱스로 정렬된 결과의 각 row마다 디스크에서 해당 데이터 블록을 찾아가야 했던 거다. 이걸 "인덱스 룩업"이라고 부른다는 걸 나중에 알았다.

인덱스의 핵심 원리

인덱스는 데이터 파일과 별도로 존재하는 자료구조로, 특정 컬럼의 값과 해당 값이 저장된 위치(ROWID)를 매핑한다. B-Tree 인덱스의 경우 균형 잡힌 트리 구조를 통해 어떤 값이든 O(log N) 시간에 찾을 수 있다. 하지만 인덱스만으로 모든 문제를 해결할 수는 없다. 인덱스를 통해 찾은 row에 접근하는 과정에서 추가 I/O가 발생할 수 있기 때문이다.

복합 인덱스의 중요성: WHERE 절의 조합을 고려하라

이 경험 이후로 나는 본격적으로 인덱스 공부를 시작했다. 그러던 중 두 번째 난관에 부딪혔다. 이번에는 주문 검색 기능이었다. 사용자가 날짜 범위, 주문 상태, 상품 카테고리 등을 조합해서 검색하는 기능이었는데, 조건이 많아질수록 응답 속도가 기하급수적으로 느려졌다.

당시 테이블에는 각 컬럼마다 단일 인덱스가 각각 걸려 있었다. created_at, status, category_id 각각에 인덱스가 있었다. 나는 "각 컬럼에 인덱스가 있으니 잘 동작하겠지"라고 생각했다. 하지만 옵티마이저는 여러 개의 단일 인덱스 중 가장 효율적인 인덱스 하나만 선택해서 사용하거나, 인덱스 병합(Index Merge)이라는 방식을 시도했다. 인덱스 병합은 각 인덱스로 검색한 결과를 메모리에서 교집합/합집합 연산하는 방식인데, 생각보다 효율적이지 않았다.

이 문제의 해결책은 복합 인덱스(Composite Index)였다. 여러 컬럼을 조합해서 하나의 인덱스로 만드는 방식이다. 여기서 중요한 건 컬럼의 순서였다. "선행 컬럼의 카디널리티(Cardinality, 값의 중복도)가 높은 순서로 배치하라"는 원칙을 배웠다. 중복도가 낮은, 즉 값의 종류가 다양한 컬럼을 앞에 두는 게 효과적이다. 예를 들어, status 컬럼은 값이 'WAIT', 'COMPLETE', 'CANCEL' 등 몇 개 안 되지만, category_id는 수백 개의 값을 가질 수 있다. 따라서 category_id를 앞에 두고 status를 뒤에 배치하는 게 더 효율적이다.

직접 실험해보니 확실히 차이가 컸다. 단일 인덱스만 사용했을 때 4~5초 걸리던 검색이, 적절한 복합 인덱스를 적용하니 0.2초로 줄어들었다. 거의 20배 이상의 성능 향상이었다. 이때 느꼈다. "아, 인덱스는 그냥 걸린다고 되는 게 아니라, 쿼리 패턴을 분석해서 전략적으로 설계해야 하는구나."

커버링 인덱스와 Include 컬럼의 활용

세 번째 깨달음은 커버링 인덱스(Covering Index)였다. 앞서 말한 SELECT * 문제를 해결하는 가장 좋은 방법은, 인덱스만으로 모든 데이터를 처리할 수 있게 만드는 거다. 즉, SELECT 절에 사용되는 모든 컬럼을 인덱스에 포함시키는 방식이다. 이렇게 하면 테이블에 접근하지 않고 인덱스 스캔만으로 쿼리가 완료되기 때문에 성능이 크게 향상된다.

실제 사례를 하나 들자면, 대시보드 화면에서 "오늘의 주문 통계"를 보여주는 쿼리가 있었다. SELECT COUNT(*), SUM(total_amount), status FROM orders WHERE created_at BETWEEN '2026-07-21 00:00:00' AND '2026-07-21 23:59:59' GROUP BY status 이런 형태였다. 여기에 (created_at, status, total_amount) 순서의 복합 인덱스를 만들어주니, 실행 계획에서 "Using index"라고 표시되면서 테이블 액세스가 완전히 사라졌다. 속도는 1.2초에서 0.03초로 줄었다. 이 정도면 사용자가 체감할 수 있는 수준의 개선이었다.

MySQL 8.0부터는 인덱스에 Include 절을 사용할 수 있다. SQL Server에서는 이미 오래전부터 지원하던 기능인데, MySQL도 뒤늦게 따라잡았다. 기본 키나 인덱스 키 컬럼이 아닌, SELECT 절에서만 필요한 컬럼을 인덱스에 포함시키는 기능이다. 예를 들어, CREATE INDEX idx_orders_created ON orders(created_at) INCLUDE (total_amount, status) 이런 식이다. 인덱스 키 정렬에는 영향을 주지 않으면서, 테이블 액세스를 줄일 수 있어서 실무에서 꽤 유용하게 쓰고 있다.

인덱스 설계 시 흔히 하는 실수들

인덱스 설계를 하면서 내가 저지른 실수들을 공유해보려고 한다. 첫 번째는 "인덱스를 너무 많이 생성하는 것"이다. 초보 개발자일 때 나는 조회 성능을 높이겠다는 일념 하나로 WHERE 절에 사용되는 모든 컬럼 조합에 인덱스를 만들어댔다. 한 테이블에 인덱스가 15개까지 늘어난 적도 있다. 결과는 참혹했다. INSERT, UPDATE, DELETE의 성능이 크게 저하됐다. 인덱스는 쓰기 작업마다 함께 업데이트되어야 하기 때문에, 인덱스가 많을수록 쓰기 부하가 커진다. 또한 디스크 공간도 많이 차지했다. 지금은 "조회 패턴을 분석해서 꼭 필요한 인덱스만 최소한으로 유지한다"는 원칙을 지키고 있다.

두 번째 실수는 LIKE 검색에 인덱스를 제대로 활용하지 못한 거다. WHERE name LIKE '%keyword%' 이런 패턴은 인덱스를 탈 수 없다는 걸 처음에는 몰랐다. 와일드카드가 문자열 앞에 붙으면 B-Tree 인덱스의 특성상 검색이 불가능하기 때문이다. WHERE name LIKE 'keyword%'처럼 와일드카드를 뒤에만 붙여야 인덱스가 동작한다. 만약 앞부분 검색이 꼭 필요하다면, 전문 검색(Full-Text Search)이나 Elasticsearch 같은 별도 검색 엔진을 도입하는 게 맞다. 이걸 모르고 무턱대고 인덱스만 걸어놓고 "왜 안 빨라지지?"라고 고민했던 기억이 난다.

세 번째 실수는 NULL 값 처리를 간과한 거다. MySQL에서 NULL은 인덱스의 맨 앞이나 맨 뒤에 저장되는데, IS NULL이나 IS NOT NULL 조건을 사용할 때 인덱스 활용이 예상과 다를 수 있다. 특히 복합 인덱스에서 NULL 허용 컬럼을 앞에 두면, NULL이 아닌 값을 찾기 위해 불필요한 스캔이 발생할 수 있다. 가능하다면 NOT NULL 제약 조건을 걸고, NULL이 꼭 필요한 경우에는 별도의 기본값(default value)을 설정하는 게 인덱스 효율에 도움이 된다.

실행 계획 분석: EXPLAIN만 잘 봐도 반은 간다

내가 인덱스 튜닝에서 가장 중요하게 생각하는 건 실행 계획(Execution Plan) 분석이다. MySQL 기준으로 EXPLAIN SELECT ... 명령어를 실행하면 옵티마이저가 어떤 방식으로 쿼리를 실행할지 보여준다. 여기서 눈여겨봐야 할 컬럼이 몇 개 있다.

type 컬럼은 테이블 액세스 방식을 알려준다. const나 eq_ref가 가장 좋고, ref, range 정도면 괜찮다. 하지만 ALL(풀 테이블 스캔)이나 index(풀 인덱스 스캔)이 나오면 인덱스 설계에 문제가 있을 가능성이 높다. Extra 컬럼도 중요하다. "Using index"는 커버링 인덱스가 잘 동작하고 있다는 신호고, "Using filesort"는 정렬이 인덱스를 사용하지 못하고 있다는 뜻이다. "Using temporary"는 임시 테이블을 사용했다는 건데, GROUP BY나 DISTINCT에서 자주 발생하므로 주의가 필요하다.

내가 즐겨 쓰는 방법은 EXPLAIN ANALYZE(MySQL 8.0.18 이상)다. 실제 쿼리를 실행하면서 각 단계별 소요 시간과 처리 건수를 보여주기 때문에, 병목 지점을 정확히 찾을 수 있다. EXPLAIN ANALYZE SELECT ...로 실행하면 "어디에서 시간이 오래 걸리는지"가 한눈에 보인다. 이걸 보면서 "아, 여기서 테이블 액세스가 많이 발생하는구나" 하고 인덱스를 수정하는 식으로 튜닝한다. 사실 이 기능을 알기 전에는 감으로 때려맞추는 경우가 많았는데, EXPLAIN ANALYZE를 쓰고 나서는 튜닝이 훨씬 체계적으로 변했다.

인덱스 유지보수와 모니터링

인덱스를 한 번 만들었다고 끝이 아니다. 시간이 지나면서 데이터가 변경되고, 인덱스도 조각화(Fragmentation)가 발생한다. 인덱스 페이지에 빈 공간이 생기거나, 논리적 순서와 물리적 순서가 달라지면서 성능이 저하될 수 있다. MySQL에서는 OPTIMIZE TABLE 명령어로 인덱스를 재구성할 수 있다. 다만, 이 명령어는 테이블 락이 걸리기 때문에 서비스 시간에는 실행하지 않는 게 좋다. 나는 보통 새벽 시간에 배치 작업으로 한 달에 한 번 정도 인덱스 재구성을 실행한다.

또한 사용되지 않는 인덱스도 주기적으로 정리해야 한다. MySQL은 performance_schema를 통해 인덱스 사용 현황을 추적할 수 있다. SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage를 조회하면 각 인덱스별로 얼마나 사용되었는지 통계를 볼 수 있다. 사용 횟수가 현저히 적은 인덱스는 삭제 대상이다. 실제로 이 쿼리로 분석해보니, 예전에 급하게 만들었다가 잊어버린 인덱스들이 꽤 있었다. 삭제하고 나니 쓰기 성능이 소폭 개선되는 걸 확인할 수 있었다.

슬로우 쿼리 로그(Slow Query Log)도 놓치지 말아야 할 모니터링 포인트다. long_query_time을 1초(또는 0.5초)로 설정해두고, 슬로우 쿼리가 발생하면 주기적으로 검토한다. 슬로우 쿼리 중 상당수는 인덱스 문제가 원인인 경우가 많다. 나는 매주 슬로우 쿼리 로그를 리뷰하면서 인덱스 개선이 필요한 쿼리를 찾아내고, 우선순위를 정해서 튜닝을 진행한다. 이게 쌓이고 쌓이면 서비스 전체의 성능이 꾸준히 개선되는 걸 체감할 수 있다.

파티셔닝과 인덱스의 관계

테이블이 너무 커지면 아무리 인덱스를 잘 설계해도 한계가 있다. 내가 경험한 사례를 하나 들자면, 로그 테이블이 1억 건을 넘어가면서 인덱스만 10GB를 차지하는 상황이 벌어졌다. 인덱스 크기가 커지면 B-Tree의 깊이가 깊어지고, 그만큼 디스크 I/O가 증가한다. 이때 고려해볼 수 있는 게 파티셔닝(Partitioning)이다.

레인지 파티셔닝을 월 단위로 적용하고, 각 파티션에 인덱스를 생성했다. 쿼리 조건에 created_at이 항상 포함되도록 애플리케이션을 수정했고, 옵티마이저가 자동으로 해당 파티션만 스캔하도록 파티션 프루닝(Partition Pruning)이 동작하게 만들었다. 결과적으로 인덱스 크기가 각 파티션별로 나뉘면서 B-Tree 깊이가 얕아졌고, 전체적인 조회 성능이 2~3배 정도 개선됐다. 다만 파티셔닝이 항상 정답은 아니다. 파티션 키를 잘못 선택하면 오히려 성능이 나빠질 수 있고, 파티션 간 데이터 분포가 균일하지 않으면 특정 파티션에만 부하가 몰릴 수 있다. 파티셔닝 도입 전에는 반드시 데이터 분포와 쿼리 패턴을 충분히 분석해야 한다.

내가 생각하는 인덱스 설계의 핵심 원칙

여러 프로젝트를 경험하면서 정리한 나만의 인덱스 설계 원칙을 공유해보겠다. 첫째, "모니터링 없이 인덱스를 만들지 말라"는 원칙이다. 인덱스를 추가하기 전에 반드시 explain으로 실행 계획을 확인하고, 인덱스 추가 후에도 성능 변화를 측정해야 한다. 둘째, "인덱스는 쿼리 패턴에 기반해야 한다"는 점이다. 테이블 구조만 보고 인덱스를 설계하지 말고, 실제 운영 환경에서 실행되는 쿼리들의 WHERE 절, JOIN 조건, ORDER BY, GROUP BY를 분석한 후에 인덱스를 설계해야 한다. 셋째, "적을수록 좋다"는 원칙이다. 불필요한 인덱스는 쓰기 성능과 디스크 공간을 낭비한다. 필요한 인덱스만 최소한으로 유지하고, 정기적으로 사용 현황을 점검해야 한다.

마지막으로 하고 싶은 말은, 인덱스 최적화는 한 번에 완벽해지기 어렵다는 거다. 서비스가 성장하면서 데이터 분포가 변하고, 새로운 쿼리 패턴이 추가되면서 인덱스 설계도 계속 진화해야 한다. 나도 처음에는 "이렇게 인덱스 걸면 평생 문제없겠지"라고 생각했지만, 막상 1년 후에 보니 전혀 다른 패턴의 쿼리가 주를 이루고 있더라. 그래서 인덱스는 지속적인 관심과 관리가 필요한 영역이라고 생각한다.

인덱스 하나 제대로 걸지 못해서 밤새 서비스 장애를 겪고, explain 명령어를 수백 번은 실행해본 개발자로서, 데이터베이스 인덱싱은 단순한 기술이 아니라 하나의 철학에 가깝다는 생각이 든다. 데이터를 어떻게 저장하고, 어떻게 읽을 것인지에 대한 고민은 결국 서비스 전체의 설계 철학과 맞닿아 있다. 여러분도 지금 당장 느린 쿼리가 있다면 explain부터 때려보길 권한다. 거기서부터 모든 인사이트가 시작된다.