인덱스 설계와 쿼리 튜닝

인덱스는 정렬이다

인덱스는 지정한 컬럼 값으로 미리 정렬해 둔 목차다. 관계형 DB의 기본 인덱스는 B+트리이고, 리프에는 정렬된 키와 실제 행의 위치(InnoDB는 기본 키)가 들어 있다. 자료 구조가 왜 B+트리인지는 DB 내부 구조: 쿼리 실행과 인덱스 자료 구조에 있다.

인덱스가 많을수록 쓰기·저장 공간 비용이 늘고, 옵티마이저가 엉뚱한 인덱스를 고를 여지도 커진다. 테이블당 인덱스는 실제 쿼리 패턴에 필요한 만큼만 둔다. 쓰기가 잦은 테이블에 인덱스를 아끼라는 말은 이 비용 때문이다.

쓰기 비용은 인덱스에 들어 있는 컬럼만큼만 생긴다. 인덱스에 넣지 않은 컬럼을 고치는 UPDATE는 보조 인덱스를 건드리지 않는다.

• InnoDB는 클러스터드 인덱스(행 자체)만 고친다. 보조 인덱스는 인덱스 키나 기본 키가 바뀔 때만 고친다.
• PostgreSQL은 UPDATE마다 새 튜플을 만들어 원래는 모든 인덱스에 새 항목이 필요하다. 인덱스 컬럼이 안 바뀌고 같은 페이지에 빈자리가 있으면 HOT 갱신으로 인덱스를 건드리지 않는다. 그래서 갱신이 잦은 테이블은 fillfactor를 낮춰 페이지에 여유를 둔다.
• INSERT·DELETE는 행 전체가 생기거나 사라지므로 모든 인덱스를 고친다.

유니크 인덱스

UNIQUE 제약을 걸면 DB는 그 컬럼에 유니크 인덱스를 만들어 중복을 검사한다. 성능 차이는 읽기와 쓰기에서 반대로 나타난다.

같음 조회가 몇 행에서 멈추는 차이는 작아서, 읽기 성능을 위해 UNIQUE를 붙이지는 않는다. 값이 실제로 유일해야 하면 붙이고, 그 대가로 쓰기 때 확인 비용을 낸다. 유니크 인덱스는 중복 검사를 위해 키 범위에 잠금을 거는 경우가 있어 동시 삽입에서 대기·교착이 생기기도 한다.

클러스터드 인덱스와 보조 인덱스

