beautifulsoups.dev
Published on

MySQL 8 인덱스가 있어도 풀스캔? 히스토그램·카디널리티 튜닝

Authors

서버를 운영하다 보면 가장 답답한 순간이 있습니다. 분명 WHERE 조건 컬럼에 인덱스를 만들어뒀는데, EXPLAIN을 찍어보면 type: ALL(풀스캔)로 나오고 쿼리는 느립니다. 인덱스가 “없는” 게 아니라, 옵티마이저가 “안 쓰는” 상황입니다.

MySQL 8에서는 옵티마이저가 선택도를 추정하는 방식이 더 다양해졌고(특히 히스토그램), 반대로 말하면 통계가 부정확하면 잘못된 실행계획을 고집하기도 합니다. 이 글에서는 “인덱스가 있는데도 풀스캔”이 발생하는 전형적인 패턴을 재현하고, 히스토그램(histogram) 과 카디널리티(cardinality) 통계를 중심으로 원인 진단과 튜닝 절차를 정리합니다.

> 운영 환경에서 DB 튜닝은 결국 “관측 → 가설 → 검증”의 반복입니다. 애플리케이션/인프라 레벨에서 장애를 추적하는 방식이 궁금하다면 Linux OOM Killer 로그 추적과 메모리 누수 진단도 같은 문제해결 흐름을 참고할 만합니다.

1. 인덱스가 있어도 풀스캔이 선택되는 이유

MySQL 옵티마이저는 대략 다음 질문에 답하면서 실행계획을 고릅니다.

  1. 이 조건이 전체 데이터 중 얼마나 걸러질까? (선택도, selectivity)
  2. 인덱스를 타면 몇 건을 읽고(랜덤 I/O), 테이블을 몇 번 다시 읽을까?
  3. 그냥 테이블을 쭉 읽는 게 더 싸지 않을까? (순차 I/O)

즉, 인덱스가 있어도 선택도가 낮거나(너무 많은 행이 매칭), 혹은 선택도를 잘못 추정하면 풀스캔이 더 싸다고 판단할 수 있습니다.

대표 원인:

  • 컬럼 분포가 치우쳐 있는데 균등 분포로 가정하는 경우(특정 값이 대부분)
  • 카디널리티 통계가 오래되었거나 부정확한 경우
  • LIKE '%keyword', FUNCTION(col) = ... 등으로 인덱스 사용이 구조적으로 불가능한 경우
  • 조인 순서/조인 방식 추정 실패(한 테이블은 인덱스를 타지만 전체 플랜은 비효율)

이 글은 그중에서도 “인덱스를 탈 수 있는 형태인데도” 풀스캔이 나오는 케이스를 다룹니다. 즉 통계/분포 추정 문제를 해결하는 접근입니다.

2. 재현: 인덱스가 있는데도 ALL이 뜨는 전형적인 케이스

예시로 주문 테이블을 만들고, status에 인덱스를 걸어보겠습니다. 문제는 status 값이 심하게 치우쳐 있다고 가정합니다(예: 99%가 PAID).

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL,
  amount INT NOT NULL,
  KEY idx_status (status),
  KEY idx_created_at (created_at)
) ENGINE=InnoDB;

이제 다음 쿼리를 자주 날린다고 합시다.

EXPLAIN FORMAT=tree
SELECT COUNT(*)
FROM orders
WHERE status = 'PAID'
  AND created_at >= '2026-01-01';

현상:

  • status='PAID'는 너무 흔해서 인덱스로 읽어도 대부분의 레코드를 방문합니다.
  • created_at 조건과 결합되면 사실상 꽤 줄어들 수 있는데, 옵티마이저가 그 결합 선택도를 정확히 못 맞추면(특히 컬럼 간 상관관계) 잘못된 결론을 냅니다.

여기서 중요한 건 “인덱스가 존재”가 아니라, 옵티마이저가 ‘얼마나 줄어드는지’를 정확히 알아야 인덱스를 선택한다는 점입니다.

3. 진단 1단계: EXPLAIN에서 통계 추정을 읽는 법

3.1 EXPLAIN에서 봐야 할 필드

EXPLAIN
SELECT *
FROM orders
WHERE status='PAID' AND created_at >= '2026-01-01';

체크 포인트:

  • type: ALL이면 풀스캔, range/ref면 인덱스 기반 접근
  • possible_keys: 후보 인덱스 목록(여기에 있는데도 사용 안 하면 “비용상 불리” 판단)
  • key: 실제 선택된 인덱스
  • rows: 옵티마이저가 예상한 읽기 행 수(추정치)
  • filtered: 조건으로 걸러질 비율(추정치)

rows와 filtered가 현실과 크게 다르면 통계/분포 추정 문제를 의심해야 합니다.

