MySQL EXPLAIN ANALYZE로 인덱스 미사용 잡기

운영에서 느린 쿼리를 마주치면 가장 먼저 떠올리는 건 “인덱스가 없나?”입니다. 하지만 실제로는 인덱스가 있어도 안 타는 경우가 더 골치 아픕니다. 통계가 틀렸거나, 조건이 인덱스를 무력화하거나, 조인 순서가 꼬이거나, 타입 캐스팅 때문에 범위 스캔이 깨지는 등 다양한 이유로 옵티마이저가 테이블 풀스캔을 선택할 수 있습니다.

이때 MySQL 8에서 특히 유용한 도구가 EXPLAIN ANALYZE입니다. 기존 EXPLAIN이 “예상 계획”을 보여줬다면, EXPLAIN ANALYZE는 실제로 실행하면서 각 단계의 소요 시간과 반복 횟수를 제공합니다. 즉, “왜 느린지”를 추측이 아니라 증거 기반으로 좁혀갈 수 있습니다.

아래에서는 EXPLAIN ANALYZE로 인덱스 미사용을 잡아내는 흐름을, 자주 터지는 패턴과 해결책(인덱스 설계/쿼리 리라이트) 중심으로 정리합니다.

EXPLAIN ANALYZE가 주는 것: 예상이 아닌 실제

MySQL 8.0.18+에서 EXPLAIN ANALYZE를 사용할 수 있습니다. 실행 계획을 출력하기 위해 쿼리를 실제로 실행하며, 각 연산자(operator)별로 다음과 같은 정보를 제공합니다.

actual time: 해당 단계의 실제 소요 시간 범위
rows: 실제로 처리한 행 수
loops: 해당 단계가 반복 실행된 횟수
(표기되는 경우) cost, filtered 등 기존 EXPLAIN의 추정치

이 조합이 중요한 이유는 단순합니다.

옵티마이저의 추정(rows, filtered)이 틀리면 조인 순서/인덱스 선택이 틀어질 수 있음
loops가 큰데 내부 단계가 비싸면 Nested Loop 조인이 병목이 됨
rows가 예상보다 훨씬 많으면 조건이 인덱스를 못 타거나, 인덱스 선택도가 낮거나, 통계가 낡았을 가능성이 큼

실습용 스키마: “인덱스 있는데 왜 안 타지?”

예시로 주문 테이블을 가정합니다.

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL,
  total_amount INT NOT NULL,
  KEY idx_orders_user_created (user_id, created_at),
  KEY idx_orders_status_created (status, created_at)
) ENGINE=InnoDB;

운영에서 흔한 조회는 “특정 유저의 최근 주문”입니다.

SELECT id, created_at, total_amount
FROM orders
WHERE user_id = 42
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

이 쿼리는 정상이라면 idx_orders_user_created를 타고 user_id로 범위를 좁힌 뒤 created_at 범위 조건을 적용하고, 정렬도 인덱스 역순 스캔으로 해결할 수 있습니다.

이제부터는 “인덱스 미사용”이 발생하는 대표 패턴을 EXPLAIN ANALYZE로 어떻게 확인하고 고치는지 보겠습니다.

1) 함수/가공으로 인덱스 무력화: 컬럼에 함수를 씌우지 말기

가장 흔한 실수는 날짜 비교를 위해 컬럼에 함수를 적용하는 것입니다.

SELECT COUNT(*)
FROM orders
WHERE DATE(created_at) = '2026-02-01';

created_at에 인덱스가 있어도 DATE(created_at)는 계산 결과를 비교하므로, 일반적으로 인덱스 범위 스캔이 어려워져 풀스캔 쪽으로 기울기 쉽습니다.

EXPLAIN ANALYZE로 확인 포인트

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM orders
WHERE DATE(created_at) = '2026-02-01';

출력에서 다음 시그널을 찾습니다.

type이 ALL에 가까운 형태(표현은 트리 형태지만 의미는 풀스캔)
rows가 테이블 전체에 가깝게 큼
actual time이 테이블 크기에 비례해 증가

해결: 범위 조건으로 리라이트

SELECT COUNT(*)
FROM orders
WHERE created_at >= '2026-02-01 00:00:00'
  AND created_at <  '2026-02-02 00:00:00';

이렇게 바꾸면 created_at 인덱스(또는 복합 인덱스의 후행 컬럼이라도 조건이 맞으면)를 활용할 여지가 생깁니다.

