인덱스 설계 심화: composite, covering, partial, functional index

인덱스를 추가한다고 쿼리가 빨라지는 게 아니다

인덱스 설계의 첫 번째 오해는 "인덱스가 많을수록 좋다"는 생각이다. 인덱스는 읽기 비용을 줄이는 대신 쓰기 비용, 스토리지, 캐시 압박, 복제 지연을 늘린다. 잘못 만든 인덱스는 쿼리를 빠르게 하기는커녕 INSERT/UPDATE/DELETE를 느리게 하고, optimizer가 더 나쁜 플랜을 고르도록 오도한다.

심화 인덱스 설계란 "어떤 컬럼에 인덱스를 만들까"보다 한 단계 위의 질문이다.

• 어떤 컬럼을 어떤 순서로 묶어야 여러 쿼리가 같은 인덱스를 공유할 수 있는가?
• index leaf에 필요한 컬럼을 포함시켜 table row 접근을 아예 없앨 수 있는가?
• 전체 행이 아니라 자주 쓰는 부분 집합만 인덱싱해서 크기와 write 비용을 줄일 수 있는가?
• 컬럼 원본이 아니라 표현식을 인덱싱해서 함수 적용 후 필터를 index-level로 내릴 수 있는가?

이 네 가지가 composite, covering, partial, functional index다.

Composite Index: 컬럼 순서가 플랜을 결정한다

복합 인덱스(composite index)는 두 개 이상의 컬럼을 하나의 인덱스로 묶는다. 개념은 간단하지만 컬럼 순서를 잘못 결정하면 쿼리가 인덱스를 전혀 사용하지 못하거나 부분적으로만 사용하게 된다.

Leftmost prefix: 인덱스는 왼쪽 컬럼부터 쓴다

(a, b, c) 복합 인덱스가 있을 때, 아래 쿼리들의 인덱스 활용 가능성을 보자.

-- 완전 활용: a 조건이 있고 순서대로 진행
SELECT * FROM orders WHERE a = 1 AND b = 2 AND c = 3;

-- 부분 활용: a만 있으면 a까지만
SELECT * FROM orders WHERE a = 1;

-- 미활용: a 없이 b만 있으면 전체 인덱스 스캔이거나 full table scan
SELECT * FROM orders WHERE b = 2;

B-tree 인덱스는 정렬된 구조이기 때문에 "왼쪽에서 시작해서 오른쪽으로 이어지는" 조건만 인덱스를 seekable하게 만든다. b만 있을 때는 a 값을 모르니 인덱스 안에서 어디서 시작해야 할지 알 수 없다.

ESR 규칙: Equality → Sort → Range

복합 인덱스 컬럼 순서를 결정할 때 실무에서 자주 쓰이는 기준은 ESR 규칙이다. Equality 조건 컬럼을 앞에, Sort 컬럼을 그 뒤에, Range 조건 컬럼을 마지막에 배치하는 원칙이다.

-- 이 쿼리 패턴에 맞는 인덱스 순서는?
SELECT id, total_amount
FROM orders
WHERE status = 'PAID'          -- E: equality
ORDER BY created_at DESC       -- S: sort
LIMIT 100;

이 경우 (status, created_at DESC) 인덱스가 ESR 규칙에 맞는다. status로 범위를 좁히고, 이미 created_at 순으로 정렬된 상태이므로 sort step 없이 LIMIT만큼만 읽고 멈출 수 있다.

Range 조건이 Sort 앞에 오면 문제가 생긴다.

-- range 조건이 있을 때
SELECT id, total_amount
FROM orders
WHERE created_at >= '2025-01-01'  -- R: range
  AND status = 'PAID'              -- E: equality
ORDER BY created_at DESC           -- S: sort
LIMIT 100;

(created_at, status) 순서로 만들면 created_at >= '2025-01-01'로 범위를 찾지만 그 안에서 status 조건은 index level에서 처리하기 어렵다. 반면 (status, created_at) 순서라면 status = 'PAID'를 equality seek으로 좁히고 created_at을 range scan하면서 sort까지 해결할 수 있다.

