데이터베이스 인덱스는 어떻게 조회를 빠르게 만들까?

데이터베이스 인덱스는 어떻게 조회를 빠르게 만들까?

조회가 느리다고 하면 일단 인덱스부터 걸어보게 되는데요. 그런데 막상 “인덱스를 걸면 왜 빨라지나요?”라는 질문을 받으면 말문이 막힙니다. “음… 책 뒤에 있는 색인 같은 거라서요?”

비유는 맞습니다. 다만 비유에서 멈추면 인덱스를 걸었는데도 안 빨라지는 상황을 만났을 때 손을 쓸 수가 없어요. 왜 빨라지는지를 알아야 왜 안 빨라지는지도 알 수 있으니까요.

이번 글에서는 50만 행짜리 데이터베이스를 직접 만들어놓고, 인덱스를 걸기 전과 후에 그 안에서 실제로 무슨 일이 벌어지는지 눈으로 확인해 보겠습니다. 실습은 SQLite로 하지만 여기서 다루는 원리는 MySQL이든 PostgreSQL이든 똑같이 적용됩니다. 마지막에는 인덱스가 공짜가 아니라는 점까지 숫자로 확인해 볼게요.

50만 명의 회원을 만들어봅시다

실습에는 Bun에 내장된 SQLite를 쓰겠습니다. 별도로 설치하거나 서버를 띄울 필요가 없어서 실험용으로 편하거든요. bun:sqlite 모듈의 기본적인 사용법은 자바스크립트에서 SQLite 데이터베이스 사용하기에서 다룬 적이 있습니다.

회원 테이블을 만들고 50만 명을 채워 넣는 코드입니다.

seed.ts
import { Database } from "bun:sqlite";

const db = new Database("members.db", { create: true });

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

const cities = ["서울", "부산", "대구", "인천", "광주"];

const insert = db.prepare(
  "INSERT INTO members (email, name, city, bio) VALUES (?, ?, ?, ?)",
);

// 50만 번의 INSERT를 하나의 트랜잭션으로 묶습니다
const insertMany = db.transaction((count: number) => {
  for (let i = 0; i < count; i++) {
    insert.run(
      `user${i}@example.com`,
      `사용자${i}`,
      cities[i % cities.length],
      "안녕하세요 자기소개입니다 ".repeat(3),
    );
  }
});

const start = performance.now();
insertMany(500_000);
console.log(`50만 행 삽입 완료: ${(performance.now() - start).toFixed(0)}ms`);

db.close();

db.transaction()으로 감싼 게 포인트인데요. 이걸 빼면 50만 번의 INSERT가 각각 별개의 트랜잭션이 되어 훨씬 오래 걸립니다.

결과
50만 행 삽입 완료: 741ms

84MB짜리 members.db 파일이 만들어졌습니다. 이제 이 안에서 특정 회원 한 명을 찾아볼 차례입니다.

인덱스가 없으면 전부 다 봅니다

user499999@example.com이라는 이메일을 가진 회원을 찾아보겠습니다.

search.ts
import { Database } from "bun:sqlite";

const db = new Database("members.db");
const sql = "SELECT * FROM members WHERE email = ?";
const target = "user499999@example.com";

// 실행 계획 확인
const plan = db.query(`EXPLAIN QUERY PLAN ${sql}`).all(target) as {
  detail: string;
}[];
console.log("실행 계획:", plan[0].detail);

// 조회 시간 측정
const stmt = db.query(sql);
stmt.get(target); // 캐시를 데우는 첫 실행

const start = performance.now();
for (let i = 0; i < 10; i++) stmt.get(target);
console.log(
  `평균 조회 시간: ${((performance.now() - start) / 10).toFixed(3)}ms`,
);

db.close();

여기서 처음 등장한 EXPLAIN QUERY PLAN이 이 글의 주인공입니다. 쿼리를 실제로 실행하는 대신, 데이터베이스가 그 쿼리를 어떤 방법으로 처리할 계획인지 알려달라는 명령이에요. 이걸 실행 계획(execution plan)이라고 부릅니다.

결과
실행 계획: SCAN members
평균 조회 시간: 22.465ms

