복합 인덱스는 왜 컬럼 순서가 중요할까?

데이터베이스 인덱스는 어떻게 조회를 빠르게 만들까?에서 이메일 하나로 회원을 찾을 때 22ms가 0.002ms로 줄어드는 걸 확인했는데요. 그런데 실무에서 만나는 쿼리는 조건이 하나뿐인 경우가 오히려 드뭅니다. “서울에 사는 30세 회원”, “결제 완료된 이번 달 주문”처럼 조건 두세 개가 AND로 엮이는 게 보통이죠.

조건이 여러 개면 인덱스도 여러 개 걸면 될 것 같은데, 실제로는 컬럼을 하나로 묶은 복합 인덱스(composite index)를 만듭니다. 그리고 이때 컬럼을 어떤 순서로 묶느냐에 따라 결과가 크게 달라집니다. 같은 두 컬럼인데 순서만 바꿨더니 어떤 쿼리는 인덱스를 타고 어떤 쿼리는 풀 스캔(full scan)으로 떨어지거든요.

이번 글에서는 그 순서 규칙을 직접 확인해 보겠습니다. 지난 글에서 “다음 글에서 아주 중요해진다”고 예고했던 실행 계획의 괄호 안을 읽는 게 핵심입니다.

실습 데이터에 컬럼을 더합니다

지난번 회원 테이블에 나이와 가입일을 추가하겠습니다.

db.run(`
  CREATE TABLE members (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    name TEXT NOT NULL,
    city TEXT NOT NULL,
    age INTEGER NOT NULL,
    joined_at TEXT NOT NULL,
    bio TEXT NOT NULL
  )
`);

const cities = ["서울", "부산", "대구", "인천", "광주"];
const DAY = 24 * 60 * 60 * 1000;
const base = Date.parse("2019-01-01");

const insertMany = db.transaction((count: number) => {
  for (let i = 0; i < count; i++) {
    insert.run(
      `user${i}@example.com`,
      `사용자${i}`,
      // city는 5종, age는 50종. 주기를 어긋나게 해서 두 컬럼을 서로 독립시킵니다
      cities[i % cities.length],
      20 + (Math.floor(i / cities.length) % 50),
      new Date(base + Math.floor(i / 200) * DAY).toISOString().slice(0, 10),
      "안녕하세요 자기소개입니다 ".repeat(3),
    );
  }
});

주석으로 적어둔 부분이 은근히 중요한데요. i % 5로 도시를 정하고 i % 50으로 나이를 정하면 50이 5의 배수라서 나이가 정해지는 순간 도시까지 결정돼 버립니다. 그러면 두 컬럼이 사실상 한 컬럼이나 마찬가지라 실험이 성립하지 않아요. Math.floor(i / 5)로 주기를 어긋나게 해서 도시 5종과 나이 50종이 골고루 섞이도록 했습니다.

50만 행을 넣으면 도시별로 10만 명씩, 그 안에서 나이도 균등하게 퍼집니다. 서울에 사는 30세는 정확히 2,000명이 됩니다.

실행 계획과 소요 시간을 함께 찍어주는 도우미도 하나 만들어 두겠습니다.

import { Database } from "bun:sqlite";

export const db = new Database("members.db");

/** 쿼리의 실행 계획과 평균 소요 시간을 함께 출력합니다 */
export function probe(label: string, sql: string, ...params: unknown[]) {
  const planStmt = db.prepare(`EXPLAIN QUERY PLAN ${sql}`);
  const plan = (planStmt.all(...(params as [])) as { detail: string }[])
    .map((r) => r.detail)
    .join("\n             ");
  planStmt.finalize();

  const stmt = db.prepare(sql);
  const rows = stmt.all(...(params as [])) as unknown[];

  const start = performance.now();
  for (let i = 0; i < 10; i++) stmt.all(...(params as []));
  const ms = (performance.now() - start) / 10;
  stmt.finalize();

  console.log(`${label}
  실행 계획: ${plan}
  결과 행수: ${rows.length.toLocaleString()}
  소요 시간: ${ms.toFixed(3)}ms
`);
}

/** 인덱스를 모두 지우고 지정한 것만 새로 만듭니다 */
export function reindex(...defs: string[]) {
  db.run("DROP INDEX IF EXISTS idx_a");
  db.run("DROP INDEX IF EXISTS idx_b");
  defs.forEach((d, i) => db.run(`CREATE INDEX idx_${"ab"[i]} ON members ${d}`));
}