ESR 규칙이 항상 완벽한 답은 아니다. 실제 row 분포, 각 조건의 선택도, workload 패턴이 다를 수 있으므로 EXPLAIN ANALYZE로 직접 검증해야 한다.

선택도(selectivity)와 컬럼 순서

Equality 조건이 여러 개일 때는 선택도가 높은 컬럼(unique에 가까운 컬럼)을 앞에 두는 것이 인덱스 scan 범위를 가장 많이 줄인다.

-- user_id는 unique에 가깝고 status는 cardinality가 낮다.
-- (user_id, status) 순서가 (status, user_id) 보다 낮은 범위를 스캔한다.
SELECT * FROM orders WHERE user_id = 42 AND status = 'PAID';

그러나 선택도 순서가 다른 쿼리도 같은 인덱스를 써야 한다면 두 쿼리의 접근 패턴을 함께 고려해야 한다. "모든 쿼리에 최적인 인덱스"는 없다. 가장 자주 실행되고 latency에 민감한 쿼리를 기준으로 설계하되, 인덱스 수를 최소화하는 방향으로 다른 쿼리와 공유 가능성을 따진다.

Covering Index: table row를 읽지 않는 인덱스

Covering index는 쿼리가 필요로 하는 모든 컬럼이 인덱스 leaf에 포함되어 있는 인덱스다. 이 경우 DB는 인덱스 leaf에서 값을 가져온 뒤 heap(table)을 추가로 읽지 않아도 된다. 이를 Index Only Scan(PostgreSQL), Using index(MySQL), Covering Index Seek/Scan(SQL Server)라고 부른다.

왜 table row 접근이 비싼가

Secondary index는 일반적으로 index key + heap pointer(physical row location) 형태로 저장된다. 인덱스에서 row를 찾으면 실제 컬럼 값을 얻기 위해 heap block을 추가로 읽어야 한다. 이 과정을 MySQL에서는 Key Lookup, PostgreSQL에서는 Heap Fetch라고도 부른다.

heap 접근이 비싼 이유는 random I/O 때문이다. 인덱스 leaf는 key 순서로 정렬되어 있지만, heap row는 물리적으로 다른 block에 흩어져 있다. 인덱스로 1,000 row를 찾았더라도 그 row들이 각각 다른 block에 있다면 최악의 경우 1,000번의 random read가 발생한다.

Covering index는 이 heap 접근을 없앤다. 쿼리 결과에 필요한 컬럼을 인덱스에 포함시켜 heap 접근 없이 인덱스 leaf만으로 결과를 만든다.

PostgreSQL: INCLUDE 절

PostgreSQL 11부터는 CREATE INDEX ... INCLUDE (col) 문법으로 인덱스 key가 아닌 payload column을 leaf에만 추가할 수 있다.

-- (customer_id, created_at)를 key로 하되, total_amount를 leaf에 포함
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id, created_at DESC)
INCLUDE (total_amount);

SELECT customer_id, created_at, total_amount
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

이 쿼리는 idx_orders_customer_covering을 Index Only Scan으로 처리할 수 있다. total_amount는 key가 아니므로 정렬이나 seek에 영향을 주지 않지만, leaf에 있으므로 heap 접근 없이 반환 가능하다.

MySQL: composite key로 covering

MySQL InnoDB는 secondary index leaf에 primary key 값을 자동으로 포함한다. 따라서 SELECT id FROM orders WHERE customer_id = 42처럼 PK만 필요한 경우라면 (customer_id) 인덱스만으로 covering이 된다.

더 많은 컬럼을 covering하려면 인덱스에 포함 컬럼을 추가한다.

-- MySQL: customer_id seek + total_amount covering
CREATE INDEX idx_orders_customer_cov ON orders (customer_id, created_at, total_amount);

EXPLAIN에서 Extra: Using index가 보이면 covering index가 동작하는 것이다.