SCAN members. 딱 두 단어인데 많은 걸 말해줍니다. members 테이블을 처음부터 끝까지 훑겠다는 뜻이에요. 흔히 풀 스캔(full scan)이나 순차 스캔(sequential scan)이라고 부르는 그 방식입니다.

생각해보면 당연합니다. 이메일이 어디에 있는지 알 방법이 없으니까요. 50만 개의 행을 하나씩 꺼내서 “이게 찾는 이메일인가?”를 확인하는 수밖에 없습니다. 우리가 찾는 회원이 마지막에 가까운 user499999라서 사실상 50만 번을 다 비교한 셈이죠.

한 건 조회에 22ms. 혼자 쓸 때는 체감이 안 되지만, 초당 100개의 요청이 들어온다면 이 쿼리 하나가 CPU를 2.2초어치 잡아먹습니다. 😅

인덱스를 걸어봅시다

이제 email 컬럼에 인덱스(index)를 만들어 보겠습니다.

create-index.ts
import { Database } from "bun:sqlite";

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

const start = performance.now();
db.run("CREATE INDEX idx_members_email ON members (email)");
console.log(`인덱스 생성: ${(performance.now() - start).toFixed(0)}ms`);

db.close();
결과
인덱스 생성: 213ms

이제 아까 그 search.ts를 그대로 다시 실행해 봅니다. 쿼리는 한 글자도 바꾸지 않았습니다.

결과
실행 계획: SEARCH members USING INDEX idx_members_email (email=?)
평균 조회 시간: 0.002ms

SCAN이 SEARCH로 바뀌었습니다. 그리고 22.465ms가 0.002ms로 줄었어요. 약 1만 배 빨라진 겁니다.

실행 계획을 읽는 법을 여기서 정리하고 갈게요. SQLite는 이 한 줄에 필요한 정보를 다 담아줍니다. 우선 맨 앞의 SCAN과 SEARCH가 갈림길입니다. 통째로 훑었느냐 찾아 들어갔느냐를 이 단어 하나가 가르거든요. 그다음 USING INDEX idx_members_email은 어떤 인덱스를 썼는지 알려주고, 마지막 괄호 안의 (email=?)는 인덱스로 범위를 좁히는 데 실제로 쓰인 조건입니다. 이 괄호는 복합 인덱스는 왜 컬럼 순서가 중요할까?에서 아주 중요해집니다.

부르는 이름은 데이터베이스마다 다릅니다. PostgreSQL은 EXPLAIN으로 계획을 보여주면서 Seq Scan과 Index Scan이라는 표현을 쓰고, MySQL은 EXPLAIN 결과의 type 컬럼이 ALL이면 풀 스캔, ref나 range면 인덱스를 탄 것으로 읽습니다. 단어만 다를 뿐 “전부 훑었나, 찾아 들어갔나”를 알려준다는 점은 똑같아요.

정렬해두면 무엇이 달라지나

그럼 인덱스는 대체 뭘 만들어둔 걸까요?

인덱스는 원본 테이블에서 특정 컬럼만 뽑아 정렬해둔 별도의 사본입니다. idx_members_email은 50만 개의 이메일을 사전순으로 줄 세우고, 각 이메일 옆에 “원본 테이블의 몇 번째 행인지”를 적어둔 목록이에요.

정렬이 왜 그렇게 대단한 걸까요? 정렬되지 않은 목록에서 원하는 값을 찾으려면 처음부터 끝까지 봐야 합니다. 하지만 정렬된 목록에서는 가운데를 펼쳐보는 것만으로 절반을 버릴 수 있습니다. 국어사전에서 “인덱스”를 찾을 때 첫 장부터 넘기지 않는 것과 같은 이치죠.

50만 개를 절반씩 줄여나가면 19번 만에 하나로 좁혀집니다. 50만 번과 19번의 차이, 이게 인덱스가 만들어내는 격차입니다.

이진 탐색 트리로는 부족한 이유

여기까지 읽으면 자연스럽게 이런 생각이 듭니다. “그럼 이진 탐색 트리(binary search tree)를 쓰면 되겠네?”

이론적으로는 맞습니다. 하지만 실제 데이터베이스는 이진 탐색 트리를 쓰지 않아요. 데이터가 메모리가 아니라 디스크에 있기 때문입니다.