• 기본 키(primary key)에 만든 인덱스를 primary index, 나머지 컬럼의 인덱스를 secondary index라고도 한다. 기본 키는 유일하고 NULL이 없어 물리 정렬 기준으로 알맞다.
• MySQL InnoDB는 테이블 자체가 기본 키 순서의 B+트리(클러스터드 인덱스)다. 그래서 기본 키 없이 만든 테이블도 클러스터드 인덱스가 있다. 기본 키가 없으면 첫 번째 NOT NULL UNIQUE 인덱스를, 그것도 없으면 6바이트짜리 숨은 행 ID(GEN_CLUST_INDEX)를 만들어 쓴다. 숨은 행 ID는 인스턴스 전체가 공유하는 카운터에서 받으므로 경합이 생기고 쿼리에 쓸 수도 없다. MySQL 8.0.30부터는 sql_generate_invisible_primary_key를 켜면 기본 키 없는 테이블에 보이지 않는 자동 증가 컬럼(my_row_id)을 기본 키로 붙인다. 복제(행 기반)에서도 기본 키가 없으면 복제본이 행을 찾느라 느려지므로 늘 명시적인 기본 키를 둔다. 보조 인덱스 리프에는 기본 키 값이 들어 있어, 보조 인덱스 조회는 「보조 인덱스 → 기본 키 → 클러스터드 인덱스」 두 번 찾기가 된다. 기본 키가 길면 모든 보조 인덱스가 같이 커진다.
• PostgreSQL은 행을 힙(heap)에 순서 없이 쌓고 모든 인덱스가 행 위치(TID)를 가리키는 보조 인덱스다. CLUSTER 명령은 한 번 정렬해 줄 뿐 이후 삽입 순서는 유지하지 않는다. SQL Server는 클러스터드 인덱스를 직접 고른다. Oracle은 테이블마다 고른다. 기본인 힙 테이블은 PostgreSQL처럼 인덱스가 행 주소(ROWID)를 가리키고, 인덱스 구조 테이블(IOT, Index-Organized Table) 로 만들면 InnoDB처럼 기본 키 B-트리 안에 행이 들어간다. IOT는 기본 키로만 찾는 작은 행이나 기본 키 범위 조회가 잦은 테이블에 맞는다. 같은 스키마가 DBMS마다 다르게 동작하는 이유 중 하나다.
• 클러스터드 키 값이 바뀌면 행이 물리적으로 옮겨진다. 그래서 기본 키는 바뀌지 않고, 짧고, 단조 증가하는 값(자동 증가, 시간순 UUIDv7)이 좋다. 무작위 UUIDv4는 삽입마다 B+트리 중간 페이지를 쪼개(page split) 쓰기가 느려지고 인덱스가 부푼다(분산 고유 ID).
• 기본 키는 인덱스인가: 기본 키는 「유일하고 NULL이 없다」는 제약이고, 인덱스는 빨리 찾기 위한 자료 구조다. 모든 RDBMS는 기본 키 제약을 검사하려고 유니크 인덱스를 자동으로 만들므로 실제로는 늘 인덱스가 딸려 있다. InnoDB에서는 그 인덱스가 테이블 자체(클러스터드)이고, PostgreSQL에서는 보조 인덱스 하나다.
• 외래 키와 인덱스: 외래 키도 제약이다. 참조되는 부모 쪽 컬럼은 기본 키나 유니크여야 하므로 이미 인덱스가 있다. 참조하는 자식 쪽 컬럼의 인덱스는 DB마다 다르다. InnoDB는 외래 키 컬럼에 인덱스가 없으면 자동으로 만든다. PostgreSQL은 만들지 않으므로 직접 만들어야 한다. 이 인덱스가 없으면 부모 행을 지우거나 키를 바꿀 때마다 자식 테이블 전체를 훑어 참조를 확인하고, 그동안 잠금도 넓게 걸린다. 조인 컬럼이기도 하므로 대개 만든다.
• 삭제·갱신이 반복되면 인덱스 페이지에 빈 공간과 지운 표시가 쌓여 인덱스가 실제 데이터보다 커진다. MySQL은 OPTIMIZE TABLE, PostgreSQL은 VACUUM·REINDEX로 정리한다. Oracle은 ALTER INDEX 이름 REBUILD로 인덱스 전체를 다시 만들거나, COALESCE로 리프 블록의 조각난 빈 공간만 합친다.

밀집 인덱스와 희소 인덱스

인덱스 항목을 모든 행마다 두느냐, 일부 행에만 두느냐에 따른 구분이다.

그래서 희소 인덱스는 정렬된 파일 위의 기본 인덱스에서만 쓰이고, 보조 인덱스는 늘 밀집이다. 클러스터드 B+트리의 내부 노드는 아래 페이지마다 키 하나만 두는 희소 인덱스, 보조 인덱스의 리프는 행마다 항목이 있는 밀집 인덱스로 볼 수 있다. LSM 트리의 SSTable도 블록마다 키 하나를 둔 희소 인덱스로 파일 안을 찾는다(DB 내부 구조).

무엇을 인덱스로 잡나

카디널리티

카디널리티(cardinality) 는 컬럼 값이 얼마나 다양한가다. 성별처럼 값 종류가 적으면 낮고, 주민등록번호처럼 거의 겹치지 않으면 높다. 인덱스는 많이 걸러 낼수록 효과가 크므로, 컬럼 하나만 잡는다면 카디널리티가 높은 컬럼을 고른다. 조회 결과가 테이블 대부분이면 인덱스로 하나씩 찾아가는 것보다 풀 테이블 스캔이 빠를 수 있고, 옵티마이저도 그렇게 판단한다. 흔히 쓰는 어림값은 대부분의 조회가 테이블의 10~15% 이하를 읽을 때 인덱스가 이득이라는 것이다.

