인덱스는 읽기를 빠르게 하는 대신 쓰기를 느리게 하고 저장 공간을 더 쓴다. 따라서 "이 컬럼이 조회에 쓰이는가" 가 아니라 "이 조회가 얼마나 자주 일어나고, 그 대가로 얼마나 많은 쓰기가 느려지는가" 를 기준으로 판단한다. 인덱스를 하나 더 만들 때마다 그 테이블의 INSERT · UPDATE · DELETE 는 인덱스 엔트리 갱신 비용을 추가로 치른다.
대부분의 RDBMS(PostgreSQL · MySQL/MariaDB · Oracle)는 기본 인덱스로 B-tree 계열을 쓴다. 아래 원칙은 B-tree 를 전제로 한다.
다음 자리에 쓰이는 컬럼이 후보다. 단, 후보라는 것이지 전부 만들라는 뜻은 아니다.
WHERE 절의 조건 컬럼JOIN 의 조인 키ORDER BY · GROUP BY 의 정렬 · 그룹 키반대로 다음은 만들지 않는다.
선택도(selectivity)는 인덱스를 탔을 때 걸러지는 비율이다. 서로 다른 값의 개수를 전체 행 수로 나눈 값으로 보면 된다. 값이 1 에 가까울수록(중복이 적을수록) 인덱스가 유리하다.
SELECT count(DISTINCT status)::numeric / count(*) AS sel_status,
count(DISTINCT email)::numeric / count(*) AS sel_email
FROM users;
sel_email 이 1 에 가깝고 sel_status 가 0 에 가깝다면 email 은 인덱스 후보이고 status 는 단독으로는 아니다.
복합 인덱스 (col1, col2, col3) 은 사전 순으로 정렬된 하나의 키다. 따라서 선두 컬럼부터 연속으로 조건이 걸릴 때만 인덱스를 탄다.
| 조건 | (a, b, c) 인덱스 사용 |
|---|---|
a = ? |
사용 |
a = ? AND b = ? |
사용 |
a = ? AND b = ? AND c = ? |
사용 |
b = ? 만 |
사용하지 않음 |
a = ? AND c = ? |
a 까지만 사용, c 는 걸러낸 뒤 필터 |
a > ? AND b = ? |
a 까지만 사용 — 범위 조건 뒤 컬럼은 정렬이 깨진다 |
그래서 컬럼 순서는 다음과 같이 잡는다.
=) 조건에 쓰이는 컬럼을 앞에> · < · BETWEEN) 조건 컬럼을 뒤에CREATE INDEX idx_orders_user_status_date
ON orders (user_id, status, order_date);
user_id = ? AND status = ? ORDER BY order_date 형태의 조회는 이 인덱스 하나로 필터와 정렬을 모두 해결한다.
조건절에서 인덱스 컬럼에 함수를 씌우거나 형이 암묵 변환되면 인덱스를 타지 못한다. 가공은 컬럼이 아니라 상수 쪽에 한다.
-- 인덱스를 타지 못한다
SELECT * FROM orders WHERE date_trunc('day', order_date) = DATE '2026-09-20';
SELECT * FROM users WHERE upper(email) = 'A@B.COM';
-- 인덱스를 탄다
SELECT * FROM orders
WHERE order_date >= TIMESTAMP '2026-09-20 00:00:00'
AND order_date < TIMESTAMP '2026-09-21 00:00:00';
가공이 꼭 필요하면 함수 인덱스(표현식 인덱스)를 만든다. 함수는 결정적(immutable)이어야 한다.
CREATE INDEX idx_users_email_lower ON users (lower(email));
MySQL/MariaDB 에서는 조인 컬럼의 문자셋 · 콜레이션이 서로 다르면 같은 형변환 문제가 생긴다. 조인 키는 양쪽 정의를 맞춘다.
부분 인덱스는 조건을 만족하는 행만 담아 인덱스 크기를 줄인다. 전체의 일부만 조회 대상인 경우(삭제 플래그, 미처리 큐 등)에 효과가 크다. PostgreSQL 은 WHERE 절을, Oracle 은 함수 인덱스가 NULL 을 담지 않는 성질을 쓴다. MySQL/MariaDB 에는 없다.
CREATE INDEX idx_users_active_email ON users (email) WHERE is_active;
커버링 인덱스는 조회가 필요로 하는 모든 컬럼을 인덱스가 담아 테이블 본체를 읽지 않게 하는 것이다. PostgreSQL 11 이상은 INCLUDE 로 키가 아닌 컬럼을 덧붙일 수 있다.
CREATE INDEX idx_orders_cover ON orders (user_id, order_date) INCLUDE (amount, status);
PostgreSQL 의 인덱스 온리 스캔은 visibility map 이 갱신돼 있어야 실제로 동작한다. VACUUM 이 밀려 있으면 계획만 인덱스 온리 스캔이고 힙을 다시 읽는다.
실행 계획은 추정이므로 실제 수행 결과와 함께 본다.
-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- MySQL / MariaDB
EXPLAIN SELECT ...;
SHOW INDEX FROM orders;
-- Oracle
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(dbms_xplan.display);
쓰이지 않는 인덱스는 비용만 남기므로 주기적으로 걷어낸다.
-- PostgreSQL — 스캔 횟수가 0 에 가까운 인덱스
SELECT schemaname, relname, indexrelname, idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC;
pg_stat_user_indexes 의 카운터는 통계 리셋 시점부터 누적된 값이다. 서버를 최근에 재기동했거나 통계를 리셋했다면 값이 작다고 해서 미사용이라고 단정하지 않는다.
통계가 낡으면 옵티마이저가 인덱스를 두고도 풀 스캔을 고른다. 대량 적재 뒤에는 통계를 갱신한다 — PostgreSQL 은 ANALYZE, Oracle 은 DBMS_STATS.GATHER_TABLE_STATS, MySQL/MariaDB 는 ANALYZE TABLE.
(a) 는 (a, b) 에 포함되므로 대개 중복이다OR 로 묶인 조건 — 인덱스가 갈라져 비효율적인 경우가 많다. UNION ALL 로 나누거나 조건을 재작성하는 편이 나을 때가 있다CREATE INDEX CONCURRENTLY, MySQL 8 · MariaDB 10.x 는 온라인 DDL 을 쓴다. 운영 중 테이블에 그냥 CREATE INDEX 를 걸지 않는다