MySQL EXPLAIN으로 느린 쿼리 잡기 | 실행 계획·인덱스 튜닝 실전

들어가며

MySQL에서 응답 지연이 나면 원인은 잘못된 인덱스, 부적절한 조인 순서, 과도한 풀 스캔, 통계 오래됨 등으로 압축됩니다. 인덱스 튜닝은 추측이 아니라 실행 계획을 읽고 가장 비싼 단계를 줄이는 작업입니다. 이 글은 InnoDB·MySQL 8.x를 기준으로 EXPLAIN 출력 필드를 해석하며, 인덱스 추가·쿼리 재작성·통계 갱신의 순서를 제시합니다. ORM을 쓰더라도 최종 SQL에 대해 같은 절차를 적용할 수 있습니다.

---

개념 설명

실행 계획이란

옵티마이저는 통계·비용 모델로 어떤 인덱스를 탈지, 조인 순서, 접근 방식(range/ref/eq_ref 등)을 정합니다. EXPLAIN은 그 결정을 사람이 읽을 수 있게 펼친 것입니다.

주요 EXPLAIN 컬럼 (요약)

컬럼	의미
id	SELECT 식별자 (서브쿼리·UNION 구분)
select_type	SIMPLE, PRIMARY, SUBQUERY, DERIVED 등
table	접근하는 테이블 이름
type	접근 방식—ALL(풀 스캔)이 가장 무거운 편, const·eq_ref·range는 상대적으로 유리
possible_keys	사용 가능한 인덱스 목록
key	실제 선택된 인덱스 이름(NULL이면 인덱스 미사용)
key_len	사용된 인덱스 길이 (복합 인덱스 일부만 사용 시 확인)
ref	인덱스와 비교되는 컬럼/상수
rows	예상 검사 행 수(작을수록 좋은 경향, 단 추정치)
filtered	WHERE 조건으로 필터링될 비율 (%)
Extra	Using filesort, Using temporary, Using index 등 부가 동작 힌트

EXPLAIN을 읽을 때 가장 먼저 기억할 것은 rows와 filtered가 추정치라는 점입니다. 옵티마이저는 인덱스 통계(인덱스 다이브 결과와 카디널리티)로 행 수를 짐작하고, 그 짐작으로 비용을 계산해 계획을 고릅니다. 그래서 EXPLAIN이 좋아 보이는데 실제로 느리거나, 반대로 인덱스가 있는데도 풀 스캔을 고르는 경우의 상당수는 “계획을 잘못 읽은 것”이 아니라 “추정이 틀린 것”입니다. 이때는 EXPLAIN ANALYZE로 추정 행 수(rows=)와 실제 행 수(actual ... rows=)를 비교해 어느 단계에서 추정이 크게 빗나갔는지부터 찾는 편이 빠릅니다.

type 접근 방식 순서 (빠름 → 느림)

type	설명	예시
const	PK 또는 유니크 인덱스로 단일 행	WHERE id = 1
eq_ref	조인 시 PK/유니크로 단일 행 매칭	JOIN users ON orders.user_id = users.id
ref	비유니크 인덱스로 여러 행	WHERE user_id = 42
range	인덱스 범위 스캔	WHERE created_at > '2026-01-01'
index	인덱스 풀 스캔	인덱스 전체 읽기
ALL	테이블 풀 스캔	인덱스 미사용

목표: ALL을 range 이상으로 개선

---

실전 구현

테스트 데이터 준비

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  status VARCHAR(16) NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_user (user_id),
  INDEX idx_created (created_at)
) ENGINE=InnoDB;
-- 테스트 데이터 삽입
INSERT INTO orders (user_id, product_id, status, amount, created_at, updated_at)
SELECT 
  FLOOR(RAND() * 10000) + 1,
  FLOOR(RAND() * 1000) + 1,
  ELT(FLOOR(RAND() * 4) + 1, 'pending', 'paid', 'shipped', 'cancelled'),
  RAND() * 1000,
  DATE_ADD('2026-01-01', INTERVAL FLOOR(RAND() * 90) DAY),
  NOW()
FROM 
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t4,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t5,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t6;
-- 4^6 = 4096행 삽입

아래 EXPLAIN 출력의 rows 값은 이 작은 테이블에서 나올 법한 모양을 보여 주는 예시이고, 실무 사례 절은 100만 행 규모의 운영 테이블을 가정합니다. 실제 숫자는 데이터 분포와 통계 상태에 따라 달라집니다.

