실행 가능한 SQLite 문서: 복합 인덱스

Q: SQLite의 복합 인덱스(composite index)란 무엇인가요?

복합 인덱스는 두 개 이상의 컬럼을 하나로 묶어 만드는 인덱스입니다. CREATE INDEX idxname ON table(cola, colb) 형태로 만들면 되고, 이렇게 만들면 SQLite는 먼저 cola 기준으로 정렬하고, 같은 cola 값 안에서 다시 colb로 정렬해서 저장합니다. 전화번호부가 성으로 먼저 정렬되고 같은 성 안에서 이름순으로 정렬되는 것과 같은 방식이라고 보면 됩니다.

Q: 복합 인덱스에서 컬럼 순서가 정말 그렇게 중요한가요?

네, 굉장히 중요합니다. SQLite는 WHERE 절이 인덱스의 최좌측 접두사(leftmost prefix) 를 조건으로 걸 때만 그 복합 인덱스를 쓸 수 있습니다. 예를 들어 (a, b, c) 인덱스라면 a만 조건으로 거는 쿼리, a와 b를 거는 쿼리, 셋 다 거는 쿼리에는 사용되지만, b만 또는 c만으로 거는 쿼리에는 쓸 수 없습니다.

인덱스 하나, 여러 컬럼

복합 인덱스(composite index), 다른 말로 다중 컬럼 인덱스는 두 개 이상의 컬럼을 묶어 하나로 만든 인덱스를 말합니다. 만드는 방법은 간단한데, 컬럼을 순서대로 나열해 주면 됩니다:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    created_at TEXT NOT NULL,
    total REAL NOT NULL
);

CREATE INDEX idx_orders_customer_status
    ON orders(customer_id, status);

INSERT INTO orders(customer_id, status, created_at, total) VALUES
    (1, 'paid',    '2026-01-10', 49.00),
    (1, 'paid',    '2026-02-03', 12.50),
    (1, 'pending', '2026-02-14',  9.99),
    (2, 'paid',    '2026-01-22', 80.00),
    (2, 'refunded','2026-02-01', 25.00);

SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid';

idx_orders_customer_status 인덱스는 항목을 먼저 customer_id 순으로 정렬하고, 같은 고객 내에서 다시 status 순으로 정렬해 저장합니다. 이 정렬 방식이 전부라고 봐도 됩니다 — sqlite 복합 인덱스(composite index)에 대한 모든 동작은 결국 여기서 출발합니다.

머릿속 모델: 정렬된 전화번호부

옛날 종이 전화번호부를 떠올려 보세요. 성(姓)으로 먼저 정렬되고, 같은 성 안에서는 이름순으로 정렬돼 있죠. (last_name, first_name) 인덱스가 딱 이런 모습입니다.

이 구조에서 어떤 조회는 빠르지만, 어떤 조회는 그렇지 않습니다:

"Patel 씨 전부 찾기" — 쉬워요. Patel들이 한 곳에 모여 있으니까요.
"Priya Patel 찾기" — 쉬워요. Patel로 점프한 뒤 Priya까지만 훑으면 됩니다.
"Priya라는 이름 전부 찾기" — 느립니다. 모든 페이지를 다 봐야 해요. Priya는 모든 성씨 사이에 흩어져 있으니까요.

SQLite의 다중 컬럼 인덱스(multi-column index)도 정확히 같은 원리로 동작합니다. 첫 번째 컬럼이 1차 정렬 기준이고, 두 번째 컬럼은 첫 번째 컬럼 값이 같은 항목들 사이에서만 정렬을 결정합니다.

최좌측 접두사 규칙 (Leftmost Prefix)

SQLite가 복합 인덱스를 활용하려면 WHERE 절이 인덱스 컬럼의 최좌측 접두사 를 제약해야 합니다. (a, b, c) 인덱스를 예로 들면:

a로 필터링 — 인덱스 사용 O.
a와 b로 필터링 — 인덱스 사용 O.
a, b, c 모두로 필터링 — 인덱스 사용 O.
b만, c만, 또는 b와 c로만 필터링 — 인덱스 사용 X.

이건 EXPLAIN QUERY PLAN으로 직접 확인할 수 있습니다:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    kind TEXT NOT NULL,
    occurred_at TEXT NOT NULL
);

