[Real MySQL 8.0] 8장. 인덱스

Real MySQL 8.0 – 인덱스 정리

인덱스는 “많이 만들수록 좋은 것”이 아니었고, “언제·어디에·어떤 걸·어떻게 써야 하는지”가 핵심이었음

목차 1. 디스크 I/O 3. B-Tree 구조 6. 읽기 패턴 9. PK 설계 체크리스트

목차

1. 디스크 I/O와 인덱스의 존재 이유
2. 인덱스란 무엇인가 – SortedList vs ArrayList
3. B-Tree 인덱스 구조 – InnoDB vs MyISAM
4. B-Tree 인덱스의 쓰기 동작
5. 인덱스 성능에 영향을 주는 요소
6. B-Tree를 통한 데이터 읽기 패턴
7. 다중 컬럼 인덱스와 Left-most 규칙
8. 기타 인덱스: R-Tree, Fulltext, 함수 기반, 멀티 밸류
9. 클러스터링 인덱스와 InnoDB의 PK 설계
10. 유니크 인덱스와 외래 키
11. 인덱스 설계 실무 체크리스트

1. 디스크 I/O와 인덱스의 존재 이유였음

데이터베이스 성능 튜닝의 출발점은 결국 디스크 I/O, 특히 랜덤 I/O를 얼마나 줄이느냐였음. DBMS는 “테이블의 일부 레코드를 여기저기서 조금씩” 읽는 경우가 많았고, 이 패턴이 랜덤 I/O를 유발했음.

1.1 HDD vs SSD, 그리고 랜덤 I/O였음

• HDD: 플래터 회전 + 헤더 이동의 기계식 구조였음 → 랜덤 I/O가 매우 느렸음
• SSD: 플래시 메모리 기반이었음 → HDD 대비 랜덤 I/O가 훨씬 빨랐음
• 공통점: SSD도 순차 I/O > 랜덤 I/O의 구조는 유지됐음

1.2 순차 I/O vs 랜덤 I/O였음

• 순차 I/O: 연속된 페이지를 한 번에 읽었음 → 헤더 이동이 거의 없었음
• 랜덤 I/O: 서로 다른 위치의 페이지를 여러 번 읽었음 → “페이지 수만큼” 이동이 필요했음
• 결론: 쿼리 튜닝의 목적은 “불필요한 랜덤 I/O를 줄이는 것”이었고 인덱스가 핵심 도구였음

도식

순차 I/O vs 랜덤 I/O 직관이었음

순차 I/O: [P1][P2][P3][P4]  (한 번 읽으면 연속으로 따라왔음)
랜덤 I/O: [P7] [P2] [P9] [P1] (각각 따로 찾아가야 했음)
=> DB는 “필요한 행만 골라 읽는” 패턴이 많아 랜덤 I/O를 자주 만들었음
      

2. 인덱스란 무엇인가 – SortedList vs ArrayList 비유였음

인덱스는 “컬럼 값과 레코드 위치(또는 PK)를 정렬된 상태로 따로 관리하는 구조”였음. 따라서 읽기(검색)는 빨라졌고, 쓰기(정렬 유지)는 느려졌음.

SortedList(인덱스) 관점이었음

• 항상 정렬 상태를 유지했음
• 검색/범위 조회가 매우 빨랐음
• 삽입/삭제/갱신 시 정렬 유지 비용이 들었음

ArrayList(데이터 파일) 관점이었음

• 삽입은 비교적 단순했음(삽입 순서/페이지 단위 관리였음)
• 검색은 조건에 따라 여기저기 뒤져야 했음
• 결국 필터링을 위한 랜덤 I/O가 커졌음

주의사항이었음

• WHERE에 등장한다고 무조건 인덱스를 걸면 안 됐음
• 인덱스가 많을수록 INSERT/UPDATE/DELETE 시 갱신해야 할 구조가 늘었음
• 인덱스 용량이 커지면 버퍼 풀에 담을 수 있는 양이 줄어 전체 성능이 악화될 수 있었음

