- Published on
MySQL 8.0 쿼리 느림? 히스토그램·인덱스 튜닝
- Authors
- Name
- 스타차일드
- https://x.com/ETFBITX
서비스가 성장하면 어느 순간부터 EXPLAIN 상으로는 그럴듯해 보이는데도 쿼리가 갑자기 느려지는 구간이 옵니다. MySQL 8.0에서는 옵티마이저가 통계(카디널리티)를 기반으로 실행 계획을 선택하는데, 이 통계가 현실과 어긋나면 최적이라고 믿고 고른 계획이 실제로는 최악이 될 수 있습니다.
이 글은 MySQL 8.0에서 특히 자주 겪는 두 문제를 묶어서 다룹니다.
- 분포가 치우친 컬럼에서 카디널리티 추정이 틀어져 잘못된 인덱스를 타는 문제
- 인덱스는 있는데도 범위 조건, 정렬, 조인 조건이 맞지 않아 인덱스를 비효율적으로 쓰는 문제
해결 도구로는 히스토그램과 인덱스 설계/튜닝을 중심으로, 실제 운영에서 적용 가능한 점검 순서와 쿼리 예제를 함께 정리합니다.
또한 DB 튜닝은 애플리케이션 자원(커넥션 풀)과도 맞물리는 일이 많습니다. 커넥션 고갈이나 대기열 증가가 함께 보인다면 Spring Boot DB 커넥션 고갈 - HikariCP 튜닝 가이드도 같이 점검하면 원인 분리가 빨라집니다.
1) 느린 쿼리의 전형적인 징후: “인덱스는 타는데 느리다”
다음과 같은 증상이면 히스토그램과 인덱스 튜닝을 의심할 가치가 큽니다.
EXPLAIN에서key가 잡히는데도rows가 지나치게 크거나 실제와 다름- 특정 값(예:
status='PENDING')에서만 유난히 느림 - 데이터가 늘수록 갑자기 실행 계획이 바뀜(어제는 빠르고 오늘은 느림)
Using filesort,Using temporary가 자주 보임
MySQL 옵티마이저는 “대략 몇 건이 나올지”를 추정해 조인 순서, 인덱스 선택, 임시 테이블/정렬 여부를 결정합니다. 문제는 분포가 치우친 컬럼에서 기본 통계만으로는 이 추정이 자주 빗나간다는 점입니다.
2) 히스토그램이 필요한 상황: 분포가 치우친 컬럼
예를 들어 주문 테이블에 status가 있고, 대부분이 COMPLETED이며 PENDING은 극히 일부라고 가정해봅시다.
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL,
created_at DATETIME NOT NULL,
KEY idx_status_created (status, created_at),
KEY idx_user_created (user_id, created_at)
);
아래 쿼리는 얼핏 idx_status_created를 타면 좋아 보입니다.
SELECT id, user_id, created_at
FROM orders
WHERE status = 'PENDING'
AND created_at >= NOW() - INTERVAL 7 DAY
ORDER BY created_at DESC
LIMIT 50;
그런데 옵티마이저가 status의 분포를 제대로 모르면, status='PENDING'이 희소하다는 사실을 과소평가하거나 과대평가해 엉뚱한 인덱스를 고르거나 조인 순서를 망칠 수 있습니다.
2-1) MySQL 8.0 히스토그램이 하는 일
히스토그램은 “인덱스가 없는 컬럼”만을 위한 기능으로 오해되곤 하지만, 핵심은 값의 분포를 더 정확히 알려주는 통계라는 점입니다. 특히 다음에 효과가 큽니다.
- 값이 몇 개 안 되는 저카디널리티 컬럼(
status,type,country등) - 특정 값으로 쿼리할 때만 느린 케이스
- 조인/필터 순서가 자주 바뀌는 케이스
2-2) 히스토그램 생성과 확인
MySQL 8.0에서는 ANALYZE TABLE로 히스토그램을 만들 수 있습니다.
ANALYZE TABLE orders
UPDATE HISTOGRAM ON status WITH 100 BUCKETS;
히스토그램은 데이터 분포에 따라 버킷 수를 적절히 잡아야 합니다. 너무 작으면 부정확하고, 너무 크면 통계 갱신 비용이 늘 수 있습니다.
생성된 히스토그램은 information_schema에서 확인 가능합니다.
SELECT
schema_name,
table_name,
column_name,
histogram
FROM information_schema.column_statistics
WHERE schema_name = DATABASE()
AND table_name = 'orders'
AND column_name = 'status';
운영에서 중요한 포인트는 “한 번 만들고 끝”이 아니라, 데이터 분포가 변하면 다시 갱신해야 한다는 것입니다. 예를 들어 이벤트성 트래픽으로 PENDING이 급증하는 기간이 있으면, 기존 히스토그램이 오히려 독이 될 수도 있습니다.
히스토그램 삭제는 아래처럼 할 수 있습니다.
ANALYZE TABLE orders
DROP HISTOGRAM ON status;
3) 인덱스 튜닝의 기본: 조건, 정렬, LIMIT의 조합을 맞춰라
히스토그램이 “옵티마이저의 눈”을 교정해 준다면, 인덱스 튜닝은 “갈 길”을 닦는 작업입니다. MySQL에서 성능이 갈리는 전형적인 패턴은 아래 조합입니다.
WHERE로 범위를 줄이고ORDER BY를 인덱스 순서로 처리하며LIMIT으로 빠르게 상위 N건만 읽기
3-1) 복합 인덱스 컬럼 순서: 동등 조건 먼저, 범위 조건 나중
대부분의 경우 다음 규칙이 실전에서 잘 맞습니다.
=(동등) 조건 컬럼을 앞에BETWEEN,>=,LIKE 'prefix%'같은 범위 조건 컬럼을 뒤에ORDER BY컬럼도 가능하면 인덱스에 포함
예를 들어 위 쿼리에서 핵심은 status = ...(동등) + created_at >= ...(범위) + ORDER BY created_at DESC 입니다.
이미 KEY idx_status_created (status, created_at)가 있으니 구조는 맞습니다. 그런데도 느리다면 보통은 다음 중 하나입니다.
- 실제로는
status선택도가 낮아서(대부분 같은 값) 인덱스를 타도 읽는 양이 큼 created_at조건이 너무 넓어 결국 대량 스캔- 커버링이 안 되어 테이블로 되돌아가는 랜덤 I/O가 큼
3-2) 커버링 인덱스로 “되돌아가기” 줄이기
쿼리가 인덱스만 읽고 끝나면(커버링) 성능이 크게 좋아질 수 있습니다.
예:
SELECT id, user_id, created_at
FROM orders
WHERE status = 'PENDING'
AND created_at >= NOW() - INTERVAL 7 DAY
ORDER BY created_at DESC
LIMIT 50;
이때 idx_status_created는 (status, created_at)까지만 포함합니다. 선택 컬럼에 user_id가 있어 테이블로 다시 읽을 수 있습니다. 커버링을 노린다면 아래처럼 확장할 수 있습니다.
ALTER TABLE orders
DROP INDEX idx_status_created,
ADD INDEX idx_status_created_cover (status, created_at, user_id, id);
주의할 점:
- 인덱스가 커질수록 쓰기 비용과 버퍼 풀 압박이 증가
- 모든 쿼리를 커버링으로 만들려다 인덱스 폭발이 나기 쉬움
따라서 “가장 핫한 쿼리 1~2개”에만 제한적으로 적용하는 것이 안전합니다.
4) 실행 계획 검증: EXPLAIN ANALYZE로 추정 vs 실제 비교
MySQL 8.0의 강력한 도구는 EXPLAIN ANALYZE입니다. 단순 EXPLAIN은 추정치 중심이고, EXPLAIN ANALYZE는 실제 수행 시간을 포함합니다.
EXPLAIN ANALYZE
SELECT id, user_id, created_at
FROM orders
WHERE status = 'PENDING'
AND created_at >= NOW() - INTERVAL 7 DAY
ORDER BY created_at DESC
LIMIT 50;
여기서 확인할 핵심은 다음입니다.
- 추정
rows와 실제rows가 얼마나 차이나는지 - 어떤 단계에서 시간이 많이 쓰였는지(필터, 정렬, 테이블 접근)
- 인덱스를 탔는데도 “실제 읽은 행”이 큰지
만약 히스토그램 적용 전후로 추정치가 크게 개선되고, 그에 따라 실행 계획이 바뀌면서 실제 시간이 줄었다면 “통계 문제”였을 가능성이 큽니다.
5) 자주 터지는 케이스별 처방
5-1) IN (...) 값이 많고 분포가 불균형한 경우
IN 리스트가 커지면 옵티마이저가 선택도를 잘못 잡기 쉽습니다. 이때는 히스토그램이 도움이 되기도 하지만, 쿼리 구조를 바꾸는 편이 더 안정적일 때가 많습니다.
예를 들어 애플리케이션에서 상태 목록을 만들어 넣는 대신, 작은 매핑 테이블을 조인하거나(또는 임시 테이블/CTE) 계획을 안정화합니다.
WITH desired_status AS (
SELECT 'PENDING' AS status
UNION ALL SELECT 'FAILED'
)
SELECT o.id, o.user_id, o.created_at
FROM orders o
JOIN desired_status s ON s.status = o.status
WHERE o.created_at >= NOW() - INTERVAL 7 DAY
ORDER BY o.created_at DESC
LIMIT 50;
5-2) ORDER BY 때문에 Using filesort가 나는 경우
정렬이 느린 이유는 보통 “필터로 충분히 줄이기 전에 정렬을 시작”하기 때문입니다.
WHERE로 줄일 수 있는 조건을 인덱스 선두에 배치ORDER BY컬럼을 인덱스에 포함- 가능하면
LIMIT와 함께 “인덱스 순서로 읽기”를 유도
예를 들어 사용자별 최신 주문을 자주 본다면 아래 인덱스가 효과적입니다.
ALTER TABLE orders
ADD INDEX idx_user_created_desc (user_id, created_at);
그리고 쿼리는 다음처럼 맞춥니다.
SELECT id, status, created_at
FROM orders
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 20;
MySQL은 인덱스를 역순으로도 읽을 수 있어(DESC 인덱스가 없어도) 이 패턴이 잘 먹힙니다. 다만 복합 정렬(여러 컬럼)이나 혼합 정렬 방향이 들어가면 인덱스 설계가 더 까다로워집니다.
5-3) 조인에서 한쪽 테이블만 커졌는데 갑자기 느려진 경우
조인은 “어떤 테이블을 먼저 읽는가”가 성능을 좌우합니다. 통계가 틀어지면 조인 순서가 뒤집혀 폭발합니다.
- 조인 키에 적절한 인덱스가 있는지
- 필터 조건이 있는 테이블을 먼저 줄일 수 있는지
- 조인 컬럼의 분포가 치우쳤는지(히스토그램 후보)
예:
SELECT o.id, u.id
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'KR'
AND o.created_at >= NOW() - INTERVAL 1 DAY;
users.country가 치우친 값이라면 country에 히스토그램을 고려할 수 있습니다.
ANALYZE TABLE users
UPDATE HISTOGRAM ON country WITH 100 BUCKETS;
6) 운영 적용 체크리스트: 안전하게 바꾸는 순서
히스토그램과 인덱스는 둘 다 “성능을 개선할 수도, 망칠 수도” 있습니다. 운영에서의 권장 순서는 다음입니다.
- 슬로우 로그 및 APM으로 대상 쿼리 1~3개를 고른다
EXPLAIN과EXPLAIN ANALYZE로 추정/실측 차이를 확인한다- 통계 문제로 보이면 히스토그램을 먼저 적용해 계획 변화를 관찰한다
- 그래도 느리면 인덱스를 쿼리 패턴에 맞게 재설계한다(커버링은 제한적으로)
- 배포 전후로
rows/지연시간/CPU/버퍼 풀 히트율을 함께 비교한다
특히 인덱스 추가는 쓰기 워크로드가 큰 서비스에서 잠재 비용이 큽니다. DB가 빨라졌는데 애플리케이션이 여전히 느리다면, 커넥션 풀 대기나 타임아웃이 병목일 수도 있으니 Spring Boot DB 커넥션 고갈 - HikariCP 튜닝 가이드처럼 애플리케이션 계층도 같이 봐야 합니다.
또한 트랜잭션 이벤트 기반 아키텍처에서 DB 부하가 급증하는 경우(예: 이벤트 재처리, 중복 발행) 쿼리 튜닝만으로는 한계가 있습니다. 이벤트 중복/순서 문제가 의심되면 DDD 이벤트 중복·순서꼬임? Outbox+Debezium 해법도 함께 참고하면 전체 병목을 줄이는 데 도움이 됩니다.
7) 결론: 히스토그램은 “정확한 추정”, 인덱스는 “짧은 경로”
MySQL 8.0에서 쿼리가 느려질 때, 단순히 인덱스를 추가하는 접근은 종종 실패합니다. 이유는 옵티마이저가 잘못된 추정으로 계획을 고르면, 좋은 인덱스가 있어도 제대로 활용하지 못하기 때문입니다.
- 특정 값에서만 느리거나, 실행 계획이 자주 바뀐다면 히스토그램으로 통계를 보강
WHERE+ORDER BY+LIMIT패턴을 인덱스 컬럼 순서로 정렬- 커버링 인덱스는 “가장 비싼 쿼리”에만 제한적으로 적용
EXPLAIN ANALYZE로 추정과 실제를 비교해 근거 기반으로 튜닝
이 순서대로 접근하면 “왜 느린지”를 설명 가능한 형태로 만들 수 있고, 재현 가능한 개선을 쌓을 수 있습니다.