MySQL 인덱스 최적화: Covering Index

들어가며: “인덱스를 걸었는데 왜 느리죠?”

MySQL 운영에서 가장 흔한 질문이다. 인덱스를 만들었지만 쿼리가 여전히 Full Table Scan을 타거나, EXPLAIN에서 type: ALL이 찍히는 상황. 대부분의 원인은 인덱스 자체가 아니라 인덱스가 쿼리에 맞게 설계되지 않았기 때문이다.

이 글에서는 MySQL(InnoDB) 인덱스 최적화의 핵심 세 가지 — Covering Index, Index Condition Pushdown(ICP), Index Merge — 를 EXPLAIN 실행 계획과 함께 실무 관점에서 다룬다. 모든 내용은 MySQL 8.0/8.4 공식 문서를 근거로 한다.

1. Covering Index: 테이블 접근 자체를 없앤다

1-1. 개념

Covering Index란 쿼리가 필요로 하는 모든 컬럼이 인덱스에 포함되어, InnoDB가 클러스터드 인덱스(Primary Key)로 돌아가지 않고 세컨더리 인덱스만으로 결과를 반환하는 최적화다.

MySQL 공식 문서(Clustered and Secondary Indexes)에 따르면, InnoDB 세컨더리 인덱스의 리프 노드는 PK 값을 포함한다. 세컨더리 인덱스에서 원하는 컬럼을 모두 찾을 수 있으면 PK 인덱스로의 랜덤 I/O(bookmark lookup)가 사라진다.

1-2. EXPLAIN에서 확인하는 법

EXPLAIN SELECT user_id, created_at
FROM orders
WHERE status = 'PAID'
  AND created_at >= '2026-01-01';

Extra 컬럼에 Using index가 표시되면 Covering Index가 적용된 것이다. 반대로 Using index 없이 Using where만 있으면 테이블(클러스터드 인덱스)까지 접근하고 있다.

1-3. 설계 예시

-- ❌ 커버링 안 됨: SELECT * 이므로 모든 컬럼 필요
SELECT * FROM orders WHERE status = 'PAID';

-- ✅ 커버링 가능: 필요한 컬럼만 SELECT
SELECT user_id, created_at FROM orders WHERE status = 'PAID';

-- 인덱스 설계
ALTER TABLE orders
  ADD INDEX idx_status_created_userid (status, created_at, user_id);

인덱스 컬럼 순서는 WHERE 조건 → ORDER BY/범위 → SELECT 전용 순서로 배치한다. SELECT에만 필요한 컬럼은 인덱스 끝에 추가하면 된다.

1-4. 실무 주의점

2. Index Condition Pushdown(ICP): 스토리지 엔진에서 미리 필터링

2-1. 개념

ICP는 MySQL 5.6에서 도입된 최적화로, 인덱스 컬럼에 대한 WHERE 조건을 서버 레이어가 아닌 스토리지 엔진(InnoDB) 레이어에서 먼저 평가하는 기법이다. 공식 문서 Index Condition Pushdown Optimization에 명시되어 있다.

2-2. ICP 없이 vs. ICP 있을 때

2-3. EXPLAIN에서 확인

-- 복합 인덱스: (status, category, price)
EXPLAIN SELECT * FROM products
WHERE status = 'ACTIVE'
  AND category LIKE '%electronics%'
  AND price > 10000;

Extra에 Using index condition이 표시되면 ICP가 적용된 것이다.

ICP가 동작하는 조건:

• 인덱스 컬럼에 대한 조건이 있지만, 해당 조건이 range 접근의 일부로 사용되지 않는 경우
• InnoDB 또는 MyISAM 테이블
• 서브쿼리가 아닌 단순 조건

2-4. 실무에서 놓치기 쉬운 포인트

• LIKE ‘%…’(좌측 와일드카드)는 range 접근으로 사용할 수 없지만, 해당 컬럼이 인덱스에 포함되어 있으면 ICP로 스토리지 엔진에서 필터링한다.
• ICP는 MySQL 5.6+ 기본 활성이다. SET optimizer_switch = 'index_condition_pushdown=off';로 비활성화하여 성능 차이를 직접 비교할 수 있다.
• ICP는 Covering Index와 동시에 적용되지 않는다. Covering Index가 성립하면 테이블 접근 자체가 없으므로 ICP가 불필요하다.

