복합 인덱스 설계

복합 인덱스의 열 순서는 WHERE에 적은 순서가 아니라, B+Tree가 정렬되는 계층을 정합니다. (A, B, C)는 A 안에서 B로, 같은 A와 B 안에서 C로 정렬됩니다. 그래서 A를 모르면 B의 한 구간을 곧바로 찾을 수 없고, 범위가 시작되면 그 뒤의 열로는 연속된 구간 하나를 더 좁히기 어렵습니다.

회원 게시판에서 사용자 화면은 작성자와 상태, 시각으로 글을 찾고, 관리자 화면은 상태와 시각으로 찾습니다. 인덱스 하나로 두 화면을 모두 최적화하려고 앞쪽 열을 타협하면 어느 쪽도 만족하지 못할 수 있으므로, 조회의 빈도와 쓰기의 비율까지 포함한 작업량(workload)으로 결정합니다.

두 화면의 작업량

사용자 화면에서는 author_id와 상태가 동등 조건이고 created_at이 범위이자 정렬입니다. 관리자 화면은 상태와 created_at만 씁니다. 두 SQL을 같은 무게로 다루지 않고, 각각의 호출량과, 그 화면의 응답이 넘지 않아야 할 시간의 목표인 지연 예산(latency budget)을 먼저 적습니다. 후보 인덱스는 (status, created_at, author_id)와 (author_id, status, created_at)입니다.

이 글도 7장 1편의 post_search_demo를 씁니다. 그 데이터는 1월의 것이므로 아래의 7월 쿼리는 0행을 돌려주며, 이 글에 나오는 행의 수는 서비스 규모를 가정해 설명하기 위한 값입니다. 실제의 계획은 직접 실행해 확인합니다.

범위 열을 앞에 둔 인덱스

created_at은 값이 다양하니 앞에 두어야 한다고 생각해, author_id를 범위 뒤에 둔 인덱스를 만들어 봅니다. 아래 인덱스의 열 순서는 created_at, author_id, status이고, 이어지는 쿼리는 작성자와 상태를 =로, 작성 시각을 7월 한 달의 범위로 거르는 사용자 화면의 조회입니다. 읽을 때는 인덱스의 첫 열이 쿼리에서 범위 조건으로 쓰인다는 점을 보면 됩니다.

CREATE INDEX ix_bad_post_created_author_status
  ON post_search_demo (
    created_at,
    author_id,
    status
  );

EXPLAIN ANALYZE
SELECT post_id, created_at, view_count
FROM post_search_demo
WHERE author_id = 42
  AND status = 'PUBLISHED'
  AND created_at >= '2026-07-01'
  AND created_at <  '2026-08-01'
ORDER BY created_at DESC;

옵티마이저가 이 인덱스를 고르면, 7월이라는 범위 안의 모든 작성자를 읽으며 작성자와 상태를 범위 안의 필터로 평가합니다. 서비스 전체의 7월 글이 6400개이고 그 가운데 작성자 42의 공개 글이 64개라면, 인덱스에서 6400개를 읽어 64개만 쓰는 셈이어서 쓸모 있는 비율이 1%입니다. 작성자 42의 조건은 앞쪽의 동등 조건으로 쓰이지 못하고, 사용자가 늘수록 읽는 양도 늘어납니다. 7장 1편의 작성자로 시작하는 인덱스도 남아 있으므로, 힌트 없는 이 쿼리가 새 인덱스를 고른다고 단정하지는 않습니다.

이것은 "값이 다양한 열을 앞에"라는 규칙 하나만 적용한 결과입니다. 인덱스의 목적은 열이 값을 잘 구별하는 데 있지 않고, 대표 쿼리의 후보를 빨리 줄이는 데 있습니다. 사용자 화면에서는 author_id의 동등 조건이 가장 먼저 전체를 개인의 범위로 나눕니다.

SQL 문장에서 작성자의 조건을 앞에 쓴다고 인덱스의 물리적 순서가 바뀌지는 않습니다. 옵티마이저는 조건의 순서를 바꿀 수 있어도, 인덱스에 없는 왼쪽 접두어를 만들 수는 없습니다.

복합 인덱스의 열 순서

인덱스를 읽을 때는 앞쪽 열부터 이어서 어떤 조건을 쓸 수 있는지, 그리고 BETWEEN이나 >, 접두 LIKE 같은 범위가 처음 나오는 열이 어디인지를 표시합니다. 읽은 행 수와 돌려준 행 수를 비교하면 범위 안에서 거르는 비용이 보이고, 한 쿼리의 최적화가 다른 화면의 쿼리를 어떻게 바꾸는지는 따로 잽니다.

