MySQL InnoDB EXPLAIN과 인덱스 구성 전략

InnoDB 테이블은 PK로 정렬된 B+Tree 구조로 저장된다. 이 구조를 이해하면 Secondary Index, Covering Index, Bookmark Lookup이 무엇을 의미하는지, 그리고 EXPLAIN이 보여주는 실행 경로가 왜 그렇게 나오는지 한눈에 보인다.

InnoDB의 테이블 구조

InnoDB에서 테이블은 곧 Clustered Index다. 다른 말로 Index-Organized Table이라고도 부르는데, PostgreSQL의 힙 테이블과 갈라지는 핵심 차이다.

flowchart TD
    R["Root (PK B+Tree)"] --> I1["Internal node"]
    R --> I2["Internal node"]
    I1 --> L1["Leaf: id=1, name, email, ..."]
    I1 --> L2["Leaf: id=2, name, email, ..."]
    I2 --> L3["Leaf: id=3, name, email, ..."]
    I2 --> L4["Leaf: id=4, name, email, ..."]

Clustered Index의 리프 노드에 행 데이터 전체가 들어있다. 즉 PK를 따라 내려가면 바로 실제 레코드에 도달한다. 이 때문에 PK 조회는 항상 빠르다.

PK를 명시하지 않으면 InnoDB는 첫 번째 NOT NULL UNIQUE 인덱스를 Clustered Index로 사용하고, 그것도 없으면 내부적으로 6바이트 숨겨진 ROW_ID를 만들어 쓴다.

명시적으로 PK를 정의하지 않으면 이 ROW_ID가 순차 증가하면서 모든 테이블에 공용으로 쓰이는 dict_sys 내부 카운터에 병목이 걸린다. PK는 반드시 명시해야 한다.

PK 설계가 중요한 이유

모든 Secondary Index는 리프에 PK 값을 저장한다. 그래서 PK가 커질수록 Secondary Index도 커진다.

idx_email 리프: ('user@example.com', id=42)
idx_name  리프: ('John',              id=42)

PK를 BIGINT로 잡으면 8바이트, UUID를 CHAR(36)으로 잡으면 36바이트가 모든 Secondary Index에 그대로 복제된다.

인덱스가 많은 테이블에서는 디스크 공간과 캐시 효율에 누적으로 영향을 준다.

PK는 가능한 짧고 단조 증가하는 값(BIGINT AUTO_INCREMENT 또는 ULID/Snowflake ID 같은 정렬 가능한 식별자)을 쓰는 것이 좋다.

Secondary Index와 Bookmark Lookup

Secondary Index의 리프 노드는 (인덱스 컬럼, PK) 쌍을 저장한다. 행 데이터는 포함하지 않는다.

CREATE INDEX idx_email ON users (email);

flowchart LR
    subgraph SI["Secondary Index: idx_email"]
      SL1["('alice@a.com', 42)"]
      SL2["('bob@b.com',   17)"]
      SL3["('carol@c.com', 88)"]
    end
    subgraph CI["Clustered Index (PK)"]
      CL1["id=17, ... 전체 행"]
      CL2["id=42, ... 전체 행"]
      CL3["id=88, ... 전체 행"]
    end
    SL1 -->|"PK lookup"| CL2
    SL2 -->|"PK lookup"| CL1
    SL3 -->|"PK lookup"| CL3

SELECT * FROM users WHERE email = 'alice@a.com'을 실행하면,

1. idx_email B+Tree를 탐색해 ('alice@a.com', 42)를 찾는다
2. PK 42를 들고 Clustered Index를 다시 탐색해 행 전체를 가져온다

이 두 번째 단계가 Bookmark Lookup(또는 Random I/O, Back-Ref)이다. Secondary Index 하나를 타는 데 실제로는 두 번의 B+Tree 탐색이 일어난다.

Bookmark Lookup이 왜 병목이 되는가?

Clustered Index에서 PK로 찾는 각 행은 서로 다른 페이지에 흩어져 있을 수 있다.

수천 건의 PK를 가져와 하나씩 찾으면 랜덤 I/O가 수천 번 발생한다.

그래서 옵티마이저는 Secondary Index로 선택도가 좋지 않다고 판단하면 차라리 Full Table Scan(Clustered Index를 순차 스캔)을 택한다. “인덱스가 있는데 왜 안 타지?”라는 의문은 대개 여기서 시작된다.

Covering Index

쿼리에 필요한 모든 컬럼이 Secondary Index 내에 이미 들어있으면, Bookmark Lookup 없이 인덱스만 읽고 끝난다. 이것을 Covering Index라고 부른다.

CREATE INDEX idx_user_status ON orders (user_id, status, amount);

-- 아래 쿼리는 인덱스만으로 응답 가능
SELECT user_id, status, amount
FROM orders
WHERE user_id = 123 AND status = 'paid';

InnoDB에서 Secondary Index는 자동으로 PK를 포함하므로, SELECT id만 필요한 쿼리는 사실상 모든 Secondary Index가 Covering이 된다.

CREATE INDEX idx_email ON users (email);

