[MySQL] 커버링 인덱스 설계: 테이블 접근 제거 조건과 과도한 인덱스 비용

728x90

커버링 인덱스: 실무적 정의와 운영상 중요성

MySQL에서 커버링 인덱스(Covering Index)는 쿼리가 요구하는 컬럼을 하나의 인덱스에서 모두 읽을 수 있도록 설계한 인덱스이다. 검색 조건만 인덱스에 존재하는 것으로는 충분하지 않다. SELECT, WHERE, JOIN, ORDER BY, GROUP BY 처리에 필요한 컬럼이 인덱스만으로 해결되어야 테이블 레코드 접근을 제거할 수 있다.

InnoDB의 보조 인덱스 리프에는 보조 인덱스 키와 기본 키(Primary Key)가 저장된다. 따라서 기본 키 컬럼은 보조 인덱스 정의에 직접 나열하지 않아도 커버링에 이용될 수 있다. 반면 인덱스에 없는 일반 컬럼을 SELECT하면 보조 인덱스에서 기본 키를 찾은 뒤 클러스터드 인덱스를 다시 탐색해야 한다.

커버링 인덱스의 목적은 단순히 EXPLAIN의 Extra에 Using index를 표시하는 것이 아니다. 랜덤한 클러스터드 인덱스 조회를 줄이고, 실제 읽은 페이지 수와 응답 시간의 변동성을 낮추는 것이 목적이다. 운영 환경에서는 좁은 조회 인덱스를 만드는 이점과 인덱스 크기, 버퍼 풀 점유, INSERT·UPDATE 비용을 함께 판단해야 한다.

Part 1. 커버링 인덱스 기본 설계 및 사용법 (Basic Syntax)

다음 테이블에서 특정 고객의 기간별 주문 목록을 조회한다고 가정한다. 조회 조건은 customer_id의 동등 조건과 ordered_at의 범위 조건이며, 결과는 최신 순으로 제한한다.

CREATE TABLE orders (
    order_id BIGINT NOT NULL,
    customer_id BIGINT NOT NULL,
    ordered_at DATETIME NOT NULL,
    status VARCHAR(20) NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    memo TEXT,
    PRIMARY KEY (order_id),
    KEY ix_orders_customer_date (customer_id, ordered_at)
) ENGINE=InnoDB;

SELECT order_id, ordered_at, status, total_amount
FROM orders
WHERE customer_id = ?
  AND ordered_at >= ?
  AND ordered_at < ?
ORDER BY ordered_at DESC
LIMIT 100;

ix_orders_customer_date는 검색 범위를 좁힐 수 있지만 status와 total_amount를 포함하지 않는다. InnoDB는 후보 인덱스 엔트리마다 기본 키를 이용해 클러스터드 인덱스 레코드를 찾아야 한다. 반환 행이 적더라도 조건에 맞는 후보가 많으면 반복적인 페이지 접근이 발생한다.

조회 빈도와 반환 컬럼이 안정적이라면 다음과 같이 확장할 수 있다. 동등 조건 컬럼을 앞에 두고, 범위 및 정렬 컬럼을 이어 배치하며, 출력 전용 컬럼은 뒤에 둔다.

CREATE INDEX ix_orders_customer_date_cover
ON orders (customer_id, ordered_at, status, total_amount);

EXPLAIN
SELECT order_id, ordered_at, status, total_amount
FROM orders
WHERE customer_id = ?
  AND ordered_at >= ?
  AND ordered_at < ?
ORDER BY ordered_at DESC
LIMIT 100;

이 인덱스에서는 order_id가 보조 인덱스 리프에 기본 키로 포함되므로 예시 쿼리의 요구 컬럼을 모두 제공할 수 있다. 실행 계획의 Extra에 Using index가 나타난다면 인덱스 엔트리만으로 결과를 구성할 수 있음을 의미한다. 다만 이 표시는 물리 디스크 읽기가 0이라는 의미가 아니다. 필요한 인덱스 페이지가 버퍼 풀에 없으면 해당 페이지를 스토리지에서 읽어야 한다.