일반적인 출발점은 자주 함께 쓰는 동등 조건을 앞에 두고, 범위 조건과 ORDER BY의 열을 그 뒤에 두는 것입니다. 동등 조건끼리의 순서는 작업량과 다시 쓸 수 있는 왼쪽 접두어, 값의 분포를 함께 보고 정하며, "카디널리티가 높은 열부터"라는 규칙만으로는 충분하지 않습니다. 이 규칙의 카디널리티는 6장 5편의 튜플 수가 아니라, 앞 글의 SHOW INDEX에서 본 열의 서로 다른 값의 수입니다.

(author_id, status, created_at)는 작성자만의 조건, 작성자와 상태의 조건, 작성자와 상태에 시각의 범위를 더한 조건을 지원합니다. 그러나 상태만 묻는 관리자 화면은 이 인덱스의 첫 열을 건너뛰어야 하므로, 별도의 (status, created_at)이 필요할 수 있습니다.

조회를 유형별로 묶으면 정리가 쉽습니다. 사용자의 최근 목록과 관리자의 상태별 목록을 서로 다른 유형으로 나누고, 분당 호출 수와 p95 지연 예산으로 어느 유형이 핵심인지 정합니다. 유형마다 그것을 만족하는 가장 좁은 후보부터 시작하고, 새 복합 인덱스가 기존 단일 인덱스의 앞부분을 대신하는지 확인합니다.

더 깊이: 내림차순 인덱스와 섞인 정렬 방향

열 순서와 함께 정할 것이 각 열의 정렬 방향입니다. MySQL 8.0 이전에는 인덱스 정의의 DESC를 받아들이기만 하고 무시했으므로, 내림차순 정렬은 오름차순 인덱스를 거꾸로 읽어야 했고 그만큼 비용이 들었습니다. 지금의 MySQL은 DESC로 정의한 열의 키를 실제로 내림차순으로 저장하므로, 내림차순 인덱스를 앞 방향으로 읽는 편이 더 효율적입니다. 그래서 7장 1편의 인덱스처럼 created_at DESC를 적어 두면 최신순 목록을 인덱스의 앞에서부터 읽을 수 있습니다.

더 큰 차이는 방향이 섞인 정렬에서 생깁니다. 인덱스를 통째로 거꾸로 읽으면 모든 열의 방향이 함께 뒤집히므로, (c1 ASC, c2 ASC) 인덱스로는 ORDER BY c1 ASC, c2 DESC를 정렬 없이 처리할 수 없습니다. 이때 (c1 ASC, c2 DESC) 인덱스가 있으면 옵티마이저가 앞 방향으로 읽어 파일 정렬을 피할 수 있습니다. 옵티마이저가 인덱스를 거꾸로 읽는 계획을 고르면 전통적인 EXPLAIN의 Extra에 Backward index scan이, 트리 형식에는 (reverse)가 붙습니다.

MySQL 8.4 매뉴얼에 따르면 내림차순 인덱스는 InnoDB의 B-tree 인덱스에서만 쓸 수 있고, HASH, FULLTEXT, SPATIAL 인덱스에는 쓸 수 없습니다. 내림차순 키 열이 있는 보조 인덱스는 변경 버퍼링(change buffering)을 쓰지 않습니다. 또 GROUP BY 없이 MIN()이나 MAX()를 구하는 쿼리의 최적화에는 내림차순 키 부분이 있는 인덱스가 쓰이지 않습니다.

조회 유형별 최소 인덱스

사용자 화면에는 작성자와 상태의 동등 조건 뒤에 시각을 두고, 관리자 화면이 실제의 서비스 수준 합의(Service Level Agreement, SLA)를 가진 경우에만 상태와 시각의 인덱스를 따로 둡니다. 모든 순열을 무작정 만들지는 않습니다.

사용자 화면에 필요한 (author_id, status, created_at DESC)는 7장 1편에서 이미 ix_post_search_author_status_created로 만들었습니다. 같은 정의를 새 이름으로 또 만들면 MySQL은 중복 인덱스라는 경고만 남기고 같은 구조를 하나 더 유지하므로, 기존 인덱스를 그대로 씁니다. 범위 열을 앞에 둔 인덱스는 지우고, 관리자용 인덱스만 더합니다.

DROP INDEX ix_bad_post_created_author_status
  ON post_search_demo;

CREATE INDEX ix_post_admin_status_created
  ON post_search_demo (
    status,
    created_at DESC
  );

EXPLAIN ANALYZE
SELECT post_id, created_at, view_count
FROM post_search_demo
WHERE author_id = 42
  AND status = 'PUBLISHED'
  AND created_at >= '2026-07-01'
  AND created_at <  '2026-08-01'
