MySQL 슬로우 쿼리 로그 분석 가이드
MySQL 슬로우 쿼리 로그(Slow Query Log)는 쿼리 성능 최적화 작업을 수행할 때 가장 중요한 출발점 중 하나이다.
이 로그는 설정된 임계 시간보다 오래 걸리는 모든 쿼리를 기록하여, 성능 병목을 유발하는 쿼리를 식별할 수 있도록 도와준다.
슬로우 쿼리 로그를 활성화하고, 분석하며, 이를 통해 쿼리를 개선하는 전체 과정을 단계별로 알아보자.
1. 슬로우 쿼리 로그 활성화 방법
1.1 설정 파일을 통한 설정
MySQL 설정 파일(my.cnf 또는 my.ini)에 다음과 같은 항목을 추가하거나 수정한다.
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1| 설정 항목 | 설명 |
|---|---|
slow_query_log | 슬로우 쿼리 로그를 활성화하는 설정이다. |
slow_query_log_file | 로그 파일이 저장될 경로이다. |
long_query_time | 몇 초 이상 소요된 쿼리를 기록할지 설정하는 값이다. 일반적으로 1초 이하로 설정한다. |
log_queries_not_using_indexes | 인덱스를 사용하지 않는 쿼리도 기록하도록 설정한다. |
1.2 서버 재시작 없이 활성화 (런타임 설정)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;이 설정은 MySQL 인스턴스가 종료되면 초기화되므로, 영구 적용을 원한다면 설정 파일을 수정하는 것이 바람직하다.
2. 슬로우 쿼리 로그 예시 및 해석
# Time: 2025-07-18T14:32:01.348491Z
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 3.432 Lock_time: 0.000 Rows_sent: 1000 Rows_examined: 500000
SET timestamp=1721308321;
SELECT * FROM orders WHERE customer_name = '홍길동';| 항목 | 설명 |
|---|---|
Query_time | 쿼리 전체 실행 시간이다. |
Lock_time | 테이블 락에 소요된 시간이다. |
Rows_sent | 결과로 반환된 행 수이다. |
Rows_examined | MySQL이 읽은 행의 수이다. 이 값이 크면 인덱스 미사용 가능성이 있다. |
3. 슬로우 쿼리 로그 분석 도구
3.1 mysqldumpslow 사용
MySQL에서 기본 제공하는 도구로, 슬로우 쿼리를 정렬하고 요약해서 보여준다.
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log| 옵션 | 설명 |
|---|---|
-s | 정렬 기준을 의미한다. 예: t (시간 기준), r (행 수 기준) 등 |
-t | 상위 몇 개의 쿼리를 출력할지 지정한다. |
-g | 특정 문자열 또는 정규식에 해당하는 쿼리만 출력한다. |
3.2 pt-query-digest 사용 (권장)
Percona Toolkit에 포함된 도구로, 쿼리 패턴을 식별하고 전체 실행 통계를 제공한다.
pt-query-digest /var/log/mysql/slow.log > digest_report.txt이 도구는 실행 시간 분포, 호출 빈도, 인덱스 사용 여부 등을 매우 상세히 보여준다. 쿼리 튜닝 보고서를 작성할 때 유용하다.
4. 슬로우 쿼리 개선 전략
4.1 인덱스 최적화
-- 문제 쿼리 예시
SELECT * FROM orders WHERE customer_name = '홍길동';
-- 개선 방법
CREATE INDEX idx_customer_name ON orders(customer_name);인덱스가 없을 경우 MySQL은 전체 테이블을 스캔하기 때문에 성능이 급격히 저하된다.
4.2 SELECT * 제거
-- 비효율적인 방식
SELECT * FROM orders WHERE status = 'active';
-- 개선된 방식
SELECT id, created_at FROM orders WHERE status = 'active';필요한 컬럼만 선택하면 I/O 비용이 줄어들어 성능이 개선된다.
4.3 조건식 최적화
- 함수 사용은 지양한다. 예:
DATE(created_at) = '2025-01-01'→created_at BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 23:59:59' OR조건은 가능한 경우UNION ALL로 분리하는 것이 효율적이다.
4.4 실행 계획(EXPLAIN) 분석
EXPLAIN SELECT * FROM orders WHERE customer_name = '홍길동';type이 ALL이고 rows 수가 크다면 테이블 풀스캔이 발생하고 있음을 의미한다.
5. 개선 후 검증 방법
실행 시간 및 행 수 검증
개선된 쿼리를 다시 슬로우 로그에 기록하거나 pt-query-digest를 재실행하여 효과를 수치로 검증한다.
| 항목 | 개선 전 | 개선 후 |
|---|---|---|
| Query_time | 3.43초 | 0.05초 |
| Rows_examined | 500000 | 500 |
| 사용 인덱스 | 없음 | 있음 |
EXPLAIN으로 실행 계획의 변화도 함께 확인한다.
6. 분석 보고서 작성 예시
문제점: 고객 이름 검색 쿼리의 평균 실행 시간이 3.4초였으며, 인덱스를 사용하지 않아 풀스캔이 발생하고 있었다.
개선 조치:customer_name컬럼에 인덱스를 추가하고SELECT *대신 필요한 컬럼만 조회하도록 쿼리를 수정하였다.
성과: 실행 시간은 0.05초로 줄었고, 스캔된 row 수도 약 99% 감소하였다.
7. 실무 활용 도구 요약
| 도구 | 역할 | 비고 |
|---|---|---|
slow_query_log | 슬로우 쿼리 기록 | MySQL 서버 설정 필요 |
mysqldumpslow | 간단한 요약 분석 | MySQL 기본 제공 |
pt-query-digest | 상세 분석 및 통계 제공 | Percona Toolkit 필요 |
EXPLAIN | 실행 계획 확인 | 병목 구간 분석 |
SHOW PROFILE | 실행 단계별 시간 측정 | SET PROFILING 필요 |
8. 고급 활용 및 확장
- 슬로우 로그를 주기적으로 수집하여 시각화 도구(Grafana, Redash 등)로 모니터링한다.
pt-query-digest분석 결과를 팀 공유용 리포트로 자동 생성한다.- Slack이나 이메일로 일정 시간 이상 소요된 쿼리를 알림 받도록 시스템을 구성한다.
정리
MySQL 슬로우 쿼리 로그는 단순한 기록 파일이 아니라, 데이터베이스 성능 문제를 해결하기 위한 강력한 진단 도구이다. 정기적인 로그 점검과 분석을 통해 데이터베이스의 안정성과 응답 속도를 지속적으로 개선할 수 있다.
