[MySQL] 낮은 선택도 인덱스에서 Full Table Scan을 선택하는 기준과 검증 방법

728x90

낮은 선택도 인덱스: 실무적 정의와 운영상 중요성

MySQL에서 인덱스가 존재한다는 사실은 해당 인덱스가 항상 사용된다는 의미가 아니다. 옵티마이저는 후보 실행 계획별 비용을 계산하고, 인덱스 접근보다 전체 테이블 스캔(Full Table Scan)의 예상 비용이 낮으면 type=ALL을 선택한다.

선택도(Selectivity)는 조건이 전체 행 중 얼마나 적은 행을 반환하는지를 나타낸다. 실무에서는 조건 일치 행 수 ÷ 전체 행 수로 반환 비율을 계산하며, 반환 비율이 낮을수록 선택도가 높다고 표현한다. 서로 다른 값의 수를 의미하는 카디널리티(Cardinality)와 선택도는 관련되지만 동일한 지표는 아니다.

예를 들어 상태 컬럼에 값이 세 종류만 존재해도 특정 값이 전체의 극소수라면 그 조건은 높은 선택도를 가질 수 있다. 반대로 값의 종류가 많아도 특정 값에 데이터가 집중되어 있다면 해당 조건의 선택도는 낮아진다. 따라서 SHOW INDEX의 카디널리티만으로 실행 계획을 단정하면 안 된다.

Part 1. 선택도와 카디널리티 기본 확인 방법 (Basic Syntax)

검증은 인덱스 정의, 전체 행 수, 값별 분포를 분리해서 확인하는 것부터 시작한다. 다음 쿼리는 테스트 대상 컬럼의 인덱스 존재 여부와 통계상 카디널리티를 확인한다.

SHOW INDEX FROM orders;

SELECT
    COUNT(*) AS total_rows,
    COUNT(DISTINCT order_status) AS distinct_values
FROM orders;

SELECT
    order_status,
    COUNT(*) AS matched_rows,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 4) AS return_pct
FROM orders
GROUP BY order_status
ORDER BY matched_rows DESC;

SHOW INDEX의 Cardinality는 통계 기반 추정치이다. 정확한 값이 아닐 수 있으며 데이터 적재와 삭제가 반복된 테이블에서는 실제 분포와 차이가 발생한다. 값별 COUNT(*) 결과는 조건별 실제 반환 비율을 확인하기 위한 별도 근거로 사용해야 한다.

확인 항목	확인 방법	판단 목적
인덱스 정의	`SHOW INDEX`	대상 컬럼의 키 순서와 통계상 카디널리티 확인
전체 행 수	`COUNT(*)`	반환 비율 계산의 분모 확인
값별 분포	`GROUP BY`	균등 분포와 편향 분포 구분
조건별 반환 비율	`matched_rows / total_rows`	인덱스 탐색 후 발생할 테이블 접근 규모 추정

테스트 테이블은 운영 데이터의 분포를 재현해야 한다. 전체 건수만 동일하고 상태값을 균등하게 생성하면 데이터 편향으로 발생하는 실행 계획을 재현할 수 없다. 작성자는 빈도가 높은 값, 중간 빈도 값, 희소 값이 포함된 테스트 데이터를 준비하고 아래 측정 방법으로 실제 결과를 확인해야 한다.

조건값	전체 행 수	실제 반환 행 수	반환 비율	분포 특성
고빈도 값	`COUNT(*)` 결과	값별 `COUNT(*)`의 최댓값	`matched_rows / total_rows × 100%`	집중 분포
중간 빈도 값	`COUNT(*)` 결과	빈도순 중앙에 가까운 값의 `COUNT(*)`	`matched_rows / total_rows × 100%`	중간 분포
희소 값	`COUNT(*)` 결과	값별 `COUNT(*)`의 최솟값	`matched_rows / total_rows × 100%`	희소 분포

Part 2. 내부 동작 메커니즘과 Full Scan 선택 기준

InnoDB의 일반 보조 인덱스(Secondary Index)는 조건에 맞는 인덱스 엔트리를 찾은 뒤 기본 키를 이용해 클러스터드 인덱스의 실제 행을 다시 조회한다. 반환 행이 많으면 이 과정이 반복되며 랜덤 페이지 접근, 버퍼 풀 페이지 탐색과 추가 CPU 사용이 발생한다.

