[MySQL] InnoDB 인덱스 구조(B+Tree, 클러스터링, 복합 인덱스)
개요
인덱스를 타면 빠르다는 말은 절반만 맞는 말이다. 어떤 인덱스를, 어떤 컬럼 순서로, 어떤 데이터 분포에서 만들었는지에 따라 결과는 천차만별이다. PK를 UUID로 잡았다는 이유로 INSERT 성능이 10분의 1이 되기도 하고, 복합 인덱스의 컬럼 순서를 잘못 잡아서 풀스캔이 발생하기도 한다.
핵심 주제는 네 가지다.
- B+Tree가 RDB 인덱스로 선택된 이유
- InnoDB의 클러스터링 인덱스. PK가 곧 데이터의 물리적 정렬 키
- 세컨더리 인덱스. 리프 노드에 PK를 저장하는 구조와 Key Lookup 비용
- 복합 인덱스. 최좌선 접두사 규칙(Leftmost Prefix Rule)과 컬럼 순서 설계
마지막으로 커버링 인덱스, 인덱스 스킵 스캔, ERD 표기법까지 다룬다.
B+Tree가 RDB 인덱스로 선택된 이유
DB 인덱스는 디스크 기반 자료구조다. 해시 테이블이 더 빠를 것 같지만, RDB가 B+Tree 를 선택한 이유는 다음 세 가지다.
- 디스크 I/O는 페이지 단위로 발생한다. InnoDB의 기본 페이지 크기는 16KB 다. 한 번 디스크에 접근할 때 16KB를 통째로 읽어 와야 한다. 트리의 한 노드를 한 페이지에 맞추면, 노드 하나 접근에 디스크 I/O 한 번이 든다.
- 범위 검색(range scan)이 효율적이다. B+Tree는 리프 노드끼리 링크드 리스트로 연결되어 있어, 범위 시작점을 찾고 나면 다음 리프 페이지를 따라가기만 하면 된다. 해시 인덱스는 정렬되어 있지 않으므로 범위 검색이 불가능하다.
- 노드의 fan-out이 크다. B+Tree는 한 노드에 수백 개의 키를 담을 수 있어 트리 높이가 매우 낮다. 보통 수억 건의 데이터도 트리 깊이가 3~4 레벨이면 충분하다.
리프 노드끼리의 연결이 B+Tree 의 결정적 장점이다. WHERE id BETWEEN 10 AND 50 같은 범위 쿼리에서, 시작점만 트리 탐색으로 찾고 그 다음은 리프 체인을 따라가면 된다.
B-Tree 와 B+Tree 의 차이
| 항목 | B-Tree | B+Tree |
|---|---|---|
| 데이터 저장 위치 | 모든 노드(내부와 리프) | 리프 노드에만 |
| 리프 노드 연결 | 없음 | 링크드 리스트로 연결 |
| 트리 높이 | 상대적으로 높음 | 낮음(내부 노드에 키만) |
| 범위 검색 | 비효율적(트리 순회 필요) | 효율적(리프 체인 순회) |
| 단일 키 검색 | 운 좋으면 내부 노드에서 조기 종료 | 항상 리프까지 도달 |
내부 노드에 데이터를 두지 않고 키만 두기 때문에 한 노드에 더 많은 키가 들어간다. fan-out이 커지고 트리가 얕아진다.
InnoDB 클러스터링 인덱스
InnoDB의 핵심 설계 결정은 PK가 곧 클러스터링 인덱스이며, 데이터의 물리적 정렬 키가 된다는 것이다.
클러스터링 인덱스란
클러스터링 인덱스는 데이터의 물리적 저장 순서 자체를 PK 순서에 맞게 정렬해 둔 인덱스다. 즉 인덱스의 리프 노드가 곧 실제 데이터 행이다. 별도의 데이터 영역이 따로 있는 것이 아니라, 인덱스의 리프 페이지에 행의 모든 컬럼이 함께 저장된다.
PK 조회는 리프 노드가 곧 데이터이므로 트리 탐색 한 번으로 모든 컬럼을 가져올 수 있다.
클러스터링 인덱스의 특징
- 테이블 당 하나만 존재한다. 데이터의 물리적 정렬 기준은 하나뿐이기 때문이다.
- PK가 없으면 InnoDB는 자동으로 정책에 따라 클러스터링 키를 정한다.
- 첫 번째 NOT NULL UNIQUE 인덱스를 사용
- 그것도 없으면 내부적으로 숨겨진 6바이트 키(DB_ROW_ID)를 생성
- 모든 세컨더리 인덱스의 리프 노드는 클러스터링 키(PK)를 저장한다. 그래서 PK 크기는 모든 인덱스의 크기에 영향을 준다.
클러스터링의 조회 이점
PK가 연속된 범위 쿼리는 한 번의 페이지 접근으로 여러 레코드를 가져올 수 있다.
만약 클러스터링되지 않은 힙(heap) 저장 방식이라면 같은 쿼리가 다음과 같이 동작한다.
이것이 InnoDB의 PK 기반 범위 검색이 매우 빠른 이유다.
클러스터링 인덱스의 약점(Page Split)
PK 순서를 따르지 않는 위치에 INSERT가 들어오거나, 페이지가 가득 찬 상태에서 추가 INSERT가 발생하면 페이지 분할(Page Split)이 일어난다. 페이지의 절반을 새 페이지로 옮기고 트리를 갱신해야 하므로 비용이 든다.
페이지 분할은 다음을 유발한다.
- 디스크 I/O 증가
- 트리 재구성 비용
- 페이지 단편화(fragmentation). 같은 양의 데이터가 더 많은 페이지를 차지
PK 선택이 모든 것을 결정한다
PK는 단순한 식별자가 아니다. InnoDB에서 PK 는 다음 세 가지 역할을 동시에 한다.
- 클러스터링 인덱스의 정렬 키. 데이터가 디스크에 어떤 순서로 놓이는지를 결정한다.
- 모든 세컨더리 인덱스의 리프 노드 값. 세컨더리 인덱스는 리프에 PK를 저장한다.
- 고유 식별자. 행을 유일하게 가리키는 값.
이 세 가지 역할이 결합되면서 PK 선택은 INSERT 성능, 디스크와 메모리 사용량, 모든 인덱스의 크기에 광범위한 영향을 준다.
정수형 PK 와 랜덤 문자열 PK
| 구분 | 예시 | INSERT 위치 | Page Split | 결과 순서 |
|---|---|---|---|---|
| 정수형 (auto_increment) | BIGINT AUTO_INCREMENT | 항상 트리의 오른쪽 끝 | 거의 발생 안 함 | 삽입 순서와 PK 순서가 일치 |
| 랜덤 문자열 | UUID v4 | 매번 트리 내부 임의 위치 | 자주 발생 | 삽입 순서와 PK 순서가 다름 |
정수형 PK(auto_increment)
삽입 순서대로 PK가 증가하므로 새 행은 항상 트리의 가장 오른쪽 끝 페이지에 추가된다. 페이지 분할이 거의 발생하지 않는다.
INSERT INTO users VALUES (1, 'Alice');
INSERT INTO users VALUES (2, 'Bob');
INSERT INTO users VALUES (3, 'Carol');
SELECT * FROM users;+----+-------+
| id | name |
+----+-------+
| 1 | Alice |
| 2 | Bob |
| 3 | Carol |
+----+-------+ORDER BY 없이 SELECT * 를 하면 InnoDB는 클러스터링 인덱스 리프 체인을 순서대로 읽는다. 결과는 삽입 순서와 일치한다.
랜덤 문자열 PK(UUID v4)
UUID v4는 시간 정보가 없는 완전 랜덤 값이다. 매번 트리 내부 임의 위치에 INSERT가 일어나므로 페이지 분할이 빈번하게 발생한다.
INSERT INTO users VALUES ('f47ac10b-...', 'Alice');
INSERT INTO users VALUES ('1a2b3c4d-...', 'Bob');
INSERT INTO users VALUES ('9e8d7c6b-...', 'Carol');
SELECT * FROM users;+--------------+-------+
| id | name |
+--------------+-------+
| 1a2b3c4d-... | Bob | (UUID 사전순으로 가장 빠름)
| 9e8d7c6b-... | Carol |
| f47ac10b-... | Alice | (가장 먼저 삽입됐지만 사전순 가장 뒤)
+--------------+-------+결과 순서는 UUID 의 사전순(lexicographic order)이다. 삽입 순서와 무관하다. 그리고 매 INSERT 마다 페이지 분할이 발생할 가능성이 높아 정수형 PK 대비 INSERT 성능이 크게 떨어진다.
UUID 를 PK 로 써야 할 때의 대안
분산 시스템에서 ID 충돌을 피하려면 UUID같은 전역 유일 키가 필요할 때가 있다. 이 경우 다음 대안을 고려한다.
- UUID v7: 앞부분에 타임스탬프가 들어가 시간순으로 정렬된다. 페이지 분할 빈도가 낮아진다.
- ULID와 KSUID: UUID v7과 같은 시간 정렬 전제. Base32 인코딩이라 인덱스가 약간 더 크다.
- Snowflake ID: 분산 환경에서 발급되는 시간 정렬 정수형 ID. BIGINT에 담을 수 있어 크기도 작다.
- 내부 BIGINT PK 와 외부 UUID 컬럼 UNIQUE: DB 내부에는 BIGINT PK 를 두고, 외부에 노출되는 ID만 별도 UUID 컬럼으로 둔다. 두 인덱스를 유지하는 비용은 있지만 INSERT 성능을 챙긴다.
PK 크기가 모든 인덱스에 영향을 준다
세컨더리 인덱스의 리프 노드는 (인덱스 컬럼들, PK) 쌍을 저장한다. PK가 크면 모든 세컨더리 인덱스가 비례하여 커진다.
- BIGINT(8바이트) PK: 세컨더리 인덱스 리프 노드 크기 = (인덱스 키 + 8B)
- UUID(16바이트 BINARY) PK: 세컨더리 인덱스 리프 노드 크기 = (인덱스 키 + 16B)
- UUID(36자 VARCHAR) PK: 세컨더리 인덱스 리프 노드 크기 = (인덱스 키 + 36B 이상)
테이블에 세컨더리 인덱스가 5개 있고 1억 건의 데이터가 있다면, PK 크기 차이는 그대로 GB 단위의 저장 공간 차이로 나타난다. 그리고 그 인덱스들이 버퍼 풀에 들어갈 수 있는지 여부에 영향을 준다.
세컨더리 인덱스의 구조
세컨더리 인덱스(non-clustered index, secondary index)는 PK가 아닌 다른 컬럼에 만드는 인덱스다. InnoDB의 세컨더리 인덱스 리프 노드는 인덱스 컬럼 값과 PK를 저장한다. 실제 행 데이터는 없다.
세컨더리 인덱스 조회(Key Lookup)
세컨더리 인덱스로 검색하면 조회 흐름이 두 단계가 된다.
- 세컨더리 인덱스 트리를 탐색해서 조건에 맞는 PK를 얻는다.
- 얻은 PK 로 클러스터링 인덱스를 다시 탐색해서 실제 행 데이터를 읽는다.
이 2단계 탐색을 Key Lookup 이라 부른다.
테이블 랜덤 액세스의 비용
Key Lookup으로 가져오는 데이터가 많으면 매 행마다 디스크 I/O 가 발생할 수 있다. 이를 테이블 랜덤 액세스(Table Random Access)라 한다.
- Single Block I/O: 한 번의 시스템 콜에 한 블록(페이지)만 읽는다. 세컨더리 인덱스 탐색과 Key Lookup이 모두 이 방식이다.
- Multi Block I/O: 한 번의 시스템 콜에 연속된 여러 블록을 한꺼번에 읽는다. 대량 순차 읽기에 매우 효율적.
만약 인덱스로 가져와야 할 데이터가 전체의 15~20% 를 넘어가면, 수많은 Single Block I/O보다 풀스캔(Multi Block I/O)이 빠를 수 있다. 이 때문에 옵티마이저는 의도적으로 인덱스를 포기하고 풀스캔을 선택하기도 한다.
-- name 인덱스가 있지만, 전체의 30% 가 'Kim' 이라면
SELECT * FROM users WHERE name = 'Kim';
-- 옵티마이저가 풀스캔 선택EXPLAIN 에서 type: ALL(풀스캔)이 나오는데 인덱스가 있는데도 안 타는 경우, 데이터 분포 때문에 옵티마이저가 일부러 포기한 것일 수 있다.
복합 인덱스와 B+Tree 정렬
복합 인덱스는 두 개 이상의 컬럼을 묶어 만드는 인덱스다.
CREATE INDEX idx_name_age ON users (name, age);이 인덱스의 B+Tree 는 (name, age) 순서로 정렬된다. 먼저 name으로, name이 같으면 age로 정렬한다.
복합 인덱스의 리프 노드 정렬 방식
users 테이블에 다음 데이터가 있다고 하자.
+----+------+-----+
| PK | name | age |
+----+------+-----+
| 1 | Park | 33 |
| 2 | Kim | 25 |
| 3 | Lee | 28 |
| 4 | Cho | 22 |
| 5 | Kim | 20 |
| 6 | Lee | 40 |
| 7 | Cho | 35 |
| 8 | Kim | 30 |
| 9 | Park | 22 |
+----+------+-----+(name, age) 복합 인덱스의 리프 노드는 다음 순서로 정렬된다.
- (Cho, 22, PK=4)
- (Cho, 35, PK=7)
- (Kim, 20, PK=5)
- (Kim, 25, PK=2)
- (Kim, 30, PK=8)
- (Lee, 28, PK=3)
- (Lee, 40, PK=6)
- (Park, 22, PK=9)
- (Park, 33, PK=1)
- name은 전체적으로 정렬되어 있다. Cho, Kim, Lee, Park 순서.
- age는 같은 name 안에서만 정렬되어 있다. Ki 안에서는 20, 25, 30이지만 전체적으로는 정렬되어있지 않음
최좌선 접두사 규칙(Leftmost Prefix Rule)
복합 인덱스는 선행 컬럼이 조건에 포함되지 않으면 인덱스를 활용할 수 없다. INDEX (name, age, city) 인덱스에 대한 WHERE 조건별 사용 가능 여부는 다음과 같다.
| 쿼리 조건 | 인덱스 사용 |
|---|---|
WHERE name = 'Kim' | O |
WHERE name = 'Kim' AND age = 25 | O |
WHERE name = 'Kim' AND age = 25 AND city = 'Seoul' | O (커버 가능) |
WHERE age = 25 | X (선행 컬럼 누락) |
WHERE age = 25 AND city = 'Seoul' | X (선행 컬럼 누락) |
WHERE name = 'Kim' AND city = 'Seoul' | 부분(name 만 사용, city 는 필터링) |
age = 25 만으로는 인덱스를 탈 수 없다. age 가 전체적으로 정렬되어 있지 않으므로 어디서부터 탐색할지 알 수 없기 때문이다.
마지막 경우(name = 'Kim' AND city = 'Seoul')는 name 으로 인덱스를 타고 Kim 영역으로 좁힌 뒤, 그 안에서 city를 필터링한다. age가 빠졌으므로 city는 인덱스의 정렬 이점을 잃고 단순 필터링이 된다.
인덱스 스킵 스캔(Index Skip Scan)
MySQL 8.0.13 부터 도입된 최적화로, 선행 컬럼이 WHERE 조건에 없어도 선행 컬럼의 카디널리티가 낮으면 옵티마이저가 인덱스를 활용할 수 있게 한다.
-- INDEX (gender, age). gender 는 'M' 과 'F' 두 값뿐
SELECT * FROM users WHERE age = 25;원칙적으로는 선행 컬럼 gender 가 없어 인덱스를 사용할 수 없다. 하지만 인덱스 스킵 스캔이 적용되면 옵티마이저가 다음과 같이 변환해서 실행한다.
SELECT * FROM users WHERE gender = 'M' AND age = 25
UNION ALL
SELECT * FROM users WHERE gender = 'F' AND age = 25;선행 컬럼의 고유값이 적을수록 효과적이다. 카디널리티가 높으면 순회 비용이 커져 풀스캔보다 느려질 수 있다. EXPLAIN 에서 Extra: "Using index for skip scan" 으로 확인할 수 있다.
복합 인덱스 컬럼 순서 설계
1. 동등 조건을 앞에, 범위 조건을 뒤에
범위 조건(>, <, BETWEEN)이 걸리면 그 뒤의 컬럼은 인덱스의 정렬 이점을 잃는다. B+Tree 에서 범위 조건이 시작되는 순간 더 이상 다음 컬럼이 정렬된 순서로 나오지 않기 때문이다.
-- INDEX (status, created_at)
WHERE status = 'ACTIVE' AND created_at > '2026-01-01'
-- status 는 동등 조건이라 인덱스 활용. created_at 은 범위. status 로 좁힌 안에서 범위 탐색-- INDEX (created_at, status)
WHERE created_at > '2026-01-01' AND status = 'ACTIVE'
-- created_at 이 범위이므로 그 뒤의 status 는 정렬 이점을 잃음같은 쿼리라도 인덱스 컬럼 순서에 따라 사용 가능 범위가 달라진다.
2. 카디널리티가 높은 컬럼을 앞에
카디널리티(Cardinality)는 컬럼의 고유값 개수를 의미한다. 카디널리티가 높을수록 한 번의 등치 조건으로 더 많은 데이터를 걸러낼 수 있다.
- gender 는 카디널리티 매우 낮음(2개)
- user_id 는 카디널리티 매우 높음(사용자 수)
user_id를 앞에 두면 단번에 검색 범위가 사용자 한 명의 데이터로 좁아진다. 일반적으로 카디널리티 높은 컬럼을 선행 컬럼으로 두는 것이 효율적이다.
다만 인덱스 스킵 스캔이 적용되는 케이스에서는 거꾸로 카디널리티 낮은 컬럼이 앞에 와야 할 수도 있다.
3. ORDER BY와 GROUP BY를 인덱스 정렬과 맞춘다
인덱스 컬럼 순서와 ORDER BY 또는 GROUP BY 컬럼 순서가 일치하면 별도 정렬(filesort)이 필요 없다.
-- INDEX (user_id, created_at)
SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC;
-- created_at 이 user_id 안에서 정렬되어 있으므로 filesort 없이 인덱스 순서 그대로 사용filesort 란
filesort는 옵티마이저가 결과를 정렬하기 위해 인덱스 순서가 아닌 별도 정렬 단계를 수행하는 것이다. 이름과 달리 항상 디스크(파일)를 쓰는 것은 아니다.
- 정렬 대상 데이터가
sort_buffer_size안에 들어가면 메모리에서 퀵소트로 처리한다. - 버퍼를 넘기면 부분적으로 정렬한 청크들을 임시 파일에 쓰고, 병합 정렬(merge sort)로 합친다. 이 단계에서 디스크 I/O 가 발생한다.
즉 filesort 자체가 항상 느린 것은 아니지만, 정렬 대상이 커질수록 임시 파일과 병합 비용이 붙어 급격히 비싸진다. 대량 결과에서 filesort는 큰 비용이다.
인덱스가 정렬을 대체하는 원리
B+Tree 리프 노드는 이미 인덱스 컬럼 순서로 정렬되어 있고, 리프끼리 링크드 리스트로 연결되어 있다. 그래서 정렬 조건이 인덱스 순서와 맞으면 옵티마이저는 리프 체인을 앞에서 뒤로(또는 뒤에서 앞으로) 읽기만 하면 된다. 정렬 연산 자체가 사라진다.
ORDER BY: 리프 체인을 순서대로 읽어 그대로 반환한다.DESC는 체인을 역방향으로 읽는다(Backward Index Scan).GROUP BY: 같은 값이 리프에서 연속으로 모여 있으므로, 이전 행과 값이 바뀌는 지점만 감지하면 그룹을 나눌 수 있다. 정렬용 임시 테이블(Using temporary)이 필요 없다.
인덱스가 없거나 정렬 조건과 어긋나면 반대로 동작한다.
ORDER BY→ 결과를 모두 모은 뒤 filesort 로 정렬한다.GROUP BY→ 그룹핑을 위해 임시 테이블을 만들거나 filesort 를 수행한다. EXPLAIN에Using temporary; Using filesort가 함께 나온다.
인덱스 정렬이 적용되는 조건
인덱스가 있어도 아래를 어기면 filesort 로 떨어진다.
| 조건 | 설명 |
|---|---|
| 최좌선 접두사 | ORDER BY/GROUP BY 컬럼이 인덱스 선행 컬럼부터 이어져야 한다 |
| 정렬 방향 일관성 | MySQL 8.0 미만은 ORDER BY a ASC, b DESC 처럼 방향이 섞이면 filesort. 8.0 부터는 인덱스를 (a ASC, b DESC) 로 만들면 혼합 정렬도 인덱스로 처리 |
| 선행 컬럼 동등 조건 | WHERE 로 고정된 선행 컬럼은 정렬 컬럼에서 빠져도 된다 (아래 예시) |
-- INDEX (user_id, created_at)
-- user_id 가 동등 조건으로 고정되므로 ORDER BY created_at 은 인덱스 정렬 활용
SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at;
-- GROUP BY 도 동일. INDEX (user_id, status) 면
-- status 가 user_id 안에서 정렬되어 있어 임시 테이블 없이 그룹핑
SELECT status, COUNT(*) FROM orders WHERE user_id = 100 GROUP BY status;EXPLAIN의 Extra에 Using filesort 또는 Using temporary 가 나오면 인덱스 정렬을 활용하지 못하고 별도 정렬 단계가 추가된 것이다.
커버링 인덱스
커버링 인덱스(Covering Index)는 쿼리가 필요로 하는 모든 컬럼이 인덱스 안에 포함되어 있어, 실제 데이터 페이지(클러스터링 인덱스)에 접근하지 않고 인덱스만으로 결과를 반환하는 경우다.
-- INDEX (name, age)
SELECT name, age FROM users WHERE name = 'Kim';
-- 인덱스 리프 노드에 name 과 age 가 모두 있으므로 테이블 접근 불필요
세컨더리 인덱스의 Key Lookup이 제거되므로 테이블 랜덤 액세스 비용이 사라진다. 특히 대량 결과를 반환하는 쿼리에서 효과가 크다.
EXPLAIN의 Extra에 Using index 가 나오면 커버링 인덱스가 동작한 것이다.
커버링 인덱스 설계 팁
자주 사용하는 SELECT 컬럼들을 인덱스에 함께 포함시키는 방식이 있다.
-- 기본 인덱스
CREATE INDEX idx_status ON orders(status);
-- 커버링용 확장 인덱스. status 로 검색하고 amount 와 created_at 을 자주 같이 조회한다면
CREATE INDEX idx_status_covering ON orders(status, amount, created_at);다만 인덱스에 컬럼을 추가하면 인덱스 크기가 커지고 INSERT와 UPDATE 비용도 늘어난다. 모든 쿼리를 커버링으로 만들 수는 없으니, 트래픽이 높은 핵심 쿼리에 한해 적용한다.
클러스터링 인덱스와 세컨더리 인덱스 정리
| 구분 | 클러스터링 인덱스 | 세컨더리 인덱스 |
|---|---|---|
| 개수 | 테이블 당 1개 | 테이블 당 여러 개 |
| 정렬 기준 | 물리적 데이터 정렬 | 논리적 인덱스 정렬 |
| 리프 노드 | 실제 데이터 행 | 인덱스 키와 PK |
| 조회 단계 | 트리 탐색 1번 | 트리 탐색 2번(Key Lookup) |
| 범위 검색 속도 | 매우 빠름(페이지 연속) | 비교적 빠름 |
| CUD 비용 | 페이지 분할 시 비쌈 | 인덱스 페이지만 수정 |
| 추가 공간 | 없음(데이터 자체) | 별도 인덱스 페이지 필요 |
ERD 표기법(인덱스와 관계의 시각화)
테이블의 PK, FK, 관계를 시각화할 때 사용하는 ERD(Entity Relationship Diagram) 표기법을 정리한다. 실무에서 가장 많이 쓰이는 것은 IE(Crow’s Foot) 표기법이다.
카디널리티 표기
| 기호 | 의미 |
|---|---|
| ` | |
| ` | o또는o |
| ` | {또는} |
o{ 또는 }o | 0개 이상 (선택, Many) |
Identifying 관계와 Non-Identifying 관계
| 관계 종류 | 선 유형 | 의미 |
|---|---|---|
| Identifying | 실선 | 부모 PK 가 자식 PK 의 일부 (강한 연관) |
| Non-Identifying | 점선 | 부모 PK 가 자식의 FK 로만 사용 (약한 연관) |
-- Identifying: order_item 은 order 없이 존재 불가
CREATE TABLE order_item (
order_id BIGINT NOT NULL,
item_seq INT NOT NULL,
product_id BIGINT NOT NULL,
PRIMARY KEY (order_id, item_seq), -- 부모 PK 가 복합 PK 의 일부
FOREIGN KEY (order_id) REFERENCES orders(id)
);
-- Non-Identifying: orders 는 자체 PK 를 갖고, user_id 는 단순 FK
CREATE TABLE orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);이 두 관계의 차이는 PK 설계에도 영향을 준다. Identifying 관계는 자식 테이블의 PK 가 복합 PK 가 되므로 모든 세컨더리 인덱스 리프가 커진다. Non-Identifying 은 자식 테이블이 자체 BIGINT PK 를 가지므로 인덱스 크기가 작아진다.
주의사항과 베스트프랙티스
1. SELECT 별표 남용은 커버링 인덱스를 죽인다
SELECT * 는 모든 컬럼을 가져오므로 인덱스만으로는 절대 만족할 수 없다. 항상 Key Lookup이 발생한다. 필요한 컬럼만 명시하면 커버링 인덱스가 활용될 가능성이 생긴다.
2. 인덱스 컬럼 함수 적용 시 인덱스 무효화
-- 인덱스 활용 X
SELECT * FROM users WHERE YEAR(created_at) = 2026;
-- 인덱스 활용 O
SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';WHERE 절의 인덱스 컬럼에 함수가 적용되면 옵티마이저는 인덱스를 사용할 수 없다. 함수 결과의 정렬 순서가 원래 컬럼의 정렬 순서와 다르기 때문이다. MySQL 8.0 부터는 함수 기반 인덱스로 우회할 수 있다.
CREATE INDEX idx_year ON users ((YEAR(created_at)));3. 인덱스가 많으면 INSERT 와 UPDATE 비용이 늘어난다
세컨더리 인덱스가 N 개면 INSERT 한 번에 N+1 개의 트리(클러스터링 인덱스와 세컨더리 인덱스 N 개)에 모두 키를 삽입해야 한다. 인덱스를 무작정 추가하면 쓰기 성능이 떨어진다.
4. PK 는 BIGINT auto_increment를 기본으로
특별한 이유가 없다면 PK 는 단조 증가하는 BIGINT 로 두는 것이 가장 안전하다. 페이지 분할이 거의 없고, 인덱스 크기가 작으며, 결과 순서도 직관적이다. 분산 시스템 등 외부 노출용으로 UUID 가 필요하면 별도 컬럼으로 두자.
5. NULL 은 인덱스를 탈 수 있지만 제한이 있다
InnoDB 는 NULL 값을 인덱스에 포함시키지만, IS NULL 조건이 인덱스를 잘 탈지는 데이터 분포에 따라 다르다. NULL 이 컬럼의 대부분이라면 인덱스를 타도 풀스캔과 비슷한 비용이 된다.
6. 인덱스 통계가 어긋나면 옵티마이저가 잘못된 선택을 한다
ANALYZE TABLE 로 통계를 갱신해야 정확한 카디널리티 추정이 가능하다. 대량의 INSERT 와 DELETE 후 옵티마이저가 이상한 실행 계획을 고르면 통계가 어긋난 것일 수 있다.
ANALYZE TABLE users;7. EXPLAIN을 습관적으로 확인하라
새 인덱스를 추가하거나 쿼리를 작성했을 때는 항상 EXPLAIN 으로 실행 계획을 확인한다. type, key, rows, Extra 네 컬럼만 봐도 인덱스가 의도대로 사용됐는지 알 수 있다.
| EXPLAIN 컬럼 | 좋은 값 | 나쁜 값 |
|---|---|---|
type | const, eq_ref, ref, range | ALL(풀스캔), index(인덱스 풀스캔) |
key | 의도한 인덱스 | NULL |
rows | 작은 값 | 큰 값(전체 행 수에 가까움) |
Extra | Using index, Using where | Using filesort, Using temporary |
8. 복합 인덱스의 컬럼 순서는 한 번 정하면 바꾸기 어렵다
운영 중인 대용량 테이블에서 인덱스를 추가하거나 변경하는 작업은 큰 비용이다. 처음 인덱스를 설계할 때 쿼리 패턴, 카디널리티, 정렬 요구를 함께 고려해서 결정해야 한다. 무작정 추가하기보다 한 번 깊이 분석하고 결정하는 편이 낫다.
InnoDB의 인덱스 구조는 단순한 자료구조가 아니라 디스크 I/O, 메모리 사용, 동시성, 쓰기 성능까지 좌우하는 시스템 전체의 핵심이다. PK 하나, 컬럼 순서 하나가 시스템 성능을 좌우하기 때문에, 구조를 정확히 이해한 뒤 설계에 임하는 것이 운영 비용을 크게 줄이는 길이다.