슬로우 쿼리 튜닝 — 원인 분석부터 최적화까지
운영 중인 서비스에서 "이 페이지가 갑자기 느려졌어요"라는 요청을 받았을 때, 가장 먼저 무엇을 확인해야 할까요?
슬로우 쿼리 로그 설정
MySQL은 실행 시간이 일정 기준을 넘는 쿼리를 자동으로 기록하는 슬로우 쿼리 로그를 제공합니다.
-- 슬로우 쿼리 로그 활성화
SET GLOBAL slow_query_log = ON;
-- 임계값 설정 (기본 10초 → 실무에서는 1~2초 권장)
SET GLOBAL long_query_time = 1;
-- 인덱스를 사용하지 않는 쿼리도 기록
SET GLOBAL log_queries_not_using_indexes = ON;
-- 로그 파일 위치 확인
SHOW VARIABLES LIKE 'slow_query_log_file';
슬로우 쿼리 로그 예시
# Time: 2026-03-19T10:15:30.123456Z
# User@Host: app_user[app_user] @ 10.0.1.50 [10.0.1.50]
# Query_time: 3.234567 Lock_time: 0.000123
# Rows_sent: 50 Rows_examined: 1500000
SET timestamp=1742554530;
SELECT * FROM orders WHERE status = 'pending'
ORDER BY created_at DESC LIMIT 50;
핵심 지표는 다음과 같습니다.
- Query_time: 쿼리 실행 시간
- Lock_time: 락 대기 시간
- Rows_sent: 클라이언트에 반환된 행 수
- Rows_examined: 서버가 검사한 행 수
Rows_examined >> Rows_sent이면 비효율적인 쿼리입니다. 150만 행을 읽어서 50행만 반환하는 위 예시가 대표적입니다.
pt-query-digest로 로그 분석
슬로우 쿼리 로그가 수천 줄이면 수작업으로 분석하기 어렵습니다. Percona Toolkit의 pt-query-digest가 이를 자동화합니다.
# 설치 (Linux)
apt install percona-toolkit
# 슬로우 쿼리 로그 분석
pt-query-digest /var/lib/mysql/slow-query.log
출력 예시:
# Profile
# Rank Query ID Response time Calls
# ==== ================================= ============= =====
# 1 0xABCD1234567890EF 45.2345 32.1% 234
# 2 0x1234ABCDEF567890 28.1234 20.0% 1502
# 3 0x567890ABCDEF1234 15.6789 11.1% 45
# Query 1: 234 QPS, 0.19 concurrency
# Scores: V/M = 0.05
# Time range: 2026-03-18 to 2026-03-19
# Attribute pct total min max avg 95%
# ============ === ===== === === === ===
# Count 15 234
# Exec time 32 45s 100ms 3s 193ms 500ms
# Rows sent 2 11700 50 50 50 50
# Rows exam 82 350M 1.5M 1.5M 1.5M 1.5M
가장 많은 시간을 소비하는 쿼리부터 순서대로 보여주므로, 어디에 집중해야 하는지 바로 알 수 있습니다.
흔한 슬로우 쿼리 패턴과 해결
1. 인덱스 미사용 — 함수 적용
-- 느린 쿼리: 컬럼에 함수 적용
SELECT * FROM orders WHERE YEAR(created_at) = 2025;
-- type: ALL (풀 스캔)
-- 개선: 범위 조건으로 변경
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';
-- type: range (인덱스 사용)
-- 느린 쿼리: 묵시적 형변환
SELECT * FROM users WHERE phone = 01012345678;
-- phone이 VARCHAR인데 숫자로 비교 → 인덱스 미사용
-- 개선: 올바른 타입으로 비교
SELECT * FROM users WHERE phone = '01012345678';
2. OFFSET 기반 페이지네이션
-- 느린 쿼리: 깊은 페이지
SELECT * FROM articles ORDER BY id DESC LIMIT 20 OFFSET 100000;
-- 100,000행을 읽고 버린 후 20행 반환
-- 개선 방법 1: 커서 기반 페이지네이션 (Keyset Pagination)
SELECT * FROM articles
WHERE id < 마지막으로_본_id
ORDER BY id DESC
LIMIT 20;
-- WHERE 조건으로 시작점을 지정하므로 OFFSET이 필요 없음
커서 기반 방식은 OFFSET 값에 관계없이 일정한 성능을 보장합니다.
-- 개선 방법 2: 커버링 인덱스 + 서브쿼리
SELECT a.* FROM articles a
JOIN (
SELECT id FROM articles ORDER BY id DESC LIMIT 20 OFFSET 100000
) t ON a.id = t.id;
-- 서브쿼리에서 PK만 추출 (커버링 인덱스로 빠름)
-- 본 쿼리에서 필요한 컬럼을 조인으로 가져옴
3. Using temporary + Using filesort
-- 느린 쿼리
SELECT department, COUNT(*) cnt FROM employees
GROUP BY department
ORDER BY cnt DESC;
-- Extra: Using temporary; Using filesort
-- 개선: GROUP BY와 ORDER BY를 같은 인덱스로
CREATE INDEX idx_department ON employees(department);
-- 또는 정렬 방향이 달라야 하면 애플리케이션에서 정렬
GROUP BY와 ORDER BY가 서로 다른 컬럼을 참조하면 임시 테이블과 파일 정렬이 모두 필요합니다. 가능하면 같은 인덱스를 활용하도록 쿼리를 재설계합니다.
4. 불필요한 SELECT *
-- 느린 쿼리: 모든 컬럼 조회
SELECT * FROM orders WHERE customer_id = 42;
-- 개선: 필요한 컬럼만 조회 (커버링 인덱스 활용)
SELECT order_id, status, total_amount
FROM orders WHERE customer_id = 42;
-- 커버링 인덱스
CREATE INDEX idx_customer_covering
ON orders(customer_id, order_id, status, total_amount);
5. 큰 IN 리스트
-- 느린 쿼리: 수천 개의 ID
SELECT * FROM products WHERE id IN (1, 2, 3, ..., 5000);
-- 개선: 임시 테이블로 조인
CREATE TEMPORARY TABLE tmp_ids (id INT PRIMARY KEY);
INSERT INTO tmp_ids VALUES (1), (2), (3), ...;
SELECT p.* FROM products p JOIN tmp_ids t ON p.id = t.id;
6. 상관 서브쿼리
-- 느린 쿼리: 행마다 서브쿼리 실행
SELECT *, (
SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id
) AS item_count
FROM orders o;
-- 개선: JOIN으로 변환
SELECT o.*, COALESCE(oi.item_count, 0) AS item_count
FROM orders o
LEFT JOIN (
SELECT order_id, COUNT(*) AS item_count
FROM order_items GROUP BY order_id
) oi ON o.id = oi.order_id;
쿼리 프로파일링
SHOW PROFILE (레거시)
SET profiling = 1;
SELECT * FROM orders WHERE customer_id = 42;
SHOW PROFILE FOR QUERY 1;
+--------------------+-----------+
| Status | Duration |
+--------------------+-----------+
| Starting | 0.000050 |
| Checking permiss.. | 0.000010 |
| Opening tables | 0.000020 |
| Init | 0.000015 |
| Executing | 0.003200 | ← 실행 시간
| Sending data | 0.001500 | ← 데이터 전송
| End | 0.000005 |
+--------------------+-----------+
Performance Schema (권장)
-- 쿼리별 실행 통계
SELECT
DIGEST_TEXT,
COUNT_STAR AS exec_count,
AVG_TIMER_WAIT / 1000000000 AS avg_ms,
SUM_ROWS_EXAMINED AS total_rows_examined,
SUM_ROWS_SENT AS total_rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;
튜닝 체크리스트
쿼리 성능 문제를 발견했을 때 순서대로 점검합니다.
-
EXPLAIN으로 실행 계획 확인
- type이 ALL인가?
- key가 NULL인가?
- Extra에 Using filesort / Using temporary가 있는가?
-
** 인덱스 확인**
- WHERE 절 컬럼에 인덱스가 있는가?
- 함수나 형변환으로 인덱스가 무효화되지 않았는가?
- 복합 인덱스의 컬럼 순서가 적절한가?
-
** 쿼리 구조 확인**
- SELECT *를 사용하고 있지는 않은가?
- OFFSET이 큰 페이지네이션인가?
- 상관 서브쿼리를 JOIN으로 바꿀 수 있는가?
-
통계 정보 확인
- ANALYZE TABLE로 통계를 갱신했는가?
- Histogram이 필요한 컬럼이 있는가?
-
** 서버 설정 확인**
- Buffer Pool 크기가 충분한가?
- sort_buffer_size, join_buffer_size가 적절한가?
주의할 점
Rows_examined >> Rows_sent이면 비효율 신호다
150만 행을 읽어서 50행만 반환하는 쿼리는 대부분의 읽기가 낭비입니다. 인덱스를 추가하거나 쿼리 구조를 변경하여 검사 행 수를 줄여야 합니다.
long_query_time 기본값 10초는 너무 길다
실무에서 10초짜리 쿼리는 이미 서비스에 문제를 일으키고 있을 가능성이 높습니다. 1~2초로 낮춰서 조기에 문제 쿼리를 발견하는 것이 좋습니다.
쿼리 하나를 최적화해도 전체 부하의 핵심이 아닐 수 있다
pt-query-digest의 결과에서 총 소요 시간(Response time) 기준으로 가장 많은 비중을 차지하는 쿼리부터 최적화해야 합니다. 개별 실행 시간이 길더라도 실행 횟수가 1회인 쿼리보다, 실행 시간이 짧지만 수만 번 실행되는 쿼리가 더 큰 문제일 수 있습니다.
정리
| 항목 | 설명 |
|---|---|
| 핵심 지표 | Rows_examined vs Rows_sent 비율 |
| 슬로우 로그 | long_query_time을 1~2초로 설정, pt-query-digest로 분석 |
| OFFSET 페이지네이션 | 깊은 페이지에서 느림, 커서 기반(Keyset)으로 대체 |
| Using filesort/temporary | 인덱스 설계 또는 쿼리 구조 변경으로 제거 |
| SELECT * | 커버링 인덱스 불가, 필요한 컬럼만 명시 |
| 튜닝 원칙 | EXPLAIN으로 시작, 측정으로 끝남 |
댓글 로딩 중...