3. Index Merge: 여러 인덱스를 합쳐서 쓴다

3-1. 개념

하나의 쿼리에서 여러 개의 단일 컬럼 인덱스를 동시에 사용한 뒤 결과를 합치는 최적화다. MySQL 공식 문서 Index Merge Optimization에 세 가지 알고리즘이 정의되어 있다:

3-2. 예시와 EXPLAIN

-- 단일 인덱스 2개가 존재한다고 가정
-- INDEX idx_email (email)
-- INDEX idx_phone (phone)

EXPLAIN SELECT * FROM users
WHERE email = 'test@example.com'
   OR phone = '010-1234-5678';

이 경우 type: index_merge, Extra: Using union(idx_email, idx_phone)가 표시될 수 있다.

3-3. Index Merge보다 복합 인덱스가 나은 경우

Index Merge Intersection이 자주 발생한다면, 그것은 복합 인덱스를 만들어야 한다는 신호다.

-- Index Merge Intersection 발생:
-- WHERE status = 'ACTIVE' AND region = 'KR'
-- → Using intersect(idx_status, idx_region)

-- 해결: 복합 인덱스로 교체
ALTER TABLE accounts
  ADD INDEX idx_status_region (status, region);

복합 인덱스는 하나의 B-Tree 탐색으로 끝나지만, Index Merge Intersection은 두 인덱스를 각각 스캔한 뒤 PK로 교집합을 계산하므로 거의 항상 더 느리다.

3-4. Index Merge Union은 허용할 수 있다

OR 조건은 복합 인덱스로 해결할 수 없으므로, Index Merge Union이 유일한 인덱스 활용 방법인 경우가 많다. 이때는 Full Table Scan 대비 이점이 있으므로 허용한다.

4. 실전 인덱스 설계 의사결정 흐름

아래 체크리스트를 순서대로 따라가면 대부분의 인덱스 최적화 판단을 내릴 수 있다.

1. EXPLAIN 먼저 실행: type이 ALL이면 인덱스 미사용. ref/range/index인지 확인한다.
2. Extra 컬럼 확인:
   • Using index → Covering Index 적용 ✅
   • Using index condition → ICP 적용 ✅
   • Using intersect/union/sort_union → Index Merge
   • Using filesort, Using temporary → 추가 최적화 필요 ⚠️
3. Index Merge Intersection이 보이면 → 복합 인덱스로 교체 검토
4. Covering Index 가능 여부 → SELECT 컬럼이 3개 이하이고 자주 실행되는 쿼리면 적극 고려
5. 인덱스 크기 vs. 읽기 성능 → SHOW INDEX FROM table로 cardinality, innodb_buffer_pool_pages_data로 메모리 사용량 확인

5. 흔한 실수와 방지법

6. EXPLAIN ANALYZE로 실측 검증

MySQL 8.0.18+에서 사용 가능한 EXPLAIN ANALYZE는 실제 실행 후 각 단계별 소요 시간과 처리 행 수를 보여준다. 인덱스 변경 전후의 효과를 정량적으로 비교할 때 필수다.

EXPLAIN ANALYZE
SELECT user_id, created_at
FROM orders
WHERE status = 'PAID'
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 20G

출력에서 actual time과 rows를 비교하면 인덱스 추가/변경의 효과를 수치로 확인할 수 있다. 추정치만 보여주는 EXPLAIN과 달리, 실제 실행 결과이므로 운영 판단의 근거가 된다.

EXPLAIN ANALYZE에 대한 상세 내용은 MySQL EXPLAIN ANALYZE 심화 글을 참고한다.

정리

인덱스는 “만들면 끝”이 아니라 쿼리 패턴에 맞게 설계하고, EXPLAIN으로 검증하고, 주기적으로 정리하는 운영 대상이다. 슬로우 쿼리 로그 → EXPLAIN → 인덱스 설계 → EXPLAIN ANALYZE 검증 순서를 반복하면, 대부분의 쿼리 성능 문제는 해결할 수 있다.

참고 자료

• MySQL 8.4 공식 문서 — Clustered and Secondary Indexes
• MySQL 8.4 공식 문서 — Index Condition Pushdown Optimization
• MySQL 8.4 공식 문서 — Index Merge Optimization
• MySQL 8.4 공식 문서 — EXPLAIN Output Format