느린 쿼리 분석

예제 쿼리 1: 단일 조건

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 42;

출력:

+----+-------------+--------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys | key      | key_len | ref   | rows | Extra |
+----+-------------+--------+------+---------------+----------+---------+-------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_user      | idx_user | 8       | const |    5 | NULL  |
+----+-------------+--------+------+---------------+----------+---------+-------+------+-------+

분석:

• type: ref (인덱스 사용)
• key: idx_user (예상대로)
• rows: 5 (예상 검사 행 수)
• Extra: NULL (추가 처리 없음) 결론: 인덱스 잘 사용됨

예제 쿼리 2: 복합 조건 (인덱스 미사용)

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

출력:

+----+-------------+--------+------+---------------+----------+---------+-------+------+-----------------------------+
| id | select_type | table  | type | possible_keys | key      | key_len | ref   | rows | Extra                       |
+----+-------------+--------+------+---------------+----------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | orders | ref  | idx_user      | idx_user | 8       | const |    5 | Using where; Using filesort |
+----+-------------+--------+------+---------------+----------+---------+-------+------+-----------------------------+

분석:

• type: ref (인덱스 사용)

• key: idx_user (user_id 인덱스만 사용)

• Extra: Using where; Using filesort

  • Using where: status 조건은 인덱스 미사용 (필터링)
  • Using filesort: ORDER BY created_at을 위해 정렬 필요 문제점:

• status 조건이 인덱스 미사용

• 정렬을 위한 filesort 발생

복합 인덱스 추가

-- user_id, status, created_at 순서로 복합 인덱스
ALTER TABLE orders
  ADD INDEX idx_user_status_created (user_id, status, created_at DESC);

인덱스 컬럼 순서 규칙:

1. 동등 조건 (=) 먼저
2. 범위 조건 (>, <, BETWEEN) 다음
3. 정렬 컬럼 마지막

이 순서가 중요한 이유는 B-Tree 인덱스가 앞 컬럼부터 차례로 정렬된 구조이기 때문입니다. (user_id, status, created_at)에서 user_id = 42 AND status = 'paid'로 두 컬럼을 고정하면 그 범위 안의 행은 이미 created_at 순으로 놓여 있어 정렬이 필요 없습니다. 반대로 범위 조건 컬럼이 중간에 오면(예: (user_id, created_at, status)에 created_at > ?) 그 뒤 컬럼은 정렬 순서도 범위 좁히기도 쓸 수 없고, key_len을 보면 인덱스의 앞부분만 쓰였음을 확인할 수 있습니다. 인덱스를 추가할 때는 쓰기 비용도 함께 늘어난다는 점을 기억해야 합니다. idx_user는 이제 새 복합 인덱스의 앞부분과 겹치므로, 다른 쿼리가 쓰지 않는다면 지워서 INSERT 비용과 버퍼 풀 사용량을 줄이는 것이 좋습니다.

재확인

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

출력:

+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------+------+-------+
| id | select_type | table  | type | possible_keys             | key                       | key_len | ref         | rows | Extra |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_user,idx_user_status_created | idx_user_status_created | 74      | const,const |    2 | NULL  |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------+------+-------+

개선 사항:

• key: idx_user_status_created (복합 인덱스 사용)
• rows: 5 → 2 (예상 행 수 감소)
• Extra: NULL (filesort 사라짐)

EXPLAIN ANALYZE (실제 실행)

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20\G

출력:

-> Limit: 20 row(s)  (cost=2.25 rows=2) (actual time=0.123..0.145 rows=2 loops=1)
    -> Index lookup on orders using idx_user_status_created (user_id=42, status='paid')  (cost=2.25 rows=2) (actual time=0.121..0.143 rows=2 loops=1)

분석:

• cost: 2.25 (옵티마이저 비용 추정)
• actual time: 0.123..0.145 (밀리초 단위, 앞은 첫 행을 내기까지의 시간, 뒤는 모든 행을 내기까지의 시간)
• rows: 2 (실제 반환 행 수, 괄호 앞 rows=2인 추정치와 비교)
• loops: 1 (이 노드가 실행된 횟수, 조인 안쪽 노드라면 loops × rows가 실제 처리 행 수)

추정 행 수와 실제 행 수가 비슷하고 정렬 노드가 없으므로 인덱스가 의도대로 쓰였습니다.

커버링 인덱스 (Covering Index)

