[MySQL] 슬로우 쿼리 실전 튜닝 사례로 배우는 병목 원인 분석

슬로우 쿼리 로그를 통해 느린 쿼리를 찾아냈다면 이제부터는 병목 원인을 분석하고 튜닝하는 단계
단순히 인덱스를 추가하는 걸 넘어서 실행계획을 해석하고 쿼리 자체의 구조를 최적화해야 한다

📌 사례 1: 인덱스 미사용으로 인한 풀 테이블 스캔

SELECT * FROM users WHERE email LIKE '%@gmail.com%';

이 쿼리는 앞쪽 와일드카드 사용으로 인해 인덱스를 사용할 수 없다
슬로우 쿼리 로그에는 다음과 같이 기록된다

Query_time: 2.582  Rows_examined: 78000

병목 원인

• email 컬럼에 인덱스가 있어도 %로 시작하는 패턴 검색은 인덱스를 타지 않음
• 전체 데이터를 스캔하며 WHERE 조건에 맞는 row를 필터링

해결 방법

• 가능하다면 이메일 도메인 분리하여 완전한 매칭으로 조건 변경
• 또는 FULLTEXT INDEX 도입 고려

ALTER TABLE users ADD FULLTEXT(email);
SELECT * FROM users WHERE MATCH(email) AGAINST('@gmail.com');

반응형

📌 사례 2: 다중 조인 + 조건 누락으로 인한 비효율

SELECT o.id, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'active';

슬로우 쿼리 로그 예시

Query_time: 5.304  Rows_examined: 150000

병목 원인

• orders.user_id 또는 users.id 중 하나에 인덱스가 없거나, status 조건이 결합 인덱스에 포함되지 않음
• JOIN 수행 시 각 row마다 users 테이블을 full scan 하게 됨

해결 방법

• 필요한 컬럼을 조합한 복합 인덱스를 생성

ALTER TABLE users ADD INDEX idx_status_id (status, id);

또는 users 테이블의 크기가 작을 경우 STRAIGHT_JOIN 힌트로 조인 순서를 강제하는 것도 가능

SELECT STRAIGHT_JOIN o.id, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'active';

📌 사례 3: ORDER BY + LIMIT 병목

SELECT * FROM logs ORDER BY created_at DESC LIMIT 50;

슬로우 쿼리 로그 예시

Query_time: 4.987  Rows_examined: 200000

병목 원인

• ORDER BY 대상인 created_at에 인덱스가 없으면 정렬을 위해 전체 테이블 스캔 발생
• LIMIT이 있더라도 정렬 후 전체에서 50건을 잘라야 함

해결 방법

• 정렬 기준 컬럼에 인덱스 추가

ALTER TABLE logs ADD INDEX idx_created_at (created_at DESC);

• 또는 페이징 방식 개선 (예: OFFSET 대신 기준 ID 기반으로 슬라이싱)

SELECT * FROM logs WHERE created_at < '2024-01-01' ORDER BY created_at DESC LIMIT 50;

반응형

📌 사례 4: GROUP BY + COUNT 병목

SELECT user_id, COUNT(*) FROM actions GROUP BY user_id;

슬로우 쿼리 로그 예시

Query_time: 6.712  Rows_examined: 500000

병목 원인

• user_id에 인덱스가 없으면 GROUP BY는 모든 데이터를 정렬 또는 임시 테이블로 처리
• COUNT 집계도 인덱스 없이 전수 검사 필요

해결 방법

• 그룹핑 대상에 인덱스 추가

ALTER TABLE actions ADD INDEX idx_user_id (user_id);

• 데이터가 커질 경우 서브 쿼리로 분할, 통계 테이블을 미리 구성하는 것도 고려

🧠 슬로우 쿼리 분석 시 체크리스트

• 해당 쿼리에 실행 계획(EXPLAIN)을 붙여보았는가
• WHERE 절의 컬럼이 인덱스를 타고 있는가
• JOIN 조건이 적절히 설정되어 있는가
• 정렬, 그룹핑 대상 컬럼이 인덱스로 보완되어 있는가
• rows_examined가 과도하게 크진 않은가
• 쿼리 호출 빈도와 캐시 가능성은 어떤가

✅ 정리

• 슬로우 쿼리 로그는 병목 쿼리를 찾아내는 도구일 뿐, 실제 개선은 튜닝 전략과 실행계획 분석에서 시작
• 인덱스 추가는 기본, 쿼리 구조 자체를 재작성하는 전략도 중요
• 실행 시간 외에도 rows_examined, Lock_time 등을 종합적으로 고려해야 진짜 성능을 개선할 수 있음

🔗 공식 문서 참고
MySQL 8.0 Reference Manual - Query Optimization