실행 계획과 커버링 인덱스

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

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

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

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

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

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

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

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

반면 LIMIT 20과 좁은 투영이 결합되면 정렬된 커버링 인덱스가 적은 리프만 읽고 끝낼 수 있습니다.

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

계획은 지정한 보조 인덱스를 읽지만 각 후보의 제목·상태 등 전체 행을 얻으려고 클러스터형 인덱스를 반복 조회합니다.

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 *는 읽기 모델 규칙을 스키마 전체와 결합합니다.

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

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

1. 강제 힌트 제거 — 힌트 없는 계획이 어떤 대안을 고르는지 기준선으로 삼습니다.
2. 실제 반복 횟수 — 가장 안쪽 원본 행 조회가 몇 번 반복되는지 읽습니다.
3. 투영 축소 — 화면이 실제 사용하는 컬럼만 SELECT 목록에 둡니다.
4. 종료 조건 — LIMIT과 안정적 정렬을 넣어 필요한 리프에서 조기 종료할 수 있게 합니다.

옵티마이저 비용 모델

보조 인덱스가 쿼리의 필터·정렬·투영 컬럼을 모두 포함하면 리프만으로 결과를 만들 수 있습니다.

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

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

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

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

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

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

커버링 인덱스 구성

최근 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;

작성자 범위의 첫 리프에서 최신순으로 20개를 읽고 원본 제목 행에 접근하지 않고 결과를 만들 수 있습니다.

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

post_id DESC까지 완전히 정렬에 맞추려면 인덱스의 PK 접미 키 방향과 실제 파일 정렬 유무를 확인합니다.

Sort가 매우 작은 20행에만 적용된다면 별도 컬럼을 추가해 리프를 넓히는 것보다 쌀 수 있습니다.

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

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

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

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

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

JSON 실행 계획 읽기

트리는 흐름을 읽기 좋고 JSON은 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 지연 시간을 함께 씁니다.

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

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

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

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

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

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

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

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

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

커버링 인덱스 도입 기준

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

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

연습 문제

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

필터는 author_id와 상태, 정렬은 created_at 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 인덱스 조건은 다른 신호입니다.
• 커버링은 좁고 빈번한 읽기 모델에서 가치가 큽니다.
• 힌트보다 통계·분포·실제 반복 횟수를 먼저 확인합니다.

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