데이터베이스를 다루다 보면 "왜 이렇게 느리지?"라는 말을 한 번쯤은 하게 됩니다. 대부분의 경우 원인은 같습니다. 바로 인덱스 전략이 제대로 잡혀있지 않기 때문이죠. 저도 실무에서 수억 건의 데이터를 다루면서 쿼리 성능 때문에 밤을 새운 적이 한두 번이 아닙니다. 오늘은 그 경험을 바탕으로 SQL 최적화의 핵심인 인덱스 전략을 솔직하게 정리해보겠습니다.
1. 인덱스의 기본 원리와 동작 방식
1-1. 인덱스가 왜 필요한가
인덱스는 책의 목차와 같습니다. 10만 페이지짜리 책에서 특정 내용을 찾으려면 처음부터 끝까지 넘기는 것보다 목차를 보는 게 훨씬 빠르겠죠. 데이터베이스도 마찬가지입니다. 테이블에 100만 건의 데이터가 있을 때, 인덱스 없이 조회하면 Full Table Scan이 발생합니다. 모든 행을 하나하나 확인하는 거죠. 반면 적절한 인덱스가 있으면 B-Tree 구조를 통해 O(log N) 수준으로 원하는 데이터를 찾아갑니다. 실제로 1,000만 건 테이블에서 인덱스 하나 추가했더니 8초 걸리던 쿼리가 0.02초로 줄어든 경험이 있습니다. 이것이 SQL 최적화의 출발점입니다.
1-2. B-Tree vs Hash 인덱스
가장 널리 쓰이는 인덱스 구조는 B-Tree입니다. 범위 검색(BETWEEN, >, <)과 정렬(ORDER BY)에 모두 활용할 수 있어서 범용성이 뛰어납니다. Hash 인덱스는 등호(=) 비교에서는 O(1)로 매우 빠르지만, 범위 검색이나 정렬에는 전혀 쓸 수 없습니다. MySQL InnoDB 기준으로 기본 인덱스는 모두 B-Tree이고, Memory 엔진에서만 Hash를 선택할 수 있습니다. PostgreSQL은 Hash 인덱스도 WAL 지원이 되지만, 실무에서는 여전히 B-Tree를 기본으로 쓰는 경우가 대부분입니다. 특별한 이유가 없다면 B-Tree로 시작하세요.
1-3. 클러스터드 vs 논클러스터드 인덱스
클러스터드 인덱스는 테이블의 물리적 정렬 순서 자체를 결정합니다. InnoDB에서는 Primary Key가 곧 클러스터드 인덱스입니다. 그래서 PK로 조회하면 추가적인 디스크 접근 없이 바로 데이터를 가져올 수 있어 가장 빠릅니다. 논클러스터드(세컨더리) 인덱스는 별도의 인덱스 트리를 만들고, 리프 노드에 PK 값을 저장합니다. 그래서 세컨더리 인덱스로 조회하면 인덱스 트리를 탄 다음, 다시 PK로 실제 데이터를 찾아가는 과정이 필요합니다. 이걸 "북마크 룩업" 또는 "테이블 랜덤 I/O"라고 하는데, 대량의 행을 조회할 때는 이 비용이 상당합니다.
2. 실전 인덱스 설계 전략
2-1. 복합 인덱스와 컬럼 순서의 비밀
복합 인덱스(Composite Index)를 만들 때 컬럼 순서가 성능을 좌우합니다. 핵심 원칙은 "가장 왼쪽 접두사 규칙(Leftmost Prefix Rule)"입니다. 예를 들어 INDEX(city, age, name)이라는 인덱스가 있다면, WHERE city = '서울'은 인덱스를 탑니다. WHERE city = '서울' AND age = 30도 탑니다. 하지만 WHERE age = 30만 단독으로 쓰면 이 인덱스를 활용하지 못합니다. 실무에서 자주 하는 실수가 바로 이겁니다. 복합 인덱스를 만들어놨는데 정작 쿼리에서 첫 번째 컬럼을 빠뜨리는 경우죠.
컬럼 순서를 정할 때는 다음 기준을 따르세요. 첫째, 등호(=) 조건에 쓰이는 컬럼을 앞에 배치합니다. 둘째, 범위 조건(BETWEEN, >, <)에 쓰이는 컬럼은 뒤에 놓습니다. 범위 조건 이후의 컬럼은 인덱스를 타지 못하기 때문입니다. 셋째, 카디널리티(고유 값의 수)가 높은 컬럼을 앞에 놓는 것이 일반적으로 유리합니다.
2-2. 커버링 인덱스로 극한의 성능 끌어내기
커버링 인덱스(Covering Index)란 쿼리가 필요로 하는 모든 컬럼이 인덱스 안에 포함되어 있어서, 테이블 데이터에 접근하지 않고 인덱스만으로 결과를 반환하는 것을 말합니다. EXPLAIN 결과에서 Extra 컬럼에 "Using index"가 표시되면 커버링 인덱스가 동작하고 있는 겁니다.
예를 들어 SELECT user_id, created_at FROM orders WHERE status = 'paid' ORDER BY created_at DESC라는 쿼리가 있다면, INDEX(status, created_at, user_id)를 만들면 커버링 인덱스가 됩니다. 테이블까지 갈 필요 없이 인덱스 트리에서 모든 걸 해결하니까 I/O가 극적으로 줄어듭니다. 수천만 건 테이블에서 이 기법 하나로 쿼리 성능이 10배 이상 좋아지는 경우를 여러 번 경험했습니다.
2-3. 부분 인덱스와 함수 기반 인덱스
PostgreSQL을 쓴다면 부분 인덱스(Partial Index)를 적극 활용해보세요. 전체 데이터가 아닌 특정 조건을 만족하는 행에만 인덱스를 거는 겁니다. 예를 들어 CREATE INDEX idx_active_users ON users(email) WHERE is_deleted = false처럼 만들면, 삭제되지 않은 사용자만 인덱싱하므로 인덱스 크기가 작아지고 쓰기 성능에 미치는 영향도 줄어듭니다. MySQL 8.0에서는 함수 기반 인덱스를 지원합니다. CREATE INDEX idx_lower_email ON users((LOWER(email)))처럼 표현식에 인덱스를 걸 수 있어서, WHERE LOWER(email) = 'test@example.com' 같은 쿼리도 인덱스를 탈 수 있게 되었습니다.
3. 쿼리 성능을 망치는 안티패턴
3-1. 인덱스를 무력화하는 흔한 실수들
인덱스를 아무리 잘 만들어도, 쿼리를 잘못 작성하면 무용지물이 됩니다. 대표적인 안티패턴을 정리하면 다음과 같습니다.
- 인덱스 컬럼에 함수 적용: WHERE YEAR(created_at) = 2024 이렇게 쓰면 created_at 인덱스를 못 탑니다. WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'로 바꿔야 합니다.
- 암묵적 타입 변환: phone 컬럼이 VARCHAR인데 WHERE phone = 01012345678처럼 숫자로 비교하면 내부적으로 타입 캐스팅이 일어나 인덱스를 타지 못합니다.
- LIKE의 앞쪽 와일드카드: WHERE name LIKE '%홍길%'은 인덱스를 사용할 수 없습니다. WHERE name LIKE '홍길%'은 가능합니다. 앞쪽 와일드카드가 필요하면 Full-Text 인덱스나 별도 검색 엔진을 고려하세요.
- OR 조건의 남용: WHERE city = '서울' OR age = 30은 각각 별도의 인덱스가 있어도 효율적으로 처리하기 어렵습니다. UNION ALL로 분리하는 것이 나을 수 있습니다.
- 불필요한 SELECT *: 필요한 컬럼만 명시해야 커버링 인덱스의 혜택을 받을 수 있습니다.
3-2. EXPLAIN으로 실행 계획 읽는 법
쿼리 앞에 EXPLAIN을 붙이면 데이터베이스가 해당 쿼리를 어떻게 실행할지 계획을 보여줍니다. MySQL 기준으로 핵심적으로 봐야 할 항목은 type, key, rows, Extra입니다. type이 ALL이면 풀 스캔이니 즉시 개선이 필요합니다. ref나 range 정도면 인덱스를 잘 타고 있다는 뜻입니다. const는 PK나 유니크 인덱스를 통한 단건 조회로 가장 이상적입니다. rows는 예상 검색 행 수인데, 실제 결과 행 수 대비 이 값이 지나치게 크면 인덱스 설계를 재검토해야 합니다. PostgreSQL이라면 EXPLAIN ANALYZE를 사용해서 실제 실행 시간과 함께 확인하는 것을 강력히 추천합니다.
4. 인덱스 유형별 장단점 비교
4-1. 주요 인덱스 비교표
| 인덱스 유형 | 장점 | 단점 | 적합한 상황 |
|---|---|---|---|
| B-Tree (기본) | 범위 검색, 정렬, 등호 비교 모두 지원. 범용성 최고 | 쓰기 시 오버헤드 발생. 인덱스 크기가 클 수 있음 | 대부분의 일반적인 쿼리 패턴 |
| Hash | 등호 비교 시 O(1) 성능으로 매우 빠름 | 범위 검색, 정렬 불가. 지원 엔진 제한적 | 정확히 일치하는 값만 조회하는 경우 |
| 복합 인덱스 | 여러 컬럼 조건을 한 번에 처리. 커버링 인덱스 활용 가능 | 컬럼 순서에 민감. 왼쪽 접두사 규칙을 지켜야 함 | 다중 조건 WHERE절이 빈번한 쿼리 |
| 부분 인덱스 | 인덱스 크기 절감. 쓰기 부담 최소화 | PostgreSQL 등 일부 DBMS만 지원 | 특정 조건의 데이터만 자주 조회하는 경우 |
| Full-Text | 자연어 검색, 앞쪽 와일드카드 검색 가능 | 설정 복잡. 한국어는 별도 형태소 분석기 필요 | 텍스트 검색, 게시글 본문 검색 |
| GIN/GiST (PostgreSQL) | JSON, 배열, 지리 데이터 등 복합 타입에 강력 | 쓰기 성능 저하. 인덱스 크기가 큼 | JSONB 필드 검색, 위치 기반 서비스 |
4-2. 인덱스 수의 트레이드오프
인덱스는 읽기 성능을 높이지만, 쓰기(INSERT, UPDATE, DELETE) 성능은 떨어뜨립니다. 인덱스가 하나 추가될 때마다 데이터 변경 시 인덱스 트리도 함께 갱신해야 하기 때문입니다. 제 경험상 OLTP 시스템에서 한 테이블에 인덱스가 7~8개를 넘어가면 쓰기 성능 저하가 체감되기 시작합니다. 읽기 위주의 시스템이라면 인덱스를 넉넉하게, 쓰기가 많은 시스템이라면 꼭 필요한 것만 최소화하는 것이 원칙입니다. 사용하지 않는 인덱스는 정기적으로 확인하고 제거해주세요. MySQL에서는 sys.schema_unused_indexes 뷰로, PostgreSQL에서는 pg_stat_user_indexes의 idx_scan 값으로 확인할 수 있습니다.
5. 실무에서 바로 쓰는 최적화 팁
5-1. 당장 적용할 수 있는 팁 5가지
- 팁 1 - 슬로우 쿼리 로그를 켜세요: MySQL이라면 slow_query_log를 활성화하고 long_query_time을 1초로 설정하세요. 어떤 쿼리가 느린지 모르면 최적화를 시작할 수도 없습니다. 이게 SQL 최적화의 첫걸음입니다.
- 팁 2 - 페이지네이션에 OFFSET을 쓰지 마세요: OFFSET 100000, 20 같은 쿼리는 100,020건을 읽고 100,000건을 버립니다. 대신 WHERE id > 마지막_id ORDER BY id LIMIT 20 방식의 커서 기반 페이지네이션을 사용하세요. 수백만 건 테이블에서 체감 차이가 엄청납니다.
- 팁 3 - COUNT(*)보다 EXISTS를 사용하세요: "데이터가 있는지" 확인하는 용도라면 COUNT(*)로 전체 개수를 세지 말고 EXISTS를 쓰세요. EXISTS는 한 건만 찾으면 즉시 멈추기 때문에 훨씬 효율적입니다.
- 팁 4 - 정기적으로 인덱스 통계를 갱신하세요: 데이터가 대량으로 변경된 후에는 ANALYZE TABLE(MySQL) 또는 ANALYZE(PostgreSQL)를 실행해서 옵티마이저가 최신 통계를 기반으로 실행 계획을 세우도록 해주세요.
- 팁 5 - 배치 작업은 청크 단위로 나누세요: UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01'처럼 대량 업데이트를 한 번에 하면 락 경합과 리두 로그 폭발이 일어납니다. 1,000~10,000건 단위로 나누어 처리하고, 각 배치 사이에 짧은 슬립을 주는 것이 안전합니다.
5-2. 인덱스 모니터링 습관 만들기
인덱스를 한번 만들고 잊어버리면 안 됩니다. 서비스가 성장하면서 쿼리 패턴이 바뀌고, 데이터 분포도 달라집니다. 월 1회 정도는 다음 사항을 점검하는 습관을 들이세요. 사용되지 않는 인덱스는 없는지, 슬로우 쿼리 로그에 새로운 패턴이 등장했는지, 인덱스 크기가 비정상적으로 커진 테이블은 없는지 확인합니다. 이런 루틴이 쿼리 성능을 지속적으로 높은 수준으로 유지하는 비결입니다.
5-3. ORM 사용 시 주의할 점
Django, JPA, ActiveRecord 같은 ORM을 사용하면 편리하지만, 내부적으로 어떤 SQL이 생성되는지 반드시 확인해야 합니다. 특히 N+1 쿼리 문제는 ORM 환경에서 가장 흔한 성능 이슈입니다. ORM이 생성하는 SQL을 로깅해서 확인하고, 필요하다면 Raw SQL이나 쿼리 힌트를 활용하는 것도 좋은 인덱스 전략의 일부입니다.
이 글을 추천하는 대상: SQL을 기본적으로 작성할 줄 알지만 "왜 느린지"를 분석하는 데 어려움을 겪는 백엔드 개발자, 데이터 분석가, 그리고 운영 중인 서비스의 DB 성능을 개선해야 하는 DBA에게 이 가이드가 도움이 될 것입니다. 인덱스 전략은 한 번 익혀두면 어떤 프로젝트에서든 써먹을 수 있는 기본기입니다. 오늘 당장 EXPLAIN 한 번 찍어보는 것부터 시작해보세요.