CREATE INDEX idx_events_user_kind_time
    ON events(user_id, kind, occurred_at);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42 AND kind = 'login';

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE kind = 'login';

첫 번째 쿼리는 SEARCH events USING INDEX idx_events_user_kind_time로 표시됩니다. 반면 두 번째는 SCAN events로 떨어지는데, kind 하나만으로 필터링하면 인덱스의 첫 컬럼인 user_id를 건너뛰게 되어 이 쿼리에서는 인덱스가 무용지물이 되기 때문입니다.

컬럼 순서는 설계 단계의 의사결정이다

최좌측 접두사 규칙(leftmost prefix) 때문에, CREATE INDEX에 컬럼을 나열하는 순서는 단순한 스타일 문제가 아니라 실질적인 설계 선택입니다. 다음 두 가지 원칙을 기억해 두면 좋습니다.

가장 자주 필터링하는 컬럼을 맨 앞에 두세요. 이 컬럼이 가장 폭넓은 쿼리에서 인덱스를 활용할 수 있게 해주는 열쇠입니다.
동등 비교(equality) 컬럼을 범위(range) 컬럼보다 앞에 두세요. SQLite는 = 조건으로 인덱스 안쪽까지 정확히 파고든 다음, <, >, BETWEEN 같은 범위 조건으로 연속된 구간을 훑을 수 있습니다. 단, 범위 스캔은 마지막으로 사용된 컬럼 하나에만 적용된다는 점을 잊지 마세요.

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    region TEXT NOT NULL,
    sold_at TEXT NOT NULL,
    amount REAL NOT NULL
);

-- 좋음: region에 대한 동등 조건이 먼저, sold_at에 대한 범위 조건이 다음.
CREATE INDEX idx_sales_region_time ON sales(region, sold_at);

INSERT INTO sales(region, sold_at, amount) VALUES
    ('EU', '2026-01-05', 120),
    ('EU', '2026-02-11', 80),
    ('US', '2026-01-09', 200),
    ('US', '2026-02-20', 150);

EXPLAIN QUERY PLAN
SELECT * FROM sales
WHERE region = 'EU' AND sold_at >= '2026-02-01';

실행 계획을 보면 SEARCH sales USING INDEX idx_sales_region_time (region=? AND sold_at>?)라고 찍혀 있습니다. SQLite가 곧바로 region = 'EU' 위치로 점프한 다음, 날짜 범위를 따라 앞으로 훑어 나가는 거죠. 만약 컬럼 순서를 (sold_at, region)으로 뒤집으면 어떻게 될까요? 같은 쿼리인데도 날짜 범위에 해당하는 모든 행을 스캔하면서 행마다 region을 다시 확인해야 합니다.

sqlite 복합 인덱스 vs. 단일 컬럼 인덱스 여러 개

자주 나오는 질문이 하나 있습니다. (a, b)로 묶은 복합 인덱스 하나를 만드는 게 나을까요, 아니면 a와 b에 각각 단일 컬럼 인덱스 두 개를 만드는 게 나을까요?

CREATE TABLE tickets (
    id INTEGER PRIMARY KEY,
    project_id INTEGER NOT NULL,
    assignee_id INTEGER NOT NULL,
    state TEXT NOT NULL
);

-- 옵션 A: 하나의 복합 인덱스.
CREATE INDEX idx_tickets_proj_state ON tickets(project_id, state);

-- 옵션 B (대안): 두 개의 단일 컬럼 인덱스.
-- CREATE INDEX idx_tickets_project ON tickets(project_id);
-- CREATE INDEX idx_tickets_state   ON tickets(state);

EXPLAIN QUERY PLAN
SELECT * FROM tickets
WHERE project_id = 7 AND state = 'open';

복합 조건으로 필터링할 때는 sqlite 복합 인덱스가 훨씬 빠릅니다. SQLite가 (project_id, state)에 해당하는 항목으로 곧장 이동하기 때문이죠. 반면 단일 컬럼 인덱스 두 개만 있으면, SQLite는 보통 그중 하나만 골라서 행을 좁힌 뒤 나머지 컬럼은 매칭된 행마다 다시 검사합니다. 두 인덱스를 교집합으로 쓰는 경우도 있긴 하지만, 두 컬럼을 함께 조회한다면 복합 인덱스가 더 깔끔한 정답입니다.