prepare()로 만든 문장을 매번 finalize()로 닫아주는 게 포인트입니다. 열어둔 채로 두면 다음 실험에서 인덱스를 지우려 할 때 database table is locked 오류를 만나거든요.

인덱스를 두 개 걸면 두 개를 다 쓸까요?

city와 age에 인덱스를 하나씩 따로 걸어보고, 두 컬럼을 하나로 묶은 인덱스와 비교해 보겠습니다.

import { db, probe, reindex } from "./probe";

const sql = "SELECT * FROM members WHERE city = ? AND age = ?";

reindex("(city)", "(age)");
db.run("ANALYZE");
probe("① city와 age에 따로따로", sql, "서울", 30);

reindex("(city, age)");
db.run("ANALYZE");
probe("② (city, age) 하나로 묶어서", sql, "서울", 30);

① city와 age에 따로따로
  실행 계획: SEARCH members USING INDEX idx_b (age=?)
  결과 행수: 2,000
  소요 시간: 4.982ms

② (city, age) 하나로 묶어서
  실행 계획: SEARCH members USING INDEX idx_a (city=? AND age=?)
  결과 행수: 2,000
  소요 시간: 2.698ms

①번 실행 계획을 보면 인덱스를 두 개나 만들어 뒀는데 idx_b (age=?) 하나만 씁니다. 데이터베이스는 보통 테이블 하나에 인덱스 하나만 골라 쓰거든요. 나이로 1만 명을 추린 다음, 그 1만 명을 하나씩 꺼내 도시가 서울인지 확인하는 식입니다. 나머지 8,000명은 헛수고였던 셈이죠.

②번은 처음부터 2,000명만 집어냅니다. 괄호 안에 city=?와 age=?가 나란히 들어 있는 게 그 증거예요.

저장 공간도 묶는 쪽이 유리했습니다. 단일 인덱스 두 개는 7.2MB와 4.8MB로 합쳐서 12.0MB인데, 복합 인덱스 하나는 8.1MB였습니다. 컬럼을 따로 두면 각자 행 위치를 중복해서 들고 있어야 하니까요.

복합 인덱스는 사전순으로 줄을 세웁니다

왜 이런 차이가 생길까요? 복합 인덱스가 무엇을 만들어 두는지 보면 답이 나옵니다.

지난 글에서 인덱스는 컬럼을 정렬해둔 사본이라고 했는데요. 복합 인덱스는 여러 컬럼을 묶어서 사전순으로 정렬해둔 사본입니다. 앞 컬럼으로 먼저 줄을 세우고, 앞 컬럼이 같으면 그다음 컬럼으로 줄을 세우는 식이에요. 국어사전이 첫 글자로 정렬하고 첫 글자가 같으면 두 번째 글자로 정렬하는 것과 똑같습니다.

(city, age) 인덱스가 실제로 어떤 순서인지 보면 이렇습니다.

광주 10만 명이 통째로 앞에 오고, 그 안에서 나이순으로 정렬됩니다. 광주가 끝나면 대구 10만 명, 그다음 부산, 서울, 인천 순이에요.

여기서 인덱스가 빠른 진짜 이유가 드러납니다. 인덱스가 도움이 되려면 찾으려는 값이 한 덩어리로 붙어 있어야 합니다. 서울이면서 30세인 2,000명은 서울 구역 안의 30세 구간에 딱 붙어 있으니, 시작 위치만 찾으면 그다음부터는 순서대로 읽어 내려가면 끝입니다.

왼쪽부터 순서대로

이 정렬 구조가 그대로 규칙이 됩니다. 조건을 바꿔가며 확인해 보겠습니다.

import { db, probe, reindex } from "./probe";

reindex("(city, age)");

probe("① city + age", "SELECT * FROM members WHERE city = ? AND age = ?", "서울", 30);
probe("② city만 (선행 컬럼)", "SELECT * FROM members WHERE city = ?", "서울");
probe("③ age만 (후행 컬럼)", "SELECT * FROM members WHERE age = ?", 30);

① city + age
  실행 계획: SEARCH members USING INDEX idx_a (city=? AND age=?)
  결과 행수: 2,000
  소요 시간: 3.566ms

