실행 계획과 커버링 인덱스

인덱스가 존재해도 옵티마이저는 테이블 스캔이 더 싸다고 판단할 수 있습니다.

인덱스 리프에서 후보를 찾은 뒤 원본 클러스터형 행을 여러 번 랜덤 접근해야 하기 때문입니다.

개발자가 원하는 키를 억지로 강제하기 전에 추정치와 실제치를 읽어야 합니다.

이번 요구는 특정 작성자의 최근 게시글 목록에서 post_id, created_at, view_count만 보여 주는 좁은 읽기 모델입니다.

화면이 필요하지 않은 메모와 긴 제목을 SELECT *로 가져오는 오류부터 시작해 커버링 인덱스의 이득과 폭 비용을 함께 계산합니다.

post_search_demo는 ch7-1에서 정확히 1만 건을 만들었고 author_id=42가 4천 건, 그중 공개 상태가 3천 건입니다.

아래 쿼리로 40% 편향이 유지되는지 다시 확인한 뒤 계획을 비교합니다.

같은 author_id가 전체의 40%라면 단일 인덱스 조회가 수천 번의 원본 접근을 만들 수 있습니다.

반면 필터뿐 아니라 동률까지의 정렬 요구도 인덱스와 맞으면 LIMIT 20에서 일찍 멈출 수 있습니다. 좁은 투영의 커버링 여부와 정렬 충족 여부는 따로 판단합니다.

편향 fixture 사전 확인

SELECT
  COUNT(*) AS total_rows,
  SUM(author_id = 42) AS author_42_rows,
  SUM(author_id = 42 AND status = 'PUBLISHED')
    AS author_42_published_rows
FROM post_search_demo;

각각 10,000·4,000·3,000이 아니면 ch7-1 fixture를 초기화한 뒤 다시 시작합니다.

서로 다른 데이터로 얻은 실행 시간을 인덱스 전후 차이라고 해석하지 않습니다.

SELECT *와 인덱스 강제

응답에 필요하지 않은 모든 컬럼을 가져오고 FORCE INDEX를 붙여 인덱스를 사용했다는 사실만 확인합니다.

후보 비율이 높은 조건에서 원본 행 조회가 누적되면 테이블 스캔보다 느릴 수 있고, 스키마 컬럼 추가가 네트워크 응답까지 암묵적으로 넓힙니다.

넓은 투영과 강제 인덱스

EXPLAIN ANALYZE
SELECT *
FROM post_search_demo FORCE INDEX (ix_post_search_author_created_title)
WHERE author_id = 42
ORDER BY created_at DESC;

-- 응답에서는 실제로 세 컬럼만 사용한다.
-- post_id, created_at, view_count

지정한 인덱스에는 제목이 이미 있지만 상태·조회수 등 전체 행의 나머지 값을 얻으려면 클러스터형 인덱스 접근이 필요합니다.

넓은 투영의 비용 설명 예시

아래 숫자는 기본 fixture의 후보 수를 바탕으로 설명한 것이며 새로 수집한 실행 계획·계측 로그가 아닙니다. 실제 노드와 접근 횟수는 실행 계획에서 확인합니다.

forced access: ix_post_search_author_created_title
actual matching rows: 4,000
clustered row lookups: about 4,000
covering: no
returned columns: all columns
application-used columns: 3

FORCE 인덱스는 문법 오류를 내지 않지만 옵티마이저의 대안을 제거합니다.

출력에서 인덱스 이름만 보고 성공이라 말하면 반복 횟수와 조회 비용을 놓칩니다.

실제 업무가 전체 4천 건인지 최근 20건인지도 쿼리에 표현되어 있지 않습니다.

SELECT *는 읽기 모델 규칙을 스키마 전체와 결합합니다.

커버링 여부를 잃고 네트워크 전송량과 직렬화 비용까지 키우므로 성능과 유지보수 양쪽에서 오류입니다.

인덱스 이름보다 반복 조회를 먼저 보기