Covering Index의 비용

Covering index는 read 성능을 크게 높이지만 인덱스 크기를 늘리고 write overhead를 높인다. INCLUDE 컬럼을 많이 추가하면 인덱스가 커져서 buffer cache 효율이 낮아진다. 또한 INCLUDE 컬럼이 자주 UPDATE되면 index page도 자주 수정된다.

운영 기준으로는 read 비중이 높고, 쓰기가 상대적으로 적으며, heap 접근이 실제 병목인지 확인된 경우에만 covering index를 추가한다.

Partial Index: 일부 행만 인덱싱한다

Partial index(PostgreSQL) / Filtered index(SQL Server)는 WHERE 조건을 만족하는 행만 인덱스에 포함시킨다. MySQL은 네이티브 partial index를 지원하지 않지만 generated column + index로 유사하게 구현할 수 있다.

언제 유용한가

대부분의 쿼리가 특정 값 또는 상태의 행만 대상으로 한다면, 그 조건에 맞는 행만 인덱싱하면 된다.

예를 들어 orders 테이블에서 status = 'PENDING'인 행은 전체의 1%이지만 "처리 대기 주문 조회"는 분당 수백 번 실행된다고 하자.

-- 전체 행 대신 PENDING 상태만 인덱싱
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'PENDING';

이 인덱스는 전체 테이블의 1%만 담기 때문에:

• 인덱스 크기가 작아 메모리 효율이 높다.
• 신규 주문이 들어올 때 PENDING 아닌 행은 이 인덱스를 수정하지 않아도 된다.
• 쿼리가 WHERE status = 'PENDING' 조건을 포함할 때만 이 인덱스가 사용된다.

-- 이 쿼리는 idx_orders_pending을 사용할 수 있다.
SELECT id, created_at FROM orders
WHERE status = 'PENDING'
ORDER BY created_at
LIMIT 100;

-- 이 쿼리는 사용 불가 (WHERE status = 'PENDING'이 없다)
SELECT id, created_at FROM orders
ORDER BY created_at
LIMIT 100;

Partial Index의 또 다른 활용: Unique 제약 조건

NULL이 있거나 특정 조건을 만족하는 행에만 unique 제약을 걸고 싶을 때 partial unique index가 유용하다.

-- 삭제되지 않은 사용자에 대해서만 email uniqueness 보장
CREATE UNIQUE INDEX idx_users_active_email
ON users (email)
WHERE deleted_at IS NULL;

전체 테이블에 unique index를 걸면 소프트 삭제된 이메일로 재가입할 수 없지만, partial unique index는 deleted_at IS NULL 행에만 unique를 요구한다.

Functional Index: 표현식을 인덱싱한다

Functional index(표현식 인덱스, expression index)는 컬럼 값 자체가 아니라 컬럼에 함수나 표현식을 적용한 결과를 인덱싱한다.

왜 필요한가

가장 흔한 케이스는 대소문자 구분 없는 검색이다.

-- 이 쿼리는 일반 email 인덱스를 사용하지 못한다.
SELECT * FROM users WHERE lower(email) = '[email protected]';

lower(email) 결과는 컬럼 email 값과 다르기 때문에, email 컬럼의 인덱스로는 이 조건을 index seek로 처리할 수 없다. 이럴 때 functional index를 만든다.

-- PostgreSQL: 표현식 인덱스
CREATE INDEX idx_users_lower_email ON users (lower(email));

이제 WHERE lower(email) = '[email protected]'은 이 인덱스를 사용할 수 있다. 단, 쿼리의 표현식이 인덱스의 표현식과 정확히 일치해야 한다.

date_trunc, JSON, 계산 컬럼

-- 월별 집계 쿼리를 자주 실행할 때
CREATE INDEX idx_orders_month ON orders (date_trunc('month', created_at));

-- JSON 필드 검색
CREATE INDEX idx_events_type ON events ((payload->>'event_type'));

MySQL 8.0+에서는 Generated Column을 이용해 유사한 효과를 얻는다.

