8장은 이 책에서 제일 두껍고, 실무에서 제일 많이 써먹는 장이다. 인덱스를 한 문장으로 줄이면 **"책 뒤의 색인"**이다. 500쪽짜리 책에서 특정 단어를 찾을 때 처음부터 다 읽는(풀 스캔) 대신 색인을 보고 페이지로 바로 점프하는 것 — 그게 인덱스다. 대신 색인을 만들고 유지하는 비용이 있어서, 읽기는 빨라지지만 쓰기는 느려진다. 이 맞바꿈을 이해하는 게 8장 전체를 관통한다.

8.1 디스크 읽기 방식 — 인덱스는 랜덤 I/O를 줄이는 장치다

왜 인덱스가 빠른지 알려면 디스크부터 봐야 한다. 디스크 I/O는 두 종류다.

• 순차 I/O(Sequential I/O): 디스크에 연속으로 붙은 데이터를 쭉 읽는 것. 빠르다.
• 랜덤 I/O(Random I/O): 여기저기 흩어진 데이터를 찾아 읽는 것. 느리다. HDD라면 디스크 헤드를 물리적으로 옮겨야 하니 특히 느리다.

핵심은 **디스크 성능의 병목이 "데이터 양"이 아니라 "몇 번 찾아가느냐(I/O 횟수)"**라는 점이다. SSD가 HDD보다 빠른 것도 바로 이 랜덤 I/O에서 격차가 크다(헤드 이동이 없으니까). 그래서 인덱스의 목적은 읽어야 할 레코드 건수를 줄여 랜덤 I/O 횟수를 줄이는 것이다. 뒤에 나오는 "전체의 20~25%를 넘게 읽어야 하면 인덱스 대신 풀 스캔이 낫다"는 규칙도 여기서 나온다 — 어차피 많이 읽을 거면 순차 I/O(풀 스캔)가 랜덤 I/O(인덱스)보다 빠르다.

8.2 인덱스란? — SortedList vs ArrayList

책이 든 비유가 명쾌하다. 인덱스는 SortedList, 데이터 파일은 ArrayList다.

• SortedList(인덱스): 값이 정렬된 채로 저장된다. 저장할 때 제자리를 찾아 넣어야 해서 쓰기가 느리지만, 정렬돼 있으니 읽기(검색)가 빠르다.
• ArrayList(데이터 파일): 그냥 들어온 순서대로 쌓인다. 쓰기는 빠르지만 특정 값을 찾으려면 처음부터 다 뒤져야 한다.

즉 인덱스는 "쓰기 성능을 희생해서 읽기 성능을 산다." 그래서 인덱스를 무작정 많이 걸면 안 된다 — INSERT/UPDATE/DELETE가 그만큼 느려진다.

인덱스는 역할로 나누면 **프라이머리 키(Primary Key)**와 **세컨더리 인덱스(Secondary Index)**로, 저장 알고리즘으로 나누면 B-Tree(가장 일반적), Hash(메모리 기반, 동등 비교만), R-Tree(공간), Fractal-Tree 등으로 나뉜다. 실무의 대부분은 B-Tree다.

8.3 B-Tree 인덱스

B-Tree는 데이터베이스 인덱스의 가장 일반적이고 가장 오래된 알고리즘이다. 참고로 여기서 B는 흔히 오해하는 **Binary(이진)가 아니라 Balanced(균형)**의 약자다.

8.3.1 구조 — 루트·브랜치·리프

B-Tree는 트리 구조다.

• 루트 노드(Root Node): 최상위 하나
• 브랜치 노드(Branch Node): 중간
• 리프 노드(Leaf Node): 최하위. 여기가 실제 데이터 레코드를 가리킨다.

중요한 건 리프 노드가 무엇을 가리키느냐인데, 이게 스토리지 엔진마다 다르다.

• MyISAM: 리프가 데이터 파일의 **물리적 주소(offset, ROWID)**를 가리킨다.
• InnoDB: 리프가 물리적 주소가 아니라 프라이머리 키 값을 가진다. 그래서 세컨더리 인덱스로 검색하면 → 리프에서 PK를 얻고 → 그 PK로 다시 클러스터링 인덱스를 타야 실제 레코드에 도달한다(뒤 8.8에서 자세히).