강제 힌트 제거 — 힌트 없는 계획이 어떤 대안을 고르는지 기준선으로 삼습니다.
실제 반복 횟수 — 가장 안쪽 원본 행 조회가 몇 번 반복되는지 읽습니다.
투영 축소 — 화면이 실제 사용하는 컬럼만 SELECT 목록에 둡니다.
종료 조건 — LIMIT과 안정적 정렬을 넣고, 인덱스가 그 순서까지 만족해 조기 종료할 수 있는지 확인합니다.

옵티마이저 비용 모델

보조 인덱스에 필요한 열 값이 모두 있으면 커버링 접근이 가능합니다. 열을 포함한다는 것과 ORDER BY의 순서까지 맞는다는 것은 다릅니다. 또 MVCC 가시성 확인이 필요한 보조 항목은 클러스터형 행을 확인할 수 있으므로 원본 접근이 항상 0이라고 단정하지 않습니다.

MySQL EXPLAIN의 Using index는 흔히 커버링 접근을 뜻하며, Using index condition은 인덱스 조건 푸시다운이지 같은 의미가 아닙니다.

커버링 인덱스는 별도 자료 복제가 아니라 리프 항목에 더 많은 컬럼을 넣는 설계입니다.

항목이 넓어지면 한 페이지에 들어가는 수가 줄고 B+Tree가 커져 캐시 효율과 쓰기 비용이 나빠집니다.

핵심 화면의 좁은 투영에만 적용합니다.

화면 투영에서 리프 구성을 역산하기

필요 컬럼 — post_id, created_at, view_count 세 값만 목록 규칙으로 고정합니다.
필터·정렬 — author_id 동등 조건 뒤의 시각·동률 해소 키가 요구 순서와 일치하는지 확인합니다.
PK 포함 — InnoDB 보조 인덱스 리프에 기본 키가 이미 포함된다는 점을 고려합니다.
폭 계산 — view_count 추가로 늘어나는 항목 크기와 조회 빈도를 비교합니다.

커버링 후보의 투영과 정렬 충족 범위
요구	후보 인덱스에서 얻는 것	남는 경계
작성자 필터	선두 `author_id` 동등 범위	후보 수는 작성자 분포에 따라 달라짐
세 열 투영	`created_at`·`view_count`와 PK `post_id` 포함	커버링 접근이 가능해도 MVCC 확인에 원본 접근이 필요할 수 있음
동률까지 정렬	작성자 안에서 `created_at DESC` 순서	뒤의 `view_count`와 PK ASC는 `post_id DESC` 요구와 다름

작성자 필터: 후보 인덱스에서 얻는 것: 선두 author_id 동등 범위
남는 경계: 후보 수는 작성자 분포에 따라 달라짐
세 열 투영: 후보 인덱스에서 얻는 것: created_at·view_count와 PK post_id 포함
남는 경계: 커버링 접근이 가능해도 MVCC 확인에 원본 접근이 필요할 수 있음
동률까지 정렬: 후보 인덱스에서 얻는 것: 작성자 안에서 created_at DESC 순서
남는 경계: 뒤의 view_count와 PK ASC는 post_id DESC 요구와 다름

후보 정의는 (author_id, created_at DESC, view_count)입니다. LIMIT 20은 반환 수이며, 파일 정렬의 입력도 20행이라는 뜻은 아닙니다.

커버링 인덱스 구성

최근 20건 화면에 필요한 컬럼만 선택하고 (author_id, created_at DESC, view_count)를 후보로 둡니다.

post_id는 보조 인덱스 리프의 PK 접미 키로 접근할 수 있지만 실행 계획에서 커버링인지 확인합니다.

최근 게시글 목록 전용 커버링 후보

CREATE INDEX ix_post_search_recent_cover
  ON post_search_demo (
    author_id,
    created_at DESC,
    view_count
  );

EXPLAIN ANALYZE
SELECT post_id, created_at, view_count
FROM post_search_demo
WHERE author_id = 42
ORDER BY created_at DESC, post_id DESC
LIMIT 20;

이 후보는 세 투영 값을 포함하지만 시각 다음에 view_count가 오므로 동률의 post_id DESC 정렬을 완성하지 않습니다. 별도 정렬이 필요하면 상위 20개를 고르기 위해 작성자 후보 전체를 읽을 수 있습니다.

정렬까지 충족할 때의 목표 계획 예시