2.1 인덱스의 여러 관점이었음

역할 기준이었음

• 프라이머리 키(Primary Key)였음
• 세컨더리 인덱스(Secondary Index)였음

저장/용도 기준이었음

• B-Tree 인덱스(일반적이었음)
• Hash 인덱스(동등 비교 특화였고 범위 검색 불가였음)
• Fulltext / R-Tree / 함수 기반 / 멀티 밸류 등이었음

3. B-Tree 인덱스 구조 – InnoDB vs MyISAM이었음

3.1 트리 구조였음

• 루트 노드: 검색 시작점이었음
• 브랜치 노드: 분기/중간 경로였음
• 리프 노드: 실제 (키, 주소/PK)가 저장됐음

도식

B-Tree 기본 형태였음

          [Root]
           /  \
      [Branch][Branch]
        /  \     /  \
     [Leaf][Leaf][Leaf][Leaf]
(Leaf에 키와 포인터(주소/PK)가 저장됐음)
        

3.2 MyISAM vs InnoDB – 세컨더리 인덱스 차이였음

구분	세컨더리 인덱스 리프가 들고 있던 것	조회 경로	성능/설계 포인트였음
MyISAM	물리적 주소(레코드 위치)였음	인덱스 → 데이터 파일로 바로 갔음	주소 기반이라 단순했음
InnoDB	PK 값이었음	세컨더리 → PK 인덱스를 한 번 더 탔음	“PK 2단 조회”가 설계/성능에 중요했음

4. B-Tree 인덱스의 쓰기 동작이었음

4.1 키 추가(INSERT)였음

1. B-Tree를 내려가며 삽입 위치를 탐색했음
2. 리프에 (키, 주소/PK)를 저장했음
3. 노드가 꽉 차면 분할(split)이 발생했고 상위 노드까지 수정이 전파될 수 있었음

4.2~4.3 DELETE/UPDATE였음

• DELETE: 리프에서 삭제 마크 후 재사용/청소가 이뤄졌음
• UPDATE(인덱스 컬럼 변경): 내부적으로 DELETE + INSERT처럼 처리됐음 → 비용이 컸음

4.4 인덱스와 잠금(InnoDB)이었음

• InnoDB는 검색에 사용된 인덱스를 기준으로 레코드 락/넥스트 키 락을 잡았음
• 적절한 인덱스가 있으면 필요한 범위만 잠갔고, 없으면 큰 범위/전체로 잠금이 확장될 수 있었음
• UPDATE/DELETE 조건절 인덱스 부재는 성능뿐 아니라 동시성 자체를 망가뜨릴 수 있었음

5. 인덱스 성능에 영향을 주는 요소였음

5.1 키 크기(길이)였음

• 페이지(기본 16KB) 단위로 인덱스 키를 담았음
• 키가 길면 페이지당 키 수가 줄어 B-Tree 높이가 커졌음
• 높이가 커지면 찾을 때 더 많은 페이지를 랜덤 I/O로 읽게 됐음

실무 팁이었음: PK를 긴 문자열로 잡으면 모든 세컨더리 인덱스에 PK가 포함되어 인덱스가 비대해졌음 → 인조 PK(AI 정수)를 고려했음

5.2~5.4 깊이/선택도/읽기량이었음

• B-Tree 깊이: 루트→리프까지 페이지를 몇 번 읽는지였음(대개 3~5 수준이었음)
• 선택도(기수성): 서로 다른 값이 많을수록(높을수록) 효율이 좋아졌음
• 읽어야 하는 레코드 수: 전체의 20~25% 이상이면 옵티마이저가 풀 스캔을 선택할 수 있었음

핵심

옵티마이저의 직관적 판단(비용 모델) 요약이었음

가정: "인덱스로 1건 읽기 비용" ≒ "테이블에서 4~5건 읽기 비용"이었음
=> 읽어야 할 비율이 커지면(대략 20~25%+) 인덱스보다 풀 스캔 + 필터링이 더 낫다고 판단할 수 있었음
      

