복합 인덱스 설계

복합 인덱스의 컬럼 순서는 WHERE 절에 적힌 순서가 아니라 B+Tree가 정렬되는 계층을 결정합니다.

(A, B, C)는 A 안에서 B, 같은 A·B 안에서 C 순입니다.

A를 모르면 B의 한 구간을 바로 찾을 수 없고, 범위가 시작되면 뒤 컬럼으로 하나의 연속 구간을 더 좁히기 어렵습니다.

회원 게시판 운영 화면은 작성자·상태·시각으로 찾고, 관리자 화면은 상태·시각으로 찾습니다.

한 인덱스로 두 화면을 모두 최적화하려다 선두 컬럼을 타협하면 어느 쪽도 만족하지 못할 수 있습니다.

조회 빈도와 쓰기 비율을 포함한 워크로드로 결정합니다.

ch7-1의 기본 fixture는 1월 데이터입니다. 그대로라면 아래 7월 쿼리는 0행이며, 6,400→64 같은 계획 숫자는 넓은 후보 대비 작은 결과를 설명하는 가정값입니다. 새로 관측한 성능 결과가 아닙니다.

후보 인덱스는 (status, created_at, author_id)와 (author_id, status, created_at)입니다.

사용자 화면은 author_id와 상태가 동등 조건이고 created_at이 범위·정렬입니다.

관리자 화면은 상태와 created_at만 사용합니다.

두 SQL을 같은 비중으로 취급하지 않고 호출량과 지연 예산을 적습니다.

범위 열 선두 배치의 문제

created_at의 값이 다양하므로 무조건 선두에 두고, author_id는 범위 뒤에 배치합니다.

특정 작성자의 한 달 기록을 찾을 때 먼저 전체 서비스의 한 달 범위를 읽은 뒤 작성자를 필터하므로 사용자가 늘수록 검사량이 커집니다.

범위 컬럼이 앞선 복합 인덱스

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;

이 시각 선두 인덱스를 선택했다면 한 달 범위의 모든 작성자를 읽으며 작성자와 상태를 잔여 필터로 평가할 수 있습니다. 기존 작성자 선두 인덱스도 남아 있으므로 힌트 없는 위 쿼리가 새 후보를 선택한다고 단정하지 않습니다.

범위가 넓은 경우의 가정 예시

index range: created_at in July
rows read from index: 6,400
rows after author/status filter: 64
useful ratio: 1%
author_id key boundary: not used as leading equality

높은 카디널리티라는 단일 규칙만 적용한 결과입니다.

인덱스의 목적은 컬럼 자체를 잘 구별하는 것이 아니라 대표 쿼리의 후보를 빨리 줄이는 것입니다.

사용자 화면에서는 author_id 동등 조건이 가장 먼저 전체를 개인 범위로 나눕니다.

SQL 텍스트에서 작성자 조건을 앞에 써도 인덱스 물리 순서는 바뀌지 않습니다.

옵티마이저는 조건 순서를 재배치할 수 있지만 존재하지 않는 왼쪽 접두어를 만들 수는 없습니다.

왼쪽 접두어와 첫 범위를 표시하기

왼쪽 접두 — 선두 컬럼부터 연속으로 어떤 조건을 사용할 수 있는지 표시합니다.
첫 범위 — BETWEEN, >, LIKE 접두사가 처음 등장하는 컬럼 뒤를 구분합니다.
읽은 행 비율 — 행 수 읽기와 반환한 행 수를 비교해 잔여 필터 비용을 찾습니다.
다른 화면 — 현재 쿼리 하나의 최적화가 관리자 쿼리를 어떻게 바꾸는지 따로 측정합니다.

복합 인덱스 열 순서

일반적인 출발점은 자주 함께 쓰는 동등 조건을 앞에 두고, 범위 조건과 ORDER BY 컬럼을 그 뒤에 두는 것입니다.

동등 조건끼리 순서는 워크로드와 재사용 가능한 왼쪽 접두어, 분포를 함께 봅니다.

무조건 카디널리티가 높은 컬럼부터라는 규칙은 충분하지 않습니다.

(author_id, status, created_at)는 작성자만, 작성자+상태, 작성자+상태+시작 범위를 지원합니다.

상태만 조회하는 관리자 화면은 이 인덱스의 선두를 건너뛰므로 별도 (status, created_at) 후보가 필요할 수 있습니다.

쿼리 유형을 인덱스 후보로 묶기

쿼리 유형 — 사용자 최근 목록과 관리자 상태 목록을 서로 다른 유형으로 묶습니다.
호출량 — 분당 호출 수와 p95 예산으로 어느 유형이 핵심인지 정합니다.
최소 인덱스 — 각 유형을 만족하는 가장 좁은 후보부터 시작합니다.
중복 제거 — 새 복합 인덱스가 기존 단일 인덱스 선두를 대체하는지 검증합니다.