8.3.2 키 추가·삭제·변경·검색

• 추가: 저장할 위치를 찾아 리프에 넣는데, 리프가 꽉 차면 **페이지 분리(split)**가 일어난다. split은 비용이 크다. 책은 인덱스가 3개인 테이블의 INSERT 비용을 대략 1.5(테이블 자체 1 + 인덱스 3개에 대해 (1.5×3+1)/3 ≈ 1.5)로 추산한다. MyISAM/MEMORY는 즉시 처리하지만, InnoDB는 필요하면 **체인지 버퍼(Change Buffer)**로 지연 처리할 수 있다.
• 삭제: 해당 키를 찾아 삭제 마크만 하면 끝(공간은 재활용). 간단하다.
• 변경: "삭제 후 새 값 추가"로 처리된다(값이 바뀌면 정렬 위치가 바뀌니까).
• 검색: 루트 → 브랜치 → 리프로 트리를 타고 내려간다. 여기서 결정적 제약 — 인덱스는 값의 앞부분(Left-most)이나 값 전체가 일치할 때만 쓸 수 있다. 값을 변형(함수 적용 등)하면 인덱스를 못 탄다. WHERE SUBSTRING(col,1,1)='X' 같은 건 인덱스를 못 쓴다.

8.3.3 B-Tree 인덱스에 영향을 주는 요소

① 키 값의 크기. 인덱스 페이지(기본 16KB)에 키가 몇 개 들어가느냐가 여기서 갈린다. 키가 크면 페이지당 키 개수가 줄고 → 같은 레코드 수를 담으려면 B-Tree 깊이(Depth)가 깊어지고 → 검색 시 디스크를 더 많이 읽어야 한다. 참고로 대용량이라도 B-Tree 깊이가 5단계 이상 깊어지는 경우는 드물다. 그러니 "키 값은 되도록 작게"가 원칙이다.

② 선택도(Selectivity) = 기수성(Cardinality). 전체 인덱스 값 중 유니크한 값의 개수다. 유니크한 값이 많을수록(중복이 적을수록) 선택도가 높고, 인덱스로 걸러지는 레코드가 적어져 검색이 빠르다. 예를 들어 전체 1만 건에서 country='KOREA' AND city='SEOUL'을 찾을 때, country만 인덱스면 유니크 값이 10개(각 1000건)일 땐 1000건을 읽어 999건을 버려야 하고, 유니크 값이 1000개(각 10건)면 10건만 읽으면 된다. 중복이 적은 칼럼이 인덱스로 값어치가 있다.

③ 읽어야 하는 레코드 건수. 인덱스를 통한 읽기는 레코드 1건당 랜덤 I/O가 붙어서, 직접 테이블을 읽는(순차 I/O) 것보다 건당 비용이 몇 배 비싸다(옵티마이저는 대략 4~5배로 본다). 그래서 전체의 20~25%를 넘게 읽어야 하는 쿼리는 인덱스를 버리고 풀 스캔(필터링)하는 게 효율적이다. 옵티마이저가 알아서 판단하지만, 힌트로 강제해도 소용없는 이유가 이것이다.

8.3.4 B-Tree 인덱스를 통한 데이터 읽기

인덱스를 쓰는 방식은 세 가지다.

① 인덱스 레인지 스캔(Index Range Scan) — 가장 대표적. 3단계로 동작한다.

1. 인덱스 탐색(Index Seek): 조건을 만족하는 시작 위치를 찾는다.
2. 인덱스 스캔(Index Scan): 시작부터 필요한 만큼 쭉 읽는다. (1+2를 합쳐 인덱스 스캔이라 부르기도)
3. 레코드 읽기: 읽은 인덱스의 PK/주소로 실제 데이터 레코드를 가져온다.

이 3번(실제 레코드 읽기)이 랜덤 I/O라 비싸다. 만약 쿼리에 필요한 칼럼이 인덱스 안에 다 있어서 3번을 아예 생략할 수 있으면 그게 **커버링 인덱스(Covering Index)**이고, 굉장히 빠르다. SHOW STATUS LIKE 'Handler%'의 Handler_read_key(탐색 횟수), Handler_read_next(정순 스캔), Handler_read_prev(역순 스캔)로 인덱스가 실제로 쓰였는지 잴 수 있다.