카디널리티(또는 그 역수인 분포도, 값 하나가 전체에서 차지하는 비율)만 보고 판단하면 틀린다. 기준은 실제로 자주 쓰는 조건값이 몇 행을 고르느냐다.

이처럼 값이 한쪽으로 쏠린 컬럼은 옵티마이저가 히스토그램이 있어야 제대로 판단한다(아래 「풀 스캔을 고르는 이유」). PostgreSQL에서는 WHERE 배송여부 = 'N'인 행만 담는 부분 인덱스(partial index)로 인덱스 크기도 줄인다.

후보 컬럼 고르기

인덱스 후보는 쿼리 하나가 아니라 쿼리 전체의 실행 빈도로 고른다. 조건절에 나오는 모든 컬럼에 인덱스를 만들면 쓰기가 무거워지므로, 하루 실행 횟수가 많은 쿼리의 조건 컬럼부터 잡고 여러 쿼리가 공유하는 컬럼은 복합 인덱스로 묶는다.

테이블의 성격에 따라 둘 수 있는 인덱스 수도 다르다.

복합 인덱스의 컬럼 순서

복합 인덱스 (a, b, c)는 a로 정렬하고, a가 같은 것끼리 b로, 다시 c로 정렬한다. 뒤 컬럼은 앞 컬럼 값이 같은 범위 안에서만 정렬돼 있다.

• MySQL 8.0.13부터는 스킵 스캔(skip scan) 으로 선행 컬럼의 고유값이 아주 적을 때 b 조건만으로도 인덱스를 쓸 수 있다. 예외적인 최적화이므로 설계는 선행 컬럼 기준으로 한다.
• WHERE 절에 조건을 쓰는 순서는 상관없다. 옵티마이저가 인덱스 순서에 맞춰 다시 배치한다. 중요한 것은 인덱스 컬럼이 조건에 들어 있는가다.
• 등호 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에 둔다. 범위 조건 뒤의 컬럼은 정렬을 이용하지 못한다.
• 키가 길면 한 페이지에 담기는 키가 줄어 트리가 높아진다. 긴 문자열은 접두사 인덱스나 해시 컬럼을 검토한다.

단일 인덱스는 컬럼의 카디널리티가, 복합 인덱스는 컬럼 순서가 성능을 가른다. 순서를 정하는 원칙은 다음과 같다.

SELECT * FROM 상품
WHERE 유형 = '식품' AND 상품명 = '우유' AND 제조일 BETWEEN '20250101' AND '20250331'
ORDER BY 제조일 DESC, 제조번호;
-- 인덱스: (유형, 상품명, 제조일, 제조번호)
--          거르기  거르기   거르기+정렬  정렬

정렬 컬럼까지 인덱스에 넣을지는 결과 행 수로 정한다. 결과가 적으면 (유형, 상품명)만 두고 가져온 뒤 정렬해도 싸고, 결과가 많아 정렬 비용이 인덱스 유지 비용보다 크면 정렬 컬럼까지 넣는다.

인덱스를 못 타게 만드는 조건

ORDER BY·GROUP BY와 커버링 인덱스

• ORDER BY: 인덱스 순서대로 읽으면 정렬 작업(filesort)이 사라진다. LIMIT이 작을 때 특히 유리하다. 반대로 WHERE에 맞는 행이 드물면 인덱스를 오래 훑어야 하므로, LIMIT이 크면 WHERE용 인덱스가 나을 수 있다.
• GROUP BY: 인덱스가 이미 정렬된 상태를 주므로 임시 테이블과 filesort가 사라진다.
• 인덱스가 없을 때: ORDER BY는 조건에 맞는 행을 모두 읽은 뒤 정렬 버퍼(MySQL sort_buffer_size, PostgreSQL work_mem)에서 정렬한다. 버퍼를 넘으면 조각별로 정렬해 디스크 임시 파일에 쓰고 병합하는 외부 정렬이 되어 느려진다. GROUP BY는 그룹 키를 담은 임시 테이블(해시)을 만들거나 정렬한 뒤 묶는다. LIMIT이 있어도 전체를 읽어야 상위 N개를 알 수 있다(상위 N개만 유지하는 힙 정렬로 메모리는 줄인다).