시나리오: id, user_id, status만 필요한 쿼리. 복합 인덱스를 만들기 전, idx_user만 있는 상태라고 가정합니다.

-- 기존 쿼리
EXPLAIN
SELECT id, user_id, status
FROM orders
WHERE user_id = 42 AND status = 'paid';

출력:

Extra: Using where

커버링 인덱스 추가:

-- 필요한 컬럼을 모두 인덱스에 포함
-- (InnoDB 보조 인덱스에는 PK(id)가 자동으로 들어 있으므로,
--  이미 idx_user_status_created가 있다면 이 인덱스는 중복입니다)
ALTER TABLE orders
  ADD INDEX idx_covering (user_id, status, id);

재확인:

EXPLAIN
SELECT id, user_id, status
FROM orders
WHERE user_id = 42 AND status = 'paid';

출력:

Extra: Using index

분석:

• Using index: 인덱스만으로 쿼리 완료 (테이블 접근 불필요)
• 성능 향상: 클러스터 인덱스 접근 생략

InnoDB에서 커버링 인덱스를 설계할 때 알아 둘 특성이 있습니다. 보조 인덱스의 각 항목에는 기본 키 값이 항상 함께 저장되어, 보조 인덱스로 찾은 뒤 그 PK로 클러스터 인덱스를 다시 찾아가는 구조입니다. 그래서 id를 인덱스에 명시하지 않아도 (user_id, status)만으로 SELECT id, user_id, status는 이미 커버되고, 앞 절의 idx_user_status_created가 있다면 위 쿼리의 Extra에는 처음부터 Using index가 나옵니다. 커버링 인덱스의 이득은 행마다 일어나는 클러스터 인덱스 랜덤 접근을 없애는 데서 오므로, 검사 행이 많을수록 효과가 큽니다. 반대로 SELECT *를 쓰는 순간 커버링은 불가능해지므로, 목록 API처럼 자주 호출되는 쿼리에서는 필요한 컬럼만 고르는 것이 인덱스 설계의 전제가 됩니다.

통계 갱신

-- 테이블 통계 갱신
ANALYZE TABLE orders;
-- 히스토그램 생성 (8.0+, 인덱스가 없는 컬럼에 유용)
ANALYZE TABLE orders UPDATE HISTOGRAM ON status;
-- 히스토그램 확인
SELECT * FROM information_schema.COLUMN_STATISTICS
WHERE TABLE_NAME = 'orders';

언제 실행:

• 대량 INSERT/UPDATE 후
• 인덱스 추가 후
• 쿼리 계획이 이상할 때

히스토그램은 인덱스가 없는 컬럼의 값 분포를 옵티마이저에 알려 주는 도구입니다. 인덱스가 있는 컬럼은 옵티마이저가 인덱스 다이브나 인덱스 통계로 행 수를 추정하므로, 같은 컬럼에 히스토그램을 만들어도 대개 쓰이지 않습니다.

---

고급 활용

Optimizer Hints

인덱스 우선 사용 (USE INDEX는 권고, 확실히 강제하려면 FORCE INDEX):

SELECT *
FROM orders USE INDEX (idx_user_status_created)
WHERE user_id = 42 AND status = 'paid';

인덱스 무시:

SELECT *
FROM orders IGNORE INDEX (idx_user)
WHERE user_id = 42;

조인 순서 힌트:

SELECT /*+ JOIN_ORDER(orders, users) */ *
FROM orders
JOIN users ON orders.user_id = users.id
WHERE orders.status = 'paid';

주의사항:

• 힌트는 최후의 수단
• 옵티마이저가 잘못 선택하는 이유 먼저 파악
• 통계 갱신으로 해결 가능한 경우 많음

USE INDEX는 “이 인덱스들만 고려하라”는 권고라서, 옵티마이저가 풀 스캔이 더 싸다고 판단하면 여전히 풀 스캔을 택할 수 있습니다. FORCE INDEX는 테이블 스캔을 매우 비싸게 간주하게 만들어 사실상 강제합니다. 힌트가 위험한 이유는 데이터가 바뀌어도 힌트는 그대로라는 점입니다. 오늘은 옳았던 인덱스가 데이터 분포가 바뀐 몇 달 뒤에는 최악의 선택이 되어도 옵티마이저가 고칠 수 없고, 힌트에 적힌 인덱스 이름이 바뀌거나 삭제되면 Key 'idx_...' doesn't exist in table 에러로 쿼리 자체가 실패합니다. 힌트를 넣는다면 왜 넣었는지 주석으로 남기고 주기적으로 다시 검토하는 것이 좋습니다.