② 인덱스 풀 스캔(Index Full Scan) — 인덱스의 처음부터 끝까지 다 읽는다. 인덱스에 있는 칼럼만으로 조건을 처리할 수 있지만 선행 칼럼 조건이 없을 때 쓴다. 테이블 풀 스캔보다는 낫다(인덱스가 데이터보다 작으니까).

③ 루스 인덱스 스캔(Loose Index Scan) — 조건에 안 맞는 중간 부분을 건너뛰며(skip) 필요한 부분만 듬성듬성 읽는다. 오라클의 "인덱스 스킵 스캔"에 해당하고, MySQL 8.0부터 GROUP BY나 MIN()/MAX() 최적화에서 확 개선됐다.

8.3.5 다중 칼럼 인덱스 (Concatenated Index)

2개 이상 칼럼으로 만든 인덱스다. 여기서 가장 중요한 규칙 — 두 번째 칼럼은 첫 번째 칼럼에 의존해서 정렬된다. 즉 INDEX (a, b)는 a로 먼저 정렬하고, a가 같은 것들끼리 그 안에서 b로 정렬한다. 그래서 b 단독 조건(WHERE b=...)으로는 이 인덱스를 못 쓴다. 칼럼 순서가 인덱스 설계의 핵심인 이유다.

8.3.6 정렬 및 스캔 방향

인덱스는 오름차순으로 저장되지만, MySQL은 거꾸로(역순) 읽을 수도 있다. ORDER BY ... DESC면 인덱스를 역순 스캔하면 되니 별도 정렬이 필요 없다. MySQL 8.0부터는 아예 **내림차순 인덱스(INDEX (a ASC, b DESC))**를 만들 수 있게 됐다 — 두 칼럼을 서로 반대 방향으로 정렬해야 하는 쿼리에서 유용하다. 정순 스캔(Forward)이 역순 스캔(Backward)보다 살짝 빠른데, 이는 InnoDB 페이지 내 레코드가 정순 링크(단방향 연결) 구조라서 그렇다.

8.3.7 인덱스의 가용성과 효율성

작업 범위 결정 조건 vs 필터링 조건 — 이 구분이 실무에서 중요하다.

• 작업 범위 결정 조건(Range Condition): 인덱스로 읽을 범위 자체를 좁혀주는 조건. 이게 많아야 빠르다.
• 필터링 조건(Filter Condition): 읽어온 뒤 조건에 맞는지 걸러내기만 하는 조건. 범위를 못 줄인다.

예로 INDEX (dept_no, emp_no)에서 WHERE dept_no='d002' AND emp_no>=10114는 둘 다 범위를 좁힌다(케이스 A). 하지만 INDEX (emp_no, dept_no)라면 dept_no가 뒤 칼럼이라, emp_no만 범위를 좁히고 dept_no는 필터링 조건이 된다(케이스 B). 같은 조건이라도 인덱스 칼럼 순서에 따라 효율이 갈린다.

그리고 인덱스를 아예 못 쓰는 조건들:

• <>, NOT IN, IS NOT NULL 같은 부정 비교
• LIKE '%..'처럼 앞부분이 아닌 패턴 (뒷부분 고정은 Left-most가 아니라서)
• WHERE SUBSTRING(col,1,1)='X'처럼 칼럼을 함수/연산으로 변형한 경우
• WHERE char_col = 10처럼 타입이 달라 칼럼을 변형해야 하는 경우
• 비교 대상과 콜레이션(Collation)이 다른 경우

참고로 MySQL은 다른 DBMS와 달리 NULL도 인덱스에 저장해서 WHERE col IS NULL은 인덱스를 탈 수 있다.

8.4 R-Tree 인덱스 — 공간 검색