다음은 비교할 목표를 적은 설명이며 바로 위 DDL에서 얻은 로그가 아닙니다. 특히 early stop과 table row lookup: absent는 현재 후보의 보장이 아닙니다.

chosen key: ix_post_search_recent_cover
access: covering index lookup/range
actual output rows: 20
early stop: LIMIT 20
table row lookup: absent
Extra: Using index

정렬까지 맞추려면 created_at DESC 바로 뒤에 post_id DESC를 명시하고 투영용 view_count를 뒤에 두는 후보와 비교할 수 있습니다. LIMIT 기반 상위 N개 정렬이 메모리를 줄여도 입력 후보 수가 20개로 줄어드는 것은 아닙니다.

Using index가 보이지 않는다고 무조건 오류도 아닙니다.

원본 조회 20회가 충분히 작다면 제목을 인덱스에 넣지 않는 선택이 더 좋습니다.

목표는 특정 표식을 얻는 것이 아니라 전체 비용과 운영 부담을 줄이는 것입니다.

힌트 없이 새 계획을 검증하기

힌트 없는 측정 — 옵티마이저가 새 인덱스를 자발적으로 고르는지 확인합니다.
투영 A/B — 세 컬럼 조회와 SELECT *의 계획·바이트·조회 반복을 비교합니다.
LIMIT A/B — 20건과 전체 목록에서 커버링 이득이 어떻게 달라지는지 측정합니다.
인덱스 제거 판단 — 기존 인덱스가 새 복합 인덱스의 왼쪽 접두어로 완전히 대체되는지 점검합니다.

JSON 실행 계획 읽기

트리는 흐름을 읽기 좋고 JSON은 used_key_parts, used_columns, rows_examined_per_scan 같은 구조화 정보를 비교하기 좋습니다.

버전별 필드 차이를 고려해 필요한 항목만 기록합니다.

넓은 조회와 좁은 조회 계획 나란히 수집

EXPLAIN FORMAT=JSON
SELECT *
FROM post_search_demo
WHERE author_id = 42
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN FORMAT=JSON
SELECT post_id, created_at, view_count
FROM post_search_demo
WHERE author_id = 42
ORDER BY created_at DESC
LIMIT 20;

SHOW INDEX FROM post_search_demo;

두 계획에서 선택된 키, 사용된 열, 검사한 행 수, 파일 정렬 여부를 표로 옮깁니다.

SHOW 인덱스의 Seq_in_index와 Cardinality는 설계 단서이지 정확한 고유값 수가 아니므로 실제 건수와 구분합니다.

통계가 비용 추정을 빗나가는 경우

옵티마이저는 통계 기반으로 비용을 추정합니다.

상관된 컬럼을 독립적으로 추정하면 오차가 커질 수 있고, 데이터가 급격히 바뀐 직후 통계가 낡을 수 있습니다.

힌트를 영구 해결책으로 붙이기 전에 통계와 분포를 확인합니다.

Invisible 인덱스를 사용하면 인덱스를 바로 삭제하지 않고 옵티마이저 후보에서 숨겨 영향도를 관찰할 수 있습니다.

다만 외래 키 제약에 필요한 인덱스와 운영 도구 지원을 확인해야 합니다.

검사한 행 수와 쓰기 지연을 함께 보기

성능 회귀 관찰에는 SQL 다이제스트, 검사한 행 수, 전송한 행 수, p95 지연 시간을 함께 씁니다.

평균 시간만 보면 일부 고빈도 작성자의 나쁜 계획을 놓칠 수 있습니다.

커버링 인덱스 추가 후 버퍼 풀 적중이 좋아졌는지, 쓰기 지연과 복제본 지연이 나빠졌는지 양방향으로 확인합니다.

원본 조회와 넓은 리프의 교환

좁은 투영: API 필드를 명확히 관리해 I/O와 전송량을 줄입니다. 쿼리별 SELECT 목록은 유지해야 합니다.
커버링: 빈번한 좁은 읽기의 원본 접근을 줄이는 대신 인덱스 폭과 쓰기 비용이 늘어납니다.
원본 조회: 작은 인덱스를 유지할 수 있지만 후보마다 추가 접근이 생깁니다. 후보가 매우 적을 때 비교합니다.
인덱스 힌트: 원인을 파악한 긴급 우회에 쓸 수 있으나 데이터 변화에 취약한 결합을 만듭니다.