• WHERE 조건과 ORDER BY를 함께 만족하도록 복합 인덱스 순서를 짜면 테이블 접근과 정렬을 모두 줄인다.
• 커버링 인덱스(covering index): 쿼리에 필요한 컬럼이 모두 인덱스에 있으면 테이블 행을 읽지 않는다. 오프셋 페이지네이션을 빠르게 하는 데도 쓴다(API 페이지네이션).
• 정렬 방향: B+트리의 리프 페이지는 앞뒤로 이어진 이중 연결 리스트라서, 오름차순 인덱스로도 ORDER BY a DESC를 정렬 없이 처리한다. 리프를 뒤에서부터 읽는 역방향 스캔이다. 다만 InnoDB는 페이지 안의 레코드가 한 방향으로만 이어져 있어 역방향 스캔이 정방향보다 조금 느리다. MySQL 8.0부터 내림차순 인덱스(INDEX (a ASC, b DESC))를 실제로 저장한다. 방향이 섞인 정렬(ORDER BY a, b DESC)도 정렬 없이 처리하고, 역방향 스캔보다 정방향 스캔이 빠르므로 자주 쓰는 정렬 방향으로 인덱스를 만든다.

EXPLAIN으로 확인하기

설계가 맞는지는 실행 계획으로 확인한다. MySQL EXPLAIN의 type은 테이블에 접근하는 방식이고, 위에서 아래로 갈수록 비싸다.

• index와 ALL이 튜닝 대상이다. Extra의 Using filesort, Using temporary는 정렬·임시 테이블이 생겼다는 뜻이고, Using index는 커버링 인덱스다.
• EXPLAIN FORMAT=JSON의 used_key_parts로 복합 인덱스 중 실제로 쓴 컬럼을 본다. 일부만 쓰인다면 인덱스를 나누는 편이 낫다.
• EXPLAIN ANALYZE(MySQL 8.0.18+)는 실제로 실행해 예측 행 수와 실제 행 수를 함께 보여 준다. 예측이 크게 틀리면 ANALYZE TABLE로 통계를 갱신한다.
• 데이터가 특정 값에 몰려 있으면 옵티마이저가 인덱스를 버리고 풀 스캔을 고르기도 한다. 이론보다 실제 데이터 분포로 확인한다.
• PostgreSQL은 EXPLAIN (ANALYZE, BUFFERS)로 같은 확인을 한다.

EXPLAIN ANALYZE는 실제로 실행하므로 UPDATE·DELETE에 쓰면 데이터가 바뀐다. 트랜잭션 안에서 실행하고 롤백한다.

Oracle의 인덱스 스캔 방식

Oracle 실행 계획은 인덱스를 읽는 방식을 이름으로 보여 준다. B-트리를 루트에서 리프까지 수직으로 내려가는지, 리프를 옆으로 수평으로 훑는지로 구별된다.

INDEX FULL SCAN은 블록을 하나씩 읽어 순서를 지키는 대신 병렬화가 안 되고, FAST FULL SCAN은 여러 블록을 한 번에 읽고 병렬로 돌 수 있지만 순서가 없다. 그래서 옵티마이저는 정렬이 필요하면 FULL SCAN을, 개수만 세거나 순서가 상관없으면 FAST FULL SCAN을 고른다.

풀 스캔을 고르는 이유

인덱스가 있어도 옵티마이저가 풀 테이블 스캔을 고르는 것은 대개 그쪽이 더 싸다고 추정했기 때문이다.