슬로우 쿼리 로그

설정

-- 슬로우 쿼리 로그 활성화
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 1초 이상 쿼리
SET GLOBAL log_queries_not_using_indexes = 'ON';
-- 로그 파일 위치 확인
SHOW VARIABLES LIKE 'slow_query_log_file';

분석 (pt-query-digest)

# Percona Toolkit 설치
sudo apt-get install percona-toolkit
# 슬로우 쿼리 로그 분석
pt-query-digest /var/log/mysql/slow.log > report.txt

리포트는 쿼리를 리터럴을 지운 패턴(fingerprint)으로 묶고, 전체 실행 시간에서 차지하는 비중이 큰 순서로 보여 줍니다. 먼저 볼 것은 각 쿼리의 Exec time 비중과 Rows examine 대 Rows sent 비율입니다. 20행을 돌려주려고 수천 행을 검사하는 쿼리가 있다면 그 쿼리가 인덱스 개선의 첫 후보입니다.

Performance Schema

-- performance_schema는 MySQL 5.6.6 이후 기본으로 켜져 있음
-- 평균 실행 시간 상위 10개 (TIMER 값은 피코초 단위)
SELECT 
  DIGEST_TEXT,
  COUNT_STAR as exec_count,
  AVG_TIMER_WAIT / 1000000000000 as avg_sec,
  SUM_ROWS_EXAMINED as total_rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

---

성능·비교

접근 방식 비교

type	예상 비용	인덱스 사용	시나리오
const	매우 낮음	PK/유니크	WHERE id = 1
eq_ref	낮음	PK/유니크 조인	JOIN ON pk
ref	낮음~중간	비유니크 인덱스	WHERE user_id = 42
range	중간	인덱스 범위	WHERE created_at > '2026-01-01'
index	높음	인덱스 풀 스캔	SELECT id FROM orders (커버링)
ALL	매우 높음	없음	WHERE YEAR(created_at) = 2026

인덱스에 따라 달라지는 작업량

예시 조건:

• 테이블: 100만 행
• 쿼리: WHERE user_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 20 | 인덱스 | type | 검사하는 행 | Extra에서 볼 것 | |--------|------|------|----------| | 없음 | ALL | 테이블 전체 | Using where; Using filesort — 전체를 읽고 걸러낸 뒤 정렬 | | idx_user | ref | 해당 사용자의 모든 주문 | Using where; Using filesort — status는 행을 읽어서 거르고, 정렬도 따로 수행 | | idx_user_status_created | ref | LIMIT 20에 필요한 만큼 | filesort 없음 — 인덱스가 이미 created_at 순서라 앞에서 20개만 읽고 멈춤 |

결론: 복합 인덱스 (user_id, status, created_at)는 WHERE의 두 조건으로 범위를 좁히고, 그 범위 안이 이미 created_at 순으로 정렬되어 있어 정렬 단계와 불필요한 행 읽기를 함께 없앱니다. 실제 개선 폭은 사용자당 주문 수와 버퍼 풀 적중률에 따라 다르므로, EXPLAIN ANALYZE(MySQL 8.0.18+)로 인덱스 전후의 실제 검사 행 수와 시간을 비교하세요.

---

실무 사례

사례 1: 목록 API - 풀 스캔 제거

Before:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN 출력:

type: ALL
rows: 1000000
Extra: Using filesort

문제점:

• 인덱스 미사용 (WHERE 조건 없음)
• 전체 테이블 스캔 후 정렬 After:

-- created_at 인덱스 추가 (앞의 스키마처럼 idx_created가 이미 있다면 생략 가능:
-- 오름차순 인덱스도 역방향으로 스캔해 DESC 정렬을 처리할 수 있음)
ALTER TABLE orders
  ADD INDEX idx_created_desc (created_at DESC);
-- 쿼리 재실행
SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN 출력:

type: index
key: idx_created_desc
rows: 20
Extra: NULL

개선 사항:

• type: ALL → index
• rows: 1,000,000 → 20
• Extra: filesort 사라짐
• 실행 시간: 전체 정렬이 사라지고 LIMIT만큼만 읽으므로 테이블이 클수록 차이가 커짐 (EXPLAIN ANALYZE로 전후 비교)