디스크는 바이트 단위로 읽지 않습니다. 정해진 크기의 덩어리 단위로 읽어요. 이 덩어리를 페이지(page)라고 부릅니다. 우리 실습 파일의 페이지 크기를 확인해 보겠습니다.

pages.ts
import { Database } from "bun:sqlite";

const db = new Database("members.db");
const one = (sql: string) => Object.values(db.query(sql).get() as object)[0];

console.log(`페이지 크기: ${one("PRAGMA page_size")} 바이트`);
console.log(
  `전체 페이지 수: ${(one("PRAGMA page_count") as number).toLocaleString()}개`,
);

db.close();
결과
페이지 크기: 4096 바이트
전체 페이지 수: 25,392개

4096바이트, 즉 4KB입니다. MySQL InnoDB는 16KB, PostgreSQL은 8KB를 기본으로 씁니다. 값은 달라도 “페이지 단위로 읽는다”는 원칙은 같아요.

여기서 이진 탐색 트리의 문제가 드러납니다. 이진 탐색 트리는 노드 하나에 값을 하나만 담습니다. 값 하나 확인하자고 4KB를 통째로 읽어오는 셈이죠. 게다가 50만 개를 담으면 트리 높이가 19단이 되니까, 최악의 경우 디스크를 19번 왔다 갔다 해야 합니다.

그래서 데이터베이스는 B-Tree를 씁니다. 아이디어는 단순해요. 어차피 페이지 하나를 통째로 읽어올 거라면, 그 페이지 안에 값을 최대한 많이 눌러 담자는 겁니다. 노드 하나에 수백 개의 값을 담으면 가지가 수백 갈래로 뻗어나가고, 트리 높이는 뚝 떨어집니다. 이렇게 한 노드에서 뻗어나가는 가지의 수를 팬아웃(fan-out)이라고 부릅니다.

실제 B-Tree를 열어봅시다

말로만 하면 와닿지 않으니 우리가 만든 인덱스의 속을 직접 들여다보겠습니다. SQLite에는 dbstat이라는 가상 테이블이 있어서 파일의 물리적 구조를 조회할 수 있어요.

btree.ts
import { Database } from "bun:sqlite";

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

const rows = db
  .query(
    `SELECT length(path) AS depth, count(*) AS pages, sum(ncell) AS cells
     FROM dbstat WHERE name = 'idx_members_email'
     GROUP BY depth ORDER BY depth`,
  )
  .all() as { pages: number; cells: number }[];

rows.forEach((r, i) => {
  const level = i === 0 ? "루트" : i === rows.length - 1 ? "리프" : `내부 ${i}`;
  console.log(
    `${level.padEnd(6)} 페이지 ${r.pages.toLocaleString().padStart(7)}개, 담긴 항목 ${r.cells.toLocaleString().padStart(9)}개`,
  );
});
console.log(`→ 트리 높이: ${rows.length}단`);

db.close();
결과
루트     페이지       1개, 담긴 항목        31개
내부 1   페이지      32개, 담긴 항목     3,713개
리프     페이지   3,745개, 담긴 항목   496,256개
→ 트리 높이: 3단

50만 개의 이메일이 단 3단짜리 트리에 담겼습니다. 이진 탐색 트리였다면 19단이었을 텐데 말이죠.

숫자를 따라가 보면 팬아웃이 무슨 뜻인지 감이 옵니다. 루트 페이지 하나가 31개의 항목으로 데이터를 32개 구간으로 나눕니다. 그 아래 내부 페이지 32개가 각각 100개 남짓한 항목을 담아 다시 잘게 쪼개고, 마지막 리프 페이지 3,745개가 실제 이메일 대부분을 나눠 갖습니다. 페이지 하나가 수십에서 수백 갈래로 뻗어나가니 3단이면 50만 개를 전부 커버하는 거예요.