6. B-Tree를 통한 데이터 읽기 패턴이었음

6.1 인덱스 레인지 스캔이었음

1. 조건의 시작 위치를 찾았음(탐색)
2. 범위 끝까지 연속적으로 인덱스를 읽었음(스캔)
3. 필요 시 데이터 페이지를 읽었음(커버링이면 생략 가능했음)

SELECT *
FROM employees
WHERE emp_no BETWEEN 10001 AND 10010;
        

6.2~6.4 Full/Loose/Skip Scan이었음

• 인덱스 풀 스캔: 인덱스를 처음부터 끝까지 읽었음(테이블보다 작아 이득일 수 있었음)
• 루스 인덱스 스캔: 중간 키를 건너뛰며 GROUP BY, MIN/MAX 최적화에 쓰였음
• 스킵 스캔: 다중 컬럼 인덱스에서 선행 컬럼 조건이 없어도 제한적으로 활용됐음(조건/제약이 있었음)

핵심

커버링 인덱스가 강력했던 이유였음

인덱스 레인지 스캔:
  (1) 인덱스에서 범위를 읽었음
  (2) 각 키마다 테이블(클러스터링) 페이지를 읽었음  <-- 랜덤 I/O가 늘 수 있었음

커버링 인덱스:
  (1) 인덱스에서 필요한 컬럼까지 모두 얻었음
  (2) 테이블 페이지 접근을 생략했음  <-- 랜덤 I/O를 크게 줄였음
      

7. 다중 컬럼 인덱스와 Left-most 규칙이었음

7.1 다중 컬럼 인덱스 구조였음

다중 컬럼 인덱스는 (column1, column2, ...) 형태로 복합 정렬이 이뤄졌음. 먼저 선행 컬럼 기준으로 정렬되고, 선행 값이 같은 구간에서 후행 컬럼이 정렬됐음.

CREATE INDEX idx_user_region_created_at
ON users (region, created_at);
      

7.2 Left-most 규칙이었음

• 선행 컬럼 조건이 있으면 레인지 스캔이 가능했음
• 선행 컬럼 조건이 없으면 후행 컬럼만으로는 레인지 스캔이 어려웠음(풀 스캔 수준이 될 수 있었음)

가능했음: WHERE region = 'SEOUL'

비효율적일 수 있었음: WHERE created_at > '2024-01-01'

최적이었음: WHERE region = 'SEOUL' AND created_at > '2024-01-01'

7.3 인덱스가 잘 안 걸리는 패턴이었음

• <>, NOT IN, NOT BETWEEN, IS NOT NULL 같은 부정 조건이었음
• LIKE '%abc'처럼 앞부분이 고정되지 않는 패턴이었음
• 인덱스 컬럼을 함수/연산으로 감싼 조건이었음(예: SUBSTRING(col,1,1))
• 타입이 다른 비교로 형변환이 발생하는 경우였음
• MySQL은 NULL도 인덱스에 저장하므로 WHERE col IS NULL은 인덱스를 사용할 수 있었음

8. 기타 인덱스: R-Tree, Fulltext, 함수 기반, 멀티 밸류였음

8.1 R-Tree(공간 인덱스)였음

2차원 공간(좌표/도형)을 위한 인덱스였고, MBR(최소 경계 사각형) 개념을 기반으로 했음.

• 위도/경도 기반 검색에 쓰였음
• 좌표 시스템을 쓰는 도메인(CAD 등)에 쓰였음

8.2 Fulltext(전문 검색)였음

긴 텍스트 본문에서 단어를 찾기 위한 인덱스였음. B-Tree로는 효율이 낮아 별도 구조가 필요했음.

• 어근 분석 기반, 혹은 n-gram 기반으로 인덱싱했음
• MATCH(...) AGAINST(...) 문법을 사용해야 했음

8.3 함수 기반 인덱스였음

