[Real MySQL 8.0 (1권)] 8장 - 인덱스

8장에서 던지는 질문

• 왜 데이터베이스 튜닝의 핵심은 디스크 I/O 줄이기인가?
• B-Tree 인덱스의 구조적 특징이 어떻게 검색 속도($O(\log N)$)를 보장하는가?
• 인덱스 설계 시 카디널리티(Cardinality)와 선택도(Selectivity)는 왜 절대적인 기준이 되는가?
• 클러스터링 인덱스와 세컨더리 인덱스의 차이는 무엇이며, PK 설정이 중요한 이유는?

디스크 I/O와 성능

DB 성능 튜닝은 결국 디스크 I/O를 얼마나 줄이느냐에 달려 있습니다.

SSD vs HDD

• SSD: DRAM보다는 느리지만 HDD보다 약 1,000배 빠릅니다.
• 랜덤 I/O: 순차 I/O에서는 SSD와 HDD의 차이가 크지 않을 수 있지만, DBMS의 핵심인 랜덤 I/O에서는 SSD가 압도적으로 빠릅니다.

랜덤 I/O vs 순차 I/O

• 순차 I/O: 책꽂이에서 책을 한 번에 쭉 꺼내는 것과 같습니다. 헤더를 한 번만 움직여 여러 페이지를 읽습니다. (예: Full Table Scan)
• 랜덤 I/O: 책꽂이의 여기저기서 책을 하나씩 꺼내는 것과 같습니다. 데이터를 읽을 때마다 헤더(혹은 SSD의 컨트롤러)가 위치를 다시 잡아야 하므로 부하가 큽니다. (예: Index Range Scan 후 데이터 파일 접근)
• 핵심: 쿼리 튜닝은 랜덤 I/O를 순차 I/O로 바꾸거나(Index Grouping), 랜덤 I/O 자체를 줄이는(Covering Index) 과정입니다.

B-Tree 인덱스

인덱스는 쓰기(INSERT, UPDATE, DELETE) 성능을 희생하고 읽기 성능을 높이는 기능입니다. B-Tree는 가장 범용적인 인덱스 알고리즘입니다.

구조 및 특성

• 구조: 루트(Root) → 브랜치(Branch) → 리프(Leaf) 노드로 구성됩니다. 리프 노드에는 실제 데이터 레코드의 주소(또는 PK)가 저장됩니다.
• 정렬: 인덱스 키는 항상 정렬된 상태를 유지합니다. 반면 실제 데이터 파일(Heap)은 정렬되어 있지 않습니다 (InnoDB 제외).
• InnoDB의 특수성 (Double Lookup):
  • InnoDB 테이블은 PK 순서대로 정렬되어 저장(클러스터링)됩니다.
  • 세컨더리 인덱스(Secondary Index)의 리프 노드에는 물리 주소가 아닌 PK 값이 저장되어 있습니다.
  • 검색 과정: 세컨더리 인덱스 탐색 → PK 값 획득 → PK 인덱스(클러스터링 인덱스) 탐색 → 실제 레코드 획득. 즉, 인덱스를 두 번 타야 합니다.

인덱스 키 추가/삭제/변경 비용

• 비용 산정: 레코드 추가 비용이 1이라면, 인덱스 하나당 추가 비용은 약 1.5 정도로 계산합니다. 인덱스가 3개라면 1 + 1.5*3 = 5.5 정도의 비용이 발생합니다.
• 체인지 버퍼 (Change Buffer):
  • InnoDB는 인덱스에 데이터를 즉시 넣지 않고, 임시 버퍼(Change Buffer)에 저장했다가 나중에 유휴 시간에 디스크에 병합(Merge)합니다. 이를 통해 쓰기 지연을 방지합니다.
  • 주의: 유니크 인덱스(Unique Index)는 중복 여부를 즉시 확인해야 하므로 체인지 버퍼를 사용할 수 없습니다. (쓰기 성능 저하의 원인)