물론 project_id와 state를 따로따로 조회하는 쿼리도 있다면 두 가지를 같이 두는 것도 방법입니다. 복합 조건용 복합 인덱스 하나, 그리고 state만 단독으로 거르는 쿼리를 위한 단일 컬럼 인덱스 하나를 함께 두는 식이죠.

커버링 인덱스 (Covering Index)

쿼리가 필요로 하는 모든 컬럼 — 필터 조건에 쓰이는 컬럼은 물론이고 SELECT 절에서 가져오는 컬럼까지 — 이 인덱스 안에 전부 들어 있다면, SQLite는 테이블 자체를 아예 들여다보지 않고도 쿼리에 답할 수 있습니다. 이게 바로 sqlite 커버링 인덱스(covering index)이며, 쿼리 속도를 끌어올릴 수 있는 가장 빠른 형태입니다.

CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    issued_at TEXT NOT NULL,
    total REAL NOT NULL,
    notes TEXT
);

CREATE INDEX idx_invoices_cover
    ON invoices(customer_id, issued_at, total);

INSERT INTO invoices(customer_id, issued_at, total, notes) VALUES
    (1, '2026-01-12', 100, '첫 거래'),
    (1, '2026-02-02', 250, NULL),
    (2, '2026-01-30',  75, '긴급');

EXPLAIN QUERY PLAN
SELECT issued_at, total FROM invoices WHERE customer_id = 1;

실행 계획에 USING COVERING INDEX idx_invoices_cover가 표시됩니다. 쿼리가 issued_at과 total을 인덱스에서 바로 읽어오기 때문에 notes나 id는 필요하지 않고, 결과적으로 테이블 자체를 열어볼 일이 없습니다. 자주 실행되는 핫 쿼리를 커버하기 위해 복합 인덱스에 컬럼을 하나 더 추가하는 것은 그만한 가치가 있는 트레이드오프입니다.

복합 UNIQUE 제약 조건

sqlite 복합 인덱스는 여러 컬럼 조합에 대한 유일성도 강제할 수 있습니다. 단일 컬럼만으로는 유일하지 않지만, 컬럼들의 조합은 반드시 유일해야 하는 상황에서 유용하게 쓰입니다:

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id  INTEGER NOT NULL,
    enrolled_at TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_enrollments_pair
    ON enrollments(student_id, course_id);

INSERT INTO enrollments VALUES (1, 100, '2026-01-05');
INSERT INTO enrollments VALUES (1, 101, '2026-01-05');  -- 정상: 다른 과목
INSERT INTO enrollments VALUES (1, 100, '2026-02-01');  -- 실패: 쌍이 중복됨

세 번째 INSERT에서 UNIQUE constraint failed: enrollments.student_id, enrollments.course_id 오류가 발생합니다. 같은 조합이 이미 인덱스에 존재하기 때문에 SQLite가 중복을 거부하는 거죠.

알아두면 좋은 함정들

선두가 아닌 컬럼에 걸린 OR는 인덱스를 무력화합니다. (a, b) 인덱스에 WHERE a = 1 OR b = 2를 쓰면 인덱스를 거의 못 씁니다. SQLite가 두 조건을 별개의 분기로 따져야 하기 때문입니다.
인덱스 컬럼에 함수를 씌우면 인덱스가 안 먹힙니다. WHERE lower(email) = 'x' 같은 쿼리는 email 컬럼의 인덱스를 사용하지 못합니다. 표현식 자체에 인덱스를 만들거나, 데이터를 INSERT 시점에 정규화해 두세요.
인덱스는 공짜가 아닙니다. 모든 INSERT, 인덱스 컬럼이 포함된 UPDATE, 그리고 DELETE마다 인덱스도 갱신됩니다. 쓰기가 많은 테이블에 복합 인덱스를 세 개쯤 걸어두면 쓰기 비용을 인덱스가 다 잡아먹게 됩니다.
인덱스를 만든 뒤에는 ANALYZE를 돌리세요. SQLite 플래너는 ANALYZE로 수집한 통계를 기반으로 후보 인덱스 중 하나를 고릅니다. 통계가 없으면 휴리스틱에 의존하는데, 늘 최적이라는 보장은 없습니다.