컬럼을 가공한 결과에 인덱스를 만들고 싶을 때 가상 컬럼/표현식을 활용했음.

ALTER TABLE employees
  ADD COLUMN full_name VARCHAR(32)
    GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
  ADD INDEX idx_full_name (full_name);
      

8.4 멀티 밸류 인덱스였음

레코드 1건이 여러 인덱스 키를 가질 수 있었음. 대표적으로 JSON 배열 각 원소를 인덱싱하는 형태였음. SQL에서 배열/문서형 데이터를 다루기 위한 기능으로 이해하면 됐음.

9. 클러스터링 인덱스와 InnoDB의 PK 설계였음

9.1 클러스터링 인덱스란 무엇이었음?

• InnoDB에서 PK가 곧 클러스터링 인덱스였음
• 클러스터링 인덱스의 리프에 행 전체가 저장됐음
• PK 변경/랜덤 삽입은 물리적 재배치가 수반되어 비쌌음

9.2 세컨더리 인덱스에 미친 영향이었음

• 세컨더리 인덱스 리프는 “주소”가 아니라 PK 값을 저장했음
• 세컨더리 인덱스 조회는 PK를 한 번 더 타야 했음

9.3 장점이었음

• PK 기반 검색이 매우 빨랐음
• PK 범위 조회도 효율적이었음
• 커버링 인덱스가 성립하면 테이블 접근을 생략할 수 있었음

9.3 단점이었음

• 모든 세컨더리 인덱스에 PK가 포함되어 인덱스 크기가 커졌음
• 세컨더리 조회 시 PK를 한 번 더 조회해야 했음
• PK 변경/랜덤 PK 삽입은 느렸음

9.4 PK 설계 시 주의사항이었음

• PK를 명시했음(내부 숨은 PK는 활용하기 어려웠음)
• PK가 길면 세컨더리 인덱스가 전부 비대해졌음
• INSERT 위주(append-only)라면 단조 증가 정수 PK가 유리했음

10. 유니크 인덱스와 외래 키였음

10.1 유니크 인덱스였음

• 인덱스이면서 제약 조건이었음(중복 불가였음)
• 읽기 성능은 일반 인덱스와 큰 차이가 없었음
• 쓰기 시 중복 체크가 필요해 더 느릴 수 있었고, 잠금/데드락 리스크가 있었음

10.2 외래 키와 인덱스/잠금이었음

• 외래 키를 만들면 참조 컬럼에 인덱스가 자동 생성될 수 있었음
• 자식 쓰기 시 부모 레코드 확인 잠금이 필요했음
• 부모 변경 시 자식 테이블까지 잠금 경합이 커질 수 있었음

외래 키는 무결성에는 유리했지만, 잠금 경합과 동시성까지 함께 고려해야 하는 설계 요소였음.

11. 인덱스 설계 실무 체크리스트였음

A. 만들기 전에 물어볼 것들이었음

• 읽기 이득 > 쓰기 비용인지 검토했음
• WHERE/JOIN/ORDER BY/GROUP BY에 자주 등장하는지 확인했음
• 다중 컬럼이면 선행 컬럼 순서가 실제 쿼리 패턴과 맞는지 점검했음
• 키가 너무 길지 않은지(특히 PK) 확인했음
• 조회 비율이 20~25%를 넘는다면 풀 스캔이 더 나은지 고민했음

B. 운영/동시성 관점이었음

• UPDATE/DELETE 조건에 적절한 인덱스가 있어 잠금 확장을 피할 수 있는지 봤음
• 유니크 인덱스를 “성능” 이유로 남발하지 않았음
• 외래 키로 불필요한 잠금 경합이 커지지 않는지 점검했음
• 커버링 인덱스/정렬 최적화가 실제로 필요한지 EXPLAIN으로 확인했음

인덱스는 “한 번 만들면 무조건 좋은” 존재가 아니었음. 실제 쿼리 패턴, 읽기/쓰기 비율, 동시성 요구사항까지 같이 보고 설계해야 했음.