인덱스와 쿼리 성능
같은 질문을 수천 배 빠르게 만드는 인덱스를 속부터 이해해요. 반씩 줄여 찾는 원리와 B-트리 구조, 실행 계획에서 SCAN과 SEARCH와 COVERING INDEX를 읽는 법, 여러 칸 인덱스의 왼쪽부터 규칙, 함수를 씌우거나 앞에 %를 붙이면 인덱스가 쓰이지 않는 이유와 식 인덱스로 고치는 법을 20만 행 데이터로 확인해요. 인덱스가 차지하는 공간과 느려지는 쓰기, 질문을 101번 보내는 N+1 문제, 뒤쪽 페이지일수록 느려지는 OFFSET과 그 대안까지 다뤄요.
첫 강의에서 인덱스 하나를 만들었더니 같은 질문이 수천 배 빨라졌어요. 질문은 한 글자도 바꾸지 않았는데요. 이번 강의에서는 인덱스가 안에서 어떻게 생겼길래 그렇게 빠른지, 어떤 질문에 쓰이고 어떤 질문에는 쓰이지 않는지, 그리고 공짜가 아니라면 무엇을 치르는지 알아봐요. 데이터베이스가 느려졌을 때 가장 먼저, 가장 자주 손보는 곳이 인덱스예요.
반씩 줄여 가며 찾기
국어사전에서 낱말을 찾을 때 첫 쪽부터 넘기지 않아요. 가운데쯤 펴서 앞인지 뒤인지 보고, 다시 그 절반에서 보는 식으로 찾죠. 정렬되어 있으면 이렇게 반씩 줄여 갈 수 있어요.
import math
for n in (1_000, 1_000_000, 1_000_000_000):
print(f"{n:,}개: 처음부터 찾으면 최악 {n:,}번, 반씩 줄여 가면 최악 {math.ceil(math.log2(n + 1))}번")
1,000개: 처음부터 찾으면 최악 1,000번, 반씩 줄여 가면 최악 10번
1,000,000개: 처음부터 찾으면 최악 1,000,000번, 반씩 줄여 가면 최악 20번
1,000,000,000개: 처음부터 찾으면 최악 1,000,000,000번, 반씩 줄여 가면 최악 30번
데이터가 천 배로 늘어도 반씩 줄여 찾는 횟수는 10번씩만 늘어요. 인덱스는 칸의 값을 정렬해 둔 별도의 찾아보기이고, 각 값 옆에 그 행이 표의 어디 있는지(행 번호)를 적어 둬요.
B-트리: 데이터베이스의 찾아보기
데이터베이스는 인덱스를 보통 B-트리라는 나무 모양으로 저장해요. 디스크에서 한 번에 읽는 단위(쪽, page)마다 정렬된 값을 수백 개씩 담고, 위층 쪽은 "이 범위의 값은 저 아래 쪽에 있다"는 길잡이 역할을 해요.
[ 맨 위: … | m | t | … ]
/ | \
[ a … f | g … l ] [ m … p | q … s ] [ t … w | x … z ] 중간 층
/ \ / \ / \
[맨 아래 쪽: 값 + 행 번호, 정렬된 순서로 옆 쪽과 이어져 있음]
한 쪽에 수백 개씩 담기 때문에 층이 아주 얕아요. 수백만 행이라도 보통 서너 층이면 끝이라, 디스크를 서너 번만 읽으면 원하는 값에 닿아요. 맨 아래 쪽들은 정렬된 순서로 이어져 있어서, "11월에 가입한 사람"처럼 범위를 찾을 때는 시작점을 찾은 뒤 옆으로 쭉 읽기만 하면 돼요.
실행 계획 읽기
20만 명의 회원 표를 만들고, 데이터베이스가 질문에 답할 계획을 들여다볼게요. 계획에서 네 번째 칸이 사람이 읽는 설명이에요.
import sqlite3
import time
CITIES = ["서울", "부산", "대구", "인천", "광주"]
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL, name TEXT, city TEXT, joined TEXT)")
db.executemany("INSERT INTO users (email, name, city, joined) VALUES (?, ?, ?, ?)",
((f"user{i}@example.com", f"사용자{i}", CITIES[i % 5],
f"2026-{i % 12 + 1:02d}-{i % 28 + 1:02d}") for i in range(200_000)))
def plan(sql, params=()):
return " / ".join(row[3] for row in db.execute("EXPLAIN QUERY PLAN " + sql, params))
q_email = "SELECT * FROM users WHERE email = ?"
print("인덱스 없이:", plan(q_email, ("x",)))
db.execute("CREATE INDEX idx_users_email ON users(email)")
print("인덱스 만든 뒤:", plan(q_email, ("x",)))
print("이메일만 꺼내면:", plan("SELECT email FROM users WHERE email = ?", ("x",)))
인덱스 없이: SCAN users
인덱스 만든 뒤: SEARCH users USING INDEX idx_users_email (email=?)
이메일만 꺼내면: SEARCH users USING COVERING INDEX idx_users_email (email=?)
SCAN users: 표 전체를 처음부터 끝까지 훑어요. 행이 늘수록 그만큼 느려져요.SEARCH ... USING INDEX: 인덱스에서 값을 찾고, 거기 적힌 행 번호로 표에 가서 나머지 칸을 꺼내요. 두 번 찾는 셈이지만 둘 다 빨라요.USING COVERING INDEX: 필요한 칸이 모두 인덱스 안에 있어서 표에 갈 필요조차 없어요. 이메일만 꺼내는 질문은 이메일 인덱스만으로 답이 나와요. 그래서SELECT *대신 필요한 칸만 적는 습관이 성능에도 좋아요.
PostgreSQL에서는 EXPLAIN(계획만)과 EXPLAIN ANALYZE(실제로 실행해서 걸린 시간까지)를 쓰고, MySQL에서도 EXPLAIN을 써요. 모양은 달라도 "전체를 훑는가, 인덱스로 찾아가는가"를 읽는 법은 같아요.
여러 칸 인덱스와 왼쪽부터 규칙
인덱스는 여러 칸을 묶어 만들 수도 있어요. (도시, 가입일) 인덱스를 만들고 여러 질문의 계획을 볼게요.
db.execute("CREATE INDEX idx_users_city_joined ON users(city, joined)")
for label, sql, params in [
("도시와 날짜", "SELECT name FROM users WHERE city = ? AND joined >= ?", ("서울", "2026-11-01")),
("도시만", "SELECT name FROM users WHERE city = ?", ("서울",)),
("날짜만", "SELECT name FROM users WHERE joined >= ?", ("2026-11-01",)),
("도시별 최근 가입 10명", "SELECT name FROM users WHERE city = ? ORDER BY joined DESC LIMIT 10", ("서울",)),
("이름순 10명", "SELECT name FROM users ORDER BY name LIMIT 10", ()),
]:
print(f"{label}: {plan(sql, params)}")
도시와 날짜: SEARCH users USING INDEX idx_users_city_joined (city=? AND joined>?)
도시만: SEARCH users USING INDEX idx_users_city_joined (city=?)
날짜만: SCAN users
도시별 최근 가입 10명: SEARCH users USING INDEX idx_users_city_joined (city=?)
이름순 10명: SCAN users / USE TEMP B-TREE FOR ORDER BY
(도시, 가입일) 인덱스는 도시로 먼저 정렬하고, 같은 도시 안에서 가입일로 정렬한 찾아보기예요. 성으로 정렬하고 같은 성 안에서 이름으로 정렬한 전화번호부와 같아요.
- 도시와 날짜, 도시만: 왼쪽 칸(도시)부터 쓰니 인덱스로 찾아가요.
- 날짜만: 전화번호부에서 성은 모르고 이름만으로 찾는 꼴이에요. 날짜는 도시마다 흩어져 있어서 인덱스를 쓸 수 없어 전체를 훑었어요. 이걸 왼쪽부터 규칙이라고 해요. 그래서 여러 칸 인덱스의 칸 순서가 중요해요. 같다(
=)로 비교하는 칸을 앞에, 범위로 비교하는 칸을 뒤에 두는 게 보통이에요. - 도시별 최근 가입 10명: 정렬이 따로 없어요. 같은 도시 안에서는 이미 가입일 순서라, 뒤에서부터 10개만 읽으면 끝이거든요.
- 이름순 10명: 이름 인덱스가 없으니 20만 명을 다 읽고 임시로 정렬(
USE TEMP B-TREE FOR ORDER BY)한 뒤에야 10명을 골라요. 목록 화면이 느릴 때 계획에 이 말이 보이면, 정렬에 쓰는 칸의 인덱스를 의심해 보세요.
인덱스를 못 쓰는 질문
인덱스가 있어도 질문을 어떻게 쓰느냐에 따라 못 쓰기도 해요.
for label, sql in [
("함수를 씌우면", "SELECT name FROM users WHERE lower(email) = ?"),
("앞이 %인 LIKE", "SELECT name FROM users WHERE email LIKE '%@example.com'"),
("범위로 바꾸면", "SELECT name FROM users WHERE email >= 'user1' AND email < 'user2'"),
]:
print(f"{label}: {plan(sql, ('x',) if '?' in sql else ())}")
db.execute("CREATE INDEX idx_users_lower_email ON users(lower(email))")
print("식 인덱스를 만들면:", plan("SELECT name FROM users WHERE lower(email) = ?", ("x",)))
함수를 씌우면: SCAN users
앞이 %인 LIKE: SCAN users
범위로 바꾸면: SEARCH users USING INDEX idx_users_email (email>? AND email<?)
식 인덱스를 만들면: SEARCH users USING INDEX idx_users_lower_email (<expr>=?)
- 칸에 함수를 씌우면 못 써요. 인덱스에는
email의 값이 정렬되어 있지,lower(email)의 값이 정렬되어 있지는 않으니까요. 날짜 칸에substr(joined, 1, 7) = '2026-11'처럼 쓰는 것도 같은 문제예요. 이럴 땐joined >= '2026-11-01' AND joined < '2026-12-01'처럼 칸은 그대로 두고 범위로 바꿔 써요. - **앞이
%인LIKE**도 못 써요. 끝부분이 무엇인지로는 정렬된 순서에서 위치를 정할 수 없어요. 앞부분이 정해진 검색('user1%')은 범위로 바꿀 수 있어서 쓸 수 있어요. 글 속 낱말 검색이 필요하다면 일반 인덱스가 아니라 전문 검색(SQLite의 FTS5, PostgreSQL의 전문 검색, 또는 검색 엔진)을 써요. - 식 인덱스: 함수를 꼭 써야 한다면, 그 식의 결과로 인덱스를 만들 수 있어요. 대소문자를 가리지 않는 이메일 로그인에 흔히 써요.
인덱스의 대가
인덱스는 공짜가 아니에요. 같은 10만 행을 인덱스 없이, 그리고 인덱스 3개와 함께 넣어 볼게요. 숫자는 SQLite 버전에 따라 조금 다를 수 있어요.
rows = [(f"user{i}@example.com", f"사용자{i}", CITIES[i % 5], f"2026-{i % 12 + 1:02d}-{i % 28 + 1:02d}")
for i in range(100_000)]
def build(index_sqls):
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, name TEXT, city TEXT, joined TEXT)")
for sql in index_sqls:
con.execute(sql)
start = time.perf_counter()
con.executemany("INSERT INTO users (email, name, city, joined) VALUES (?, ?, ?, ?)", rows)
con.commit()
return time.perf_counter() - start, con.execute("PRAGMA page_count").fetchone()[0]
plain_time, plain_pages = build([])
indexed_time, indexed_pages = build(["CREATE INDEX i1 ON users(email)",
"CREATE INDEX i2 ON users(city, joined)",
"CREATE INDEX i3 ON users(name)"])
print(f"인덱스 0개: {plain_pages:,}쪽 / 인덱스 3개: {indexed_pages:,}쪽 ({indexed_pages / plain_pages:.1f}배)")
print("인덱스 3개 쪽이 넣기가 1.5배 넘게 느린가요?", indexed_time / plain_time > 1.5)
인덱스 0개: 1,558쪽 / 인덱스 3개: 3,667쪽 (2.4배)
인덱스 3개 쪽이 넣기가 1.5배 넘게 느린가요? True
- 공간: 인덱스도 값을 따로 정렬해 저장하니, 3개를 달았더니 데이터베이스가 2.4배 커졌어요.
- 쓰기: 행을 넣거나 고치거나 지울 때마다 인덱스도 모두 고쳐야 해요. 이 컴퓨터에서는 넣는 시간이 4배 가까이 걸렸어요.
그래서 인덱스는 자주 묻는 질문에 맞춰 필요한 것만 만들어요. 기준은 이래요.
WHERE,JOIN ... ON,ORDER BY에 자주 나오는 칸이 후보예요.- 외래 키 칸은 대개 인덱스가 필요해요. 이어 붙일 때 쓰이고, 가리키는 행을 지울 때 "이 행을 가리키는 행이 있는지" 확인하는 데도 쓰여요. PostgreSQL과 SQLite는 외래 키에 인덱스를 저절로 만들어 주지 않아요.
- 값의 종류가 적은 칸(예/아니요, 다섯 도시)만으로 만든 인덱스는 효과가 작아요. 절반을 골라내는 데는 전체를 훑는 것과 별 차이가 없거든요. 다른 칸과 묶어 쓰면 쓸모가 생겨요.
- 쓰기가 아주 많은 표(로그, 센서 기록)에는 인덱스를 아껴요.
질문 수를 줄이기: N+1 문제
최근 주문 100건을 고객 이름과 함께 보여 주는 화면을 만든다고 해 볼게요. 주문을 가져온 뒤, 주문마다 고객 이름을 따로 물으면 어떻게 될까요?
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, amount INTEGER NOT NULL)")
db.executemany("INSERT INTO orders (user_id, amount) VALUES (?, ?)",
((i * 7 % 200_000 + 1, 10_000 + i % 50 * 1_000) for i in range(50_000)))
executed = []
db.set_trace_callback(executed.append) # 실행되는 SQL 문장을 모두 기록해요
recent = db.execute("SELECT id, user_id FROM orders ORDER BY id DESC LIMIT 100").fetchall()
names = [db.execute("SELECT name FROM users WHERE id = ?", (uid,)).fetchone()[0] for _, uid in recent]
print("하나씩 물으면 문장 수:", len(executed))
executed.clear()
joined = db.execute("""SELECT o.id, u.name FROM orders o JOIN users u ON u.id = o.user_id
ORDER BY o.id DESC LIMIT 100""").fetchall()
print("JOIN 한 번이면 문장 수:", len(executed))
print("결과는 같나요?", names == [name for _, name in joined])
db.set_trace_callback(None)
하나씩 물으면 문장 수: 101
JOIN 한 번이면 문장 수: 1
결과는 같나요? True
목록 하나에 1번, 항목마다 N번, 합해서 N+1번 물어서 이런 이름이 붙었어요. 같은 컴퓨터 안의 SQLite에서는 문장 하나가 아주 빨라서 티가 덜 나지만, 데이터베이스 서버가 네트워크 너머에 있으면 문장마다 왕복 시간이 붙어요. 왕복 1ms만 해도 101번이면 0.1초가 그냥 사라지고, 목록이 길어질수록 화면이 느려져요.
특히 ORM(파이썬 객체로 데이터베이스를 다루게 해 주는 도구)을 쓰면 order.user.name처럼 평범해 보이는 코드가 몰래 질문을 하나씩 보내는 일이 많아요. JOIN 한 번으로 가져오거나, 필요한 번호를 모아 WHERE id IN (...)으로 한 번에 묻거나, ORM의 "미리 함께 가져오기" 기능을 써서 고쳐요. 위처럼 실행되는 문장을 세어 보는 게 가장 확실한 진단법이에요.
뒤쪽 페이지일수록 느린 OFFSET
목록을 20개씩 나눠 보여 줄 때 LIMIT 20 OFFSET 190000처럼 쓰면, 9,501쪽을 보여 주려고 앞의 19만 행을 읽고 버려요. 마지막으로 본 번호를 기억했다가 그다음부터 가져오면 달라요.
def timed(sql, params=(), repeat=20):
start = time.perf_counter()
for _ in range(repeat):
db.execute(sql, params).fetchall()
return time.perf_counter() - start
offset_sql = "SELECT id, name FROM users ORDER BY id LIMIT 20 OFFSET 190000"
keyset_sql = "SELECT id, name FROM users WHERE id > ? ORDER BY id LIMIT 20"
print("OFFSET:", plan(offset_sql))
print("마지막 번호 기준:", plan(keyset_sql, (190000,)))
print("같은 쪽인가요?", db.execute(offset_sql).fetchall() == db.execute(keyset_sql, (190000,)).fetchall())
print("마지막 번호 기준이 10배 넘게 빠른가요?", timed(offset_sql) / timed(keyset_sql, (190000,)) > 10)
OFFSET: SCAN users
마지막 번호 기준: SEARCH users USING INTEGER PRIMARY KEY (rowid>?)
같은 쪽인가요? True
마지막 번호 기준이 10배 넘게 빠른가요? True
같은 20행인데 OFFSET은 처음부터 훑고, 마지막 번호 기준은 그 번호로 바로 찾아가요. 이 방식을 키셋 페이지네이션(커서 방식)이라고 해요. 무한 스크롤처럼 "다음"만 있는 목록에 잘 맞아요. "9,501쪽으로 바로 가기"는 어렵지만, 실제로 그렇게 깊이 가는 사람은 드물고 대개 검색이나 필터를 써요. 도중에 새 글이 끼어들어도 같은 글이 두 번 보이거나 빠지는 일이 없다는 장점도 있어요.
느린 질문을 고치는 순서
- 잰다. 어떤 질문이 느린지부터 찾아요. 짐작으로 인덱스를 달지 않아요. 서버형 데이터베이스는 느린 질문 기록(PostgreSQL의
pg_stat_statements, 느린 쿼리 로그)을 제공해요. - 계획을 본다.
EXPLAIN에서 큰 표를SCAN하거나 임시로 정렬하는 곳을 찾아요. - 고친다. 인덱스를 더하거나, 칸에 씌운 함수를 범위로 바꾸거나, N+1을 JOIN으로 바꾸거나, 필요한 칸만 꺼내요.
- 다시 잰다. 실제 크기의 데이터로 확인해요. 데이터 100행일 때 빠른 것은 아무것도 증명하지 않아요.
데이터베이스는 표의 통계(행 수, 값의 분포)를 보고 계획을 세워요. 데이터가 크게 바뀐 뒤 계획이 이상해지면 통계를 새로 모으는 명령(SQLite와 PostgreSQL의 ANALYZE)을 실행해 보세요.
실습
- 이메일 인덱스를 만들기 전과 후에
WHERE email = ?질문 1,000번이 걸리는 시간을 재서 몇 배 차이인지 확인해 보세요. - (도시, 가입일) 인덱스를 (가입일, 도시) 순서로 바꿔 만들면, 위의 다섯 질문 계획이 어떻게 달라지나요?
SELECT name, city FROM users WHERE email = ?가COVERING INDEX를 쓰게 하려면 어떤 인덱스가 필요할까요? 만들고 계획을 확인해 보세요.WHERE substr(joined, 1, 7) = '2026-11'의 계획을 보고, 인덱스를 쓰는 범위 조건으로 바꿔 써 보세요(가입일 인덱스가 필요해요).- N+1 예제를
WHERE id IN (...)한 번으로 바꿔 보세요. 문장 수는 몇 개가 되나요? - 키셋 페이지네이션을 이름순 목록에 적용하려면 무엇이 더 필요할까요? 이름이 같은 사람이 여럿이면 어떻게 하나요?
자주 하는 실수
- 느린 곳을 재지 않고 인덱스부터 단다. 쓰지 않는 인덱스는 공간과 쓰기 시간만 먹어요.
- 모든 칸에 인덱스를 단다. 쓰기가 느려지고 저장 공간이 늘어요.
- 여러 칸 인덱스의 순서를 생각하지 않는다. 왼쪽 칸 없이는 쓰이지 않아요.
- 칸에 함수를 씌워 비교한다. 인덱스를 못 써요. 칸은 그대로 두고 범위로 바꾸거나 식 인덱스를 만들어요.
- 외래 키 칸에 인덱스를 잊는다. JOIN과 삭제 확인이 표 전체를 훑어요.
- 반복문 안에서 질문을 보낸다. N+1이에요. JOIN이나
IN으로 한 번에 가져와요. - 깊은 페이지를
OFFSET으로 가져온다. 뒤로 갈수록 느려져요. 마지막 번호 기준으로 바꿔요. - 작은 데이터로만 시험한다. 실제 크기에서만 드러나는 문제가 대부분이에요.
점검 문제
- 정렬된 데이터에서 10억 개 중 하나를 찾을 때 반씩 줄여 가면 최악 몇 번이면 되나요?
- B-트리의 층이 얕은 이유는?
SCAN,SEARCH USING INDEX,USING COVERING INDEX는 각각 무엇을 뜻하나요?- (도시, 가입일) 인덱스가 "날짜만" 질문에 쓰이지 않는 이유는?
WHERE lower(email) = ?가 이메일 인덱스를 쓰지 못하는 이유와 두 가지 해결법은?- 인덱스를 많이 달면 치르는 대가 두 가지는?
- N+1 문제란 무엇이고, 서버형 데이터베이스에서 특히 느린 이유는?
OFFSET이 뒤쪽 페이지일수록 느린 이유와 대안은?
정답
- 30번이에요.
- 쪽 하나에 정렬된 값을 수백 개씩 담아서, 한 층 내려갈 때마다 범위가 수백분의 일로 줄어들기 때문이에요. 수백만 행도 서너 층이면 끝나요.
SCAN은 표 전체를 처음부터 훑기,SEARCH USING INDEX는 인덱스로 찾은 뒤 표에서 행을 꺼내기,USING COVERING INDEX는 필요한 칸이 모두 인덱스에 있어 표에 가지 않고 답하기예요.- 인덱스가 도시로 먼저 정렬되어 있어서, 같은 날짜가 도시마다 흩어져 있기 때문이에요. 왼쪽 칸 없이 오른쪽 칸만으로는 정렬된 순서를 쓸 수 없어요.
- 인덱스에는
email값이 정렬되어 있지lower(email)값이 정렬되어 있지 않기 때문이에요. 저장할 때부터 소문자로 바꿔 저장하거나,lower(email)로 식 인덱스를 만들어요. - 저장 공간이 늘고, 행을 넣고 고치고 지울 때마다 인덱스도 고쳐야 해서 쓰기가 느려져요.
- 목록을 한 번 묻고 항목마다 다시 하나씩 물어서 질문이 N+1번 가는 문제예요. 서버형 데이터베이스는 질문마다 네트워크 왕복 시간이 붙어서, 질문 수만큼 시간이 쌓이기 때문이에요.
- 건너뛸 행을 모두 읽고 버리기 때문이에요. 마지막으로 본 행의 키를 기억했다가
WHERE id > ?로 그다음부터 가져오는 키셋 방식을 써요.
다음 강의
지금까지는 표에 담는 관계형 데이터베이스를 다뤘어요. 하지만 세상에는 문서, 키와 값, 그래프처럼 다른 모양으로 담는 데이터베이스도 많아요. 다음 강의에서는 NoSQL이라고 불리는 저장소들이 무엇을 위해 생겼는지, 언제 관계형 데이터베이스 대신 고르면 좋은지 기준을 세워요.