반면 전체 테이블 스캔은 테이블 또는 클러스터드 인덱스의 페이지를 넓은 범위로 순차 접근한다. 많은 행을 읽어야 하는 조건에서는 수많은 보조 인덱스 조회와 행 단위 재접근보다 전체 페이지를 한 번 훑는 계획이 저렴할 수 있다. 이로 인해 인덱스가 정상적으로 존재해도 옵티마이저가 ALL을 선택한다.

Full Scan으로 전환되는 고정 반환 비율은 존재하지 않는다. 테이블 크기, 행 길이, 인덱스 폭, 커버링 여부, 버퍼 풀 적중 상태, 저장장치 비용, 통계 정확도와 조건 결합 방식이 함께 반영된다. 특정 비율을 모든 테이블에 적용하는 것은 가장 흔한 판단 오류이다.

EXPLAIN으로 예상 비용 검증

EXPLAIN FORMAT=JSON
SELECT order_id, customer_id, created_at
FROM orders
WHERE order_status = 'TARGET_VALUE';

EXPLAIN
SELECT order_id, customer_id, created_at
FROM orders
WHERE order_status = 'TARGET_VALUE';

기본 EXPLAIN에서는 type, possible_keys, key, rows, filtered, Extra를 함께 확인한다. possible_keys에 인덱스가 표시되지만 key가 NULL이고 type=ALL이라면 인덱스를 후보로 인식한 뒤 비용 비교에서 제외했을 가능성이 높다.

rows는 실제 반환 행 수가 아니라 검사할 것으로 예상한 행 수이다. 예상 행 수와 실제 행 수의 차이가 크면 통계가 데이터 편향을 충분히 반영하지 못했는지 확인해야 한다. FORMAT=JSON이 제공하는 비용 정보도 절대 실행 시간이 아니라 동일 환경의 후보 계획을 비교하기 위한 추정값으로 해석해야 한다.

EXPLAIN ANALYZE로 실제 실행 검증

EXPLAIN ANALYZE
SELECT order_id, customer_id, created_at
FROM orders
WHERE order_status = 'TARGET_VALUE';

EXPLAIN ANALYZE는 쿼리를 실제로 실행하므로 운영 환경에서 주의가 필요하다. 대용량 조회, 조인 또는 정렬이 포함되면 CPU 사용량, 버퍼 풀 경합과 디스크 I/O 병목을 유발할 수 있다. 먼저 복제본이나 격리된 테스트 환경에서 수행하는 것이 원칙이다.

조건	EXPLAIN type	key	예상 rows	실제 rows	실제 시간
고빈도 값	`EXPLAIN`의 `type` 확인	`EXPLAIN`의 `key` 확인	`EXPLAIN`의 `rows` 확인	`EXPLAIN ANALYZE`의 실제 행 수 확인	`EXPLAIN ANALYZE`의 실제 시간 확인
중간 빈도 값	`EXPLAIN`의 `type` 확인	`EXPLAIN`의 `key` 확인	`EXPLAIN`의 `rows` 확인	`EXPLAIN ANALYZE`의 실제 행 수 확인	`EXPLAIN ANALYZE`의 실제 시간 확인
희소 값	`EXPLAIN`의 `type` 확인	`EXPLAIN`의 `key` 확인	`EXPLAIN`의 `rows` 확인	`EXPLAIN ANALYZE`의 실제 행 수 확인	`EXPLAIN ANALYZE`의 실제 시간 확인

이 표에서 예상 행과 실제 행이 비슷하고 Full Scan이 더 짧은 시간에 완료된다면 옵티마이저의 선택은 합리적일 가능성이 높다. 예상 행과 실제 행의 차이가 크고 강제 인덱스 계획이 반복 측정에서 더 적은 자원을 사용한다면 통계 오류나 비용 추정 오차를 의심해야 한다.

Part 3. 성능 최적화 및 튜닝 전략

1. 통계를 갱신한 뒤 동일 조건을 다시 비교한다

ANALYZE TABLE orders;

EXPLAIN FORMAT=JSON
SELECT order_id, customer_id, created_at
FROM orders
WHERE order_status = 'TARGET_VALUE';

ANALYZE TABLE은 인덱스 통계를 다시 수집해 옵티마이저의 행 수 추정에 반영한다. 실행 전후의 rows와 선택된 key를 기록해야 통계 갱신이 판단에 어떤 영향을 주었는지 확인할 수 있다. 운영 환경에서는 테이블 크기와 버전에 따른 부하 및 잠금 영향을 사전에 검토해야 한다.

2. 편향된 분포는 히스토그램 적용 여부를 검토한다

ANALYZE TABLE orders
UPDATE HISTOGRAM ON order_status
WITH 64 BUCKETS;