• 변경: 인덱스 키 값은 수정이 불가능합니다. 따라서 기존 키 삭제(마킹) → 새로운 키 삽입의 과정을 거칩니다.

검색 시 주의사항 (인덱스를 못 쓰는 경우)

다음과 같은 쿼리는 인덱스를 타지 못하고 Full Table Scan을 유발합니다.

1. 키 값의 뒷부분만 검색 (%hn)

   SELECT * FROM employee WHERE name LIKE '%hwan'; -- (X) 앞부분을 모르므로 스캔 불가능
   

2. 인덱스 컬럼에 변형을 가한 경우 (WHERE func(column) = ...)

   -- (X) salary 컬럼을 가공해서 비교
   SELECT * FROM employee WHERE salary * 10 > 150000;
       
   -- (O) 값을 가공해서 비교해야 함
   SELECT * FROM employee WHERE salary > 150000 / 10;
   

3. 타입 불일치로 인한 암묵적 형변환

   -- string_col이 VARCHAR 타입일 때
   SELECT * FROM table WHERE string_col = 12345; -- (X) 컬럼이 숫자로 변환되어 비교됨
   

4. 부정 비교 (<>, NOT IN)

   SELECT * FROM employee WHERE name <> 'Kim'; -- (X) B-Tree는 일치하거나 범위인 것만 찾음
   

인덱스 성능 영향 요소

1. 인덱스 키 값의 크기

• 인덱스는 페이지(Page, 기본 16KB) 단위로 관리됩니다.
• 키 값이 커지면(예: 긴 VARCHAR), 한 페이지에 담을 수 있는 인덱스 키 개수가 줄어듭니다.
  • → 디스크 I/O 횟수 증가(같은 양의 데이터를 읽어도 더 많은 페이지를 디스크에서 읽어야 합니다.)
  • → InnoDB 버퍼 풀의 캐시 효율 저하(InnoDB 버퍼 풀(메모리)에 캐시할 수 있는 레코드 수가 줄어듭니다.)

2. B-Tree 깊이 (Depth)

• 키 값이 커지면 같은 데이터 양이라도 B-Tree의 깊이가 깊어집니다. (Depth 3인 경우 약 2천만~수억 건 저장 가능)
• 깊이가 깊을수록 디스크 읽기 횟수가 늘어납니다.(깊이가 1단계 깊어질 때마다 디스크 랜덤 I/O가 1회씩 더 발생합니다.)

3. 카디널리티 (Cardinality)

인덱스 설계의 가장 중요한 기준입니다.

• 중복된 값이 적을수록(유니크할수록) 카디널리티가 높다고 합니다.
• 높은 카디널리티 = 검색 대상 범위 축소 = 성능 향상.
• 예시:
  • 성별 (남/여): 카디널리티 2. (낮음 → 인덱스 효율 최악)
  • 주민번호: 카디널리티 = 행 개수. (높음 → 인덱스 효율 최상)
• 원리: 카디널리티가 높아야 검색 범위를 좁혀서(Narrowing) 불필요한 I/O를 줄일 수 있습니다.

4. 읽어야 하는 레코드 건수

• 인덱스를 거쳐 읽는 것은 직접 테이블을 읽는 것보다 약 4~5배 비쌉니다.
• 전체 테이블의 20~25% 이상을 읽어야 한다면, 인덱스를 타지 않고 풀 스캔(Full Scan) 후 필터링하는 것이 더 효율적입니다.(인덱스를 통해 레코드를 1건 읽는 비용 $\approx$ 테이블에서 직접 4~5건 읽는 비용이므로 레코드의 20~25% 이상을 읽어야 한다면, 인덱스를 쓰는 것보다 강제로 인덱스를 안 쓰고(Ignore Index) 풀 스캔하는 것이 더 빠릅니다.
  • 예: “전체 사원의 50%가 ‘서울’에 산다.” → WHERE city = '서울' 인덱스는 비효율적.)

