데이터베이스 인덱스 정리

본 문서는 MySQL InnoDB 스토리지 엔진을 기준으로, 인덱스의 구조·동작·스캔 방식과 설계 시 유의점을 체계적으로 정리합니다.

1) 인덱스란 무엇인가

• 정의: 테이블의 특정 컬럼(또는 컬럼 조합)에 대해 정렬된 탐색 구조를 유지하여 검색 속도를 높이는 자료구조입니다. 백과사전의 색인에 비유할 수 있습니다.
• 장점: 탐색/정렬/조인 성능 향상, 랜덤 I/O 감소(적중 시).
• 단점: 쓰기(INSERT/UPDATE/DELETE) 시 정렬 구조 유지 비용이 발생(페이지 분할/병합, 재정렬). 디스크 공간 추가 사용.

2) 어떤 자료구조로 구현되나 (InnoDB)

• InnoDB의 일반 인덱스는 B+Tree(B-Tree 변형)로 구현됩니다.

  • 루트 → 브랜치(내부) → 리프 노드 구조
  • 리프 노드가 모두 연결(Linked List) 되어 있어 범위 스캔이 효율적
  • 키는 정렬 상태 유지
• 클러스터드 인덱스(Primary Key)

  • InnoDB에서 테이블 데이터 자체가 PK 기준 B+Tree의 리프에 저장됩니다(클러스터드 테이블).
• 세컨더리 인덱스(보조 인덱스)

  • 리프 노드에 PK 값이 저장됩니다(레코드 물리 주소가 아님).
  • 세컨더리 인덱스로 찾은 뒤, PK를 이용해 한 번 더 클러스터드 인덱스를 조회(Back to Table)합니다.

요약: 세컨더리 인덱스는 항상 PK로 한 단계 더 조회가 필요하므로, 가능하면 커버링 인덱스를 설계하여 Back to Table을 줄이는 것이 좋습니다.

3) 인덱스 스캔 방식(MySQL)

3.1 인덱스 레인지 스캔 (Index Range Scan)

• 조건으로 범위가 명확할 때 사용되며 가장 빠른 방식입니다.
• 동작: 시작 키 탐색(Index Seek) → 필요 범위만 순차 스캔(Index Scan) → (필요 시) Back to Table.
• 특성: 범위가 넓고 레코드 접근 비율이 전체 20~25% 이상이면 풀 테이블 스캔이 더 유리할 수 있습니다(랜덤 I/O 누적 때문).

3.2 인덱스 풀 스캔 (Index Full Scan)

• 인덱스를 처음부터 끝까지 스캔합니다.
• 예: 인덱스가 (A, B, C) 순으로 만들어졌는데 B 또는 C만 단독 조건으로 사용할 때 등 선두 컬럼 불일치 상황.
• 테이블 전체 스캔보다는 데이터 레코드를 건너뛸 수 있어 유리할 수 있으나, 목적 설계는 아닙니다.

3.3 루스 인덱스 스캔 (Loose Index Scan)

• 인덱스를 듬성듬성 건너뛰며 필요한 키만 집계.
• GROUP BY, MIN/MAX 최적화에 사용될 수 있습니다(모든 값을 스캔하지 않음).
• 사용 가능성은 쿼리 형태와 인덱스 구성에 따라 제한됩니다.

4) 복합 인덱스와 설계 원칙

• 좌측(선두) 접두사 규칙(Leftmost Prefix Rule)

  • 인덱스 (A, B, C)는 다음 패턴을 효율적으로 지원합니다: A, (A, B), (A, B, C) 및 각각의 범위 조건.
  • B만, C만 조건이면 선두 불일치로 Range Scan이 어려워 Index Full Scan/Full Table Scan으로 전락할 수 있습니다.
• 정렬/그룹을 인덱스로 흡수

  • ORDER BY A, B가 인덱스 순서와 동일하면 filesort 없이 처리 가능.
  • GROUP BY A, B도 유사하게 인덱스 정렬 활용.
• 선택도(Selectivity)

  • 카디널리티(서로 다른 값 개수) 가 높을수록 효율적입니다. 불리언/저카디널리티 컬럼 단독 인덱스는 효과가 미약할 수 있습니다.