정말 인덱스가 나은데 잘못 고른다면 힌트(FORCE INDEX, USE INDEX)로 강제할 수 있지만, 데이터 분포가 바뀌면 힌트가 독이 되므로 통계와 쿼리를 먼저 고친다.

조인 방식

SQL의 JOIN 종류(INNER, LEFT 등)는 결과의 모양이고, DB가 실제로 두 입력을 맞추는 물리 조인 알고리즘은 따로 있다. 옵티마이저가 입력 크기와 인덱스를 보고 고른다.

• MySQL은 중첩 루프가 기본이고, 8.0.18부터 해시 조인을 쓴다(8.0.20부터 인덱스 없는 조인의 블록 중첩 루프를 대체). 정렬 병합 조인은 없다. PostgreSQL·Oracle·SQL Server는 세 가지를 모두 쓴다.
• 드라이빙 테이블(driving, outer)은 중첩 루프에서 바깥 루프를 도는 쪽이고, 드리븐 테이블(driven, inner)은 행마다 찾아지는 쪽이다. 드리븐 쪽은 바깥 행 수만큼 탐색되므로 그 조인 컬럼에 인덱스가 있어야 한다. 옵티마이저는 WHERE로 거른 뒤 결과가 작은 쪽을 드라이빙으로, 인덱스로 찾을 수 있는 쪽을 드리븐으로 두는 순서를 비용으로 고른다. LEFT JOIN은 왼쪽 행을 모두 살려야 하므로 순서를 바꿀 여지가 줄어든다.
• 인덱스가 성능을 가르는 것은 주로 중첩 루프다. 드리븐 쪽 인덱스가 있으면 탐색 한 번이 O(log N)이고, 없으면 풀 스캔이 바깥 행 수만큼 반복된다. 해시 조인은 인덱스 없이도 각 입력을 한 번씩만 읽는다.

세 테이블 이상의 조인도 한 번에 두 입력씩 처리한다. (A ⋈ B)의 중간 결과가 다음 조인의 바깥 입력이 되어 C와 조인되는 식이다.

      ⋈            중간 결과(A⋈B)가 C의 드라이빙 입력이 된다
     / \
    ⋈   C          첫 조인의 결과가 크면 그 뒤 모든 조인이 무거워진다
   / \
  A   B

• 조인 순서의 경우의 수는 테이블 수의 팩토리얼로 늘어난다. 옵티마이저는 테이블이 많으면 전부 따지지 않는다. MySQL은 탐욕적 탐색 깊이(optimizer_search_depth)를 두고, PostgreSQL은 join_collapse_limit(기본 8)를 넘는 명시적 JOIN 순서를 재배치하지 않으며 테이블이 geqo_threshold(기본 12)개 이상이면 유전 알고리즘으로 근사한다.
• 행 수 추정 오차는 조인마다 곱해져 커진다. 그래서 조인이 많을수록 엉뚱한 순서·방식을 고를 위험이 커진다. 가장 많이 거르는 조인을 먼저 하도록 조건을 두고, 중간 결과가 큰 단계를 EXPLAIN ANALYZE로 찾는다.

빠른 쿼리 체크리스트

1. 필요한 컬럼만 가져온다. SELECT *는 읽고 보내는 양을 늘리고 커버링 인덱스를 막는다.
2. 조건 컬럼을 가공하지 않는다. 계산은 상수 쪽으로 옮긴다.
3. 앞쪽 와일드카드 LIKE '%…'를 피한다.
4. 중복 제거(DISTINCT, UNION)를 줄인다. 존재 여부만 필요하면 EXISTS, 중복이 없음을 안다면 UNION ALL.
5. 집계 전에 거른다. 같은 조건이면 HAVING보다 WHERE로 먼저 줄인다. WHERE가 GROUP BY보다 먼저 실행된다.
6. 조인 순서는 옵티마이저에 맡기되, 테이블이 아주 많으면 확인한다. 테이블 수가 늘면 순서 탐색 비용 때문에 옵티마이저가 차선의 순서를 고를 수 있다.
7. 자주 쓰는 집계는 미리 만들어 둔다. 요약 테이블·구체화된 뷰는 실시간성을 포기하는 대신 조회를 가볍게 한다. 주로 분석계에서 쓴다(데이터베이스 확장과 성능 최적화).