**공간 인덱스(Spatial Index)**로, 2차원 좌표(위경도 등)를 다룬다. B-Tree가 1차원 스칼라 값을 다루는 것과 대비된다. 핵심 개념은 MBR(Minimum Bounding Rectangle, 최소 경계 사각형) — 도형을 감싸는 가장 작은 사각형이다. R-Tree는 이 MBR의 포함 관계를 계층으로 쌓은 트리다.

GPS·지도 서비스에서 "반경 5km 안의 가게" 같은 검색에 쓴다. ST_Contains()/ST_Within()(사각 박스 포함 판정)으로 1차 필터링하고, ST_Distance_Sphere()(실제 구면 거리)로 2차 정밀 판정하는 식으로 조합한다.

🐘 PostgreSQL 비교 — 공간 검색은 오히려 PostgreSQL + PostGIS가 사실상 업계 표준이다. PG는 GiST/SP-GiST 인덱스로 공간 데이터를 다루고, PostGIS 확장의 기능이 MySQL 내장 공간 기능보다 훨씬 풍부하다. 지리 데이터가 핵심인 서비스라면 PG를 고르는 게 일반적이다.

8.5 전문 검색(Full Text) 인덱스

문서 본문의 내용에서 키워드를 뽑아 인덱싱하는 것. 일반 B-Tree는 값의 앞부분(Left-most) 일치나 전체 일치만 되니 "문서 중간에 든 단어"는 못 찾는다. 전문 검색 인덱스는 그걸 가능하게 한다. 방식이 두 가지다.

• 어근 분석(Stemming): 형태소를 분석해 단어 뿌리를 찾는다. 언어별 규칙이 필요해서, 한국어는 형태소 분석기 MeCab을 쓴다.
• n-gram: 본문을 n글자씩 잘라 토큰으로 만든다. 규칙이 없어 언어 중립적이라 널리 쓰인다. 보통 2-gram(bigram). 예: "To be or not to be"를 2글자씩 중첩해 자른다.

검색에 의미 없는 단어(a, the, is…)는 **불용어(Stop Word)**로 걸러낸다. MATCH(...) AGAINST(...) 구문으로 검색하고, 인덱스를 구성한 칼럼을 MATCH 괄호 안에 다 명시해야 한다.

🐘 PostgreSQL 비교 — PG는 전문 검색이 코어에 잘 통합돼 있다. tsvector/tsquery 타입과 GIN 인덱스로 처리하고, 한국어도 별도 확장으로 지원한다. 기능 성숙도 면에서 PG의 full text가 앞선다는 평이 많다.

8.6 함수 기반 인덱스

칼럼 값을 그대로가 아니라 계산·변형한 결과에 인덱스를 걸고 싶을 때 쓴다. 앞서 "칼럼을 함수로 변형하면 인덱스를 못 쓴다"고 했는데, 그럴 때 아예 그 함수 결과를 인덱싱하는 것이다. 두 방식이 있다.

-- ① 가상 칼럼(Virtual Column)을 만들어 인덱싱
ALTER TABLE user
  ADD full_name VARCHAR(30) AS (CONCAT(first_name,' ',last_name)) VIRTUAL,
  ADD INDEX ix_fullname (full_name);

-- ② 함수를 직접 인덱싱 (MySQL 8.0+)
CREATE TABLE user (
  ...,
  INDEX ix_fullname ((CONCAT(first_name,' ',last_name)))
);

내부 구현은 둘이 동일해서 성능 차이는 없다. 단, 쿼리의 WHERE 조건에 쓴 표현식이 인덱스에 정의된 표현식과 정확히 같아야 인덱스를 탄다.

🐘 PostgreSQL 비교 — PG는 훨씬 전부터 **표현식 인덱스(Expression Index)**를 자연스럽게 지원해왔다. CREATE INDEX ix ON user (LOWER(email));처럼 함수 결과에 바로 인덱스를 건다. MySQL이 8.0에 와서 따라잡은 기능이다.

8.7 멀티 밸류 인덱스 (Multi-Value Index)

지금까지의 인덱스는 "레코드 1건 = 인덱스 키 1개"였는데, 멀티 밸류 인덱스는 레코드 1건이 여러 개의 키 값을 가진다. JSON 배열 필드를 인덱싱할 때 쓴다. CAST(... AS UNSIGNED ARRAY)로 정의하고, MEMBER OF(), JSON_CONTAINS(), JSON_OVERLAPS() 함수로 검색해야 인덱스를 탄다. 예전에 JSON 인덱싱이 필요해 MongoDB로 갔던 요구를 MySQL에서 흡수한 기능이다.

