실행 계획과 커버링 인덱스

인덱스가 있어도 옵티마이저는 테이블 스캔이 더 싸다고 판단할 수 있습니다. 인덱스의 리프에서 후보를 찾은 뒤 클러스터형 인덱스의 원래 행에 여러 번 흩어져 접근해야 하기 때문입니다. 원하는 인덱스를 억지로 강제하기 전에, 추정치와 실제의 값을 먼저 읽어야 합니다.

이 글의 요구는 한 작성자의 최근 게시글 목록에서 post_id와 created_at, view_count만 보여 주는 좁은 읽기 모델입니다. 화면에 필요하지 않은 열까지 SELECT *로 가져오는 오류에서 시작해, 커버링 인덱스로 얻는 것과 인덱스가 넓어져 생기는 비용을 함께 계산합니다.

7장 1편에서 만든 post_search_demo는 정확히 1만 행이고, 그 가운데 author_id = 42가 4천 행, 그중 공개 글이 3천 행입니다. 같은 작성자가 전체의 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;

total_rows | author_42_rows | author_42_published_rows
10000      | 4000           | 3000

값이 다르면 7장 1편의 데이터를 다시 만든 뒤 시작합니다. 서로 다른 데이터에서 얻은 실행 시간을 인덱스 전후의 차이로 해석하지 않습니다.

SELECT *와 인덱스의 강제

응답에 필요하지 않은 모든 열을 가져오면서, FORCE INDEX를 붙여 인덱스를 썼다는 사실만 확인해 봅니다. 아래 쿼리는 SELECT *로 모든 열을 고르고, FORCE INDEX로 7장 2편에서 만든 (author_id, created_at, title) 인덱스만 쓰도록 옵티마이저에 지시합니다. 끝의 주석은 이 결과를 받는 화면이 실제로 쓰는 열이 세 개뿐이라는 가정입니다.

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

강제한 인덱스 (author_id, created_at, title)에는 제목까지 들어 있지만, 상태와 조회수 같은 나머지 열을 얻으려면 행마다 클러스터형 인덱스에 다시 접근해야 합니다. 이 쿼리에는 LIMIT도 없으므로, 작성자 42의 4000개 항목을 모두 읽고 원래 행도 약 4000번 찾게 됩니다. 그러나 응답이 실제로 쓰는 열은 세 개뿐입니다. 실제 노드와 반복 횟수는 EXPLAIN ANALYZE의 출력에서 확인하며, 그 출력은 환경마다 달라 교재에 옮기지 않습니다.

FORCE INDEX는 문법 오류를 내지 않지만 옵티마이저의 다른 선택지를 없앱니다. 출력에서 인덱스의 이름만 보고 성공이라고 하면 반복 횟수와 조회의 비용을 놓치고, 업무가 전체 4천 건인지 최근 20건인지도 쿼리에 드러나지 않습니다. SELECT *는 읽기 모델의 형식을 스키마 전체와 묶어, 커버링을 쓸 수 없게 하고 네트워크로 보내는 양과 직렬화의 비용까지 키우므로 성능과 유지보수 양쪽에서 오류입니다.

먼저 힌트를 빼고 옵티마이저가 어떤 계획을 고르는지 기준으로 삼습니다. 가장 안쪽에서 원래 행을 찾는 조회가 몇 번 반복되는지 읽고, SELECT 목록에는 화면이 실제로 쓰는 열만 둡니다. 마지막으로 LIMIT과 안정적인 정렬을 넣고, 인덱스가 그 순서까지 만족해 일찍 멈출 수 있는지 확인합니다.

커버링과 옵티마이저의 비용

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

전통적인 EXPLAIN의 Extra에 나오는 Using index는 대개 커버링 접근을 뜻하고, Using index condition은 7장 2편의 인덱스 조건 푸시다운으로 둘은 다른 신호입니다. 트리 형식에서는 커버링 접근이 Covering index lookup이나 Covering index scan 같은 이름의 노드로 나타납니다.

커버링 인덱스는 데이터를 따로 복제하는 것이 아니라, 리프 항목에 열을 더 넣는 설계입니다. 항목이 넓어지면 한 페이지에 들어가는 항목이 줄고 B+Tree가 커져, 캐시의 효율과 쓰기의 비용이 나빠집니다. 그래서 핵심 화면의 좁은 투영에만 적용합니다.

화면의 투영에서 리프의 구성을 거꾸로 계산해 봅니다. 목록의 형식을 post_id, created_at, view_count의 세 값으로 고정하고, author_id의 동등 조건 뒤에 오는 시각과 같은 값을 가르는 키가 요구한 순서와 맞는지 봅니다. InnoDB의 보조 인덱스 리프에는 기본 키가 이미 들어 있다는 점을 고려하고, view_count를 더해 늘어나는 항목의 크기와 조회의 빈도를 비교합니다.

커버링 인덱스를 구성한다

최근 20건 화면에 필요한 열만 고르고, 먼저 (author_id, created_at DESC, view_count)를 후보로 둡니다. post_id는 보조 인덱스 리프에 붙은 기본 키로 얻을 수 있지만, 커버링인지는 실행 계획으로 확인합니다.

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;

