인덱스 선택도가 성능에 미치는 영향

목차

• #문제가 되는 상황
• #Cardinality와 선택도를 구분해서 보기
• #같은 열도 조건값에 따라 선택도가 다르다
• #낮은 선택도 인덱스를 옵티마이저가 피하는 이유
• #boolean 열은 정말 인덱스 가치가 없을까
• #복합 인덱스에서는 맥락이 달라진다
• #데이터 쏠림이 평균을 무너뜨린다
• #통계가 오래되면 잘못된 계획을 고를 수 있다
• #선택도만으로 열 순서를 정하면 안 된다
• #부분 인덱스와 데이터 모델 대안
• #실제 분포와 실행 계획을 확인하는 방법
• #결론
• #관련 노트

문제가 되는 상황

주문 테이블에 is_deleted 열이 있고 활성 주문만 조회한다고 하자.

SELECT id, created_at, status
FROM orders
WHERE is_deleted = false
ORDER BY created_at DESC
LIMIT 20;

WHERE에 등장하는 열이니 다음 인덱스를 만들고 싶어진다.

CREATE INDEX ix_orders_is_deleted
ON orders(is_deleted);

하지만 1,000만 건 중 990만 건이 false라면 인덱스로 990만 엔트리를 찾은 뒤 본 테이블 행까지 읽어야 한다. 전체 데이터를 순서대로 읽는 것보다 오히려 랜덤 lookup이 많아질 수 있다. 옵티마이저가 인덱스를 무시하고 전체 스캔을 고르는 것도 이상한 일이 아니다.

반대로 삭제된 10만 건만 찾는 배치 쿼리는 같은 인덱스에서 작은 범위를 읽을 수 있다.

SELECT id
FROM orders
WHERE is_deleted = true;

같은 열, 같은 인덱스라도 조건값에 따라 비용이 크게 다르다. “boolean은 선택도가 낮아서 인덱스를 쓰면 안 된다”는 문장만으로는 이 차이를 설명할 수 없다.

Cardinality와 선택도를 구분해서 보기

용어는 문서마다 조금 다르게 쓰이지만 인덱스를 판단할 때 두 질문을 나눠 보는 것이 유용하다.

1. 이 열에는 서로 다른 값이 몇 개인가?
2. 지금 조건을 적용하면 전체 행 중 몇 행이 남는가?

Cardinality를 서로 다른 값의 개수라고 하면 boolean의 cardinality는 최대 2다.

total rows       = 10,000,000
distinct values  = 2 (true, false)

간단한 열 선택도를 다음처럼 계산하기도 한다.

column selectivity = distinct values / total rows
                   = 2 / 10,000,000

하지만 실제 쿼리 비용에는 특정 조건이 남기는 비율이 더 직접적이다.

WHERE is_deleted = false → 9,900,000 rows → 99%
WHERE is_deleted = true  →   100,000 rows →  1%

여기서는 이를 조건 선택도 또는 반환 비율로 생각하겠다. 반환 비율이 작을수록 인덱스가 읽기 범위를 크게 줄일 가능성이 높다.

같은 열도 조건값에 따라 선택도가 다르다

주문 상태에는 값이 네 종류뿐이어도 분포가 균등하지 않을 수 있다.

CREATE INDEX ix_orders_status
ON orders(status);

status = 'FAILED'는 0.1%만 남기므로 유용할 수 있다. 반면 status = 'COMPLETED'는 테이블 대부분을 반환한다. 특히 SELECT *이면 보조 인덱스에서 기본 키를 얻은 뒤 850만 행을 다시 찾아야 할 수 있어 전체 스캔보다 불리하다.

SELECT * FROM orders WHERE status = 'FAILED';
SELECT * FROM orders WHERE status = 'COMPLETED';

옵티마이저가 파라미터 값과 통계를 고려한다면 두 쿼리에 다른 계획을 고를 수 있다. prepared statement와 plan cache를 사용하는 DB에서는 처음 또는 대표 값으로 만든 계획이 다른 분포의 값에도 재사용되는지 확인해야 한다.

낮은 선택도 인덱스를 옵티마이저가 피하는 이유

보조 인덱스를 사용한 조회 비용을 단순화하면 다음 두 단계다.

1. 보조 B+Tree에서 조건에 맞는 엔트리 범위 읽기
2. 각 엔트리의 기본 키로 본 테이블 행 읽기

후보가 100건이면 두 번째 단계도 작다. 후보가 900만 건이면 기본 키 lookup이 누적되고, 인덱스 페이지와 데이터 페이지를 모두 읽게 될 수 있다.

flowchart LR
    A[status 보조 인덱스] --> B[수백만 개 PK]
    B --> C[클러스터드 인덱스 반복 탐색]
    C --> D[전체 행 반환]

전체 스캔은 많은 페이지를 읽지만 비교적 순차적인 경로로 테이블을 한 번 훑을 수 있다. 그래서 어느 비율 이상이면 전체 스캔이 항상 유리하다는 고정 임계치는 없다.