8.8 클러스터링 인덱스 — InnoDB의 핵심

**클러스터링 인덱스(Clustering Index)**는 프라이머리 키 값에 의해 레코드의 물리적 저장 위치가 결정되는 것이다. 즉 PK 순서로 데이터가 실제로 정렬돼 저장된다. InnoDB만 지원한다(MyISAM엔 없다).

그래서 InnoDB에서 PK는 곧 데이터 그 자체이고, 세컨더리 인덱스의 리프는 (물리 주소가 아니라) PK 값을 저장한다. 세컨더리 인덱스 검색 → PK 획득 → 클러스터링 인덱스 재탐색 → 레코드 도달, 이렇게 두 번 탄다.

장점과 단점

• 장점: PK로 검색할 때 엄청 빠르다(특히 범위 검색). 모든 세컨더리 인덱스가 리프에 PK를 갖고 있어 커버링 효과도 크다.
• 단점: PK가 바뀌면 레코드를 물리적으로 옮겨야 해서 느리다. 세컨더리 인덱스가 전부 PK를 저장하므로 PK 크기가 크면 인덱스 전체가 다 커진다. INSERT도 PK 순서상 자리를 찾아 넣어야 해서 느릴 수 있다.

정리하면 클러스터링 인덱스는 읽기(SELECT)가 빠르고 쓰기가 느린 구조다. 읽기:쓰기 비율이 보통 8:2~9:1인 온라인 서비스(OLTP)에선 이 맞바꿈이 남는 장사다.

PK 설계 두 가지 원칙

① PK는 되도록 작게, 업무적으로 대표성 있는 칼럼으로. PK 크기가 커지면 모든 세컨더리 인덱스가 다 커진다(5개 세컨더리 인덱스면 PK 크기가 5배로 곱해진다).
② 마땅한 게 없으면 **AUTO_INCREMENT 인조 식별자(Surrogate Key)**를 PK로 써라. 특히 여러 칼럼이 묶인 복합 PK로 크기가 커지느니, 작은 인조 키가 낫다.

🐘 PostgreSQL 비교 — 여기가 두 DB의 가장 근본적인 차이다. PostgreSQL은 클러스터링 인덱스가 없다. 테이블이 **힙(Heap)**에 그냥 쌓이고(들어온 순서), PK조차 별도의 인덱스일 뿐 데이터 저장 위치를 결정하지 않는다. 그래서 PG의 세컨더리 인덱스는 PK가 아니라 **물리적 튜플 위치(ctid)**를 가리킨다 — InnoDB처럼 인덱스를 두 번 타지 않는다. 대신 PG는 CLUSTER 명령으로 일시적 물리 정렬만 가능하고(유지 안 됨), 이 구조가 PG의 MVCC·VACUUM 이야기로 이어진다.

8.9 유니크 인덱스

유니크 인덱스는 사실 **인덱스라기보다 "제약(Constraint)"**에 가깝다. 같은 값이 2개 이상 저장될 수 없게 하는 것. 성능 관점에서 오해가 많은데:

• 읽기: 유니크가 빠르다고 흔히 생각하지만 사실이 아니다. 어차피 인덱스로 1건 읽는 건 유니크든 아니든 같다. 읽어야 할 레코드 건수가 같으면 성능 차이는 거의 없다.
• 쓰기: 유니크 인덱스는 느리다. 새 값을 넣을 때 중복이 있는지 체크해야 하는데, 이 과정에서 읽기 잠금과 쓰기 잠금을 함께 걸어 데드락이 자주 발생한다. 게다가 유니크 인덱스는 체인지 버퍼(Change Buffer)를 못 쓴다(중복 체크를 지연시킬 수 없으니까). 그래서 일반 세컨더리 인덱스보다 변경이 더 느리다.

유니크 인덱스를 중복으로 걸지 마라