관측 도구의 비용

MySQL performance_schema는 서버 곳곳의 계측 지점(instrument)에서 호출 횟수·소요 시간을 모아 소비자(consumer) 테이블에 쌓는다. 자주 쓰는 항목은 기본으로 켜져 있다.

• setup_instruments, setup_consumers 테이블의 ENABLED·TIMED로 온라인에서 켜고 끈다.
• 계측 지점을 전부 켜면 처리량이 눈에 띄게 떨어진다. 타이밍 수집(TIMED)까지 켜면 더 떨어진다. 필요한 항목만 골라 켠다.
• InnoDB 내부 지표(information_schema.innodb_metrics)는 상대적으로 부담이 작다.
• 느린 쿼리를 찾는 첫 단계는 보통 슬로 쿼리 로그와 sys 스키마의 요약 뷰다.

NoSQL의 인덱스

NoSQL도 인덱스를 둔다. 다만 저장 구조가 다르므로 무엇으로 찾을 수 있나가 제품마다 크게 다르다.

RDB 인덱스와 다른 점은 셋이다. 분산 저장이라 파티션 키를 모르면 모든 노드를 뒤져야 하고, 보조 인덱스가 원본과 비동기로 맞춰지는 경우가 있으며, 조인이 없으므로 인덱스도 조회 패턴 하나하나에 맞춰 설계한다.

MongoDB

문서 DB도 원리는 같다. 인덱스는 정렬된 B-트리이고, 필드 순서와 접두사(prefix) 규칙이 그대로 적용된다.

• 복합 인덱스 { userid: 1, score: -1 }은 userid, userid + score 조회에 쓰이고 score 단독 조회에는 못 쓴다.
• 정렬도 인덱스 접두사를 따라야 하고, 필드별 방향이 인덱스와 같거나 모두 반대여야 한다({a: 1, b: -1} 인덱스는 sort({a: 1, b: -1}), sort({a: -1, b: 1})에 쓰인다). 단일 필드 인덱스는 방향과 상관없다.
• ESR 규칙: 복합 인덱스 필드를 같음(Equality) → 정렬(Sort) → 범위(Range) 순서로 둔다. 범위 필드가 정렬 필드보다 앞에 오면 메모리 정렬이 생긴다.
• 서로 다른 인덱스를 합쳐 쓰는 인덱스 교차는 복합 인덱스보다 느리다. 함께 조회하는 필드는 복합 인덱스로 만든다.
• 확인 도구: explain("executionStats")로 스캔한 문서 수와 반환 문서 수를 비교하고, $indexStats로 쓰이지 않는 인덱스를 찾고, hint()로 특정 인덱스를 강제해 비교한다.
• 인덱스는 메모리(working set)에 올라와 있어야 빠르다. 쓰기가 많은 컬렉션은 인덱스를 적게 유지한다.

정리

• 인덱스는 정렬이다. 복합 인덱스는 왼쪽부터 이어지는 접두사만 쓰이고, 범위 조건 뒤는 끊긴다.
• 카디널리티가 높은 컬럼, 등호 조건 컬럼을 앞에 둔다. MongoDB는 ESR 순서다.
• 컬럼 가공, 앞쪽 와일드카드, 타입 불일치는 인덱스를 무력화한다.
• 설계는 EXPLAIN과 실제 데이터 분포로 검증한다. 이론적으로 맞는 인덱스도 데이터에 따라 버려진다.

관련 노트: DB 내부 구조: 쿼리 실행과 인덱스 자료 구조, 데이터베이스 확장과 성능 최적화, SQL 기초, MongoDB 시작하기와 CRUD, API 페이지네이션, 빠르고 유지보수하기 좋은 DB 조회 패턴