SELECT HISTOGRAM
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = DATABASE()
  AND TABLE_NAME = 'orders'
  AND COLUMN_NAME = 'order_status';

히스토그램은 값 분포가 균등하지 않은 컬럼의 조건 선택도를 추정하는 데 사용된다. 버킷 수를 높이는 것이 항상 정확도 향상을 보장하지 않으며 통계 관리 비용도 함께 고려해야 한다. 적용 전후에 같은 조건의 예상 행 수와 실제 행 수를 비교해야 한다.

3. 강제 인덱스는 진단용 대조군으로 사용한다

EXPLAIN ANALYZE
SELECT order_id, customer_id, created_at
FROM orders
WHERE order_status = 'TARGET_VALUE';

EXPLAIN ANALYZE
SELECT order_id, customer_id, created_at
FROM orders FORCE INDEX (idx_orders_status)
WHERE order_status = 'TARGET_VALUE';

FORCE INDEX는 Full Scan 계획과 인덱스 계획을 같은 조건에서 비교하는 진단 수단이다. 한 번의 실행 시간만으로 결론을 내리면 캐시 예열, 동시 부하와 스토리지 상태의 영향을 분리할 수 없다. 실행 순서를 교차하고 동일한 결과 건수, 동일한 세션 설정과 유사한 부하 조건에서 여러 차례 측정해야 한다.

운영 쿼리에 강제 힌트를 즉시 고정하는 것은 위험하다. 데이터 분포가 바뀌면 현재 유리한 인덱스가 이후에는 더 많은 랜덤 I/O를 유발할 수 있다. 강제 인덱스는 통계 갱신과 인덱스 설계 검토 후에도 비용 모델이 지속적으로 잘못 판단할 때 제한적으로 적용해야 한다.

4. 커버링 인덱스와 복합 인덱스를 쿼리 형태에 맞춘다

CREATE INDEX idx_orders_status_created
ON orders (order_status, created_at);

EXPLAIN
SELECT order_status, created_at
FROM orders
WHERE order_status = 'TARGET_VALUE'
  AND created_at >= 'START_DATETIME'
  AND created_at < 'END_DATETIME';

선택도가 낮은 단일 컬럼에 날짜 범위처럼 선택도를 높이는 조건을 결합하면 읽어야 할 인덱스 범위를 줄일 수 있다. 조회 컬럼이 인덱스 안에 포함되는 커버링 인덱스가 되면 클러스터드 인덱스 재접근도 감소한다. 다만 인덱스 폭과 개수가 증가하면 쓰기 비용, 버퍼 풀 점유와 저장 공간이 증가하므로 실제 쿼리 빈도를 기준으로 결정해야 한다.

날짜 조건을 추가하더라도 인덱스 컬럼에 함수를 적용하면 범위 탐색이 무력화될 수 있다. 관련 원리는 DATE_FORMAT이 유발하는 Full Table Scan과 쿼리 튜닝 가이드와 DATEDIFF 성능 저하와 인덱스 무력화 해결 방법에서 확인할 수 있다.

인덱스가 있어도 Full Scan이 발생할 때의 판단 순서

단계	검증 내용	다음 조치
1	`possible_keys`와 인덱스 선두 컬럼 확인	후보에도 없으면 조건식과 인덱스 구조 점검
2	전체 행 수와 조건별 실제 반환 비율 계산	고빈도 조건이면 Full Scan의 합리성 검토
3	`EXPLAIN` 예상 rows와 실제 rows 비교	차이가 크면 통계와 데이터 편향 점검
4	통계 갱신 및 히스토그램 적용 전후 비교	추정 정확도 개선 여부 확인
5	기본 계획과 `FORCE INDEX` 계획 측정	시간뿐 아니라 읽은 행과 I/O도 함께 비교
6	커버링·복합 인덱스의 쓰기 비용 검토	반복 쿼리에 한해 설계 변경

관련 함수 조건이 인덱스 탐색을 방해하는지는 STR_TO_DATE 함수 오남용으로 인한 전체 테이블 스캔 분석과 동일한 방식으로 실행 계획을 대조할 수 있다.

성능 최적화의 핵심은 인덱스 사용 자체를 목표로 삼지 않고 실제 읽기 비용을 줄이는 것이다. DBA는 데이터 분포, 조건별 반환 비율, 예상 행과 실제 행의 차이, 인덱스 강제 계획의 측정 결과를 순서대로 확인해야 한다. 통계가 정확한데도 많은 행을 반환한다면 Full Table Scan은 장애 원인이 아니라 합리적인 실행 계획일 수 있다.