사례 2: OR 조건 - UNION 분할

Before:

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 42 OR product_id = 100;

EXPLAIN 출력:

type: ALL
key: NULL
rows: 1000000
Extra: Using where

문제점:

• OR 조건으로 인덱스 미사용
• 풀 스캔 발생

OR 조건이 항상 풀 스캔이 되는 것은 아닙니다. 양쪽 컬럼에 각각 인덱스가 있으면 MySQL은 두 인덱스 결과를 합치는 index merge(Extra에 Using union(idx_user,idx_product))를 쓸 수 있습니다. 앞의 스키마에는 product_id 인덱스가 없으므로, 아래 예시는 idx_product (product_id)를 추가했다고 가정합니다. 한쪽 컬럼에 인덱스가 없거나, 옵티마이저가 합치는 비용이 더 크다고 판단하면 풀 스캔으로 갑니다. 아래처럼 UNION으로 나누면 각 SELECT가 독립적으로 가장 좋은 인덱스를 쓰게 되지만, 두 조건을 모두 만족하는 행이 중복되지 않게 하려면 UNION(중복 제거, 추가 정렬 비용)을 쓰거나 두 번째 쿼리에 첫 조건의 부정을 넣고 UNION ALL을 쓰는 식으로 의도를 분명히 해야 합니다. After:

-- UNION으로 분할
EXPLAIN
SELECT * FROM orders WHERE user_id = 42
UNION
SELECT * FROM orders WHERE product_id = 100;

EXPLAIN 출력:

-- 첫 번째 SELECT
type: ref
key: idx_user
rows: 5
-- 두 번째 SELECT
type: ref
key: idx_product
rows: 3

개선 사항:

• 각 SELECT가 인덱스 사용
• 실행 시간: 풀 스캔 대신 두 번의 인덱스 탐색이 되므로, 각 조건에 해당하는 행이 적을수록 크게 개선

사례 3: 함수로 감싼 컬럼 - 범위 조건 변환

Before:

EXPLAIN
SELECT *
FROM orders
WHERE DATE(created_at) = '2026-03-30';

EXPLAIN 출력:

type: ALL
key: NULL
rows: 1000000
Extra: Using where

문제점:

• DATE() 함수로 인덱스 미사용
• 풀 스캔 발생 After:

-- 범위 조건으로 변환
EXPLAIN
SELECT *
FROM orders
WHERE created_at >= '2026-03-30 00:00:00'
  AND created_at < '2026-03-31 00:00:00';

EXPLAIN 출력:

type: range
key: idx_created
rows: 150
Extra: Using index condition

개선 사항:

• type: ALL → range
• key: idx_created 사용
• 실행 시간: 해당 날짜 범위의 행만 읽으므로 전체 대비 비율만큼 줄어듦

사례 4: 조인 최적화

Before:

EXPLAIN
SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid';

EXPLAIN 출력:

-- orders 테이블
type: ALL
key: NULL
rows: 1000000
Extra: Using where
-- users 테이블
type: eq_ref
key: PRIMARY
rows: 1

문제점:

• orders 테이블 풀 스캔
• status 인덱스 미사용

status처럼 값의 종류가 몇 개뿐인 컬럼에 단독 인덱스를 거는 것은 조심해야 합니다. paid가 전체 주문의 대부분이라면 인덱스로 행을 하나씩 찾아가는 것보다 테이블을 순서대로 읽는 편이 싸서 옵티마이저가 인덱스를 무시하고, 드문 값(refunded)을 찾을 때만 인덱스가 효과를 냅니다. 인덱스를 만들지 않은 상태라면 히스토그램(ANALYZE TABLE orders UPDATE HISTOGRAM ON status)이 이런 치우친 분포를 옵티마이저에 알려 조인 순서 같은 추정을 개선하고, 자주 함께 쓰이는 조건이 있다면 (status, created_at)처럼 복합 인덱스로 만드는 편이 낫습니다. After:

-- status 인덱스 추가
ALTER TABLE orders
  ADD INDEX idx_status (status);
-- 쿼리 재실행
EXPLAIN
SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid';

EXPLAIN 출력:

-- orders 테이블
type: ref
key: idx_status
rows: 5000
-- users 테이블
type: eq_ref
key: PRIMARY
rows: 1

개선 사항:

• type: ALL → ref
• rows: 1,000,000 → 5,000
• 실행 시간: paid 비율이 낮을수록 크게 개선되며, 비율이 높으면 옵티마이저가 인덱스를 무시할 수 있음