② city만 (선행 컬럼)
  실행 계획: SEARCH members USING INDEX idx_a (city=?)
  결과 행수: 100,000
  소요 시간: 155.294ms

③ age만 (후행 컬럼)
  실행 계획: SCAN members
  결과 행수: 10,000
  소요 시간: 37.750ms

②번은 앞 컬럼인 city만 줬는데도 인덱스를 탑니다. 괄호 안에 (city=?)만 들어 있죠. 서울 구역이 통째로 붙어 있으니 그 구역의 시작과 끝만 알면 되거든요.

문제는 ③번입니다. SCAN members, 즉 풀 스캔으로 떨어졌습니다. 인덱스가 분명히 age를 품고 있는데도 쓰이지 못했어요.

아까 정렬 표를 다시 떠올려 보면 이유가 보입니다. 30세인 1만 명은 광주 구역에 2,000명, 대구 구역에 2,000명, 이런 식으로 다섯 덩어리로 흩어져 있습니다. 인덱스는 한 덩어리를 빠르게 집어내는 도구인데 다섯 군데를 뒤져야 하니 이점이 사라진 거죠.

이걸 최좌측 접두사(leftmost prefix) 규칙이라고 부릅니다. 복합 인덱스는 왼쪽부터 끊김 없이 이어지는 컬럼에만 쓸 수 있다는 뜻이에요. (A, B, C) 인덱스라면 A, A+B, A+B+C 조합은 되지만 B나 C, B+C로는 안 됩니다.

순서를 뒤집으면 정확히 반대가 됩니다

같은 두 컬럼을 순서만 바꿔서 묶어보면 이 규칙이 더 분명해집니다.

import { db, probe, reindex } from "./probe";

reindex("(age, city)");

probe("④ age만 (이제는 선행 컬럼)", "SELECT * FROM members WHERE age = ?", 30);
probe("⑤ city만 (이제는 후행 컬럼)", "SELECT * FROM members WHERE city = ?", "서울");

④ age만 (이제는 선행 컬럼)
  실행 계획: SEARCH members USING INDEX idx_a (age=?)
  결과 행수: 10,000
  소요 시간: 12.091ms

⑤ city만 (이제는 후행 컬럼)
  실행 계획: SCAN members
  결과 행수: 100,000
  소요 시간: 94.578ms

방금 풀 스캔이던 age 조회가 인덱스를 타고 37.750ms에서 12.091ms로 줄었습니다. 대신 잘 되던 city 조회가 풀 스캔으로 떨어졌어요. 컬럼 구성은 한 글자도 안 바뀌었고 순서만 뒤집었을 뿐인데 말이죠.

그래서 복합 인덱스를 설계할 때는 어떤 컬럼을 왼쪽에 둘지가 곧 어떤 쿼리를 살릴지 정하는 일이 됩니다. 자주 쓰이는 조건, 특히 단독으로도 검색되는 컬럼을 앞에 두는 게 기본이에요.

규칙에는 예외가 있습니다

그런데 방금 본 ③번 풀 스캔에는 반전이 있습니다. 데이터베이스에 통계를 만들어 주고 똑같은 쿼리를 다시 돌려보겠습니다.

import { db, probe, reindex } from "./probe";

const sql = "SELECT * FROM members WHERE age = ?";

reindex("(city, age)");
probe("① 통계 없이", sql, 30);

db.run("ANALYZE");
probe("② ANALYZE로 통계를 만든 뒤", sql, 30);

① 통계 없이
  실행 계획: SCAN members
  결과 행수: 10,000
  소요 시간: 51.854ms

② ANALYZE로 통계를 만든 뒤
  실행 계획: SEARCH members USING INDEX idx_a (ANY(city) AND age=?)
  결과 행수: 10,000
  소요 시간: 14.611ms

풀 스캔이던 게 인덱스 탐색으로 바뀌면서 3.5배 빨라졌습니다. 최좌측 접두사 규칙을 어겼는데도요.