이 후보는 세 열을 모두 담지만, 리프의 순서는 (author_id, created_at DESC, view_count, post_id)입니다. 시각 다음에 view_count가 오므로 같은 시각에서 post_id DESC의 순서를 맞추지 못하고, 그래서 따로 정렬하는 단계가 남습니다. 정렬해야 하면 상위 20개를 고르기 위해 작성자 42의 후보 4000개를 모두 읽을 수 있으며, LIMIT으로 상위 N개만 남기는 정렬이 메모리를 줄여 주어도 입력이 20개로 줄지는 않습니다.

정렬까지 맞추려면 시각 바로 뒤에 post_id DESC를 적고, 투영에만 쓰는 view_count를 그 뒤에 둡니다. InnoDB는 이렇게 기본 키를 직접 적은 보조 인덱스에는 같은 열을 다시 붙이지 않습니다.

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

EXPLAIN FORMAT=TREE
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;

옵티마이저가 이 인덱스를 고르면, 계획은 정렬 노드 없이 Limit 아래에 이 인덱스의 커버링 조회 노드 하나만 있는 모양이 됩니다. 리프를 순서대로 20개 읽은 뒤 멈추고, 전통적인 EXPLAIN의 Extra에는 Using filesort 없이 Using index가 나옵니다. 다만 이 테이블에는 이제 비슷한 인덱스가 여럿 있으므로, 어느 것을 고르는지는 통계와 비용에 달려 있습니다.

Using index가 보이지 않는다고 무조건 잘못된 것은 아닙니다. 원래 행의 조회가 20번으로 충분히 작다면, 제목을 인덱스에 넣지 않는 선택이 더 나을 수 있습니다. 목표는 특정한 표시를 얻는 것이 아니라, 전체의 비용과 운영의 부담을 줄이는 것입니다.

새 계획은 힌트 없이 확인합니다. 옵티마이저가 새 인덱스를 스스로 고르는지 보고, 세 열의 조회와 SELECT *의 계획과 보내는 양, 원래 행 조회의 반복을 비교합니다. 20건과 전체 목록에서 커버링의 이득이 어떻게 다른지 재고, 기존 인덱스가 새 복합 인덱스의 왼쪽 접두어로 완전히 대체되는지도 점검합니다.

JSON 형식의 실행 계획

트리 형식은 흐름을 읽기 좋고, JSON 형식은 used_key_parts, used_columns, rows_examined_per_scan 같은 구조화된 정보를 비교하기 좋습니다. MySQL의 버전마다 필드가 조금씩 다르므로, 필요한 항목만 골라 기록합니다.

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 INDEX의 Seq_in_index는 인덱스 안에서 열의 순서이고, Cardinality는 서로 다른 값의 수를 추정한 것일 뿐 정확한 수가 아니므로 실제 건수와 구별합니다.

옵티마이저는 통계를 바탕으로 비용을 추정합니다. 서로 관련된 열을 독립적이라고 보고 추정하면 오차가 커질 수 있고, 데이터가 크게 바뀐 직후에는 통계가 낡아 있을 수 있습니다. 힌트를 영구적인 해결책으로 붙이기 전에 통계와 분포부터 확인하고, 인덱스를 바로 지우는 대신 ALTER TABLE ... ALTER INDEX ... INVISIBLE로 옵티마이저에게서 숨겨 영향을 지켜볼 수 있습니다. 다만 외래 키에 필요한 인덱스인지와 운영 도구가 지원하는지는 확인합니다.

더 깊이: InnoDB의 통계는 언제 어떻게 만들어지는가

옵티마이저가 쓰는 InnoDB의 인덱스 통계는 기본으로 영구 통계(persistent statistics)이고, mysql.innodb_table_stats와 mysql.innodb_index_stats에 저장되어 서버를 다시 시작해도 남습니다. Cardinality 같은 값은 인덱스 페이지 몇 개를 표본으로 읽어 계산하며, 표본의 수는 innodb_stats_persistent_sample_pages(기본값 20)가 정합니다. 표본을 늘리면 추정이 정확해지는 대신 ANALYZE TABLE이 느려지고, 줄이면 그 반대입니다. 테이블마다 STATS_SAMPLE_PAGES 옵션으로 따로 정할 수도 있습니다.

통계를 다시 계산하는 시점도 정해져 있습니다. 기본으로 켜져 있는 innodb_stats_auto_recalc는 테이블 행의 10%보다 많이 바뀌면 통계를 다시 계산하게 하는데, 이 계산은 백그라운드에서 비동기로 이루어져 몇 초 늦어질 수 있습니다. 그래서 대량으로 적재한 직후에 실행 계획을 확인한다면, ANALYZE TABLE로 통계를 바로 다시 계산한 뒤에 보는 편이 정확합니다. ANALYZE TABLE은 분석하는 동안 InnoDB 테이블에 읽기 잠금(read lock)을 겁니다.