인덱스 스캔 방식

1. 인덱스 레인지 스캔 (Index Range Scan)

• 검색해야 할 범위가 결정됐을 때 사용하는 가장 빠른 방식입니다. (<, >, IS NULL, BETWEEN, IN, LIKE 'abc%')
• 루트 → 브랜치 → 리프 탐색 후, 리프 노드에서 필요한 만큼 순차적으로 스캔합니다.

2. 인덱스 풀 스캔 (Index Full Scan)

• 인덱스의 처음부터 끝까지 읽는 방식입니다.
• 테이블 풀 스캔보다는 효율적이지만(인덱스 크기가 더 작으므로), 아주 효율적인 방식은 아닙니다.
• 언제 쓰나?: 쿼리가 인덱스 컬럼만 필요로 하는데(커버링 인덱스), 검색 조건이 인덱스 첫 번째 컬럼이 아닌 경우.
  • 예: 인덱스 (A, B, C) / 쿼리 SELECT B FROM table WHERE C = 'x'

3. 루스 인덱스 스캔 (Loose Index Scan)

• 중간에 필요 없는 인덱스 키는 건너뛰며 읽습니다.
• 용도: 주로 GROUP BY 또는 MIN() / MAX() 최적화.
  • 예: GROUP BY dept_no를 하면, 각 dept_no의 첫 번째 레코드만 읽고 건너뜁니다.

4. 인덱스 스킵 스캔 (Index Skip Scan) (MySQL 8.0+)

• 상황: 복합 인덱스 (A, B)가 있을 때, WHERE B = '값' 처럼 선행 컬럼 A가 조건에 없음.
• 동작: 옵티마이저가 A 컬럼의 유니크한 값들을 추출해서, 마치 WHERE A=값1 AND B=값, WHERE A=값2 AND B=값 쿼리를 실행하는 것처럼 최적화합니다.
• 제약: A 컬럼의 카디널리티가 낮아야(종류가 적어야) 효과가 있습니다. A가 주민번호처럼 유니크하다면 비효율적입니다.

다중 컬럼(복합) 인덱스

두 개 이상의 컬럼으로 구성된 인덱스입니다. 순서(Order)가 생명입니다.

• 원리: 첫 번째 컬럼으로 정렬되고, 첫 번째 컬럼 값이 같으면 두 번째 컬럼으로 정렬됩니다.
• 예시: INDEX idx_team_user (team_id, user_id)
  • WHERE team_id = 1 AND user_id = 5 (O, 아주 빠름)
  • WHERE team_id = 1 (O, 빠름)
  • WHERE user_id = 5 (X, 인덱스 못 탐 - 스킵 스캔 제외)
• 설계 팁:
  1. WHERE 절에서 자주 같이 사용되는가?
  2. = (Equal) 조건으로 비교되는 컬럼을 앞쪽에 둡니다.
  3. 카디널리티가 높은(유니크한) 컬럼을 앞쪽에 두는 것이 대체로 유리합니다.

클러스터링 인덱스 (Clustering Index)

PK(Primary Key) 자체가 데이터의 저장 위치를 결정합니다. “책의 페이지 번호”와 같습니다.

구분	클러스터링 인덱스 (PK)	세컨더리 인덱스 (Non-Clustered)
저장 내용	리프 노드에 모든 컬럼 데이터 포함	리프 노드에 PK 값만 포함
정렬	PK 순서대로 물리적 저장	인덱스 키 순서대로 정렬
개수	테이블당 1개만 존재 가능	여러 개 생성 가능

PK가 없다면?

1. NOT NULL Unique Index 중 첫 번째를 선택.
2. 없으면 내부적으로 자동 증가 컬럼(Gen_Clust_Index)을 생성하여 대체.

장단점