flowchart TB
    accTitle: 인덱스 B-Tree를 따라 이메일 하나를 찾아 내려가는 경로
    accDescr: 루트 페이지에서 검색값이 속한 구간을 고르고, 내부 페이지에서 범위를 더 좁힌 뒤, 리프 페이지에서 정확한 값과 행 위치를 얻는다. 페이지를 세 번만 읽으면 끝난다.

    root["루트 페이지 1개<br/>32개 구간으로 분할"]
    mid["내부 페이지 32개<br/>구간을 다시 잘게 분할"]
    leaf["리프 페이지 3,745개<br/>정확한 이메일과 행 위치"]
    row["members 테이블의 해당 행"]

    root -- "user499999는<br/>이 구간" --> mid
    mid -- "범위를 더 좁힘" --> leaf
    leaf -- "행 위치로 이동" --> row

한 가지 재미있는 점이 있습니다. 세 층의 항목 수를 더해보면 31 + 3,713 + 496,256으로 정확히 50만이 됩니다. 리프뿐 아니라 루트와 내부 페이지에도 실제 이메일 값이 들어 있다는 뜻이에요. SQLite의 인덱스가 순수한 B-Tree이기 때문인데, MySQL InnoDB는 B+Tree라서 모든 값이 리프에만 모이고 위층에는 길잡이용 키만 둡니다. 구현의 차이일 뿐 “페이지를 크게 쓰고 트리를 낮게 유지한다”는 목표는 다르지 않습니다.

이제 아까 그 0.002ms가 이해되실 겁니다. 루트에서 내부로, 내부에서 리프로. 페이지를 세 번 읽으면 원하는 이메일에 도달합니다. 50만 개를 훑는 대신에요.

인덱스는 정렬 상태도 저장합니다

인덱스의 이득은 검색에만 있는 게 아닙니다. 이미 정렬해서 저장해뒀다는 사실 자체가 또 다른 무기예요.

sort.ts
import { Database } from "bun:sqlite";

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

const probe = (label: string, sql: string) => {
  const plan = (
    db.query(`EXPLAIN QUERY PLAN ${sql}`).all() as { detail: string }[]
  )
    .map((r) => r.detail)
    .join("\n             ");
  const stmt = db.query(sql);
  stmt.all();
  const start = performance.now();
  for (let i = 0; i < 10; i++) stmt.all();
  console.log(`${label}
  실행 계획: ${plan}
  소요 시간: ${((performance.now() - start) / 10).toFixed(3)}ms
`);
};

probe(
  "① 인덱스가 있는 email로 정렬",
  "SELECT email FROM members ORDER BY email LIMIT 5",
);
probe(
  "② 인덱스가 없는 name으로 정렬",
  "SELECT name FROM members ORDER BY name LIMIT 5",
);

db.close();
결과
① 인덱스가 있는 email로 정렬
  실행 계획: SCAN members USING COVERING INDEX idx_members_email
  소요 시간: 0.001ms

② 인덱스가 없는 name으로 정렬
  실행 계획: SCAN members
             USE TEMP B-TREE FOR ORDER BY
  소요 시간: 27.250ms

②번의 USE TEMP B-TREE FOR ORDER BY가 눈에 띄는데요. 정렬을 위해 임시 B-Tree를 만들었다는 뜻입니다. 50만 개를 전부 읽어서 메모리에 쌓고 정렬한 다음, 앞의 5개만 꺼내고 나머지는 버린 거예요. 5개가 필요한데 50만 개를 정렬한 셈이죠.

반면 ①번은 아무것도 안 합니다. 인덱스가 이미 이메일순으로 정렬되어 있으니 앞에서 5개만 떼어오면 끝이거든요. ORDER BY 절이 계획에서 아예 사라졌습니다.

SCAN이라는 단어가 붙었는데도 0.001ms인 게 의아할 수 있는데요. 여기서는 조건이 없어서 인덱스를 앞에서부터 읽는 것뿐이고, LIMIT 5 덕분에 5개만 읽고 멈춥니다. 참고로 COVERING이라는 수식어는 필요한 데이터가 인덱스 안에 전부 있어서 원본 테이블을 아예 들여다보지 않았다는 뜻인데, 이건 커버링 인덱스에서 제대로 다루겠습니다.

인덱스는 공짜가 아닙니다