3.2 JSON/Tree 포맷으로 비용 추정을 더 보기

EXPLAIN FORMAT=JSON
SELECT *
FROM orders
WHERE status='PAID' AND created_at >= '2026-01-01';

JSON에는 cost_info, rows_examined_per_scan 등 힌트를 더 제공합니다. “왜 풀스캔이 더 싸다고 봤는지”를 비용 관점에서 확인할 수 있습니다.

4. 카디널리티(cardinality)란 무엇이고 왜 틀어질까?

카디널리티는 “인덱스가 얼마나 다양한 값을 가지는지(대략 distinct 수)”에 대한 통계입니다. 옵티마이저는 이를 기반으로 선택도를 계산합니다.

4.1 카디널리티가 부정확해지는 대표 상황

  • 대량 배치/이관 후 ANALYZE TABLE을 안 함
  • 값 분포가 시간에 따라 변했는데 통계 갱신이 늦음
  • InnoDB 통계가 샘플링 기반이라 표본이 운 나쁘게 치우침

4.2 인덱스 카디널리티 확인

SHOW INDEX FROM orders;

여기서 Cardinality가 지나치게 작거나(예: 실제 distinct가 수천인데 10으로 표시), 테이블 규모 대비 말이 안 되면 통계 갱신이 필요합니다.

5. 해결 1: ANALYZE TABLE로 통계 갱신(가장 먼저 해볼 것)

가장 안전하고 기본적인 처방은 통계 갱신입니다.

ANALYZE TABLE orders;

이후 다시 EXPLAIN을 비교합니다.

  • 플랜이 인덱스로 바뀌면: 원인은 “오래된/부정확한 통계”였을 가능성이 큼
  • 그대로면: 분포 추정의 한계(특히 skew, 상관관계)로 넘어갑니다

5.1 InnoDB 통계 샘플링 정확도 조정

샘플링 정확도를 올리면 통계 품질이 좋아질 수 있습니다(대신 ANALYZE 비용 증가).

-- 전역/세션/테이블 단위로 조절 가능(환경에 맞게)
SET GLOBAL innodb_stats_persistent = ON;
SET GLOBAL innodb_stats_persistent_sample_pages = 64;
  • innodb_stats_persistent=ON: 재시작 후에도 통계 유지
  • innodb_stats_persistent_sample_pages: 샘플 페이지 수(기본보다 늘리면 정확도↑)

운영에서는 무턱대고 크게 올리기보다, 문제가 된 테이블에만 영향이 가도록 테이블 옵션을 쓰거나 점진적으로 조정하는 편이 안전합니다.

6. 해결 2: MySQL 8 히스토그램으로 “치우친 분포”를 알려주기

카디널리티는 “distinct 수” 중심이라, 값 분포가 심하게 치우친(skewed) 컬럼에서는 선택도 추정이 틀어지기 쉽습니다. 예를 들어 status가 5종류뿐이라 카디널리티는 낮지만, 그중 하나가 99%를 차지하면 status='CANCELLED'는 매우 선택적이고 status='PAID'는 거의 필터링이 안 됩니다.

이때 히스토그램이 도움이 됩니다.

6.1 히스토그램 생성

ANALYZE TABLE orders
  UPDATE HISTOGRAM ON status
  WITH 64 BUCKETS;
  • BUCKETS는 분포를 나누는 구간 수입니다. 너무 작으면 거칠고, 너무 크면 생성/유지 비용이 늘 수 있습니다.

6.2 히스토그램 확인

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

여기에 JSON 형태로 분포 정보가 저장됩니다.

6.3 히스토그램이 특히 효과적인 쿼리

  • col = 'rare_value' 처럼 희소 값 조회
  • IN (...) 목록 조회
  • 범위 조건(BETWEEN, >, <)은 컬럼 타입/히스토그램 타입에 따라 도움

반대로, 여러 컬럼의 결합 분포(상관관계) 까지 완벽히 해결하진 못합니다. MySQL 히스토그램은 기본적으로 컬럼 단위 통계이기 때문입니다.

7. “히스토그램 만들었는데도” 풀스캔이면: 상관관계와 복합 인덱스

실무에서 더 흔한 문제는 다음입니다.

  • status='PAID'는 흔함(선택도 낮음)
  • 그런데 created_at >= ...를 붙이면 사실상 최근 데이터만 보게 되어 줄어듦
  • 옵티마이저가 두 조건의 결합 선택도를 잘 추정 못함

이때는 통계만으로 버티기보다 복합 인덱스 설계가 정답인 경우가 많습니다.

7.1 복합 인덱스 후보

쿼리가 아래처럼 고정이라면:

SELECT *
FROM orders
WHERE status='PAID'
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 50;

다음 인덱스가 유효할 수 있습니다.

