beautifulsoups.dev
Published on

MySQL 히스토그램 통계로 옵티마이저 오판 잡기

Authors

서버나 애플리케이션 코드는 그대로인데, 특정 쿼만 갑자기 느려지거나 실행 계획이 들쭉날쭉 바뀌는 경우가 있습니다. 많은 경우 원인은 옵티마이저의 카디널리티 추정 오차입니다. MySQL은 기본적으로 테이블/인덱스 통계로 행 수를 추정하지만, 값 분포가 치우친 컬럼(스큐, skew)에서는 통계만으로는 분포를 제대로 반영하지 못해 오판이 발생합니다.

이 글에서는 MySQL 8.0의 히스토그램 통계(histogram statistics) 를 사용해 옵티마이저 오판을 줄이고, 실행 계획을 안정화하는 실전 흐름을 정리합니다.

관련해서 DB 튜닝이 전반적으로 필요하다면 버퍼 풀 관점도 함께 보시면 좋습니다. MySQL InnoDB Buffer Pool 부족? 히트율로 튜닝

1) 옵티마이저 오판이 성능을 망치는 전형적인 패턴

옵티마이저는 WHERE 조건이 얼마나 많은 행을 걸러낼지(선택도, selectivity)를 추정해 조인 순서, 인덱스 사용 여부, 조인 알고리즘 등을 결정합니다. 그런데 다음 상황에서 추정이 크게 틀어지기 쉽습니다.

  • 특정 값에 데이터가 몰린 컬럼(예: status, country, is_deleted)
  • 범위 조건이지만 실제 분포가 균일하지 않은 컬럼(예: 특정 날짜 구간에 트래픽 폭증)
  • 복합 조건에서 컬럼 간 상관관계가 큰 경우(예: country와 city가 강하게 묶임)
  • ANALYZE TABLE은 했는데도 여전히 분포를 못 잡는 경우

오판의 결과는 보통 다음 중 하나로 나타납니다.

  • 인덱스를 타야 하는데 풀스캔으로 감
  • 풀스캔이 더 싼데 비효율적인 인덱스를 탐
  • 조인 순서가 뒤집혀서 작은 테이블을 먼저 필터링하지 못함
  • LIMIT이 있는데도 엉뚱한 인덱스를 타서 정렬/임시테이블 비용이 커짐

히스토그램은 이런 상황에서 컬럼 값의 분포를 더 자세히 알려주는 통계로, 옵티마이저의 선택도 추정을 보정하는 역할을 합니다.

2) 히스토그램이 해결하는 것과 해결하지 못하는 것

히스토그램이 잘하는 것

  • 단일 컬럼 조건의 분포를 반영한 선택도 추정 개선
  • 값이 특정 구간/값에 몰린 경우(스큐)에서 실행 계획 안정화
  • 인덱스가 있어도 선택도 오판으로 인덱스를 안 타는 문제 완화

히스토그램이 만능은 아닙니다

  • 히스토그램은 인덱스를 만들어주지 않습니다. 인덱스 설계가 근본적으로 잘못되면 히스토그램으로도 한계가 있습니다.
  • 컬럼 간 상관관계(다중 컬럼 상관)를 완벽히 모델링하진 못합니다.
  • 데이터가 자주 바뀌면 히스토그램이 금방 낡아 추정이 다시 틀어질 수 있습니다.

따라서 히스토그램은 “인덱스와 통계가 있는데도 옵티마이저가 계속 틀리는 케이스”에서 특히 효과적입니다.

3) 준비: 문제 쿼리와 실행 계획을 먼저 고정적으로 관찰하기

먼저 재현 가능한 형태로 쿼리를 잡고, 실행 계획과 추정치가 어떻게 틀리는지 확인합니다.

실행 계획 확인

아래처럼 EXPLAIN ANALYZE를 사용하면 추정 행 수와 실제 행 수를 함께 볼 수 있어 원인 파악이 빠릅니다.