실전 튜닝 워크플로

느린 쿼리를 다듬을 때 보통 이런 흐름으로 진행합니다.

쿼리에 EXPLAIN QUERY PLAN을 돌려 현재 SQLite가 무엇을 하고 있는지 확인합니다.
스캔이 일어나고 있다면 WHERE 절을 살펴봅니다. 등호 비교는 어떤 컬럼이고, 범위 비교는 어떤 컬럼이며, SELECT로 가져오는 건 무엇인가요?
등호 컬럼 먼저, 범위 컬럼 나중 순서로 sqlite 복합 인덱스를 만들고, 커버링이 도움이 된다면 SELECT 컬럼까지 뒤에 붙입니다.
ANALYZE를 실행합니다.
다시 EXPLAIN QUERY PLAN을 돌려 실행 계획이 바뀌었는지, 새 인덱스가 실제로 사용되는지 확인합니다.
실데이터에 가까운 데이터로 적용 전후 쿼리 시간을 측정합니다.

6번을 건너뛰면 낭패를 봅니다. 실행 계획상으로는 그럴듯해 보이는 인덱스도, 테이블이 작거나 플래너가 다른 경로를 골라버리면 실제로는 더 느릴 수 있거든요.

다음 주제: 부분 인덱스

복합 인덱스는 테이블의 모든 행을 대상으로 합니다. 하지만 실제로는 일부 행만 의미 있는 경우가 많죠. 열린 티켓, 아직 처리되지 않은 작업, 삭제되지 않은 레코드처럼요. 부분 인덱스(partial index) 를 쓰면 인덱스 자체에 WHERE 절을 박아 넣어서 필요한 행만 인덱싱할 수 있습니다. 다음 페이지에서 다룰 주제입니다.

자주 묻는 질문

SQLite의 복합 인덱스(composite index)란 무엇인가요?

복합 인덱스는 두 개 이상의 컬럼을 하나로 묶어 만드는 인덱스입니다. CREATE INDEX idx_name ON table(col_a, col_b) 형태로 만들면 되고, 이렇게 만들면 SQLite는 먼저 col_a 기준으로 정렬하고, 같은 col_a 값 안에서 다시 col_b로 정렬해서 저장합니다. 전화번호부가 성으로 먼저 정렬되고 같은 성 안에서 이름순으로 정렬되는 것과 같은 방식이라고 보면 됩니다.

복합 인덱스에서 컬럼 순서가 정말 그렇게 중요한가요?

네, 굉장히 중요합니다. SQLite는 WHERE 절이 인덱스의 최좌측 접두사(leftmost prefix) 를 조건으로 걸 때만 그 복합 인덱스를 쓸 수 있습니다. 예를 들어 (a, b, c) 인덱스라면 a만 조건으로 거는 쿼리, a와 b를 거는 쿼리, 셋 다 거는 쿼리에는 사용되지만, b만 또는 c만으로 거는 쿼리에는 쓸 수 없습니다.

복합 인덱스가 나은가요, 아니면 단일 컬럼 인덱스 여러 개가 나은가요?

여러 컬럼을 늘 같이 묶어서 필터링하거나 정렬한다면 복합 인덱스가 유리합니다. 반대로 각 컬럼이 독립적으로 따로따로 조회된다면 단일 컬럼 인덱스를 여러 개 두는 편이 낫습니다. 헷갈릴 땐 EXPLAIN QUERY PLAN을 돌려서 SQLite가 실제로 어떤 인덱스를 선택하는지 직접 확인하는 게 가장 확실합니다.

SQLite의 커버링 인덱스(covering index)는 무엇인가요?

커버링 인덱스란 쿼리가 필요로 하는 모든 컬럼을 인덱스 자체에 포함시켜서, 테이블을 따로 읽지 않고 인덱스만으로 결과를 돌려줄 수 있게 만든 인덱스입니다. 이렇게 동작하면 EXPLAIN QUERY PLAN에 USING COVERING INDEX가 찍히죠. 자주 호출되는 쿼리를 위해 복합 인덱스에 컬럼 한두 개를 일부러 더 넣어 커버링으로 만드는 건 흔한 최적화 패턴입니다.

SQLite 복합 인덱스: 컬럼 순서와 최좌측 접두사 규칙