인덱스가 없는 열의 분포는 인덱스 통계에 들어가지 않으므로, MySQL은 ANALYZE TABLE ... UPDATE HISTOGRAM ON 열 이름으로 만드는 히스토그램(histogram)을 따로 둡니다. 버킷은 기본 100개이고 1부터 1024까지 정할 수 있으며, 서로 다른 값이 버킷 수 이하이면 값 하나가 버킷 하나인 singleton으로, 더 많으면 값의 범위를 나눈 equi-height로 만들어집니다. 기본값인 MANUAL UPDATE에서는 요청할 때만 만들고 갱신하므로 데이터가 바뀔수록 낡아 가고, AUTO UPDATE를 지정하면 InnoDB가 영구 통계를 다시 계산할 때 함께 갱신합니다(MySQL 8.4의 ANALYZE TABLE). 옵티마이저는 범위 최적화로 행 수를 추정할 수 있으면 히스토그램보다 그 추정을 먼저 씁니다.

운영에서 따질 것

성능의 회귀는 SQL 다이제스트와 검사한 행의 수, 보낸 행의 수, p95 지연을 함께 보고 판단합니다. SQL 다이제스트(statement digest)는 리터럴 값을 ?로 바꾸어 같은 모양의 문장을 하나로 묶은 정규화 형태이고, p95 지연은 요청의 95%가 그 안에 끝나는 응답 시간입니다. 평균 시간만 보면 일부 작성자에게서만 나쁜 계획을 놓칠 수 있습니다. 커버링 인덱스를 더한 뒤에는 버퍼 풀(buffer pool)의 적중률이 좋아졌는지와, 쓰기의 지연과 복제 서버의 지연이 나빠졌는지를 양쪽으로 확인합니다. 버퍼 풀은 InnoDB가 읽은 테이블과 인덱스의 데이터를 메모리에 캐시해 두는 영역이므로, 적중률이 높을수록 디스크를 덜 읽습니다.

실험으로 분포와 폭을 바꾸어 볼 수도 있습니다. 작성자 42가 전체의 1%, 40%, 90%를 차지하도록 데이터를 바꾸어 계획의 선택을 기록하고, 제목을 커버링 인덱스의 끝에 더해 index_length와 DML의 시간을 비교합니다. 겹치는 인덱스를 INVISIBLE로 바꾸어 대표 쿼리가 느려지지 않는지 확인하고, LIMIT을 20과 2000으로 바꾸어 실제 반복 횟수와 시간이 어떻게 달라지는지 잽니다.

긴 VARCHAR 전체를 커버링에 넣으면 인덱스 키의 길이 제한과 정렬 규칙에 따른 바이트 수를 확인해야 합니다. 문자열의 앞부분만 저장하는 접두사 인덱스(prefix index)는 고르는 데는 쓸 수 있어도 원래 값을 완전히 담지 못합니다. InnoDB의 COUNT(*)는 작은 보조 인덱스를 읽어 셀 수는 있어도, 정확한 전체 건수를 언제나 즉시 돌려주는 메타데이터가 있는 것은 아닙니다. 준비된 문장의 파라미터 분포가 크게 다르면, 대표 계획 하나가 모든 호출에 최적이지 않을 수 있습니다.

커버링은 SELECT *를 빠르게 만드는 전략이 아니라, 읽기 모델을 좁혀서 얻는 최적화입니다. API의 형식이 분명하지 않다면 투영부터 고칩니다.

정리

• 옵티마이저는 인덱스를 탐색하는 비용과 원래 행에 접근하는 비용을 합쳐 비교합니다.
• Using index는 대개 커버링 접근을, Using index condition은 인덱스 조건 푸시다운을 뜻하는 다른 신호입니다.
• 필요한 열을 모두 담는 것과 정렬까지 만족하는 것은 다르며, 정렬까지 맞아야 LIMIT에서 일찍 멈춥니다.
• 커버링은 좁고 자주 쓰는 읽기 모델에서 가치가 크고, 인덱스가 넓어지는 비용과 함께 판단합니다.
• 힌트보다 통계와 분포, 실제 반복 횟수를 먼저 확인합니다.

연습 문제

1. 작성자별 최근 공개 글 카드

작성자별 최근 공개 글 카드가 author_id, created_at, view_count, post_id만 씁니다. 필터는 author_id와 상태이고, 정렬은 created_at DESC와 post_id DESC입니다. 인덱스의 후보를 만들고, 상태 열의 위치와 커버링의 범위를 설명해 보세요.

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

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 5;

created_at                 | view_count
2026-01-03 18:39:00.000000 | 13
2026-01-03 18:38:00.000000 | 12
2026-01-03 18:37:00.000000 | 11
2026-01-03 18:35:00.000000 | 120
2026-01-03 18:34:00.000000 | 119

2. 넓은 조회와 좁은 조회의 원래 행 접근

본문의 SELECT * 쿼리에 LIMIT 20을 붙이고 FORCE INDEX를 뺀다면, 원래 행에 접근하는 횟수는 어떻게 될 수 있나요? 세 열만 고르는 쿼리와 비교해 설명해 보세요.

다음 글 복합 인덱스 설계에서는 복합 인덱스의 왼쪽 접두어와 범위 뒤의 열, 정렬, 쓰기의 비용을 한 작업량 안에서 함께 판단합니다.