ALTER TABLE orders
  ADD KEY idx_status_created_at (status, created_at);
  • status로 먼저 좁히고, 그 안에서 created_at 범위를 효율적으로 스캔
  • ORDER BY created_at에도 유리(정렬/파일소트 감소 가능)

반대로 created_at 범위가 더 선택적이고 status가 덜 중요하면 순서를 바꿔야 합니다.

ALTER TABLE orders
  ADD KEY idx_created_at_status (created_at, status);

인덱스 컬럼 순서는 “자주 쓰는 조건”이 아니라 실제 선택도와 정렬/그룹핑 패턴을 기준으로 결정해야 합니다.

8. 강제로 인덱스를 쓰게 하면 해결될까? (대부분은 임시처방)

다음처럼 힌트를 줄 수는 있습니다.

SELECT /*+ SET_VAR(optimizer_switch='prefer_ordering_index=on') */ *
FROM orders FORCE INDEX (idx_status)
WHERE status='PAID' AND created_at >= '2026-01-01';

하지만 권장 흐름은:

  1. 통계 갱신(ANALYZE)
  2. 히스토그램으로 분포 반영
  3. 그래도 안 되면 인덱스 설계(복합 인덱스/커버링)
  4. 마지막 수단으로 힌트/플랜 고정

FORCE INDEX는 데이터 분포가 바뀌었을 때 오히려 성능을 망칠 수 있습니다. “지금 당장”은 빨라져도 “내일”은 느려질 수 있습니다.

9. 커버링 인덱스로 랜덤 I/O 비용을 줄여 풀스캔을 이기는 방법

옵티마이저가 풀스캔을 선택하는 이유 중 하나는 “인덱스를 타면 결국 테이블로 다시 가야 해서 랜덤 I/O가 많다”입니다. 이때 커버링 인덱스(covering index) 로 테이블 접근을 없애면 인덱스 플랜이 더 싸질 수 있습니다.

예:

SELECT user_id, amount
FROM orders
WHERE status='CANCELLED'
  AND created_at >= '2026-01-01';

커버링 인덱스:

ALTER TABLE orders
  ADD KEY idx_status_created_cover (status, created_at, user_id, amount);

EXPLAIN에서 Extra에 Using index가 뜨면(조건에 따라 다름) 테이블을 덜 읽는 방향으로 최적화된 것입니다.

주의: 커버링 인덱스는 쓰기 비용과 인덱스 크기를 증가시킵니다. “핵심 조회”에만 제한적으로 적용하세요.

10. 운영 적용 체크리스트(안전하게)

10.1 변경 전/후 비교 쿼리

  • 동일 파라미터로 EXPLAIN ANALYZE(가능한 버전에서) 또는 EXPLAIN FORMAT=JSON 비교
  • 실행시간뿐 아니라 rows 추정이 현실에 가까워졌는지 확인
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE status='CANCELLED'
  AND created_at >= '2026-01-01'
LIMIT 100;

10.2 히스토그램 유지 전략

  • 값 분포가 자주 바뀌는 컬럼이면 주기적으로 재생성 필요
  • 배치 직후(대량 적재 후) ANALYZE TABLE ... UPDATE HISTOGRAM을 작업에 포함

10.3 “통계가 맞는데도” 느리면 인프라 병목도 점검

DB 튜닝이 통계 문제로 보였지만, 실제로는 I/O 크레딧, 스토리지 지연, 커넥션 폭주 같은 외부 요인일 수 있습니다. 장애 분석 관점의 접근은 Cloud Run 504 Timeout 원인·해결 9가지처럼 “레이어별로 원인을 분리”하는 방식이 도움이 됩니다.

11. 요약: 풀스캔을 ‘없애는’ 게 아니라 ‘이유를 제거’하자

  • 인덱스가 있어도 풀스캔이 나오는 핵심 이유는 옵티마이저의 비용 판단이며, 그 비용 판단은 통계(카디널리티/분포) 에 크게 의존합니다.
  • 가장 먼저 할 일: ANALYZE TABLE로 통계 갱신
  • 분포가 치우친 컬럼: 히스토그램으로 선택도 추정 개선
  • 컬럼 간 상관관계/결합 조건이 문제라면: 복합 인덱스(필요 시 커버링)로 구조적으로 비용을 낮추기
  • FORCE INDEX는 최후의 수단으로만 사용

인덱스는 “만들면 끝”이 아니라, 데이터가 변하면 통계도 변하고 플랜도 변합니다. MySQL 8의 히스토그램은 그 간극을 줄이는 강력한 도구이니, 풀스캔을 만났을 때 ‘인덱스가 왜 무시되는지’를 통계 관점에서 먼저 의심해보세요.

Discuss on Twitter • View on GitHub