EXPLAIN ANALYZE
SELECT o.id, o.user_id, o.status, o.created_at
FROM orders o
WHERE o.status = 'CANCELLED'
  AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;

여기서 핵심은 “추정 rows”와 “actual rows”의 괴리입니다. 예를 들어 status='CANCELLED'가 전체의 1%라고 추정했지만 실제로는 40%라면, 옵티마이저는 인덱스 선택과 조인 순서에서 엉뚱한 결정을 내릴 가능성이 큽니다.

4) 히스토그램 생성: ANALYZE TABLE ... UPDATE HISTOGRAM

MySQL 8.0에서는 특정 컬럼에 히스토그램을 만들 수 있습니다.

ANALYZE TABLE orders
  UPDATE HISTOGRAM ON status, created_at
  WITH 256 BUCKETS;
  • BUCKETS는 분포를 나누는 구간 수입니다. 너무 작으면 분포를 못 잡고, 너무 크면 통계 생성 비용과 메타데이터 크기가 늘 수 있습니다.
  • 보통 64~256 정도에서 시작해 효과를 보고 조정하는 편이 안전합니다.

어떤 컬럼에 히스토그램을 만들까

우선순위는 다음이 실전에서 효율적입니다.

  1. WHERE에 자주 등장하는데 값 분포가 치우친 컬럼
  2. 인덱스 선두 컬럼이 아니어서 인덱스 통계만으로 선택도 추정이 약한 컬럼
  3. 조인 키는 아닌데 필터링에 강하게 쓰이는 컬럼(예: status, type, category)

반대로, 고유값이 매우 많고(거의 유니크) 이미 인덱스로 선택도가 잘 추정되는 컬럼은 히스토그램 체감이 적을 수 있습니다.

5) 히스토그램이 실제로 만들어졌는지 확인하기

히스토그램은 데이터 딕셔너리에 저장됩니다. 다음 쿼리로 확인할 수 있습니다.

SELECT
  schema_name,
  table_name,
  column_name,
  histogram
FROM information_schema.column_statistics
WHERE schema_name = DATABASE()
  AND table_name = 'orders';

histogram 컬럼은 JSON 형태로 저장되며, 버킷 경계값과 누적 분포 등이 들어있습니다. 운영 환경에서는 JSON을 매번 읽기보다는 “존재 여부/갱신 시점”을 점검하는 용도로 주로 씁니다.

6) 적용 효과 검증: 실행 계획이 어떻게 바뀌었는지 비교

히스토그램 생성 전후로 동일 쿼리에 대해 EXPLAIN ANALYZE를 다시 비교합니다.

EXPLAIN ANALYZE
SELECT o.id, o.user_id, o.status, o.created_at
FROM orders o
WHERE o.status = 'CANCELLED'
  AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;

여기서 확인할 포인트는 다음입니다.

  • 추정 rows가 실제 rows에 더 가까워졌는가
  • 인덱스 선택이 합리적으로 바뀌었는가
  • 정렬/임시테이블 비용이 줄었는가
  • 조인 순서가 개선되었는가(조인이 있는 쿼리라면)

히스토그램은 “플랜을 강제로 고정”하는 기능이 아니라 “옵티마이저가 더 똑똑하게 추정하도록 돕는 기능”이라서, 결과적으로 플랜이 바뀔 수도 있고 동일할 수도 있습니다. 중요한 것은 추정 정확도가 개선되었는지입니다.

7) 자주 겪는 함정: 히스토그램이 있어도 느린 경우

7-1) 인덱스 자체가 부족한 경우

예를 들어 WHERE status = ... AND created_at >= ... ORDER BY created_at DESC LIMIT 50 패턴이 많다면, 상황에 따라서는 복합 인덱스가 더 직접적인 해결책일 수 있습니다.

CREATE INDEX idx_orders_status_created_at
ON orders(status, created_at);

히스토그램은 선택도 추정을 개선하지만, 정렬과 범위 스캔을 효율화하는 건 결국 인덱스 설계가 좌우합니다.

7-2) 데이터가 빠르게 변해서 히스토그램이 낡는 경우