이미 UNIQUE INDEX ux_nickname (nickname)이 있으면, 유니크 인덱스가 일반 인덱스 역할도 겸하므로 INDEX ix_nickname (nickname)을 또 만들 필요가 없다. 같은 칼럼에 유니크와 일반 인덱스를 둘 다 만드는 실수를 흔히 하는데, 유니크 하나면 충분하다.

8.10 외래 키(Foreign Key)

외래 키는 InnoDB에서만 지원하고, 외래 키 제약을 걸면 대상 칼럼에 자동으로 인덱스가 생성된다. 실무에서 성능·잠금 관점의 특징 두 가지가 중요하다.

• 자식 테이블의 변경이 부모를 기다린다: 자식 테이블에 INSERT/UPDATE할 때, 참조하는 부모 레코드가 잠겨 있으면 그 잠금이 풀릴 때까지 대기한다(부모의 존재를 확인해야 하니까).
• 부모 테이블의 변경이 자식을 기다린다: 부모 레코드를 삭제/변경할 때, ON DELETE CASCADE 등이 걸려 있으면 자식 레코드까지 연쇄로 잠기고 처리된다.

즉 외래 키는 잠금이 테이블 경계를 넘어 전파되므로, 동시성에 영향을 준다. 그래서 대량 트래픽 환경에선 외래 키를 물리적으로 걸지 않고 애플리케이션 레벨에서 정합성을 관리하는 선택도 흔하다.

🐘 PostgreSQL 비교 — PG는 외래 키가 특정 스토리지 엔진에 묶이지 않고 코어 기능으로 항상 지원된다(엔진 선택 자체가 없으니까). 잠금 전파로 인한 동시성 이슈는 두 DB 모두 비슷하게 존재한다.

정리

8장에서 실무로 들고 갈 것.

• 인덱스는 쓰기를 희생해 읽기를 산다. 무작정 많이 걸지 마라 — 인덱스마다 INSERT/UPDATE/DELETE가 느려진다.
• 전체의 20~25%를 넘게 읽는 쿼리는 인덱스가 오히려 손해다. 인덱스는 랜덤 I/O, 풀 스캔은 순차 I/O라 많이 읽을 땐 풀 스캔이 빠르다. 옵티마이저가 이걸 판단한다.
• 다중 칼럼 인덱스는 칼럼 순서가 전부다. 뒤 칼럼은 앞 칼럼에 의존해 정렬되므로, 뒤 칼럼 단독 조건으로는 인덱스를 못 쓴다. 작업 범위 결정 조건 vs 필터링 조건을 구분해서 순서를 정하라.
• 칼럼을 함수/연산으로 변형하거나(SUBSTRING(col)), 타입이 다르거나, LIKE '%..', 부정 비교(<>, NOT IN)면 인덱스를 못 쓴다. 변형이 꼭 필요하면 함수 기반 인덱스(8.0)를 써라.
• InnoDB의 PK는 곧 데이터다(클러스터링 인덱스). PK는 작게, 업무 대표성 있게, 없으면 AUTO_INCREMENT로. PK가 크면 모든 세컨더리 인덱스가 다 커진다.
• 유니크 인덱스는 읽기가 빠른 게 아니다. 쓰기는 중복 체크 때문에 더 느리고 데드락도 잦다. 같은 칼럼에 유니크+일반 인덱스를 중복으로 걸지 마라.
• 커버링 인덱스(쿼리에 필요한 칼럼이 인덱스에 다 있어 실제 레코드를 안 읽는 것)를 노려라. 랜덤 I/O를 통째로 생략해 아주 빠르다.
• 외래 키는 잠금이 테이블을 넘어 전파된다. 대량 트래픽에선 물리 FK 대신 앱 레벨 정합성 관리를 고려하라.

반대로 지금 깊이 안 파도 되는 것 — R-Tree(공간)·전문 검색·멀티 밸류 인덱스는 그 데이터를 실제로 다룰 때 찾아보면 되고(공간은 PostGIS가 더 강하다는 것만 기억), 루스 인덱스 스캔의 세부 동작이나 오름/내림차순 인덱스의 정순·역순 성능 차이도 문제가 될 때 들여다보면 된다.