DB 인덱스 슬로우 쿼리 때문에 이틀을 날렸다. 원인을 찾는 데 이틀, 해결하는 데 5분이 걸린 이야기다. 유저 목록 API가 운영에서 갑자기 30초씩 걸리기 시작했고, 결국 동료의 한마디가 끝냈다.
상황: 갑자기 느려진 API
created_at 기준으로 최근 가입자를 정렬해서 가져오는 단순한 쿼리였다.
SELECT * FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 20;로컬 환경에서는 0.1초도 안 걸렸다. 운영 DB에 데이터가 50만 건을 넘어가면서 이 쿼리가 30초 이상 걸리기 시작했다. 로컬 DB에는 테스트 데이터가 200건밖에 없었으니 당연히 체감이 안 됐던 것이다. 로컬에서 빠른 게 운영에서도 빠르다는 보장은 없다.
시도 1: 쿼리를 바꿔봤다 — 실패
처음에는 쿼리 자체가 비효율적인 줄 알았다. SELECT * 대신 필요한 컬럼만 지정하고, 서브쿼리로 분리도 해봤다.
SELECT id, name, email, created_at FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 20;결과는 29초. 거의 차이가 없었다. 컬럼 수가 문제가 아니었다.
시도 2: 서버 스펙 의심 — 헛짚음
“운영 서버 DB 스펙이 부족한 건가?” 싶어서 모니터링을 확인했다. CPU 20%, 메모리 여유. 서버 문제가 아니었다. 원인은 더 아래에 있었다.
DB 슬로우 쿼리 진단: EXPLAIN 한 줄이면 답이 보인다
결국 동료가 한마디 했다. “EXPLAIN 찍어봤어?” 그 한 줄이 답을 줬다.
EXPLAIN SELECT * FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 20;결과에서 type: ALL이 보였다.
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 498234 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+세 가지가 바로 눈에 들어왔다.
type: ALL— 풀 테이블 스캔(Full Table Scan, 풀스캔). 50만 건을 처음부터 끝까지 전부 훑는다key: NULL— 쓴 인덱스가 없다Extra: Using filesort— ORDER BY를 인덱스 없이 처리하느라 별도 정렬을 한 번 더 한다
복합 인덱스를 추가했다.
CREATE INDEX idx_users_status_created
ON users (status, created_at DESC);인덱스 추가 후 EXPLAIN을 다시 찍었다.
+----+-------------+-------+------+--------------------------+--------------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+--------------------------+--------------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | users | ref | idx_users_status_created | idx_users_status_created | 767 | const | 20 | Using index condition |
+----+-------------+-------+------+--------------------------+--------------------------+---------+-------+------+-----------------------+type: ref, rows: 20. Using filesort도 사라졌다. 쿼리 실행 시간: 30초 → 0.02초.
EXPLAIN 출력에서 type과 rows가 핵심이다. type이 ALL이면 인덱스가 없거나 옵티마이저가 인덱스를 무시 중이고, rows가 테이블 전체 행 수에 가까우면 풀 스캔이 진행 중이다. (MySQL EXPLAIN 공식 문서)
왜 인덱스 없으면 슬로우 쿼리가 발생하는가
인덱스가 없으면 DB는 조건에 맞는 행을 찾기 위해 테이블 전체를 스캔한다. 데이터가 적을 때는 느끼지 못하지만, 수십만 건을 넘어가면 기하급수적으로 느려진다.
복합 인덱스를 (status, created_at DESC) 순서로 만든 이유가 있다. WHERE 절의 등호(=) 조건 컬럼을 앞에, ORDER BY 컬럼을 뒤에 배치하는 것이 기본 원칙이다. status 컬럼은 카디널리티(선택도)가 낮더라도 등호 조건에 쓰이므로 앞에 와야 한다. 순서를 (created_at, status)로 바꾸면 status = 'active' 필터를 인덱스에서 효율적으로 처리하지 못하고 Using filesort가 다시 나타난다.
(status, created_at DESC) 인덱스가 있으면 DB는 status = 'active'인 행만 인덱스에서 바로 찾고, 이미 created_at 기준으로 정렬된 상태에서 상위 20건만 꺼내면 된다. rows가 498234에서 20으로 줄어든 이유다.
참고로 인덱스는 읽기를 빠르게 하지만 쓰기를 느리게 한다. INSERT, UPDATE, DELETE가 일어날 때마다 인덱스도 같이 갱신되기 때문이다. 자주 조회되는 쿼리 패턴에만 추가해야 한다.
이 삽질에서 배운 것
첫째, 로컬과 운영의 데이터 규모 차이를 항상 고려하자. 로컬에서 빠르다고 안심하면 안 된다. 운영 환경과 비슷한 규모의 더미 데이터로 테스트하는 습관이 필요하다.
둘째, 느린 쿼리는 EXPLAIN부터 찍자. 원인 추측으로 시간을 낭비하지 말고, 실행 계획을 먼저 확인하면 대부분 답이 보인다.
셋째, WHERE + ORDER BY 조합에는 복합 인덱스를 고려하자. 단일 컬럼 인덱스로는 정렬까지 커버하지 못할 수 있다.
운영에서 슬로우 쿼리를 자동으로 감지하려면 MySQL 슬로우 쿼리 로그를 켜두는 게 좋다. 특정 시간 이상 걸리는 쿼리가 자동으로 파일에 쌓인다.
-- 1초 이상 걸리는 쿼리 자동 기록
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SHOW VARIABLES LIKE 'slow_query_log_file';DB 인덱스 슬로우 쿼리 진단 요약
| EXPLAIN 증상 | 원인 | 해결책 |
|---|---|---|
type: ALL | 인덱스 없음 | WHERE 컬럼에 인덱스 추가 |
type: ALL + Using filesort | 인덱스 없음 + 정렬 비효율 | WHERE + ORDER BY 복합 인덱스 |
type: ref + Using filesort | ORDER BY 컬럼이 인덱스 범위 밖 | 복합 인덱스 컬럼 순서 재검토 |
rows가 전체 행 수와 비슷 | 풀 테이블 스캔 진행 중 | 인덱스 누락 또는 옵티마이저 무시 |
DB 인덱스 슬로우 쿼리 자주 묻는 질문
EXPLAIN 결과에서 먼저 봐야 할 컬럼은?
type 컬럼을 먼저 본다. ALL이면 풀 테이블 스캔, ref나 range면 인덱스를 쓰고 있다는 뜻이다. 다음으로 rows를 확인한다. 테이블 전체 행 수와 비슷하면 거의 전부 스캔 중이다. Extra의 Using filesort는 정렬 인덱스가 없다는 신호다.
복합 인덱스 컬럼 순서는 어떻게 정하나?
WHERE 절의 등호(=) 조건 컬럼을 앞에 놓고, ORDER BY나 범위 조건 컬럼을 뒤에 놓는 것이 기본이다. 이 순서가 틀리면 인덱스를 추가해도 Using filesort가 사라지지 않는다.
인덱스를 걸었는데도 여전히 느리면?
옵티마이저가 인덱스를 무시하는 케이스다. WHERE 절에서 컬럼에 함수를 쓰거나(WHERE DATE(created_at) = '2024-01-01'), 묵시적 형변환이 일어나거나, 특정 값으로 데이터가 극단적으로 쏠려 있으면 옵티마이저가 인덱스보다 풀 스캔이 낫다고 판단하기도 한다. 이 케이스는 EXPLAIN 결과에서 possible_keys에 인덱스가 있는데 key가 NULL로 나타난다.
한줄 정리: DB 인덱스 슬로우 쿼리가 생기면 코드보다 EXPLAIN을 먼저 찍자.
type: ALL이 보이면 인덱스 누락이고, WHERE 컬럼 + ORDER BY 컬럼을 묶은 복합 인덱스 하나로 대부분 해결된다.