• 행과 인덱스 엔트리의 크기
• 필요한 SELECT 열
• covering 여부
• 데이터 페이지의 물리적·논리적 분포
• 버퍼 풀 적중률
• 스토리지 지연
• 병렬 스캔 지원
• LIMIT과 정렬 조건

이 요소에 따라 손익분기점이 달라진다. “전체의 10%면 인덱스” 같은 숫자는 출발 가설일 수는 있어도 규칙이 될 수 없다.

boolean 열은 정말 인덱스 가치가 없을까

boolean의 서로 다른 값은 두 개뿐이지만 분포가 한쪽으로 크게 기울면 드문 값 조회에는 의미가 있다.

processed = true   99.99%
processed = false   0.01%

미처리 작업만 가져오는 워커라면 다음 인덱스가 작은 READY 집합을 빠르게 찾을 수 있다.

CREATE INDEX ix_jobs_processed_created
ON jobs(processed, created_at);

SELECT id
FROM jobs
WHERE processed = false
ORDER BY created_at
LIMIT 100;

processed = false인 범위 안에서 created_at 순서로 100건을 읽고 멈출 수 있다. 반대로 대부분이 false인 서비스 초기에는 같은 인덱스의 효과가 작을 수 있다. 데이터 생명주기에 따라 분포가 변한다는 점도 고려해야 한다.

복합 인덱스에서는 맥락이 달라진다

is_deleted가 전체 테이블에서 99% false여도, tenant별 조회에서 복합 키의 일부로 의미가 있을 수 있다.

CREATE INDEX ix_orders_tenant_deleted_created
ON orders(tenant_id, is_deleted, created_at DESC);

SELECT id, created_at
FROM orders
WHERE tenant_id = 8421
  AND is_deleted = false
ORDER BY created_at DESC
LIMIT 20;

먼저 tenant_id가 전체 테이블을 한 사용자의 주문 구간으로 줄인다. 그 안에서 활성·삭제 행을 분리하고 created_at 순서를 제공한다.

전체 orders 10,000,000
→ tenant_id=8421  12,000
→ is_deleted=false 11,700
→ created_at 최신순 20건에서 중단

is_deleted 자체는 많이 줄이지 못해도 중간 열로 고정되어 뒤의 created_at이 활성 행 안에서 정렬되게 한다. 거의 모든 조회가 false를 사용하고 삭제 데이터가 섞여 있다면 의미가 있다.

하지만 is_deleted 조건이 없는 tenant 전체 목록도 중요하다면 같은 인덱스는 created_at 전체 정렬을 직접 제공하지 못할 수 있다. 활성/삭제 값별로 날짜가 나뉘기 때문이다. 이 경우 (tenant_id, created_at)과의 필요성을 쿼리별로 비교한다.

데이터 쏠림이 평균을 무너뜨린다

전체 평균이 좋아 보여도 특정 tenant나 특정 날짜 구간에 데이터가 몰릴 수 있다.

평균 주문 수 / tenant = 100
tenant 8421 주문 수    = 2,000,000
나머지 tenant 대부분   = 10~50

평균 100건 데이터로 테스트한 인덱스는 대형 tenant의 실제 쿼리를 설명하지 못한다. 다중 테넌트 열과 status 사이에 상관관계가 있을 수도 있다.

전체 FAILED 비율 = 0.1%
tenant 8421의 FAILED 비율 = 35%

옵티마이저 통계가 각 열을 독립적으로 가정하면 tenant_id = 8421 AND status = FAILED의 행 수를 크게 과소평가할 수 있다. 잘못된 cardinality 추정은 조인 순서, 인덱스 선택, 중첩 루프 횟수를 잘못 결정하게 만든다.

EXPLAIN ANALYZE
SELECT ...
FROM orders
WHERE tenant_id = 8421
  AND status = 'FAILED';

실행 계획에서 estimated rows와 actual rows 차이가 큰지 확인해야 하는 이유다.

통계가 오래되면 잘못된 계획을 고를 수 있다

옵티마이저는 모든 행을 직접 세어 계획을 만들지 않는다. 수집된 인덱스 통계, 샘플, 히스토그램 등을 사용해 조건 결과를 추정한다. 대량 데이터 적재나 삭제 후 통계가 현재 분포를 반영하지 못하면 비효율적인 계획을 고를 수 있다.

통계가 기억하는 상태: PENDING 50%
현재 실제 상태:       PENDING 0.5%

DB가 제공하는 통계 갱신 명령과 자동 갱신 정책을 확인한다. 예를 들어 대량 백필 후 계획이 갑자기 나빠졌다면 인덱스 유무만 보지 말고 통계 시점과 예상 행 수를 확인한다.

히스토그램은 인덱스가 없는 열 또는 값별로 치우친 분포를 더 잘 추정하는 데 도움을 줄 수 있다. 다중 열 상관관계를 다루는 확장 통계를 지원하는 DB도 있다. 기능과 문법은 제품·버전에 따라 다르므로 대상 환경을 기준으로 사용한다.