조회 유형별 최소 인덱스

사용자 화면에는 작성자·상태 동등 뒤 시각을 두고, 관리자 화면이 실제 SLA(Service Level Agreement, 서비스 수준 합의)를 가진 경우에만 상태·시각 인덱스를 별도로 둡니다.

무작정 모든 순열을 만들지 않습니다.

아래 사용자용 정의는 ch7-1의 ix_post_search_author_status_created와 같습니다. 원문 후보 DDL은 독립 비교용이며, 누적 실습에서는 같은 정의를 새 이름으로 또 만들지 말고 기존 키를 재사용합니다. 뒤 연습의 ix_post_workload_primary도 같은 정의입니다.

사용자용과 관리자용 접근 경로 분리

DROP INDEX ix_bad_post_created_author_status
  ON post_search_demo;

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

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;

작성자 선두 후보가 선택되면 작성자·상태 동등 범위 안에서 7월 시각 범위를 탐색할 수 있습니다. 기본 fixture에는 그 범위의 행이 없습니다.

관리자 쿼리는 별도 선두 상태 범위를 사용할 수 있습니다.

두 접근 경로의 목표 설명 예시

user query:
  key = ix_post_user_status_created
  equality prefix = author_id, status
  range/order = created_at
  rows read = 64
  rows returned = 64

admin query candidate:
  key = ix_post_admin_status_created

두 인덱스는 created_at과 상태를 중복 저장하므로 쓰기·공간 비용이 생깁니다.

관리자 화면이 하루 몇 번만 실행된다면 사용자용 인덱스만 두고 관리자 쿼리의 스캔을 허용하거나 사전 집계·비동기 내보내기로 바꿀 수 있습니다.

복합 인덱스가 있다면 (author_id) 단일 인덱스는 대체 가능할 수 있지만 외래 키·다른 정렬·인덱스 크기를 검증하고 비가시 전환을 거칩니다.

이름만 비슷하다고 바로 삭제하지 않습니다.

읽기 이득과 DML 비용을 한 번에 대조하기

두 계획 수집 — 사용자·관리자 쿼리를 같은 데이터 스냅샷에서 각각 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_length는 배포 전후 차이로 기록합니다.

통계 값은 근사치이므로 DML 성능 측정과 실제 디스크 사용을 함께 봅니다.

최소 인덱스 집합의 대안

인덱스 하나: 쓰기·공간을 줄이는 대신 보조 화면의 스캔을 허용합니다. 해당 화면의 지연 예산이 느슨할 때 비교합니다.
핵심 쿼리별 두 개: 두 경로를 지원하는 대신 리프와 DML 비용이 늘어납니다. 두 화면 모두 중요할 때 비교합니다.
넓은 범용 후보: 여러 투영을 담을 수 있으나 캐시 밀도와 변경 비용이 나빠질 수 있습니다. 읽기 모델이 안정적이어야 합니다.
사전 집계: 관리자 대량 조회를 분리하되 신선도와 갱신 파이프라인을 관리해야 합니다.

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

범위 뒤 컬럼도 인덱스 조건 푸시다운으로 리프에서 필터될 수 있어 완전히 무용한 것은 아닙니다.

그러나 탐색 구간 자체를 작성자별로 분리하지 못하므로 읽는 리프 수는 줄지 않을 수 있습니다.

탐색 경계와 잔여 필터를 구분합니다.

건너뛰기 스캔 같은 옵티마이저 기능이 선두 컬럼을 건너뛴 접근을 시도할 수 있지만 선두 DISTINCT 수와 비용에 민감합니다.

일반적인 핵심 쿼리 설계를 우연한 최적화에 맡기지 않고 계획으로 확인합니다.

혼합 정렬·테넌트·FK 인덱스 예외

ORDER BY 방향이 혼합되면 인덱스 정의의 ASC/DESC 조합과 정확히 맞는지 확인합니다.
낮은 카디널리티 상태도 작성자 동등 범위 안에서는 유용하게 구간을 나눌 수 있습니다.
테넌트 시스템에서는 tenant_id가 모든 인덱스 선두에 필요한 보안·분할 경계인지 별도 판단합니다.
인덱스 삭제 전 외래 키가 다른 적합한 인덱스를 사용할 수 있는지 검증하지 않으면 DDL이 거절되거나 제약 지원을 잃습니다.

계절성 쿼리와 복제본 비용

인덱스 수가 늘면 INSERT뿐 아니라 상태·created_at UPDATE가 모든 관련 B+Tree를 수정합니다.

변경이 많은 컬럼을 여러 인덱스에 중복할 때 잠금·리두 로그·복제 비용을 관찰합니다.

사용되지 않는 인덱스 통계는 관찰 기간과 워크로드 계절성을 고려합니다.