2) 타입 불일치로 인덱스 미사용: 암묵적 캐스팅이 범위를 깨뜨린다

user_id가 BIGINT인데 문자열로 비교하는 경우가 종종 있습니다.

SELECT *
FROM orders
WHERE user_id = '42';

MySQL은 상황에 따라 암묵적 형변환을 수행합니다. 이때 인덱스 사용이 불리해지거나, 예상보다 많은 rows를 읽는 방향으로 갈 수 있습니다.

점검 방법

애플리케이션 바인딩 타입을 확인(ORM이 문자열로 바인딩하는지)
EXPLAIN ANALYZE에서 rows가 비정상적으로 큰지 확인

해결

바인딩 타입을 숫자로 맞추기
컬럼 타입과 비교값 타입을 일치시키기

SELECT *
FROM orders
WHERE user_id = 42;

3) OR 조건이 인덱스를 망가뜨릴 때: UNION ALL로 분해

다음은 인덱스가 있어도 옵티마이저가 애매한 선택을 하는 전형적인 형태입니다.

SELECT id
FROM orders
WHERE (user_id = 42 AND created_at >= '2026-01-01')
   OR (status = 'CANCELLED' AND created_at >= '2026-01-01');

각 조건은 각각의 복합 인덱스를 탈 수 있지만, OR로 묶이면 계획이 복잡해지고, 경우에 따라 풀스캔이 선택될 수 있습니다.

EXPLAIN ANALYZE에서 보는 것

한쪽 인덱스만 타거나
rows가 급증하거나
조건 필터링이 뒤로 밀려 actual time이 커지는지

해결: UNION ALL로 분해 후 중복 제거 전략 선택

SELECT id
FROM orders
WHERE user_id = 42
  AND created_at >= '2026-01-01'
UNION ALL
SELECT id
FROM orders
WHERE status = 'CANCELLED'
  AND created_at >= '2026-01-01';

중복 가능성이 있으면 UNION(distinct)로 바꾸거나, 상위에서 SELECT DISTINCT id로 정리합니다. 핵심은 각 분기가 “자기 인덱스를 잘 타는 형태”로 분해되는 것입니다.

4) LIKE 패턴이 앞에 와일드카드면 B-Tree는 거의 무력

SELECT id
FROM users
WHERE email LIKE '%@example.com';

B-Tree 인덱스는 접두(prefix) 매칭에 강합니다. 앞에 %가 오면 범위를 잡기 어렵습니다.

해결 옵션

접미 검색이 필요하면 역방향 컬럼(예: REVERSE(email))을 별도 컬럼으로 저장하고 인덱싱
도메인만 검색이라면 email_domain 컬럼을 분리해 인덱싱
전문 검색 요구면 Fulltext 또는 별도 검색엔진 고려

도메인 분리 예시:

ALTER TABLE users
  ADD COLUMN email_domain VARCHAR(255) GENERATED ALWAYS AS (SUBSTRING_INDEX(email, '@', -1)) STORED,
  ADD KEY idx_users_email_domain (email_domain);

SELECT id
FROM users
WHERE email_domain = 'example.com';

5) 조인에서 인덱스가 “있는데도” 느린 이유: loops가 말해준다

조인 병목은 EXPLAIN ANALYZE가 특히 강합니다. 예를 들어 주문과 주문아이템을 조회합니다.

SELECT o.id, oi.product_id
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.user_id = 42
  AND o.created_at >= '2026-01-01';

여기서 order_items(order_id) 인덱스가 없으면 내부 테이블이 매번 크게 스캔될 수 있습니다.

EXPLAIN ANALYZE에서 보는 핵심

Nested Loop에서 바깥쪽이 읽은 rows가 5만이고
안쪽 테이블 접근이 loops=50000이며
안쪽 단계 actual time이 누적되어 전체 시간을 잡아먹는지

이 패턴이면 거의 확실히 조인 키 인덱스가 문제입니다.

해결

ALTER TABLE order_items
  ADD KEY idx_order_items_order_id (order_id);

조인 키 인덱스는 “있으면 좋음”이 아니라, 대개 “없으면 재앙”에 가깝습니다.

6) 커버링 인덱스로 “읽는 행 수” 자체를 줄이기

인덱스를 탄다고 끝이 아닙니다. 인덱스로 후보를 찾은 뒤, 실제 레코드를 다시 읽는 과정(랜덤 I/O)이 비싸면 느릴 수 있습니다.

