[MySQL] 인덱스 심화 — 10%룰, B+Tree 리밸런싱, 복합 인덱스 컬럼 순서

1. 문서 제목

• MySQL 인덱스 심화 — 10%룰, B+Tree 리밸런싱, 복합 인덱스 컬럼 순서

2. 기술 개요 요약

인덱스를 "있으면 무조건 빠르다"로 이해하면 실전 튜닝에서 막힌다. 옵티마이저가 인덱스 대신 풀스캔을 택하는 기준, B+Tree가 삽입·삭제마다 스스로 균형을 맞추는 방식, 복합 인덱스에서 컬럼 순서를 정하는 진짜 기준까지 알아야 EXPLAIN 결과를 해석하고 인덱스를 설계할 수 있다. 이 문서는 이 세 가지를 정리한다. 인덱스 레인지 스캔과 랜덤 I/O, covering index는 이미 다뤘으므로 여기서는 다루지 않는다.

3. 핵심 기능/개념 정리

개념	설명	실무 포인트
인덱스 vs 풀스캔 임계점("10%룰")	조회 대상 로우 비율이 일정 수준을 넘어 세컨더리 인덱스의 랜덤 I/O 누적 비용이 순차 풀스캔보다 커지면 옵티마이저가 풀스캔을 선택	"10%"는 하드코딩된 값이 아니라 관행적 경험치. 실제로는 통계 정보 기반 비용 계산으로 결정되며 인덱스 종류·디스크 특성에 따라 가변적
B-Tree vs B+Tree	B-Tree는 모든 노드에 데이터를 저장. B+Tree는 리프 노드에만 데이터를 저장하고 리프끼리 연결리스트로 연결	MySQL InnoDB는 B+Tree 사용. 노드당 키 밀도가 높아 트리 높이가 낮고, 리프 연결리스트 덕분에 range scan이 빠름
Split (분할)	리프 노드가 꽉 찬 상태에서 삽입 시 노드를 둘로 나누고 부모에 경계 키 추가	부모도 꽉 차 있으면 연쇄적으로 상위까지 split이 전파되어 트리 높이가 증가할 수 있음. MySQL 공식 문서에 따르면 순차 삽입 시 페이지는 약 15/16까지 채워지고, 랜덤 삽입 시에는 1/2~15/16 수준으로만 채워짐
Merge (병합)	삭제로 페이지 점유율(fill factor)이 MERGE_THRESHOLD(기본값 50%, innodb_merge_threshold로 1~50 범위 조정 가능) 미만이 되면 InnoDB가 인덱스 트리를 축소(인접 노드와 병합/재분배) 시도	"약 50%"는 관행적 추정치가 아니라 MySQL 공식 문서에 명시된 기본값. B-tree와 R-tree(공간 인덱스) 모두에 적용됨
복합 인덱스 컬럼 순서	카디널리티보다 "등치 조건이냐 범위 조건이냐"가 우선 기준 — 실무에서는 이를 Equality → Range → Order 순서로 부르지만 이는 MySQL 공식 용어가 아니라 커뮤니티에서 통용되는 비공식 표현(mnemonic)임	범위 조건을 만나면 그 뒤 컬럼에는 인덱스 확장 적용이 되지 않는다는 것이 MySQL 공식 Range Optimization 문서에 명시된 동작. 카디널리티는 같은 역할(등치끼리) 컬럼들 사이의 2차 기준

4. 사용 예시 및 코드 스니펫

B+Tree split 발생 시나리오:

리프 노드 [10, 20, 30, 40] (꽉 참)
새 키 25 삽입 시도
  -> split: [10, 20] / [25, 30, 40]
  -> 부모 노드에 경계 키 25 추가
  -> 부모도 꽉 차 있으면 부모도 split, 루트까지 전파 시 트리 높이 +1

복합 인덱스 컬럼 순서 — 잘못된 예와 올바른 예:

-- 쿼리: WHERE a = 1 AND b > 10 AND c = 5

-- 잘못된 순서 (카디널리티만 고려, b가 c보다 카디널리티 높다고 가정)
CREATE INDEX idx_bad ON t(b, a, c);
-- b(범위)가 맨 앞이라 a, c 조건까지 인덱스 정렬을 활용하지 못함

-- 올바른 순서 (등치 먼저, 범위 마지막)
CREATE INDEX idx_good ON t(a, c, b);
-- a=1, c=5로 정확히 좁힌 뒤 b>10 범위를 마지막에 적용

5. 실제 사용 시 주의점 / Best Practice

• "10%룰"을 절대 기준으로 암기하지 말 것. ANALYZE TABLE로 통계가 최신인지 먼저 확인하고, 실제로는 EXPLAIN의 rows/filtered 추정치로 옵티마이저의 판단 근거를 확인하는 것이 더 정확하다.
• AUTO_INCREMENT처럼 계속 증가하는 값을 PK로 쓰면 삽입이 트리 오른쪽 끝에서만 순차적으로 일어나 split이 예측 가능하다. UUID 같은 무작위 PK는 삽입 위치가 트리 전체에 흩어져 split이 빈번해지고 페이지 활용률도 떨어진다.
• 복합 인덱스를 설계할 때는 먼저 "이 쿼리에서 각 컬럼이 등치 조건인지 범위 조건인지"를 표로 정리한 뒤, 등치 → 범위 → 정렬/그룹핑 순서로 컬럼을 배치하고, 마지막에만 카디널리티로 등치 조건끼리의 순서를 조정한다.

6. 참고자료 / 공식 문서 출처

• MySQL Reference Manual, How MySQL Uses Indexes: https://dev.mysql.com/doc/refman/8.4/en/mysql-indexes.html
• MySQL Reference Manual, Physical Structure of an InnoDB Index(split 시 페이지 채움 비율 15/16, 1/2 근거): https://dev.mysql.com/doc/refman/8.4/en/innodb-physical-structure.html
• MySQL Reference Manual, Optimizing InnoDB with the Change Buffer / Index Page Merge Threshold(MERGE_THRESHOLD 기본값 50%): https://dev.mysql.com/doc/refman/8.4/en/index-page-merge-threshold.html
• MySQL Reference Manual, Range Optimization(범위 조건 이후 컬럼의 인덱스 활용 제한 근거): https://dev.mysql.com/doc/refman/8.4/en/range-optimization.html
• MySQL Reference Manual, Multiple-Column Indexes(leftmost prefix 원칙): https://dev.mysql.com/doc/refman/8.4/en/multiple-column-indexes.html