ORDER BY created_at DESC;

사용자의 쿼리가 ix_post_search_author_status_created를 고르면, 작성자와 상태가 같은 구간 안에서 7월의 시각 범위만 탐색합니다. 앞의 가정대로라면 64개를 읽어 64개를 돌려주는 셈입니다. 이 데이터에는 그 범위의 행이 없어 결과는 0행이고, 관리자의 쿼리는 상태로 시작하는 새 인덱스의 범위를 쓸 수 있습니다.

두 인덱스는 created_at과 상태를 겹쳐 저장하므로 쓰기와 공간의 비용이 생깁니다. 관리자 화면이 하루에 몇 번만 실행된다면, 사용자용 인덱스만 두고 관리자 쿼리의 스캔을 허용하거나, 미리 집계하거나 사용량이 적은 시간에 내보내는 방식으로 바꿀 수 있습니다. 복합 인덱스가 있다면 (author_id)만 담은 단일 인덱스는 대체될 수 있지만, 외래 키와 다른 정렬, 인덱스의 크기를 확인하고 INVISIBLE로 바꾸어 지켜본 뒤에 지웁니다. 이름이 비슷하다고 바로 지우지 않습니다.

읽기의 이득과 쓰기의 비용은 한 번에 맞춰 봅니다. 사용자와 관리자의 쿼리를 같은 데이터에서 각각 EXPLAIN ANALYZE로 보고, 1천 건의 INSERT와 상태의 UPDATE 전후로 지연 시간과 리두 로그, 인덱스의 크기를 비교합니다. 대체할 수 있는 인덱스를 INVISIBLE로 두고 쿼리 다이제스트가 느려지지 않는지 지켜보며, 남긴 인덱스마다 대표 쿼리와 지울 조건을 테이블 정의서에 적어 둡니다.

읽기와 쓰기의 비용을 비교한다

인덱스의 선택은 쿼리 하나의 계획 화면으로 끝나지 않습니다. information_schema의 테이블 크기와 대표 DML의 처리량, 느린 쿼리 로그에 기록된 검사 행 수를 배포 전후로 비교합니다.

SHOW INDEX FROM post_search_demo;

SELECT
  table_name,
  table_rows,
  data_length,
  index_length
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name = 'post_search_demo';

EXPLAIN FORMAT=TREE
SELECT post_id
FROM post_search_demo
WHERE status = 'DRAFT'
  AND created_at >= CURRENT_TIMESTAMP - INTERVAL 1 DAY
ORDER BY created_at DESC
LIMIT 100;

SHOW INDEX에서는 앞쪽 열의 순서가 같은 인덱스가 겹쳐 있지 않은지 찾습니다. 지금까지의 글을 따라왔다면 이 테이블에는 7장 2편과 3편에서 실험하며 만든 인덱스도 남아 있으므로, 이번 결정에 쓰지 않는 것이 무엇인지 함께 정리해 봅니다. table_rows와 index_length는 통계에 기반한 근사치이므로 배포 전후의 차이로 기록하고, DML 성능의 측정과 실제 디스크 사용량을 함께 봅니다. 마지막 관리자 쿼리는 지금 시각에서 하루 전까지를 찾으므로, 1월의 데이터에서는 0행입니다.

ICP와 건너뛰기 스캔을 과신하지 않는다

범위 뒤의 열도 인덱스 조건 푸시다운으로 리프에서 걸러질 수 있으므로 쓸모가 없지는 않습니다. 그러나 탐색할 구간 자체를 작성자별로 나누지는 못하므로, 읽는 리프의 수는 줄지 않을 수 있습니다. 탐색의 범위와 범위 안의 필터를 구별합니다.

MySQL 8.0.13부터는 앞쪽 열을 건너뛰고 뒤의 열로 범위를 찾는 건너뛰기 스캔(skip scan)도 있어, 계획의 Extra에 Using index for skip scan으로 나타납니다. 하지만 이 방식은 앞쪽 열의 서로 다른 값이 적을 때만 유리하고 조건도 까다로우므로, 핵심 쿼리의 설계를 이런 우연한 최적화에 맡기지 말고 계획으로 확인합니다.

예외와 운영에서 따질 것

ORDER BY의 방향이 섞여 있다면 인덱스 정의의 ASC와 DESC 조합이 정확히 맞는지 확인합니다. 카디널리티가 낮은 상태 열도 작성자의 동등 범위 안에서는 구간을 쓸모 있게 나눌 수 있습니다. 여러 고객의 데이터를 한 테이블에 두는 시스템이라면 tenant_id가 모든 인덱스의 앞에 와야 하는 보안과 분할의 기준인지 따로 판단하고, 인덱스를 지우기 전에는 외래 키가 쓸 다른 인덱스가 있는지 확인합니다. 그렇지 않으면 DDL이 거부되거나 제약을 지원할 인덱스를 잃습니다.