괄호 안의 ANY(city)가 무슨 일이 벌어졌는지 말해줍니다. 스킵 스캔(skip-scan)이라는 최적화인데요. 도시가 다섯 종류뿐이라는 걸 알게 되자 “광주 구역에서 30세 찾고, 대구 구역에서 30세 찾고” 하는 식으로 다섯 번 반복 탐색하기로 한 겁니다. 아까 인덱스를 못 쓰는 이유였던 “다섯 덩어리로 흩어져 있다”를 정면으로 받아들인 셈이죠. 다섯 번 뒤지는 게 50만 행을 훑는 것보다 싸다는 계산입니다.

ANALYZE가 필요했던 이유가 여기 있습니다. 이 명령은 각 컬럼에 값이 몇 종류나 있는지 세어서 sqlite_stat1 테이블에 적어둡니다. 그 통계가 없으면 쿼리 플래너(query planner)는 도시가 다섯 종류인지 50만 종류인지 알 수가 없어요. 만약 도시가 회원마다 다 달랐다면 스킵 스캔은 50만 번 반복 탐색이 되어 풀 스캔보다 한참 느렸을 겁니다. 모르는 상태에서 함부로 시도하지 않은 게 맞는 판단이었죠.

그렇다고 규칙을 잊어도 된다는 뜻은 아닙니다. 스킵 스캔은 앞 컬럼의 값 종류가 아주 적을 때만 발동하는 구제책이고, 제대로 설계한 인덱스보다는 여전히 느리니까요. 앞의 ④번에서 age를 선행 컬럼으로 두었을 때가 12.091ms였는데 스킵 스캔은 14.611ms였습니다.

동등 조건을 먼저, 범위 조건을 나중에

컬럼이 셋으로 늘면 규칙 하나가 더 붙습니다. 조건의 종류까지 따져야 하거든요.

서울에 사는 30세 중에 2025년 이후 가입한 회원을 찾아보겠습니다. 같은 세 컬럼을 순서만 바꿔 묶고 비교합니다.

import { db, probe, reindex } from "./probe";

const sql = `SELECT * FROM members
             WHERE city = ? AND age = ? AND joined_at >= ?`;
const params = ["서울", 30, "2025-01-01"] as const;

reindex("(city, age, joined_at)");
probe("① 동등 조건 둘 뒤에 범위 조건", sql, ...params);

reindex("(city, joined_at, age)");
probe("② 범위 조건이 가운데", sql, ...params);

① 동등 조건 둘 뒤에 범위 조건
  실행 계획: SEARCH members USING INDEX idx_a (city=? AND age=? AND joined_at>?)
  결과 행수: 246
  소요 시간: 0.289ms

② 범위 조건이 가운데
  실행 계획: SEARCH members USING INDEX idx_a (city=? AND joined_at>?)
  결과 행수: 246
  소요 시간: 0.713ms

찾아낸 회원은 246명으로 똑같은데 2.5배 차이가 납니다. 왜 그런지는 괄호 안을 세어보면 바로 보여요. ①번은 조건 세 개가 모두 들어 있고, ②번은 두 개뿐입니다. age=?가 괄호에서 빠졌죠.

범위 조건이 등장하는 순간 그 뒤 컬럼의 정렬이 깨지기 때문입니다. (city, joined_at, age) 인덱스에서 서울이면서 2025년 이후인 구간을 잡으면, 그 구간 안의 나이는 뒤죽박죽입니다. 가입일이 다르면 나이 정렬이 처음부터 다시 시작되니까요. 그래서 데이터베이스는 age 조건을 인덱스로 좁히는 데 쓰지 못하고, 구간에 걸린 행을 하나씩 꺼내 나이를 확인하는 뒷정리 작업으로 처리합니다.

반면 ①번은 city와 age가 둘 다 동등 조건이라 정렬이 유지됩니다. 서울 → 30세까지 좁히고 나면 그 안은 가입일순으로 깔끔하게 정렬되어 있어서, 범위 조건까지 인덱스로 처리할 수 있어요.

정리하면 동등 조건(=)으로 걸리는 컬럼을 앞에, 범위 조건(>, <, BETWEEN)으로 걸리는 컬럼을 맨 뒤에 두면 됩니다. 범위 조건은 하나만 인덱스에 태울 수 있으니 가장 뒤에 배치하는 게 이득이고요.

이 규칙을 지켰는지는 실행 계획의 괄호만 세어봐도 알 수 있습니다. WHERE 절의 조건 개수와 괄호 안의 조건 개수가 같은지 보면 되니까요.

인덱스만으로 답할 수 있다면