월말 관리자 쿼리처럼 드문 핵심 작업을 일주일 지표만 보고 삭제하지 않습니다.

순열 대신 가설 두 개만 검증하기

순열 비교 — 세 컬럼의 모든 순열을 만들지 말고 두 후보만 가설로 세워 계획을 비교합니다.
IN 변환 — 상태 범위를 IN의 동등 목록으로 바꾸었을 때 범위 수와 정렬이 어떻게 변하는지 봅니다.
파일 정렬 허용 — 작은 결과의 파일 정렬과 넓은 인덱스 추가 비용을 수치로 비교합니다.
비가시 회귀 — 제거 후보 인덱스를 숨긴 뒤 주요 다이제스트의 키 변화와 지연 시간을 관찰합니다.

인덱스 장의 결론은 ‘많이 만들기’가 아니라 쿼리 유형별 최소 접근 경로와 검증 근거를 남기는 것입니다.

다음 장의 무결성 제약도 인덱스를 만들 수 있으므로 성능용 인덱스와 중복을 함께 관리합니다.

인덱스 조합 선택 기준

판단 축	확인할 질문
유형	각 인덱스에 대표 쿼리 유형이 연결되는가?
접두어	동등 조건과 첫 범위가 컬럼 순서에 맞는가?
대체	기존 단일·복합 인덱스와 중복되지 않는가?
쓰기	DML·공간·복제본 비용을 측정했는가?
수명	비가시 검증과 제거 조건을 기록했는가?

복합 인덱스는 컬럼 조합 문제가 아니라 워크로드 포트폴리오 결정입니다.

읽기 하나를 최적화한 뒤 전체 쓰기와 다른 쿼리 회귀를 반드시 다시 봅니다.

연습 문제

세 쿼리가 있습니다: A는 작성자+상태+기간, B는 작성자만 최신순, C는 상태+기간 관리자 목록입니다.

A 1,000회/분, B 100회/분, C 2회/일입니다.

최소 인덱스 집합과 C 처리 전략을 제안하세요.

해설과 예시 답안

연습 문제의 세 조회와 인덱스 재사용 경계
조회와 주어진 빈도	주요 요구	우선 검토
A · 분당 1,000회	작성자·상태 동등 + 시각 범위	`(author_id, status, created_at DESC)`를 핵심 후보로
B · 분당 100회	작성자 동등 + 전체 상태 최신순	작성자 접두는 사용 가능하나 중간 `status` 때문에 정렬은 별도 확인
C · 하루 2회	상태 동등 + 시각 범위	허용 가능한 스캔·비동기 내보내기부터 비교하고 SLA가 필요하면 별도 후보

A · 분당 1,000회: 주요 요구: 작성자·상태 동등 + 시각 범위
우선 검토: (author_id, status, created_at DESC)를 핵심 후보로
B · 분당 100회: 주요 요구: 작성자 동등 + 전체 상태 최신순
우선 검토: 작성자 접두는 사용 가능하나 중간 status 때문에 정렬은 별도 확인
C · 하루 2회: 주요 요구: 상태 동등 + 시각 범위
우선 검토: 허용 가능한 스캔·비동기 내보내기부터 비교하고 SLA가 필요하면 별도 후보

빈도는 문제에서 정한 조건입니다. 실제 지연이나 성능 관측값이 아닙니다.

아래 CREATE INDEX는 이 후보를 독립적으로 준비할 때 사용합니다. 앞 절까지 따라왔다면 동일 정의를 재사용하고 EXPLAIN만 실행합니다.

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

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

-- C는 별도 오프피크 export로 측정한 뒤 SLA가 깨질 때만
-- (status, created_at) 인덱스를 추가한다.

A뿐 아니라 B의 파일 정렬 크기와 C의 실제 실행 시간을 측정하고, 인덱스 하나를 추가했을 때 쓰기 비용까지 의사결정 기록에 포함합니다.

핵심 정리

복합 인덱스는 왼쪽부터 계층적으로 정렬됩니다.
동등 조건 뒤 첫 범위와 정렬 컬럼을 두는 것이 일반적 출발점입니다.
쿼리 유형마다 인덱스가 필요하다고 가정하지 않고 호출량과 SLA를 봅니다.
중복 인덱스 제거는 비가시 검증과 DML 비용 측정을 거칩니다.

7장 실습을 끝낸 뒤 운영 스키마와 분리된 fixture만 정리하려면 다음 문장을 실행합니다.

이후 실습을 다시 시작할 때는 ch7-1의 생성 블록부터 실행합니다.

인덱스 실습 fixture reset

DROP TABLE IF EXISTS post_search_demo;

다음 장에서는 빠른 조회보다 먼저 지켜야 할 데이터 무결성과 트랜잭션 단위를 다룹니다.

이 페이지의 목차