MySQL에서 인덱스를 이해할 때 가장 먼저 구분해야 하는 것은 인덱스의 저장 구조와 쿼리 실행 방식입니다. 클러스터드 인덱스는 테이블의 데이터가 어떤 기준으로 저장되는지를 결정하는 구조입니다. 반면 커버링 인덱스는 특정 쿼리가 테이블 데이터까지 접근하지 않고 인덱스만으로 처리될 수 있는 실행 상태를 의미합니다. 둘은 비슷해 보이지만 완전히 다른 개념입니다.
- 클러스터드 인덱스: 테이블 데이터가 저장되는 구조
- 보조 인덱스: 클러스터드 인덱스에 접근하기 위한 별도 인덱스
- 커버링 인덱스: 쿼리에 필요한 컬럼을 인덱스만으로 모두 해결하는 최적화 상태
이번 글에서는 MySQL InnoDB 기준으로 이 세 가지 개념을 정리하고, SHOW INDEX, EXPLAIN, Extra 컬럼을 어떻게 해석해야 하는지까지 함께 살펴보겠습니다.
클러스터드 인덱스란?
InnoDB 테이블은 데이터를 클러스터드 인덱스에 저장합니다. 일반적으로 클러스터드 인덱스는 Primary Key와 같습니다.
예를 들어 다음과 같은 테이블이 있다고 하겠습니다.
CREATE TABLE member (
id BIGINT NOT NULL,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
PRIMARY KEY (id)
);
이 경우 InnoDB는 id를 기준으로 클러스터드 인덱스를 구성합니다.
개념적으로 보면 다음과 같습니다.
클러스터드 인덱스
1001 -> [id = 1001, name = 'kim', age = 35]
1002 -> [id = 1002, name = 'ji', age = 33]클러스터드 인덱스의 리프 노드에는 실제 행 데이터가 저장됩니다. 그래서 PK로 조회하면 인덱스를 찾는 과정이 곧 데이터 행을 찾는 과정입니다.
SELECT *
FROM member
WHERE id = 1001;
이 쿼리는 PK 인덱스를 통해 바로 실제 데이터에 접근할 수 있습니다.
id = 1001
-> 클러스터드 인덱스 탐색
-> 실제 데이터 조회
클러스터드 인덱스는 테이블당 하나만 존재합니다. 테이블 데이터 자체가 클러스터드 인덱스 구조에 저장되기 때문입니다.
PK가 없으면 어떻게 될까?
InnoDB는 테이블에 PK가 있으면 PK를 클러스터드 인덱스로 사용합니다.
PK가 없으면 NOT NULL 조건을 만족하는 첫 번째 UNIQUE 인덱스를 클러스터드 인덱스로 사용합니다.
그것도 없으면 InnoDB가 내부적으로 숨은 클러스터드 인덱스를 생성합니다.
따라서 InnoDB 테이블에는 명시적으로든 내부적으로든 항상 클러스터드 인덱스가 존재합니다.
실무에서는 가능하면 명시적인 PK를 두는 것이 좋습니다.
PK는 보조 인덱스에도 함께 저장되므로, 너무 긴 PK를 사용하면 모든 보조 인덱스의 크기도 함께 커집니다.
보조 인덱스란?
보조 인덱스는 클러스터드 인덱스가 아닌 나머지 인덱스를 의미합니다.
예를 들어 name 컬럼에 인덱스를 추가해 보겠습니다.
CREATE INDEX idx_member_name ON member(name);
이 인덱스는 name 값을 기준으로 정렬된 별도의 인덱스입니다. InnoDB의 보조 인덱스 리프 노드에는 실제 행 전체가 저장되지 않고, 해당 행의 PK 값이 함께 저장됩니다. 개념적으로는 다음과 같습니다.
보조 인덱스 idx_member_name
'kim' -> 1001
'ji' -> 1002여기서 1001, 1002은 클러스터드 인덱스에 접근하기 위한 PK 값입니다.
다음 쿼리를 보겠습니다.
SELECT *
FROM member
WHERE name = 'kim';
이 쿼리는 name 조건으로 보조 인덱스를 먼저 찾고, 보조 인덱스에 저장된 PK를 이용해 클러스터드 인덱스를 다시 탐색합니다.
name = 'kim'
-> 보조 인덱스 idx_member_name 탐색
-> PK 1001 획득
-> 클러스터드 인덱스에서 id = 1001 탐색
-> 실제 데이터 조회
즉, 보조 인덱스는 보통 다음 순서로 데이터를 읽습니다.
보조 인덱스 -> 클러스터드 인덱스 -> 실제 데이터
이 과정을 흔히 테이블 접근, PK lookup, bookmark lookup이라고 설명합니다.
보조 인덱스는 테이블에 여러 개 만들 수 있습니다. 하지만 인덱스가 많아질수록 INSERT, UPDATE, DELETE 시 함께 관리해야 할 인덱스도 많아집니다. 인덱스는 조회 성능을 높이는 대신 쓰기 비용과 저장 공간을 증가시킵니다.
커버링 인덱스란?
커버링 인덱스는 별도의 인덱스 종류가 아닙니다. 특정 쿼리에 필요한 모든 컬럼이 하나의 인덱스 안에 들어 있어서, 테이블 데이터까지 접근하지 않아도 되는 실행 상태를 의미합니다. 앞에서 만든 인덱스를 다시 보겠습니다.
CREATE INDEX idx_member_name ON member(name);
그리고 다음 쿼리를 실행합니다.
SELECT id, name
FROM member
WHERE name = 'kim';
이 쿼리는 id, name만 조회합니다. name은 보조 인덱스 컬럼이고, id는 InnoDB 보조 인덱스에 함께 저장되는 PK입니다. 따라서 이 쿼리는 클러스터드 인덱스로 다시 가지 않고 보조 인덱스만 읽고 끝낼 수 있습니다.
(InnoDB의 보조 인덱스 레코드에는 인덱스로 지정한 컬럼들 + 해당 row의 PK 컬럼이 함께 저장됩니다)
name = 'kim'
-> 보조 인덱스 idx_member_name 탐색
-> 인덱스 안에서 id, name 확인
-> 테이블 접근 없음이것이 커버링 인덱스입니다.
반대로 다음 쿼리는 커버링 인덱스가 아닙니다.
SELECT id, name, age
FROM member
WHERE name = 'kim';
age 컬럼은 idx_member_name 인덱스에 없습니다. 따라서 MySQL은 보조 인덱스에서 name = 'kim'을 찾은 뒤, PK로 클러스터드 인덱스를 다시 찾아가서 age 값을 읽어야 합니다. 이때는 다음과 같은 흐름이 됩니다.
name = 'kim'
-> 보조 인덱스 idx_member_name 탐색
-> PK 1001 획득
-> 클러스터드 인덱스 접근
-> age 포함 실제 데이터 조회
커버링 인덱스를 만들고 싶다면 쿼리에서 사용하는 조건 컬럼과 조회 컬럼을 함께 고려해야 합니다.
예를 들어 다음 쿼리가 자주 실행된다고 하겠습니다.
SELECT id, name, age
FROM member
WHERE name = 'kim';
이 쿼리를 인덱스만으로 처리하고 싶다면 다음과 같이 인덱스를 구성할 수 있습니다.
CREATE INDEX idx_member_name_age ON member(name, age);
InnoDB 보조 인덱스에는 PK가 함께 저장되므로, 이 인덱스에는 개념적으로 다음 값들이 들어 있습니다.
(name, age, id)
따라서 WHERE name = ? 조건과 SELECT id, name, age 조회를 인덱스만으로 처리할 수 있습니다.
다만 커버링 인덱스를 위해 조회 컬럼을 무조건 인덱스에 추가하는 것은 좋은 전략이 아닙니다. 인덱스 컬럼이 많아지면 인덱스 크기가 커지고, 쓰기 비용도 증가합니다. 자주 실행되고 응답 시간이 중요한 쿼리부터 실행 계획을 확인하면서 적용해야 합니다.
인덱스가 항상 좋은 것은 아니다
인덱스가 있다고 해서 MySQL이 항상 인덱스를 사용하는 것은 아닙니다. MySQL 옵티마이저는 여러 실행 계획의 비용을 비교한 뒤 더 저렴하다고 판단되는 계획을 선택합니다. 인덱스를 사용하는 비용이 풀 테이블 스캔보다 크다고 판단하면, 인덱스가 있어도 풀 테이블 스캔을 선택할 수 있습니다. 대표적인 경우는 다음과 같습니다.
SELECT *
FROM member
WHERE age >= 1;
만약 대부분의 회원이 age >= 1 조건을 만족한다면, 인덱스를 타고 많은 행을 하나씩 찾아가는 것보다 테이블을 처음부터 끝까지 읽는 편이 더 효율적일 수 있습니다.
인덱스를 사용하면 보통 다음 비용이 발생합니다.
인덱스 탐색
-> 조건에 맞는 PK 획득
-> 클러스터드 인덱스 반복 접근
-> 실제 데이터 조회
조건에 맞는 행이 많으면 클러스터드 인덱스에 반복 접근하는 비용이 커집니다. 이 접근은 순차적으로 쭉 읽는 방식이 아니라 여러 위치를 반복해서 찾아가는 방식이므로 랜덤 I/O 비용이 커질 수 있습니다.
반대로 풀 테이블 스캔은 테이블을 순차적으로 읽습니다.
테이블 처음부터 끝까지 순차 읽기
-> WHERE 조건 평가
읽어야 하는 데이터 비율이 높다면 순차 스캔이 더 유리할 수 있습니다. 따라서 “인덱스가 있으면 무조건 빠르다”가 아니라, “인덱스를 사용해서 읽는 범위를 충분히 줄일 수 있을 때 빠르다”라고 이해해야 합니다.
좋은 인덱스의 기준: 카디널리티와 선택도
인덱스의 효과는 조건을 통해 얼마나 많은 데이터를 줄일 수 있느냐에 달려 있습니다. 여기서 중요한 개념이 카디널리티와 선택도입니다.
카디널리티는 특정 컬럼이 가지는 고유한 값의 수입니다. 예를 들어 gender 컬럼 값이 M, F 두 개뿐이라면 카디널리티가 낮습니다.
gender
- M
- F
반대로 email, order_no, user_id처럼 값이 거의 중복되지 않는 컬럼은 카디널리티가 높습니다. 선택도는 전체 행 중 특정 조건으로 얼마나 적은 행을 선택할 수 있는지를 의미합니다. 개념적으로는 다음과 같이 볼 수 있습니다.
selectivity = cardinality / total rows
선택도가 1에 가까울수록 중복이 적고, 인덱스로 검색 범위를 크게 줄일 수 있습니다. 예를 들어 전체 회원이 100만 명이고 email 값이 대부분 고유하다면 다음 조건은 매우 적은 행만 찾습니다.
SELECT *
FROM member
WHERE email = 'test@example.com';
이런 컬럼은 인덱스 효과가 큽니다. 반대로 status 컬럼 값이 ACTIVE, INACTIVE 두 개뿐이고 대부분이 ACTIVE라면 다음 쿼리는 인덱스 효과가 작을 수 있습니다.
SELECT *
FROM member
WHERE status = 'ACTIVE';
조건에 맞는 행이 너무 많기 때문입니다.
다만 카디널리티가 낮은 컬럼이라고 해서 항상 인덱스가 쓸모없는 것은 아닙니다. 다른 컬럼과 함께 복합 인덱스를 구성하거나, 특정 상태값의 비율이 매우 낮은 경우에는 효과가 있을 수 있습니다.
예를 들어 전체 주문 중 FAILED 상태가 0.1%라면 다음 조건은 좋은 인덱스 후보가 될 수 있습니다.
SELECT *
FROM orders
WHERE status = 'FAILED';
중요한 것은 컬럼 자체의 값 종류만 보는 것이 아니라, 실제 쿼리 조건이 얼마나 많은 행을 줄이는지 확인하는 것입니다.
복합 인덱스의 핵심: 왼쪽부터 사용한다
복합 인덱스는 두 개 이상의 컬럼으로 구성된 인덱스입니다.
CREATE INDEX idx_orders_status_created_at
ON orders(status, created_at);
복합 인덱스는 컬럼 순서가 중요합니다. MySQL은 복합 인덱스를 왼쪽부터 누적해서 사용할 수 있습니다. 위 인덱스는 다음 조건에 사용할 수 있습니다.
WHERE status = 'PAID'
--
WHERE status = 'PAID'
AND created_at >= '2026-01-01'
하지만 다음 조건은 인덱스를 제대로 활용하지 못합니다.
WHERE created_at >= '2026-01-01'
created_at은 인덱스의 두 번째 컬럼입니다. 첫 번째 컬럼인 status를 건너뛰었기 때문에 (status, created_at) 인덱스를 효율적으로 사용하기 어렵습니다. 복합 인덱스는 다음과 같은 정렬 구조라고 보면 됩니다.
(status, created_at)
CANCELED, 2026-01-01
CANCELED, 2026-01-03
PAID, 2026-01-01
PAID, 2026-01-02
READY, 2026-01-01
먼저 status로 정렬되고, 같은 status 안에서 created_at으로 정렬됩니다. 따라서 status 조건 없이 created_at만으로 찾으려면 인덱스 전체를 넓게 훑어야 합니다.
복합 인덱스에서는 등호 조건을 앞에, 범위 조건을 뒤에 둔다
복합 인덱스를 설계할 때 자주 사용하는 규칙이 있습니다.
등호 조건 컬럼을 앞에 두고, 범위 조건 컬럼을 뒤에 둔다.
예를 들어 다음 쿼리가 자주 실행된다고 하겠습니다.
SELECT *
FROM orders
WHERE user_id = 1001
AND status = 'PAID'
AND created_at >= '2026-01-01'
AND created_at < '2026-02-01';
이 경우 다음과 같은 인덱스가 좋은 후보가 됩니다.
CREATE INDEX idx_orders_user_status_created_at
ON orders(user_id, status, created_at);
user_id, status는 등호 조건입니다. 이 두 조건으로 검색 범위를 먼저 좁힌 뒤, created_at 범위 조건을 적용할 수 있습니다.
user_id = 1001
-> status = 'PAID'
-> created_at 범위 스캔
반대로 범위 조건 컬럼을 앞에 두면 그 뒤 컬럼의 정렬 효과를 충분히 활용하기 어렵습니다.
CREATE INDEX idx_orders_created_user_status
ON orders(created_at, user_id, status);
이 인덱스에서는 created_at 범위를 먼저 넓게 스캔한 뒤 user_id, status를 필터링해야 합니다. 조건에 따라 비효율적일 수 있습니다.
다만 인덱스 설계에는 절대 공식이 없습니다. ORDER BY, GROUP BY, LIMIT, 컬럼 분포, 조건 선택도, 실제 실행 빈도까지 함께 봐야 합니다.
IN 조건은 항상 범위 조건보다 좋은가?
IN 조건은 여러 개의 동등 비교처럼 동작할 수 있습니다.
WHERE status IN ('READY', 'PAID')
개념적으로는 다음 조건과 비슷합니다.
WHERE status = 'READY'
OR status = 'PAID'
복합 인덱스에서 작은 개수의 IN 조건은 인덱스를 효율적으로 활용하는 데 도움이 될 수 있습니다. 예를 들어 다음 인덱스가 있다고 하겠습니다.
CREATE INDEX idx_orders_status_created_at
ON orders(status, created_at);
다음 쿼리는 status 후보별로 created_at 범위를 탐색할 수 있습니다.
SELECT *
FROM orders
WHERE status IN ('READY', 'PAID')
AND created_at >= '2026-01-01'
AND created_at < '2026-02-01';
하지만 IN 목록이 너무 많으면 오히려 비용이 커질 수 있습니다. 또한 옵티마이저가 항상 기대한 방식으로 실행 계획을 선택하는 것도 아닙니다. 따라서 >=를 무조건 IN으로 바꾸면 더 빠르다고 일반화하면 안 됩니다. 쿼리의 조건 분포와 실행 계획을 확인해야 합니다.
SHOW INDEX로 인덱스 정보 확인하기
MySQL에서는 SHOW INDEX 명령으로 테이블의 인덱스 정보를 확인할 수 있습니다.
SHOW INDEX FROM member;
주요 컬럼은 다음과 같습니다.
| 컬럼 | 의미 |
| Key_name | 인덱스 이름 |
| Non_unique | 중복 허용 여부. 0이면 unique, 1이면 non-unique |
| Seq_in_index | 인덱스 안에서 컬럼이 등장하는 순서 |
| Column_name | 인덱스에 포함된 컬럼명 |
| Collation | 인덱스 정렬 방향. A는 오름차순, D는 내림차순 |
| Cardinality | 인덱스 값의 고유 개수에 대한 추정치 |
| Index_type | 인덱스 타입. 일반적으로 BTREE |
예를 들어 다음 인덱스가 있다고 하겠습니다.
CREATE INDEX idx_orders_status_created_at
ON orders(status, created_at);
SHOW INDEX 결과는 인덱스 컬럼 수만큼 여러 줄로 표시됩니다.
Key_name Seq_in_index Column_name Cardinality
idx_orders_status_created_at 1 status 3
idx_orders_status_created_at 2 created_at 500000
여기서 Seq_in_index = 1은 복합 인덱스의 첫 번째 컬럼입니다. Seq_in_index = 2는 두 번째 컬럼입니다. 복합 인덱스의 Cardinality는 각 컬럼 단독의 고유 개수라고 단순하게 해석하면 안 됩니다. MySQL은 복합 인덱스의 왼쪽 prefix 기준으로 통계를 관리합니다.
예를 들어 (status, created_at) 인덱스라면 다음처럼 이해하는 것이 좋습니다.
Seq_in_index = 1 -> status 기준의 추정 카디널리티
Seq_in_index = 2 -> (status, created_at) 조합 기준의 추정 카디널리티
Cardinality는 실제 값을 매번 전부 세는 값이 아니라 통계 기반 추정치입니다. 통계가 오래되었거나 데이터 분포가 크게 바뀌면 실제와 차이가 날 수 있습니다. 이럴 때는 다음 명령으로 통계를 갱신할 수 있습니다.
ANALYZE TABLE orders;
PK, UK, FK에는 인덱스가 자동으로 생길까?
MySQL에서 Primary Key와 Unique Key는 제약 조건을 지키기 위해 인덱스가 필요합니다. Primary Key는 중복될 수 없고 NULL도 허용하지 않습니다. Unique Key는 중복 값을 허용하지 않습니다. 따라서 INSERT나 UPDATE 시 기존 값과 충돌하는지 빠르게 확인해야 합니다. 인덱스가 없다면 중복 여부를 확인하기 위해 테이블 전체를 탐색해야 합니다. 그래서 PK와 UK는 인덱스로 관리됩니다.
외래키도 인덱스가 필요합니다. 다만 외래키는 “항상 단일 인덱스가 자동 생성된다”고 표현하면 정확하지 않습니다. MySQL은 외래키 검사를 빠르게 수행하기 위해 외래키 컬럼에 적절한 인덱스를 요구합니다. 이때 외래키 컬럼들이 인덱스의 선행 컬럼으로 포함되어 있어야 합니다. 예를 들어 다음 외래키가 있다고 하겠습니다.
CREATE TABLE orders (
id BIGINT NOT NULL,
member_id BIGINT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_orders_member
FOREIGN KEY (member_id)
REFERENCES member(id)
);
member_id에 적절한 인덱스가 없으면 MySQL이 인덱스를 생성할 수 있습니다. 하지만 이미 다음과 같은 인덱스가 있다면 외래키 검사용으로 사용할 수 있습니다.
CREATE INDEX idx_orders_member_created_at
ON orders(member_id, created_at);
이 인덱스는 member_id가 선행 컬럼이므로 외래키 검사에 사용할 수 있습니다. 반대로 다음 인덱스는 외래키 검사에 적절하지 않습니다.
CREATE INDEX idx_orders_created_at_member
ON orders(created_at, member_id);
member_id가 선행 컬럼이 아니기 때문입니다.
EXPLAIN으로 실행 계획 확인하기
인덱스를 만들었다면 반드시 EXPLAIN으로 실행 계획을 확인해야 합니다.
EXPLAIN
SELECT *
FROM orders
WHERE member_id = 1001
AND created_at >= '2026-01-01'
AND created_at < '2026-02-01';
주요 컬럼은 다음과 같습니다.
| 컬럼 | 의미 |
| type | 테이블 접근 방식 |
| possible_keys | 후보 인덱스 |
| key | 실제 선택된 인덱스 |
| key_len | 사용된 인덱스 길이. 복합 인덱스에서 어디까지 사용했는지 판단하는 데 도움 |
| rows | MySQL이 읽을 것으로 예상한 행 수 |
| filtered | 조건 적용 후 남을 것으로 예상한 비율 |
| Extra | 추가 실행 정보 |
여기서 가장 먼저 볼 컬럼은 type, key, rows, Extra입니다.
key가 NULL이면 MySQL이 해당 쿼리에서 사용할 인덱스를 선택하지 않았다는 뜻입니다.
rows는 실제 읽은 행 수가 아니라 예상치입니다. MySQL의 통계 기반 추정값이므로 실제 결과와 다를 수 있습니다. MySQL 8.0 이상에서는 EXPLAIN ANALYZE를 사용해 실제 실행 결과와 비교하는 것이 좋습니다.
EXPLAIN type 이해하기
type은 MySQL이 테이블에 접근하는 방식을 나타냅니다.
자주 보는 값은 다음과 같습니다.
| type | 의미 |
| const | PK 또는 Unique Index로 최대 1행을 찾는 경우 |
| eq_ref | 조인에서 PK 또는 Unique Not Null 인덱스로 정확히 1행을 찾는 경우 |
| ref | 인덱스로 특정 값에 매칭되는 여러 행을 찾는 경우 |
| range | 인덱스 범위 스캔 |
| index | 인덱스 전체 스캔 |
| ALL | 풀 테이블 스캔 |
일반적으로 ALL은 피하고 싶지만, 항상 나쁜 것은 아닙니다. 작은 테이블이거나 많은 데이터를 읽어야 하는 쿼리라면 풀 테이블 스캔이 더 합리적일 수 있습니다. index는 인덱스를 사용한다는 점에서 좋아 보일 수 있지만, 실제로는 인덱스 전체를 읽는 방식입니다. 읽는 범위를 줄이지 못한다면 비용이 클 수 있습니다. 따라서 type만 보고 판단하면 안 됩니다. key, rows, filtered, Extra를 함께 봐야 합니다.
Extra의 Using index, Using where, Using index condition
EXPLAIN의 Extra 컬럼은 실행 계획을 해석할 때 자주 확인하는 필드입니다.
Using index
Using index는 커버링 인덱스를 사용했다는 뜻입니다.
즉, 쿼리에 필요한 컬럼을 인덱스만으로 읽었고, 실제 테이블 행을 추가로 읽지 않았다는 의미입니다.
CREATE INDEX idx_member_name_age
ON member(name, age);
EXPLAIN
SELECT id, name, age
FROM member
WHERE name = 'kim';
InnoDB 보조 인덱스에는 PK가 함께 저장되므로, idx_member_name_age 인덱스만으로 id, name, age를 모두 가져올 수 있습니다.
이 경우 Extra에 다음이 표시될 수 있습니다.
Using index
Using where
Using where는 WHERE 조건을 평가한다는 뜻입니다. 중요한 점은 Using where 자체가 인덱스 사용 여부를 의미하지 않는다는 것입니다. 인덱스를 사용해도 Using where가 나올 수 있고, 인덱스를 사용하지 않아도 Using where가 나올 수 있습니다.
인덱스 사용 여부는 key 컬럼을 확인해야 합니다.
예를 들어 다음처럼 key가 존재하고 Extra에 Using where가 표시될 수 있습니다.
type: ref
key: idx_member_name
Extra: Using where
이 경우 인덱스를 사용했지만, 읽은 행에 대해 WHERE 조건을 추가로 평가했다는 뜻입니다.
반대로 다음과 같은 실행 계획은 인덱스를 사용하지 않은 것입니다.
type: ALL
key: NULL
Extra: Using where
이 경우 테이블 전체를 읽고 WHERE 조건을 평가합니다.
Using where; Using index
Using where; Using index는 WHERE 조건을 평가하면서도, 필요한 컬럼은 인덱스만으로 해결했다는 뜻입니다.
즉, 커버링 인덱스입니다.
Extra: Using where; Using index
이 실행 계획은 테이블 행을 추가로 읽지 않습니다. 따라서 Using where가 있다고 해서 무조건 테이블 접근이 발생한다고 해석하면 안 됩니다. Using index가 함께 있으면 커버링 인덱스일 수 있습니다.
Using index condition
Using index condition은 Index Condition Pushdown, 즉 ICP가 적용되었다는 뜻입니다. ICP는 보조 인덱스를 사용할 때, WHERE 조건 중 인덱스 컬럼만으로 판단할 수 있는 조건을 스토리지 엔진 단계에서 먼저 평가하는 최적화입니다.
예를 들어 다음 인덱스가 있다고 하겠습니다.
CREATE INDEX idx_people_zip_last_first
ON people(zipcode, lastname, firstname);
그리고 다음 쿼리를 실행합니다.
SELECT *
FROM people
WHERE zipcode = '12345'
AND lastname LIKE '%kim%';
zipcode 조건으로 인덱스를 사용할 수 있고, lastname도 인덱스에 포함되어 있습니다. 하지만 SELECT *이므로 최종적으로는 테이블 행을 읽어야 합니다. ICP가 적용되면 MySQL은 테이블 행을 읽기 전에 인덱스 엔트리 단계에서 lastname 조건을 먼저 평가합니다. 조건에 맞는 경우에만 실제 테이블 행을 읽습니다.
보조 인덱스 탐색
-> 인덱스 컬럼으로 일부 WHERE 조건 먼저 평가
-> 조건을 만족하는 경우에만 클러스터드 인덱스 접근
이때 Extra에는 다음이 표시됩니다.
Using index condition
Using index condition은 커버링 인덱스가 아닙니다. 최종적으로 실제 테이블 행을 읽어야 하므로 Using index와는 다릅니다.
Extra를 빠른 순서로 단순 암기하면 안 된다
다음처럼 Extra 값을 성능 순서로 외우는 경우가 있습니다. 큰 방향성은 맞지만, 절대적인 성능 순서는 아닙니다.
- Using index
- Using where; Using index
- Using index condition
- Using where + index
- Using where + full scan
실제 성능은 다음 요소에 따라 달라집니다.
- 읽어야 하는 행 수
- 인덱스 선택도
- 테이블 크기
- 버퍼 풀 캐시 상태
- 정렬 여부
- LIMIT 여부
- 조인 순서
- 통계 정확도
예를 들어 Using index가 있더라도 인덱스 전체를 많이 읽는다면 느릴 수 있습니다. 반대로 작은 테이블의 ALL은 문제가 되지 않을 수 있습니다. 따라서 Extra는 실행 계획을 이해하기 위한 힌트로 봐야 합니다. 최종 판단은 EXPLAIN ANALYZE, 실제 응답 시간, 읽은 row 수를 함께 보고 내려야 합니다.
ORDER BY와 인덱스
인덱스는 WHERE 조건뿐 아니라 ORDER BY 최적화에도 중요합니다.
예를 들어 다음 쿼리가 자주 실행된다고 하겠습니다.
SELECT *
FROM orders
WHERE member_id = 1001
ORDER BY created_at DESC
LIMIT 20;
이 경우 다음 인덱스가 좋은 후보가 됩니다.
CREATE INDEX idx_orders_member_created_at
ON orders(member_id, created_at DESC);
member_id로 먼저 범위를 좁히고, 그 안에서 created_at DESC 순서로 데이터를 읽을 수 있습니다. 이렇게 되면 별도의 정렬 비용을 줄일 수 있습니다. MySQL 8.0 이상에서는 내림차순 인덱스를 실제로 저장하고 활용할 수 있습니다. 복합 인덱스에서 정렬 방향이 섞이는 경우에도 인덱스 설계가 중요합니다.
ORDER BY member_id ASC, created_at DESC
이런 쿼리가 중요하다면 다음과 같은 인덱스를 고려할 수 있습니다.
CREATE INDEX idx_orders_member_created_desc
ON orders(member_id ASC, created_at DESC);
정렬 최적화는 WHERE 조건과 함께 봐야 합니다. 인덱스가 WHERE 조건에는 좋아도 ORDER BY에는 맞지 않을 수 있고, 반대로 ORDER BY에는 좋아도 WHERE 조건의 선택도가 낮을 수 있습니다.
인덱스 힌트는 신중하게 사용한다
MySQL에는 USE INDEX, FORCE INDEX, IGNORE INDEX 같은 인덱스 힌트가 있습니다.
SELECT *
FROM orders FORCE INDEX (idx_orders_member_created_at)
WHERE member_id = 1001
AND created_at >= '2026-01-01';
인덱스 힌트는 옵티마이저의 선택에 개입합니다. 특정 상황에서는 도움이 되지만, 데이터 분포가 바뀌거나 MySQL 버전이 바뀌면 오히려 성능을 망칠 수 있습니다. 따라서 힌트는 기본 전략이 아닙니다. 먼저 확인해야 할 것은 다음입니다.
- 쿼리 조건이 인덱스를 잘 활용할 수 있는 형태인지
- 복합 인덱스 컬럼 순서가 맞는지
- 통계가 오래되지 않았는지
- 불필요한 인덱스가 너무 많지는 않은지
- ANALYZE TABLE 후에도 실행 계획이 같은지
- EXPLAIN ANALYZE 기준 실제 실행 시간이 어떤지
힌트는 옵티마이저가 명백히 잘못된 선택을 하고, 그 원인을 이해한 뒤에도 쿼리나 인덱스 구조로 해결하기 어려울 때 제한적으로 사용하는 것이 좋습니다.
인덱스 설계 체크리스트
인덱스를 설계할 때는 다음 순서로 확인하면 좋습니다.
1. 쿼리 패턴을 먼저 본다
인덱스는 컬럼 기준으로 만드는 것이 아니라 쿼리 기준으로 만들어야 합니다.
다음 요소를 함께 봐야 합니다.
- WHERE 조건
- JOIN 조건
- ORDER BY
- GROUP BY
- LIMIT
- SELECT 컬럼
- 실행 빈도
- 응답 시간 요구사항
2. 선택도가 높은 조건으로 검색 범위를 줄인다
조건을 걸었을 때 많은 행이 남는 컬럼은 인덱스 효과가 작습니다.
다만 낮은 카디널리티 컬럼도 특정 값의 비율이 낮거나, 다른 컬럼과 복합 인덱스로 묶이면 효과가 있을 수 있습니다.
3. 복합 인덱스는 왼쪽 prefix를 고려한다
(A, B, C) 인덱스는 다음 조건에 사용할 수 있습니다.
A
A, B
A, B, C
하지만 다음 조건에는 효율적으로 사용하기 어렵습니다.
B
B, C
C
4. 등호 조건과 범위 조건의 순서를 고려한다
일반적으로 등호 조건 컬럼을 앞에 두고, 범위 조건 컬럼을 뒤에 둡니다.
(user_id, status, created_at)
WHERE user_id = ?
AND status = ?
AND created_at >= ?
AND created_at < ?
5. 커버링 인덱스는 중요한 쿼리에만 적용한다
커버링 인덱스는 테이블 접근을 줄일 수 있지만, 인덱스 크기를 키웁니다.
조회 성능이 중요한 쿼리, 자주 실행되는 쿼리, 읽는 행 수가 많은 쿼리에 우선 적용해야 합니다.
6. 인덱스는 쓰기 비용을 증가시킨다
인덱스가 많으면 INSERT, UPDATE, DELETE가 느려집니다.
특히 인덱스에 포함된 컬럼을 UPDATE하면 테이블 데이터뿐 아니라 인덱스도 함께 변경해야 합니다.
조회 성능만 보고 인덱스를 계속 추가하면 쓰기 성능과 저장 공간이 악화됩니다.
7. 실행 계획과 실제 실행 결과를 함께 본다
EXPLAIN은 예상 실행 계획입니다. rows, filtered는 추정치입니다.
MySQL 8.0 이상에서는 가능하면 EXPLAIN ANALYZE로 실제 실행 결과를 확인해야 합니다.
결론
MySQL 인덱스를 이해할 때는 단순히 “인덱스를 타느냐, 안 타느냐”만 보면 안 됩니다. 클러스터드 인덱스, 보조 인덱스, 커버링 인덱스는 각각 다른 관점의 개념입니다.
클러스터드 인덱스는 InnoDB 테이블의 데이터 저장 구조입니다. 일반적으로 PK가 클러스터드 인덱스가 되며, 리프 노드에 실제 행 데이터가 저장됩니다.
보조 인덱스는 클러스터드 인덱스가 아닌 인덱스입니다. InnoDB 보조 인덱스에는 보조 인덱스 컬럼과 함께 PK 값이 저장됩니다. 따라서 보조 인덱스로 조회한 뒤 필요한 컬럼이 인덱스에 없으면 PK를 통해 클러스터드 인덱스를 다시 찾아갑니다.
커버링 인덱스는 쿼리에 필요한 모든 컬럼이 인덱스 안에 있어 테이블 접근을 생략할 수 있는 실행 상태입니다. EXPLAIN의 Using index는 이 상황을 나타냅니다.
좋은 인덱스는 단순히 많이 만드는 것이 아니라, 자주 실행되는 쿼리의 검색 범위를 줄이고, 정렬 비용을 줄이며, 필요하다면 테이블 접근까지 줄이는 방향으로 설계해야 합니다.
마지막으로 인덱스 설계의 정답은 실행 계획으로 확인해야 합니다. 인덱스는 쿼리 패턴과 데이터 분포를 보고 검증하면서 설계해야 하는 성능 최적화 도구입니다.
EXPLAIN
SELECT ...
EXPLAIN ANALYZE
SELECT ...
'Server > Spring' 카테고리의 다른 글
| [Spring] DataSource와 PlatformTransactionManager (0) | 2025.05.02 |
|---|---|
| [Spring] Transaction Propagation (0) | 2025.02.15 |
| [Spring] AOP(Aspect Oriented Programming) (0) | 2025.02.02 |
| [Spring] Bean Life Cycle (0) | 2025.01.05 |
| [Spring] @PostPersist와 TransactionPhase.AFTER_COMMIT (0) | 2024.06.30 |