여기까지만 보면 모든 컬럼에 인덱스를 걸고 싶어집니다. 하지만 인덱스를 만든다는 건 정렬된 사본을 하나 더 떠안는 일입니다. 사본을 유지하려면 대가가 따르죠.

인덱스가 있는 테이블과 없는 테이블에 각각 50만 행을 넣어 비교해 봤습니다.

결과
인덱스 없이 50만 행 삽입: 672ms, 84.4MB
인덱스 두고 50만 행 삽입: 1042ms, 100.6MB

쓰기 시간 1.6배, 저장 공간 +16.1MB (+19%)

행을 하나 넣을 때마다 데이터베이스는 인덱스 B-Tree에서 그 값이 들어갈 자리를 찾아 끼워 넣어야 합니다. 페이지가 꽉 찼으면 페이지를 쪼개는 작업까지 따라붙어요. 수정과 삭제도 마찬가지입니다. 인덱스를 다섯 개, 열 개 걸어두면 이 비용이 그만큼 누적됩니다.

그래서 인덱스는 읽기 성능을 쓰기 성능과 저장 공간으로 사는 거래입니다. 조회가 압도적으로 많은 컬럼에는 남는 장사지만, 로그 테이블처럼 쓰기가 대부분인 곳에 인덱스를 잔뜩 걸면 손해를 볼 수 있어요.

한 가지 더 짚고 갈 게 있습니다. 인덱스를 걸었다고 항상 빨라지는 것도 아닙니다. 검색어가 문자열 중간에 오는 LIKE '%인덱스%' 같은 조건은 정렬 순서를 활용할 수가 없어서 인덱스가 있어도 풀 스캔으로 떨어집니다. 이런 검색이 필요하다면 역색인(inverted index)처럼 아예 다른 자료구조를 써야 하는데, SQLite FTS5로 전문 검색 구현하기에서 자세히 다룹니다.

마치며

인덱스가 조회를 빠르게 만드는 원리를 정리해 볼게요.

인덱스는 컬럼을 정렬해둔 사본입니다. 정렬되어 있으면 범위를 절반씩 줄여가며 찾을 수 있고, 디스크를 페이지 단위로 읽는 특성에 맞춰 B-Tree로 저장하기 때문에 50만 개를 3단으로 커버합니다. 그래서 22ms가 0.002ms로 줄어듭니다. 대신 쓰기가 1.6배 느려지고 저장 공간이 19% 늘어나죠.

무엇보다 EXPLAIN QUERY PLAN을 손에 익혀두시면 좋겠습니다. 인덱스를 걸었으면 SCAN이 SEARCH로 바뀌었는지 확인하는 습관, 이게 추측을 확인으로 바꿔주는 가장 확실한 방법이거든요.

이어지는 글 복합 인덱스는 왜 컬럼 순서가 중요할까?에서는 컬럼을 여러 개 묶는 경우를 다룹니다. (city, age) 순서로 만든 인덱스가 age 조건만으로는 왜 무용지물이 되는지, 실행 계획의 괄호 안을 읽으면 그 규칙이 그대로 보입니다.

인덱스 내부 구조를 더 깊이 파고 싶으시다면 SQLite의 파일 포맷 문서에 B-Tree 페이지의 바이트 단위 구성까지 나와 있으니 참고해 보세요.

This work is licensed under CC BY 4.0CCBY

개발자를 위한 뉴스레터

달레가 정리한 AI 개발 트렌드와 직접 만든 콘텐츠를 전해드립니다.

관련 포스트

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

데이터베이스 인덱스는 어떻게 조회를 빠르게 만들까?에서 이메일 하나로 회원을 찾을 때 22ms가 0.002ms로 줄어드는 걸 확인했는데요. 그런데 실무에서 만나는 쿼리는 조건이 하나뿐인 경우가 오히려 드뭅니다. "서울에 사는 30세 회원", "결제 완료된 이번 달 주문"처럼 조건 두세 개가 AND로 엮이는 게 보통이죠. 조건이 여러 개면 인덱스도 여러 개 걸면 될 것 같은데, 실제로는 컬럼을 하나로 묶은 복합 인덱스(composite index)를 만듭니다. 그리고 이때 컬럼을 어떤 순서로 묶느냐에 따라 결과가 크게 달라집니다. 같