-- Bookmark Lookup 없음 (id는 인덱스 리프에 이미 있음)
SELECT id FROM users WHERE email = 'alice@a.com';

EXPLAIN 결과에서 Extra: Using index가 표시되면 Covering Index가 동작했다는 뜻이다.

Covering Index는 공짜가 아니다. 컬럼을 많이 포함시킬수록 인덱스 크기가 커지고, INSERT/UPDATE/DELETE 시 인덱스 갱신 비용이 늘어난다. 핵심 읽기 쿼리 몇 개에 대해서만 의도적으로 설계하는 것이 맞다.

MySQL 8.0+의 함수 기반 인덱스

MySQL 8.0부터는 표현식에 대한 인덱스도 만들 수 있다. 함수가 씌워진 컬럼에도 인덱스를 태우려면 이 기능을 써야 한다.

-- 예: 이메일 소문자 비교를 항상 하는 쿼리
CREATE INDEX idx_lower_email ON users ((LOWER(email)));

SELECT * FROM users WHERE LOWER(email) = 'alice@a.com';

EXPLAIN 읽는 법

기본 Tabular 출력

EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	orders	ref	idx_user_status	idx_user_status	8	const,const	42	100.00	Using index

각 컬럼의 의미를 보는 순서가 중요하다.

type: 접근 방식

가장 먼저 봐야 할 컬럼이다. 위로 갈수록 좋다.

type	의미
system, const	PK/UNIQUE로 단일 행
eq_ref	조인에서 PK/UNIQUE로 1:1 매칭
ref	비유일 인덱스로 등호 조회
range	인덱스 범위 스캔 (BETWEEN, >, <, IN)
index	인덱스 전체 스캔 (리프 전체 순회)
ALL	Full Table Scan

ALL이 큰 테이블에 나오면 대부분 문제다. index는 이름과 달리 인덱스 전체를 스캔하므로 테이블 크기에 비례해 느려진다.

key: 실제 사용된 인덱스

possible_keys는 후보 인덱스 목록이고, key가 옵티마이저가 실제로 선택한 인덱스다. possible_keys에 인덱스가 있는데 key가 NULL이면 옵티마이저가 인덱스보다 Full Scan이 낫다고 판단한 것이다.

rows × filtered: 실제 검사 행수

rows는 인덱스 탐색 후 엔진이 읽을 것으로 예상되는 행수, filtered는 그 중 WHERE 조건을 통과할 비율(%)이다.

rows=1000, filtered=10.00 → 100행이 다음 단계로 간다

조인에서는 이 값이 누적되므로 초기 단계의 rows × filtered를 낮춰야 한다.

Extra: 중요 신호들

Extra	의미
Using index	Covering Index. 테이블 접근 없음
Using where	인덱스 조회 후 WHERE 필터 추가 적용
Using index condition	Index Condition Pushdown (ICP)
Using temporary	임시 테이블 생성. GROUP BY/DISTINCT에서 자주
Using filesort	인덱스로 정렬이 불가해 별도 정렬 수행
Using join buffer	조인 키에 인덱스가 없어 조인 버퍼 사용

Using temporary와 Using filesort가 함께 나오는 쿼리는 거의 모두 튜닝 대상이다.

EXPLAIN ANALYZE (MySQL 8.0.18+)

EXPLAIN ANALYZE는 쿼리를 실제로 실행하고, 각 단계의 예상 행수와 실제 행수, 실행 시간을 TREE 포맷으로 보여준다.

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.country = 'KR'
GROUP BY u.id;

-> Group aggregate: count(o.id)  (actual time=0.812..12.431 rows=1523 loops=1)
    -> Nested loop left join  (cost=2341.20 rows=8500)
                              (actual time=0.043..10.812 rows=18240 loops=1)
        -> Index lookup on u using idx_country (country='KR')
           (cost=410.50 rows=1523) (actual time=0.032..1.204 rows=1523 loops=1)
        -> Index lookup on o using idx_user_id (user_id=u.id)
           (cost=1.27 rows=5) (actual time=0.004..0.006 rows=12 loops=1523)

각 노드에서 확인할 것.

• cost vs actual time: 예상 비용과 실제 시간이 극단적으로 다르면 통계가 낡았거나 편향 데이터가 있다
• rows (예상) vs actual rows: 10배 이상 차이나면 통계 갱신(ANALYZE TABLE) 또는 히스토그램 생성 후보
• loops: Nested Loop 내부의 값은 actual time × loops가 실제 누적 시간이다. 위 예시에서 내부 인덱스 조회는 0.006ms지만 1523번 반복되어 총 ~9ms가 들었다

인덱스 구성 팁

일반 원칙(Leftmost Prefix, 카디널리티 고려, 함수와 타입 변환 함정)은 Database Index Strategy에서 다뤘으니, 여기서는 InnoDB와 EXPLAIN 맥락에서 특히 부각되는 항목만 짚는다.

복합 인덱스의 컬럼 순서

등호, 정렬, 범위 순으로 컬럼을 배치한다.

-- 쿼리
SELECT * FROM orders
WHERE user_id = 123 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