---

트러블슈팅

문제 1: 인덱스를 만들었는데 key가 NULL

증상:

ALTER TABLE orders ADD INDEX idx_status (status);
EXPLAIN SELECT * FROM orders WHERE status = 'paid';
-- key: NULL (인덱스 미사용)

원인 1: 통계 오래됨

ANALYZE TABLE orders;

원인 2: 카디널리티 부족

-- status 값 분포 확인
SELECT status, COUNT(*) as cnt
FROM orders
GROUP BY status;
-- 결과: 대부분 'paid' (선택도 낮음)
-- paid: 950000
-- pending: 30000
-- shipped: 15000
-- cancelled: 5000

해결: 선택도가 낮으면 옵티마이저가 풀 스캔 선택 가능 (정상) 원인 3: 데이터 타입 불일치

-- status는 VARCHAR인데 숫자로 비교
WHERE status = 1  -- 암시적 변환 → 인덱스 미사용
-- 해결
WHERE status = '1'

문제 2: rows가 실제와 크게 다름

증상:

EXPLAIN SELECT * FROM orders WHERE user_id = 42;
-- rows: 5 (예상)
-- 실제 실행
SELECT COUNT(*) FROM orders WHERE user_id = 42;
-- 결과: 500 (실제)

원인: 인덱스 통계가 오래되었거나, 대량 변경 뒤 InnoDB 영구 통계가 아직 다시 계산되지 않음 해결:

-- 통계 갱신
ANALYZE TABLE orders;

InnoDB는 인덱스의 일부 페이지만 샘플링해 통계를 만들기 때문에, 분포가 치우친 테이블에서는 갱신 후에도 오차가 남을 수 있습니다. 이때는 테이블별 STATS_SAMPLE_PAGES를 늘리는 방법이 있습니다. 히스토그램은 user_id처럼 인덱스가 있는 컬럼의 추정에는 대개 쓰이지 않으므로 이 문제의 해결책이 아닙니다.

문제 3: Using temporary 발생

증상:

EXPLAIN
SELECT user_id, COUNT(*) as cnt
FROM orders
GROUP BY user_id
ORDER BY cnt DESC
LIMIT 10;
-- Extra: Using temporary; Using filesort

원인: 그룹핑 컬럼(user_id)을 인덱스 순서로 읽지 못하면 집계를 위해 임시 테이블이 필요하고, 정렬 기준(cnt)이 집계 결과이므로 그룹핑이 끝난 뒤 별도 정렬이 반드시 필요합니다.

해결: user_id 인덱스가 있으면 옵티마이저가 인덱스를 순서대로 읽으며 그룹핑해 Using temporary를 없앨 수 있습니다(Using index로 커버링되기도 합니다). 그래도 ORDER BY cnt는 집계 값이라 인덱스로 미리 정렬해 둘 수 없으므로 Using filesort는 남습니다. 다만 정렬 대상이 원본 행이 아니라 사용자 수만큼의 그룹 결과라 대부분 부담이 크지 않습니다. 서브쿼리로 감싸는 것만으로는 정렬이 사라지지 않으며, 이 쿼리가 자주 호출된다면 사용자별 주문 수를 별도 집계 테이블에 유지하는 쪽이 근본적인 해결입니다.

-- 이미 idx_user가 있다면 생략
ALTER TABLE orders ADD INDEX idx_user (user_id);

문제 4: 로컬에선 빠른데 운영만 느림

원인 1: 데이터 양 차이

-- 로컬: 1000행
-- 운영: 100만 행
-- 해결: 운영 데이터 샘플로 로컬 테스트

원인 2: 버퍼 풀 워밍업

-- 버퍼 풀 상태 확인
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 버퍼 풀 크기 조정 (my.cnf)
[mysqld]
innodb_buffer_pool_size = 8G

원인 3: 동시 쿼리

-- 현재 실행 중인 쿼리 확인
SHOW PROCESSLIST;
-- 또는 Performance Schema
SELECT * FROM performance_schema.threads
WHERE TYPE = 'FOREGROUND';

---

같이 보면 좋은 글

• SQL 쿼리 최적화 실전 가이드 | 인덱스·실행 계획
• 직접 만든 쿼리 엔진 최적화
• 데이터베이스 인덱싱 심화 | B-트리·클러스터드·스캔/시크·커버링·운영 패턴

---