선택도만으로 열 순서를 정하면 안 된다

복합 인덱스에서 “선택도가 가장 높은 열을 맨 앞에 둔다”는 공식은 실제 접근 패턴을 놓칠 수 있다.

INDEX (order_id, tenant_id)
INDEX (tenant_id, status, created_at)

order_id는 매우 높은 선택도를 갖지만 기본 키 단건 조회라면 이미 다른 인덱스가 지원한다. 반면 tenant_id는 상대적으로 낮아도 거의 모든 목록 쿼리의 필수 조건이고, 그 뒤 status와 created_at을 연결한다.

열 순서를 결정하는 질문은 다음과 같다.

1. 어떤 조건 조합이 실제로 함께 사용되는가?
2. 각 열은 등호, IN, 범위 중 어떤 연산을 사용하는가?
3. ORDER BY와 LIMIT을 인덱스 순서로 처리할 수 있는가?
4. 조건값별 실제 반환 행 수는 얼마인가?
5. 접두 부분이 다른 중요한 쿼리에도 쓰이는가?
6. 기존 인덱스와 쓰기 비용이 겹치지 않는가?

왼쪽 접두와 범위 조건의 역할은 복합 인덱스의 왼쪽 접두 규칙에서 상세히 다뤘다.

부분 인덱스와 데이터 모델 대안

활성 행이 전체의 극히 일부이고 DB가 조건부 또는 부분 인덱스를 지원한다면 필요한 행만 인덱싱하는 방법을 검토할 수 있다.

-- 지원 DB에서 사용할 수 있는 개념 예시
CREATE INDEX ix_ready_jobs_created
ON jobs(created_at)
WHERE processed = false;

인덱스가 미처리 행만 포함하므로 작고, 워커 쿼리와 직접 맞는다. 다만 MySQL을 포함해 DB별 지원 방식이 다르므로 대상 제품 문법을 확인해야 한다.

데이터 생명주기가 명확하다면 활성·아카이브 테이블을 분리하거나 파티셔닝하는 모델도 후보가 된다. 하지만 운영 복잡도, 트랜잭션, 조회 통합 비용이 늘어난다. 낮은 선택도 열 하나 때문에 성급히 모델을 분리하기보다 데이터 크기와 보관 정책을 함께 판단한다.

실제 분포와 실행 계획을 확인하는 방법

먼저 값별 건수를 확인한다.

SELECT status, COUNT(*) AS row_count
FROM orders
GROUP BY status
ORDER BY row_count DESC;

tenant별 쏠림도 본다.

SELECT tenant_id, COUNT(*) AS row_count
FROM orders
GROUP BY tenant_id
ORDER BY row_count DESC
LIMIT 20;

복합 조건의 실제 분포를 확인한다.

SELECT tenant_id, status, COUNT(*) AS row_count
FROM orders
WHERE tenant_id IN (10, 20, 8421)
GROUP BY tenant_id, status
ORDER BY tenant_id, row_count DESC;

대표값 하나만 측정하지 않는다.

EXPLAIN ANALYZE
SELECT id, created_at
FROM orders
WHERE status = 'FAILED';

EXPLAIN ANALYZE
SELECT id, created_at
FROM orders
WHERE status = 'COMPLETED';

확인할 항목은 예상·실제 행 수, 인덱스 range와 전체 스캔 선택, 본 테이블 lookup 수, 필터 후 버린 행, 정렬과 임시 구조, 차갑고 따뜻한 캐시에서의 지연이다. 읽기만 보지 말고 인덱스 추가 후 INSERT와 UPDATE 비용도 함께 측정한다.

결론

인덱스 선택도의 핵심은 열에 서로 다른 값이 몇 개인가보다, 실제 쿼리의 조건값이 전체 행 중 얼마나 작은 범위를 남기는가에 있다. 같은 status 인덱스도 드문 FAILED 조회에는 유용하고 대부분을 차지하는 COMPLETED 조회에는 전체 스캔보다 불리할 수 있다.

boolean과 낮은 cardinality 열을 무조건 제외해서도 안 된다. 앞 열로 범위를 줄인 복합 인덱스 안에서 정렬을 연결하거나, 매우 드문 상태와 LIMIT을 찾는 데는 충분한 가치가 있다. 평균 분포 대신 값별·tenant별 skew를 확인하고, 통계의 예상 행 수와 실제 행 수를 비교해야 한다. 최종 결정은 선택도 공식 하나가 아니라 WHERE·ORDER BY·LIMIT의 접근 패턴, covering 여부, 읽기와 쓰기 측정값을 함께 보고 내려야 한다.

관련 노트

• B+Tree 인덱스가 검색 범위를 줄이는 원리
• 복합 인덱스의 왼쪽 접두 규칙
• Covering Index로 테이블 접근 줄이기
• 인덱스가 많아질수록 쓰기가 느려지는 이유
• MySQL 복합 인덱스의 순서와 중복 인덱스 판단하기