선택도·폭·LIMIT을 바꾸는 실험

분포 왜곡 — 작성자 42가 1%, 40%, 90%를 차지하도록 바꾸고 계획 선택을 기록합니다.
폭 실험 — 제목을 커버링 인덱스 끝에 추가해 index_length와 DML 시간을 비교합니다.
비가시 실험 — 중복 인덱스를 INVISIBLE로 바꾸고 대표 쿼리 회귀를 확인합니다.
결과 크기 — LIMIT 20과 2,000에서 실제 반복 횟수·경과가 어떻게 달라지는지 측정합니다.

긴 문자열·접두사·파라미터 편향

긴 VARCHAR 전체를 커버링하면 인덱스 키 길이 제한과 정렬 규칙 바이트 수를 확인해야 합니다.
접두사 인덱스는 문자열 일부만 저장하므로 선택에는 쓸 수 있어도 원문 투영을 완전히 포함하지 못할 수 있습니다.
COUNT(*)는 InnoDB에서 특정 보조 인덱스를 읽을 수 있지만 정확한 전체 건수를 상시 즉시 반환하는 메타데이터가 아닙니다.
준비된 구문 파라미터 분포가 크게 다르면 하나의 대표 계획이 모든 호출에 최적이지 않을 수 있습니다.

다음 문서에서는 여러 복합 인덱스 후보를 컬럼 순서와 워크로드 전체 비용으로 줄입니다.

물리 모델링에서는 이 읽기 모델을 테이블 정의서의 인덱스 근거로 기록합니다.

커버링 인덱스 도입 기준

판단 축	확인할 질문
투영	화면이 필요한 컬럼이 적고 안정적인가?
조회	원본 행 접근 반복이 실제 병목인가?
빈도	인덱스 유지비를 감수할 만큼 자주 실행되는가?
폭	추가 컬럼이 리프와 캐시를 지나치게 넓히지 않는가?
대체	기존 인덱스를 통합하거나 비가시로 검증했는가?

커버링은 SELECT *를 빠르게 만드는 전략이 아니라 읽기 모델을 좁혀 얻는 최적화입니다.

API 규칙이 불분명하면 먼저 투영부터 고칩니다.

연습 문제

작성자별 최근 공개 게시글 카드가 author_id, created_at, view_count, post_id만 사용합니다.

필터는 author_id와 상태, 정렬은 created_at DESC입니다.

인덱스 후보를 만들고 상태 위치와 커버링 범위를 설명하세요.

해설과 예시 답안

동등 조건 author_id·상태를 앞에 두고 정렬 created_at을 이어 둡니다.

view_count를 끝에 포함하면 좁은 카드 조회를 포함할 수 있습니다.

이 후보는 투영을 커버하지만 view_count가 시각과 PK 사이에 있으므로 아래 post_id DESC 동률 정렬까지 충족하지는 않습니다. 정렬까지 맞추려면 시각 다음의 명시적 post_id DESC 후보와 비교합니다.

CREATE INDEX ix_post_search_author_status_recent_cover
  ON post_search_demo (
    author_id,
    status,
    created_at DESC,
    view_count
  );

EXPLAIN ANALYZE
SELECT post_id, created_at, view_count
FROM post_search_demo
WHERE author_id = 42
  AND status = 'PUBLISHED'
ORDER BY created_at DESC, post_id DESC
LIMIT 20;

Using 인덱스 여부만 보지 말고 원본 조회 노드, 실제 행 수, 정렬 크기, 기존 인덱스 중복까지 확인합니다.

핵심 정리

옵티마이저는 인덱스 탐색과 원본 행 접근의 합산 비용을 비교합니다.
Using 인덱스와 Using 인덱스 조건은 다른 신호입니다.
커버링은 좁고 빈번한 읽기 모델에서 가치가 큽니다.
힌트보다 통계·분포·실제 반복 횟수를 먼저 확인합니다.

다음 문서에서는 복합 인덱스의 왼쪽 접두어, 범위 이후 컬럼, 정렬, 쓰기 비용을 한 워크로드에서 판단합니다.

이 페이지의 목차