-- 이상적인 복합 인덱스
CREATE INDEX idx ON orders (user_id, status, created_at DESC);

순서의 원칙:

1. 등호 조건(=) 컬럼이 가장 앞: B+Tree에서 정확한 위치를 잡아주기 때문에 탐색 범위를 최소화한다
2. 정렬(ORDER BY) 컬럼이 그 다음: 등호로 잡힌 구간 내에서 이미 정렬된 상태로 읽히므로 filesort가 제거된다
3. 범위 조건 컬럼은 마지막: 범위 이후의 컬럼은 인덱스 정렬이 무너져 더 이상 인덱스를 활용하지 못한다

-- Bad: 범위가 앞에 오면 뒤 컬럼들이 인덱스로 활용되지 않음
CREATE INDEX idx ON orders (created_at, user_id, status);

EXPLAIN에서 Extra: Using filesort가 사라지는지 여부가 이 원칙이 잘 적용됐는지 확인하는 가장 빠른 방법이다.

Secondary Index의 Bookmark Lookup 줄이기

Secondary Index로 많은 행이 걸리면 Bookmark Lookup이 대량 발생한다. 이때는 두 가지 선택지가 있다.

(a) Covering Index로 만들기

-- SELECT 컬럼을 인덱스에 포함
CREATE INDEX idx_cover ON orders (user_id, status, amount, created_at);

(b) 페이지네이션을 키셋 방식으로 변경

-- Bad: OFFSET 100000은 실제로 10만 건을 읽고 버린다
SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

-- Good: 마지막 본 커서를 이용해 바로 그 위치부터 읽음
SELECT * FROM orders
WHERE user_id = 123 AND created_at < :last_seen
ORDER BY created_at DESC LIMIT 20;

사용되지 않는 인덱스 정리

시간이 지나면 쿼리 패턴이 바뀌고, 과거에 만든 인덱스가 더 이상 쓰이지 않는 경우가 흔하다. MySQL은 다음 쿼리로 식별한다.

SELECT * FROM sys.schema_unused_indexes;

사용되지 않는 인덱스는 저장 공간만 차지하는 것이 아니라, INSERT/UPDATE/DELETE마다 갱신되면서 쓰기 성능을 떨어뜨린다.

Bookmark Lookup이 터지는 예시

주문 검색 페이지에서 다음 쿼리가 느리다.

SELECT id, user_id, amount, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

현재 인덱스는 idx_status (status).

문제 진단

EXPLAIN SELECT id, user_id, amount, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC LIMIT 50;

type: ref, key: idx_status, rows: 1200000, Extra: Using where; Using filesort

• rows=1,200,000: status='paid' 조건에 걸리는 행이 120만 개
• Using filesort: created_at 정렬을 위해 별도 정렬
• Bookmark Lookup이 120만 건 × 랜덤 I/O로 발생

해결

정렬까지 커버하는 복합 인덱스를 만든다.

CREATE INDEX idx_status_created ON orders (status, created_at DESC, user_id, amount);

• status로 범위를 좁힌 뒤, 인덱스 내에서 이미 created_at DESC로 정렬되어 있다 → filesort 제거
• LIMIT 50이므로 인덱스 리프를 50개만 읽는다
• SELECT 컬럼이 모두 인덱스에 있어 Covering → Bookmark Lookup 제거

type: ref, key: idx_status_created, rows: 50, Extra: Using index

여기서 주의할 점: 이 인덱스를 만들면 다른 쿼리 패턴(예: WHERE status = 'paid' AND user_id = 42)에도 그대로 유효하다.

반대로 WHERE user_id = 42 단독 쿼리에는 쓸 수 없다(Leftmost Prefix).

인덱스 설계는 항상 실제 쿼리 패턴 집합을 놓고 판단해야 한다.

정리

개념	핵심
Clustered Index	InnoDB 테이블 = PK B+Tree. 리프에 행 전체가 들어있다
Secondary Index	리프에 (인덱스 컬럼, PK) 저장. 행 데이터는 없음
Bookmark Lookup	Secondary Index → PK로 Clustered Index 재탐색. 주된 비용 원인
Covering Index	쿼리 컬럼이 모두 Secondary Index에 있으면 Bookmark Lookup 생략

인덱스를 구성할 때 우선순위는 분명하다. 쿼리 패턴을 먼저 보고 인덱스를 설계한다. 반대로 가면 항상 실패한다. 복합 인덱스는 등호, 정렬, 범위 순으로 컬럼을 배치하고, 핵심 읽기 쿼리는 Covering Index로 만들어 Bookmark Lookup을 제거한다.

References

• MySQL 8.4 Reference Manual — Clustered and Secondary Indexes
• MySQL 8.4 Reference Manual — EXPLAIN Statement
• MySQL 8.4 Reference Manual — EXPLAIN Output Format
• MySQL 8.4 Reference Manual — Optimizing Queries with EXPLAIN
• MySQL Blog — EXPLAIN ANALYZE
• Percona — Using EXPLAIN ANALYZE in MySQL 8
• Use The Index, Luke! — A guide to SQL indexing
• Understanding InnoDB clustered indexes — Ovais Tariq