ALTER TABLE users
  ADD COLUMN email_lower VARCHAR(255) AS (LOWER(email)) STORED,
  ADD INDEX idx_users_email_lower (email_lower);

VIRTUAL(인덱스 쓸 때 계산) 대신 STORED를 쓰면 컬럼 값을 미리 계산해 저장하므로 조회가 더 빠르지만 스토리지가 늘어난다.

Functional Index 주의사항

• 정확한 표현식 일치가 필요하다. lower(email) 인덱스는 LOWER(email)과 매칭되지만 UPPER(email)나 lower(TRIM(email))은 별도 인덱스가 필요하다.
• 통계가 정확해야 한다. PostgreSQL은 functional index의 통계를 별도로 수집하므로, ANALYZE를 주기적으로 실행해야 optimizer가 올바른 계획을 선택한다.
• 쓰기 비용이 있다. 표현식 결과를 INSERT/UPDATE마다 계산해서 인덱스에 저장한다.

인덱스 설계 원칙 종합

실무 체크리스트

인덱스를 설계하거나 추가할 때 다음을 확인한다.

설계 전:

• 느린 쿼리의 실제 실행 횟수와 latency를 측정했는가?
• EXPLAIN ANALYZE로 현재 플랜의 병목이 인덱스 부재인지 확인했는가?
• query rewrite, 통계 갱신, WHERE 조건 정리로도 해결할 수 없는가?

설계 중:

• Composite: ESR 순서를 따랐는가? 기존 인덱스의 leftmost prefix로 해결 가능하지 않은가?
• Covering: INCLUDE 컬럼이 UPDATE 빈도가 낮은 컬럼인가?
• Partial: 조건이 쿼리에 항상 포함되는가? 인덱스 조건에 맞지 않는 쿼리가 full scan하지 않는가?
• Functional: 쿼리 표현식이 인덱스 표현식과 정확히 일치하는가?

설계 후:

• staging에서 실제 데이터 분포로 EXPLAIN ANALYZE를 검증했는가?
• 인덱스 추가 후 write latency, replication lag, storage 증가를 모니터링했는가?
• 기존 인덱스와 중복되거나 거의 사용되지 않는 인덱스가 생기지 않았는가?

Index 남용 신호

인덱스 관련 문제는 "인덱스가 없어서"만큼이나 "인덱스가 너무 많아서" 발생한다.

사용되지 않는 인덱스를 주기적으로 삭제하는 것도 인덱스 설계의 일부다. PostgreSQL에서는 CONCURRENTLY 옵션으로 서비스 중단 없이 인덱스를 삭제하거나 재생성할 수 있다.

References

• PostgreSQL Documentation, "11.3. Multicolumn Indexes" — https://www.postgresql.org/docs/current/indexes-multicolumn.html
• PostgreSQL Documentation, "11.8. Partial Indexes" — https://www.postgresql.org/docs/current/indexes-partial.html
• PostgreSQL Documentation, "11.7. Indexes on Expressions" — https://www.postgresql.org/docs/current/indexes-expressional.html
• MySQL 8.4 Reference Manual, "10.3.6 Multiple-Column Indexes" — https://dev.mysql.com/doc/refman/8.4/en/multiple-column-indexes.html
• Markus Winand, "The Index Lookup" — https://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys
• DEV Community, "Beyond Basic Indexes: Mastering Partial, Composite, and Covering Indexes in SQL" — https://dev.to/vivekdraxlr/beyond-basic-indexes-mastering-partial-composite-and-covering-indexes-in-sql-2led
• YugabyteDB blog, "ESR (Equality, Sort, Range) rule applied to indexes" — https://dev.to/yugabyte/esr-equality-sort-range-rule-for-yugabytedb-indexes-fi4
• JusDB Blog, "Advanced Database Indexing: Composite, Covering, Partial & Expression Indexes" — https://www.jusdb.com/blog/advanced-indexing-strategies-for-database-scalability