조건	커버링 가능 여부	운영상 영향
요구 컬럼이 모두 한 인덱스에 존재	가능	클러스터드 인덱스 재탐색을 제거할 수 있다.
SELECT에 비인덱스 컬럼이 하나라도 포함	불가능	기본 키를 이용한 테이블 레코드 조회가 발생한다.
보조 인덱스와 기본 키만 조회	가능	InnoDB 보조 인덱스의 기본 키 저장 특성을 활용한다.
`SELECT *` 사용	대부분 불가능	모든 컬럼을 인덱스에 넣는 과도한 설계로 이어진다.
인덱스 페이지가 버퍼 풀에 없음	논리적으로 가능	테이블 접근은 없어도 인덱스 페이지의 물리 I/O가 발생한다.

Part 2. 내부 동작 메커니즘과 과도한 인덱스의 안티 패턴

InnoDB의 B+Tree는 고정 크기의 페이지에 여러 인덱스 레코드를 저장한다. 인덱스 레코드가 넓어지면 한 리프 페이지에 들어가는 엔트리 수가 감소한다. 동일한 행 수를 보관하는 데 더 많은 페이지가 필요하고, 상위 노드의 fan-out도 낮아질 수 있다. 결과적으로 인덱스 크기와 버퍼 풀 점유량이 증가하며 범위 스캔에서 읽는 페이지 수도 늘어난다.

과도한 인덱스를 판정하는 모든 시스템에 공통인 단일 바이트 기준은 없다. 컬럼 선언 길이만으로 판단해서도 안 된다. 데이터 타입, 문자 집합, 실제 값 길이, NULL 가능 여부, 기본 키 폭, 압축 여부, 데이터 분포와 쿼리 빈도가 함께 영향을 미친다. DBA는 절대적인 컬럼 개수보다 페이지 효율과 읽기·쓰기 측정값으로 판단해야 한다.

실무적인 경고 신호는 명확하다. 커버링 컬럼을 추가한 뒤 인덱스 크기와 읽은 페이지 수는 크게 증가했지만 테이블 접근 감소 효과가 작거나, INSERT 처리량과 지연 시간이 허용 범위를 벗어나면 인덱스 폭이 과도하다. 버퍼 풀에 핵심 데이터와 인덱스의 작업 집합이 유지되지 못하고 페이지 교체가 증가하는 경우도 동일하다.

가장 치명적인 안티 패턴은 여러 화면의 출력 컬럼을 하나의 인덱스에 모두 추가하는 것이다. 긴 VARCHAR, TEXT, JSON, 변경 빈도가 높은 상태 컬럼을 무분별하게 포함하면 읽기 한 건의 이익을 위해 모든 쓰기 트랜잭션에 유지 비용을 부과한다. MySQL의 인덱스 키 길이와 컬럼 지원 범위에는 버전, 행 형식, 문자 집합에 따른 제한도 있으므로 실제 운영 버전의 공식 제한을 확인해야 한다.

-- 피해야 할 설계 예시
CREATE INDEX ix_orders_everything
ON orders (
    customer_id, ordered_at, status, total_amount,
    shipping_address, recipient_name, updated_at
);

위 인덱스는 특정 쿼리를 커버할 수 있지만 모든 INSERT에서 더 넓은 인덱스 엔트리를 기록해야 한다. 인덱스 컬럼이 UPDATE되면 보조 인덱스 수정과 페이지 분할 가능성도 증가한다. 긴 출력 컬럼이 필요하다면 먼저 좁은 인덱스로 기본 키 집합을 제한한 뒤 필요한 행만 조회하는 2단계 접근이 더 안정적일 수 있다.

판정 항목	확인 방법	과도함을 의심할 조건
커버링 효과	동일 조건에서 EXPLAIN과 실제 실행 통계를 비교한다.	테이블 접근 제거 후에도 읽은 페이지와 지연이 거의 개선되지 않는다.
인덱스 크기	`mysql.innodb_index_stats`의 `size` 또는 관리 도구의 인덱스 통계를 확인한다.	추가 컬럼 대비 크기 증가가 크고 버퍼 풀 작업 집합을 압박한다.
페이지 읽기	세션·Performance Schema·스토리지 지표로 동일 실행의 읽기를 측정한다.	넓어진 인덱스의 페이지 밀도 저하가 테이블 접근 감소분을 상쇄한다.
쓰기 비용	동일 데이터와 동시성으로 INSERT 처리량 및 지연 분포를 비교한다.	서비스의 처리량 또는 지연 목표를 벗어난다.
사용 범위	쿼리 다이제스트와 인덱스 사용 통계를 확인한다.	낮은 빈도의 한 쿼리만 이익을 얻고 모든 쓰기가 비용을 부담한다.

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