• 커버링 인덱스(Covering Index)

  • 쿼리에 필요한 모든 컬럼이 인덱스에 포함되면 Back to Table 생략.
  • 예: INDEX(user_id, created_at, amount)로 SELECT user_id, created_at, amount ... 를 충족.
• SARGable(인덱스 가능한) 조건을 유지

  • WHERE func(col) = ..., WHERE col + 1 = ..., LIKE '%suffix' 는 인덱스 활용이 어렵습니다.
  • 대안: 계산 결과 컬럼 저장/정규화, 전방 일치(LIKE 'prefix%'), 생성(가상) 컬럼 + 인덱스.

5) 실행 계획과 통계

• EXPLAIN으로 접근 방식(type: range, ref, eq_ref, ALL 등)과 사용 인덱스를 확인하십시오.
• ANALYZE TABLE, InnoDB 통계가 부정확하면 옵티마이저가 잘못된 계획을 선택할 수 있습니다.
• 힌트(e.g., USE INDEX, FORCE INDEX)는 최후의 수단으로 일시적 교정에 활용합니다.

6) 인덱스 유지 비용과 쓰기 최적화

• 쓰기 시 B+Tree는 페이지 분할/병합과 리밸런싱이 발생할 수 있습니다.
• 대량 적재는 인덱스를 최소화하거나 중간 인덱스 비활성화/재생성 전략을 검토하십시오.
• 자주 갱신되는 컬럼에 불필요한 인덱스는 제거하여 쓰기 성능과 잠금 경합을 줄입니다.

7) 예제

7.1 설계 예: 주문 검색(사용자·기간·정렬)

-- 복합 인덱스: 사용자 → 기간 → 정렬키
CREATE INDEX ix_orders_user_created_amount
  ON orders(user_id, created_at, amount);

-- 전형적 쿼리 1: 사용자/기간 필터 + 시간순 정렬 (filesort 회피)
SELECT user_id, created_at, amount
FROM orders
WHERE user_id = ?
  AND created_at BETWEEN ? AND ?
ORDER BY created_at DESC;

-- 전형적 쿼리 2: 최근 사용자별 첫 주문 시각 (MIN) - 루스 스캔 후보
SELECT user_id, MIN(created_at)
FROM orders
GROUP BY user_id;

7.2 커버링 인덱스 예

-- 필요한 컬럼만 읽고 끝나므로 Back to Table 회피
CREATE INDEX ix_orders_cover ON orders(user_id, created_at, amount);
SELECT user_id, created_at, amount
FROM orders
WHERE user_id = ?
  AND created_at >= ?;

7.3 SARGable 변환

-- 비권장: 함수 적용으로 인덱스 사용 제한
SELECT * FROM users WHERE DATE(created_at) = '2025-11-01';

-- 권장: 범위 조건으로 변환
SELECT * FROM users
WHERE created_at >= '2025-11-01 00:00:00'
  AND created_at <  '2025-11-02 00:00:00';

8) 체크리스트

• 쿼리의 주요 조건/정렬/그룹을 인덱스로 흡수했는가
• 좌측 접두사 규칙을 위반하지 않는가
• 커버링 인덱스로 Back to Table을 줄일 수 있는가
• 선택도가 낮은 컬럼에 인덱스를 남발하지 않았는가
• EXPLAIN 결과와 실행 시간을 주기적으로 검증하는가
• 쓰기 빈도가 높은 테이블에 불필요한 인덱스가 존재하지 않는가

9) 요약

• InnoDB 인덱스는 B+Tree로, 클러스터드(PK) 와 세컨더리 인덱스가 핵심입니다.
• 스캔 방식은 Range > Full Index > Full Table 순으로 일반적으로 효율적이나, 데이터 분포/범위 비율에 따라 다릅니다.
• 좌측 접두사·커버링·SARGable 원칙을 지키고, EXPLAIN으로 검증하며, 쓰기 비용과 운영 특성을 함께 고려하여 인덱스를 설계하십시오.