• 장점: PK 범위 검색(BETWEEN)이 매우 빠릅니다. 인접한 페이지를 순차적으로 읽기 때문입니다.
• 단점:
  1. PK가 변경되면 레코드의 물리적 위치가 이동해야 합니다. (비용 큼)
  2. 모든 세컨더리 인덱스가 PK 값을 가지고 있으므로, PK 크기가 커지면 전체 인덱스 크기가 비대해집니다.

UUID를 PK로 쓸 때의 문제점 분산 환경에서 UUID를 PK로 많이 쓰는데, MySQL InnoDB에서는 치명적일 수 있습니다.

• UUID는 랜덤하므로 INSERT 시 매번 엉뚱한 위치의 페이지를 메모리로 불러와야 합니다. (랜덤 I/O 폭발)
• 인덱스 페이지가 꽉 찼을 때 중간에 끼어들면 페이지 분할(Page Split)이 발생해 성능이 급격히 저하됩니다.
• 대안: TSID나 ULID처럼 정렬 가능한(Time-sorted) ID를 사용하는 것이 좋습니다.

커버링 인덱스 (Covering Index)

쿼리가 필요한 모든 컬럼을 인덱스가 가지고 있어서, 실제 데이터 블록을 읽지 않고 인덱스만 읽고 끝내는 경우입니다.

-- 인덱스: (userid, age)
-- userid로 찾고, age도 인덱스에 있으니 실제 테이블 안 가고 바로 반환합니다.
SELECT age FROM employee WHERE userid = 'test';

유니크 인덱스 vs 일반 인덱스

• 읽기 성능: 차이가 거의 없습니다. (유니크는 1건 찾고 종료, 일반은 더 찾을지 확인하는 CPU 연산 차이 정도)
• 쓰기 성능: 유니크 인덱스는 중복 체크 과정이 필요하며, 이 때문에 체인지 버퍼(Change Buffer)를 사용하지 못해 더 느립니다.
• 결론: 유일성이 꼭 필요한 게 아니라면 성능을 위해 일반 인덱스를 사용하는 것이 좋습니다.

외래키 (Foreign Key)

• 외래키 생성 시 연관 테이블 컬럼에 인덱스가 자동 생성됩니다.
• 잠금 전파 (Lock Propagation):
  • 자식 테이블을 변경할 때 부모 테이블의 해당 레코드에 쓰기 잠금이 걸려 있으면 해제될 때까지 대기합니다.
  • 외래키와 무관한 컬럼 변경 시에는 잠금 대기가 발생하지 않습니다.

기타 인덱스

• R-Tree 인덱스: 2차원 공간 데이터 저장 및 검색 (ST_Contains 등).
• 전문 검색 인덱스 (Full-text): 문서 내용 전체를 분석하여 인덱싱.
• 함수 기반 인덱스: 값을 변형(함수 적용)한 결과로 인덱스 생성.
• 멀티 밸류 인덱스: JSON 배열 처럼 하나의 레코드가 여러 인덱스 키를 가질 때 사용.

정리

1. 랜덤 I/O 줄이기: 인덱스를 타게 해서 필요한 데이터만 읽거나(Range Scan), 아예 인덱스만 읽게(Covering Index) 하세요.
2. 인덱스 다이어트: 쓰기 성능을 잡아먹고 공간을 차지하는 미사용 인덱스를 주기적으로 정리하세요.
3. 복합 인덱스 최적화: WHERE 절 조건의 순서와 컬럼의 카디널리티를 고려해 인덱스 순서를 결정하세요. (= 조건이 범위 조건보다 앞에 와야 함)
4. PK 선정 신중히: PK는 너무 길지 않게, 그리고 가급적 순차적인 값으로 설정하세요. (UUID 주의)
5. 정렬 방향 고려: ORDER BY create_date DESC가 많다면 인덱스도 DESC로 생성하세요. (역순 스캔은 정순보다 약 30% 느립니다)