지난 글에서 COVERING이라는 수식어를 보고 넘어갔는데요. 복합 인덱스에서 이게 꽤 큰 무기가 됩니다.

import { db, probe, reindex } from "./probe";

reindex("(city, age, joined_at)");

const where = "WHERE city = ? AND age = ?";
probe("① SELECT *", `SELECT * FROM members ${where}`, "서울", 30);
probe("② SELECT joined_at", `SELECT joined_at FROM members ${where}`, "서울", 30);
probe("③ SELECT count(*)", `SELECT count(*) FROM members ${where}`, "서울", 30);

① SELECT *
  실행 계획: SEARCH members USING INDEX idx_a (city=? AND age=?)
  결과 행수: 2,000
  소요 시간: 2.946ms

② SELECT joined_at
  실행 계획: SEARCH members USING COVERING INDEX idx_a (city=? AND age=?)
  결과 행수: 2,000
  소요 시간: 0.202ms

③ SELECT count(*)
  실행 계획: SEARCH members USING COVERING INDEX idx_a (city=? AND age=?)
  결과 행수: 1
  소요 시간: 0.107ms

조건도 인덱스도 같은데 ②번이 ①번보다 15배 가까이 빠릅니다. 차이는 SELECT 뒤에 무엇을 적었느냐뿐이에요. 참고로 ③번의 결과 행수가 1인 건 개수를 세어 한 줄로 돌려주기 때문입니다.

①번은 *를 요구하니 email과 name, bio까지 필요합니다. 이 값은 인덱스에 없으니 인덱스에서 행 위치를 알아낸 다음 원본 테이블로 2,000번을 다시 찾아가야 해요. 이렇게 되짚어 가는 걸음을 테이블 룩업(table lookup)이라고 합니다.

②번이 요구한 joined_at은 인덱스 안에 이미 들어 있습니다. 그러니 원본 테이블은 쳐다볼 필요도 없죠. 이렇게 쿼리에 필요한 컬럼을 인덱스가 전부 품고 있는 상태를 커버링 인덱스(covering index)라고 부르고, 실행 계획에 USING COVERING INDEX로 표시됩니다.

그래서 복합 인덱스를 설계할 때는 조회 조건뿐 아니라 자주 함께 조회되는 컬럼을 뒤에 하나 더 붙이는 선택지도 생깁니다. 물론 인덱스가 무거워지는 건 감수해야 합니다. 우리 실습에서도 (city, age)가 8.1MB였는데 joined_at을 더하니 13.4MB로 늘었거든요. 조회가 압도적으로 많은 쿼리라면 충분히 남는 장사입니다.

마치며

복합 인덱스의 컬럼 순서를 정하는 규칙을 정리해 볼게요.

복합 인덱스는 왼쪽 컬럼부터 차례로 사전순 정렬해둔 사본입니다. 그래서 왼쪽부터 끊김 없이 이어지는 조건에만 쓸 수 있고, 뒤쪽 컬럼만으로 검색하면 값이 흩어져 있어서 풀 스캔으로 떨어집니다. 범위 조건을 만나면 그 뒤 컬럼의 정렬이 깨지므로 동등 조건을 앞에, 범위 조건을 맨 뒤에 두어야 하고요. 필요한 컬럼을 인덱스가 전부 품고 있으면 원본 테이블을 찾아가지 않아 열 배 넘게 벌어지기도 합니다.

스킵 스캔처럼 이 규칙을 비켜 가는 최적화도 있으니, 외우는 것보다 확인하는 습관이 낫습니다. EXPLAIN QUERY PLAN을 돌려서 괄호 안의 조건 개수가 WHERE 절의 조건 개수와 같은지 세어보세요. 개수가 모자라면 그 인덱스는 설계한 만큼 일하고 있지 않다는 뜻입니다.

컬럼 순서를 잘 맞췄는데도 인덱스가 아예 안 걸리는 경우가 있는데요. 인덱스를 걸었는데 왜 안 탈까?에서 쿼리를 어떻게 쓰면 멀쩡한 인덱스가 무시되는지 이어서 다룹니다.

인덱스가 실제로 어떻게 선택되는지 더 파고들고 싶으시다면 SQLite 쿼리 플래너 문서에 스킵 스캔을 비롯한 최적화 규칙이 자세히 정리되어 있으니 참고해 보세요.