인덱스가 늘면 INSERT뿐 아니라 상태나 created_at을 바꾸는 UPDATE도 관련된 모든 B+Tree를 고칩니다. 자주 바뀌는 열을 여러 인덱스에 겹쳐 넣을 때는 잠금과 리두 로그, 복제의 비용을 지켜봅니다. 쓰이지 않는 인덱스의 통계는 관찰한 기간과 작업량의 계절성을 고려해 읽어야 하며, 월말의 관리자 쿼리처럼 드물지만 중요한 작업을 일주일의 지표만 보고 지우지 않습니다.

검증은 순열이 아니라 가설로 합니다. 세 열의 모든 순열을 만들지 말고 두 후보만 세워 계획을 비교하고, 상태의 범위를 IN의 동등 목록으로 바꾸었을 때 범위의 수와 정렬이 어떻게 변하는지 봅니다. 결과가 작을 때의 따로 정렬하는 비용과 넓은 인덱스를 더하는 비용을 숫자로 비교하고, 지울 후보를 INVISIBLE로 숨긴 뒤 주요 다이제스트의 키와 지연이 어떻게 바뀌는지 지켜봅니다.

인덱스 장의 결론은 많이 만드는 것이 아니라, 쿼리 유형마다 최소한의 접근 경로와 그 판단의 기록을 남기는 것입니다. 다음 장의 무결성 제약도 인덱스를 만들 수 있으므로, 성능용 인덱스와 겹치지 않는지 함께 관리합니다.

복합 인덱스는 열의 조합 문제가 아니라 전체 작업량을 고려한 결정입니다. 읽기 하나를 최적화한 뒤에는 전체의 쓰기와 다른 쿼리가 느려지지 않았는지 반드시 다시 봅니다.

정리

• 복합 인덱스는 왼쪽 열부터 계층적으로 정렬되며, 왼쪽 접두어가 없는 조건은 범위를 만들지 못합니다.
• 동등 조건을 앞에, 첫 범위와 정렬의 열을 그 뒤에 두는 것이 일반적인 출발점입니다.
• 범위 뒤의 열은 범위를 나누지 못하고 범위 안의 필터로만 쓰일 수 있습니다.
• 쿼리 유형마다 인덱스가 필요하다고 가정하지 말고, 호출량과 SLA로 최소한의 구성을 고릅니다.
• 겹치는 인덱스는 INVISIBLE로 확인하고 DML의 비용을 잰 뒤에 지웁니다.

연습 문제

1. 세 쿼리의 최소 인덱스

세 쿼리가 있습니다. A는 작성자와 상태, 기간으로 찾고, B는 작성자의 글을 상태와 관계없이 최신순으로 보여 주며, C는 상태와 기간으로 찾는 관리자 목록입니다. 호출량은 A가 분당 1,000회, B가 분당 100회, C가 하루 2회입니다. 최소한의 인덱스 구성과 C의 처리 방법을 제안해 보세요.

먼저 B의 정렬을 작은 사례로 확인해 봅니다. 작성자 42에게 10:00의 DRAFT 글과 11:00의 PUBLISHED 글이 하나씩 있고, 상태의 키 순서는 DRAFT가 PUBLISHED보다 앞입니다. (author_id, status, created_at DESC)의 작성자 42 구간을 앞에서부터 읽으면 어느 글이 먼저 나오나요?

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

2. 왼쪽 접두어로 쓸 수 있는 조건

인덱스 (author_id, status, created_at DESC)가 있을 때, 다음 네 조건은 각각 인덱스의 범위를 어디까지 쓸 수 있나요?

• (가) author_id = 42
• (나) status = 'DRAFT'
• (다) author_id = 42 AND created_at >= '2026-01-02'
• (라) author_id = 42 AND status IN ('DRAFT', 'PUBLISHED') AND created_at >= '2026-01-02'

7장의 실습을 마친 뒤 누적 스키마와 떨어진 실습 테이블만 정리하려면 다음 문장을 실행합니다. 실습을 다시 시작할 때는 7장 1편의 생성 블록부터 실행합니다.

DROP TABLE IF EXISTS post_search_demo;

다음 글 무결성 제약 조건에서는 빠른 조회보다 먼저 지켜야 할 데이터의 무결성과, 여러 변경을 한 단위로 묶는 트랜잭션으로 넘어갑니다.