인덱스를 걸었는데 왜 안 탈까?

데이터베이스 인덱스는 어떻게 조회를 빠르게 만들까?에서 인덱스가 빠른 이유를, 복합 인덱스는 왜 컬럼 순서가 중요할까?에서 컬럼 순서 규칙을 확인했는데요. 그런데 실무에서 더 자주 마주치는 건 이런 상황입니다. 인덱스를 분명히 걸었고 컬럼 순서도 맞췄는데 쿼리가 여전히 느린 경우요. 인덱스는 만들어 두는 것만으로 일하지 않습니다. 쿼리를 어떻게 쓰느냐에 따라 멀쩡한 인덱스가 통째로 무시되기도 하거든요. 이번 글에서는 인덱스가 있는데도 안 타는 경우를 하나씩 재현해 보겠습니다. 실험 준비 실습 데이터는 지난 글에서 쓰던 50만 명짜리

자바스크립트에서 SQLite 데이터베이스 사용하기

이번 포스팅에서는 자바스크립트에서 경량화 데이터베이스 SQLite를 사용하는 기본적인 방법에 대해서 알아보겠습니다. 파이썬에서 SQLite 데이터베이스를 사용하는 방법에 대해서는 관련 게시물을 참고 바랍니다. SQLite란? SQLite는 전 세계에서 가장 많이 사용되는 경량화 데이터베이스입니다. 알게 모르게 우리가 쓰고 있는 대부분의 전자기기에서 SQLite가 사용되고 있기 때문이죠. SQLite는 다른 데이터베이스와는 달리 클라이언트와 서버 간에 데이터를 송수신하지 않고, 모든 데이터를 로컬 컴퓨터에 저장하는데요. SQLite는

SQLite FTS5로 전문 검색 구현하기

블로그나 문서 앱을 만들다 보면 처음에는 검색 기능을 아주 단순하게 시작하게 됩니다. 제목이나 본문에 검색어가 들어 있는지 LIKE '%검색어%'로 확인하면 되니까요. 데이터가 몇십 개일 때는 이것만으로도 충분합니다. 그런데 글이 수백 개, 수천 개로 늘어나면 느낌이 달라집니다. 검색 속도가 느려지고, 관련도 순 정렬도 어렵고, 검색 결과에서 어느 부분이 매칭됐는지 보여주기도 번거로워요. "검색"이라는 단어가 한 번 들어간 글과 본문 전체가 검색에 관한 글을 같은 수준으로 다루게 되는 것도 아쉽습니다. 이럴 때 SQLite만 쓰고

WorkOS AuthKit으로 최신 MCP 인증 붙이기

원격 MCP 서버에 OAuth를 붙일 때 의외로 까다로운 부분은 로그인 화면이 아닙니다. 처음 만난 MCP 클라이언트를 인가 서버(Authorization Server)에 어떻게 등록할지가 더 큰 문제인데요. 데스크톱 앱, 코드 편집기, 에이전트 플랫폼을 인가 서버에 일일이 미리 등록할 수는 없기 때문입니다. 2025년 11월 25일 MCP 스펙은 이 문제를 풀기 위해 클라이언트 ID 메타데이터 문서(Client ID Metadata Documents, CIMD)를 권장 등록 방식으로 추가했습니다. 이전 스펙에서 사용하던 동적 클라

실시간 단방향 통신을 위한 Server-Sent Events(SSE)

ChatGPT에게 질문을 던지면 답변이 한 글자씩 타이핑되듯 흘러나오는 모습, 다들 익숙하시죠? 증권 앱의 시세가 새로고침 없이 실시간으로 바뀌거나, 웹 대시보드에 알림이 띵 하고 도착하는 것도 마찬가지인데요. 이런 화면들의 공통점은 서버에서 새로운 데이터가 생길 때마다 클라이언트가 곧바로 받아본다는 것입니다. 실시간 양방향 통신을 위한 웹소켓(WebSocket) 글에서 우리는 HTTP의 한계와 이를 극복하는 웹소켓을 살펴봤는데요. 그런데 위 예시를 가만히 보면 한 가지 공통점이 더 있습니다. 데이터가 서버에서 클라이언트로 한 방향