예를 들어 아래 쿼리가 자주 뜨고, total_amount까지 자주 가져온다면:

SELECT id, created_at, total_amount
FROM orders
WHERE user_id = 42
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

현재 인덱스가 (user_id, created_at)이면 total_amount를 위해 테이블을 다시 읽을 수 있습니다.

해결: 커버링 인덱스

ALTER TABLE orders
  ADD KEY idx_orders_user_created_cover (user_id, created_at, total_amount, id);

선택 컬럼/정렬 컬럼/필터 컬럼을 인덱스에 포함하면
Using index 계열로 테이블 접근을 줄일 수 있습니다

주의: 인덱스가 커지면 쓰기 비용과 버퍼 풀 부담이 증가합니다. “핵심 핫쿼리”에만 제한적으로 적용하세요.

7) 통계가 틀리면 계획이 틀린다: ANALYZE TABLE과 히스토그램

인덱스가 있는데도 풀스캔을 선택한다면, 옵티마이저가 선택도를 잘못 추정했을 수 있습니다.

대량 데이터 변경 직후
특정 값에 쏠림이 큰 컬럼(예: status가 대부분 PAID)

7-1) 기본 통계 갱신

ANALYZE TABLE orders;

7-2) 히스토그램으로 분포 알려주기(특정 컬럼에서 유용)

ANALYZE TABLE orders
  UPDATE HISTOGRAM ON status WITH 64 BUCKETS;

히스토그램은 “인덱스가 없는데도” 도움이 되기도 하지만, 여기서는 주로 “잘못된 선택도 추정으로 인덱스를 안 타는” 상황을 줄이는 데 의미가 있습니다.

8) EXPLAIN ANALYZE를 운영에서 쓸 때의 주의점

EXPLAIN ANALYZE는 쿼리를 실제 실행합니다. 따라서 다음을 지키는 편이 안전합니다.

트래픽 한가한 시간대, 혹은 리드 레플리카에서 실행
잠금/부하가 큰 쿼리는 LIMIT를 걸어 형태를 단순화한 뒤 재현
동일 조건으로 여러 번 실행해 편차 확인(캐시/버퍼풀 영향)

또한 성능 이슈가 DB만의 문제가 아닐 때도 있습니다. 예를 들어 네트워크 경로나 게이트웨이에서 지연이 생기면 DB가 느린 것처럼 보일 수 있습니다. 인프라 레벨에서 5xx를 추적하는 방법은 AWS VPC Reachability Analyzer로 502 추적하기 같은 접근도 함께 참고하면 원인 분리가 빨라집니다.

9) “인덱스 미사용”을 잡는 체크리스트

아래 순서대로 보면 대부분의 케이스가 정리됩니다.

EXPLAIN ANALYZE로 실제 병목 단계 확인
- rows, loops, actual time가 큰 노드가 어디인지
조건이 인덱스를 타기 좋은 형태인지 점검
- 컬럼에 함수 적용 여부
- 타입 불일치/암묵적 캐스팅
- 앞 와일드카드 LIKE
- OR 남용
조인 키 인덱스 존재 여부
- 특히 N:1, 1:N에서 외래키 컬럼 인덱스
인덱스 설계 재검토
- 복합 인덱스 컬럼 순서(동등 조건 먼저, 범위 조건 다음)
- 정렬/그룹핑까지 고려한 인덱스
- 커버링 인덱스로 테이블 접근 최소화
통계 갱신
- ANALYZE TABLE
- 필요 시 히스토그램

마무리: EXPLAIN만 보지 말고, ANALYZE로 “증거”를 보자

인덱스가 있는데도 느린 쿼리는 대부분 “옵티마이저가 그렇게 선택할 만한 이유”가 있습니다. 그 이유는 감으로 맞히기보다 EXPLAIN ANALYZE로 실제 실행 데이터를 보고 좁혀야 합니다.

rows가 예상보다 많으면 조건/타입/분포를 의심하고
loops가 폭발하면 조인 키 인덱스와 조인 순서를 의심하며
인덱스를 타도 느리면 커버링 인덱스나 불필요 컬럼 조회를 줄이는 방향으로 가는 것이 정석입니다.

같은 “원인 추적” 관점에서, 애플리케이션 런타임 환경 문제를 빠르게 분리하는 글로는 EKS CrashLoopBackOff 진단 - Pod 재시작 원인 추적도 함께 참고하면 장애 대응 흐름을 더 탄탄하게 만들 수 있습니다.