히스토그램은 생성 시점의 분포를 저장합니다. 이벤트성 트래픽, 시즌성 데이터, 배치 적재 등으로 분포가 급변하면 히스토그램이 곧바로 “과거의 진실”이 됩니다.

이 경우에는 다음이 필요합니다.

  • 주기적 재생성(배치)
  • 분포가 크게 바뀌는 테이블만 선별해서 갱신

8) 운영 팁: 히스토그램 갱신을 자동화하는 방법

운영에서는 “언제 히스토그램을 다시 만들 것인가”가 핵심입니다. 가장 단순한 방법은 야간 배치로 중요한 테이블만 갱신하는 것입니다.

이벤트 스케줄러로 야간 갱신 예시

CREATE EVENT ev_refresh_hist_orders
ON SCHEDULE EVERY 1 DAY
STARTS '2026-02-27 03:00:00'
DO
  ANALYZE TABLE orders
    UPDATE HISTOGRAM ON status, created_at
    WITH 128 BUCKETS;
  • 이벤트 스케줄러 사용이 어려운 환경이라면, 애플리케이션 배치나 운영 잡에서 동일 SQL을 호출해도 됩니다.
  • 갱신 주기는 “분포가 바뀌는 주기”에 맞추는 게 정답입니다. 무조건 매일은 과할 수 있습니다.

변경량 기반으로 갱신하기(실무적 접근)

정교하게 하려면 테이블 변경량을 기준으로 갱신 여부를 결정합니다. 예를 들어 하루 동안 전체 행의 10% 이상이 바뀌었을 때만 갱신하도록 운영 룰을 둘 수 있습니다. 변경량 측정은 스키마/파이프라인에 따라 다르지만, 다음처럼 updated_at 기반으로 근사치를 잡는 방식도 많이 씁니다.

SELECT COUNT(*) AS changed_rows
FROM orders
WHERE updated_at >= NOW() - INTERVAL 1 DAY;

9) 히스토그램 삭제와 롤백 전략

히스토그램을 적용했는데 특정 쿼리에서 오히려 플랜이 나빠질 수도 있습니다(드물지만 가능합니다). 이때는 히스토그램을 제거해 빠르게 롤백할 수 있어야 합니다.

ANALYZE TABLE orders
  DROP HISTOGRAM ON status, created_at;

롤백이 쉽다는 점이 히스토그램의 큰 장점입니다. 인덱스 추가처럼 대규모 DDL 비용이 들지 않고, 통계만 제거하면 됩니다.

10) 실전 체크리스트: “옵티마이저 오판”을 히스토그램으로 잡는 순서

  1. 느린 쿼리를 선정하고 EXPLAIN ANALYZE로 추정 rows와 실제 rows 괴리를 확인
  2. 분포가 치우친 컬럼을 찾고, 해당 컬럼에 히스토그램 생성
  3. 동일 쿼리로 실행 계획과 추정치 개선 여부 재검증
  4. 효과가 있으면 갱신 주기와 대상 테이블을 운영 정책으로 고정
  5. 부작용이 있으면 DROP HISTOGRAM으로 즉시 롤백

11) 마무리: 히스토그램은 “플랜 고정”이 아니라 “추정 교정”이다

MySQL 성능 문제는 흔히 인덱스 추가로만 접근하지만, 인덱스가 있는데도 플랜이 흔들리거나, 특정 값에서만 유독 느려지는 문제는 통계 품질이 원인인 경우가 많습니다. 히스토그램은 이때 비용 대비 효과가 좋은 카드입니다.

다만 히스토그램은 어디까지나 옵티마이저의 눈을 교정하는 도구입니다. 인덱스 설계, 쿼리 형태, 버퍼 풀과 I/O 병목 같은 기본 요소와 함께 봐야 재발을 줄일 수 있습니다. 특히 캐시/메모리 병목이 의심되면 MySQL InnoDB Buffer Pool 부족? 히트율로 튜닝도 같이 점검해 보세요.

Discuss on Twitter • View on GitHub