검증은 커버링 전후를 동일한 조건에서 비교해야 한다. 데이터 건수와 분포, 파라미터, 동시성, 버퍼 풀 상태를 기록하고 한 번의 실행값이 아니라 반복 측정의 중앙값과 상위 지연 구간을 확인한다. 캐시가 따뜻한 상태와 재시작 직후 또는 통제된 콜드 상태는 결과가 다르므로 두 조건을 구분해야 한다.

ANALYZE TABLE orders;

EXPLAIN ANALYZE
SELECT order_id, ordered_at, status, total_amount
FROM orders
WHERE customer_id = ?
  AND ordered_at >= ?
  AND ordered_at < ?
ORDER BY ordered_at DESC
LIMIT 100;

SELECT index_name, stat_name, stat_value
FROM mysql.innodb_index_stats
WHERE database_name = DATABASE()
  AND table_name = 'orders'
  AND index_name IN (
      'ix_orders_customer_date',
      'ix_orders_customer_date_cover'
  )
  AND stat_name IN ('size', 'n_leaf_pages');

EXPLAIN ANALYZE는 지원되는 MySQL 버전에서 실제 실행 행 수와 반복 정보를 확인하는 데 사용한다. 운영 트래픽에서 실행하면 쿼리가 실제로 수행되므로 부하가 큰 SQL에는 주의가 필요하다. 인덱스 통계는 근사값일 수 있으므로 전후 크기와 페이지 수를 비교하는 보조 지표로 사용한다.

읽은 페이지 수는 서버 전체 카운터의 단순 전후 차이만으로 판단하면 다른 세션의 활동이 섞인다. 격리된 검증 환경에서 Performance Schema의 statement 및 wait 계측, 버퍼 풀 읽기 지표, 스토리지 I/O 지표를 함께 수집해야 한다. 쿼리 태그나 별도 계정을 사용해 대상 워크로드를 구분하면 측정 오차를 줄일 수 있다.

INSERT 비용은 같은 스키마와 데이터 분포를 가진 두 환경에서 비교한다. 한쪽에는 기존 인덱스, 다른 쪽에는 후보 커버링 인덱스를 구성하고 동일한 배치 크기, 커밋 주기, 연결 수와 지속 시간으로 부하를 실행한다. 초당 처리 건수뿐 아니라 커밋 지연, redo 생성량, 페이지 분할 관련 지표, 버퍼 풀 dirty page와 스토리지 쓰기량을 기록해야 한다.

후보 인덱스는 다음 순서로 결정한다. 먼저 WHERE와 JOIN의 선택도가 높은 동등 조건 컬럼을 배치한다. 그다음 범위 검색과 정렬을 담당하는 컬럼을 배치한다. 마지막으로 실제 반환 빈도가 높고 폭이 작은 컬럼만 커버링 목적으로 추가한다. 범위 조건 뒤의 컬럼은 탐색 범위를 더 줄이지 못할 수 있지만 결과 반환과 정렬 회피에는 기여할 수 있으므로 실행 계획으로 확인해야 한다.

함수로 인덱스 컬럼을 가공하면 커버링 여부와 별개로 효율적인 범위 탐색이 무너질 수 있다. 날짜 조건을 SARGable 범위로 작성하는 원칙은 MySQL DATEDIFF 성능 저하와 인덱스 무력화 해결 방법에서 확인할 수 있다. 같은 이유로 DATE_FORMAT이 유발하는 Full Table Scan 튜닝 가이드와 DATE_ADD/DATE_SUB 인덱스 튜닝 기법도 함께 적용할 필요가 있다.

성능 최적화의 핵심은 Using index 한 항목이 아니라 전체 비용의 감소를 확인하는 것이다. 커버링 인덱스는 요구 컬럼을 모두 제공하고 클러스터드 인덱스 접근을 제거할 때 I/O를 줄인다. 그러나 인덱스 페이지 자체의 읽기는 남으며, 폭 증가가 페이지 밀도와 버퍼 풀 효율을 악화시키면 이점이 상쇄된다. DBA는 EXPLAIN Extra, 실제 읽은 페이지 수, 인덱스 크기와 INSERT 처리량을 동일 조건에서 비교한 뒤 운영 목표를 만족하는 가장 좁은 인덱스를 채택해야 한다.