SQL 인덱스와 EXPLAIN QUERY PLAN: 100만 행 실측

시리즈: SQL 강좌 4/6 (중급). SELECT·JOIN·GROUP BY 는 쓸 줄 알지만 "어느 날 갑자기 느려진 쿼리"를 만난 분을 위한 글입니다. 다 읽으면 EXPLAIN QUERY PLAN 을 읽고, 어떤 열에 어떤 순서로 인덱스를 만들지 정하고, 인덱스를 무력화하는 네 가지 작성법을 알아볼 수 있습니다.

서비스 데이터가 수십만 건을 넘어가면서 "마이페이지 주문 내역만 느려요"라는 문의가 들어오거나, 코드 리뷰에서 "그 컬럼 인덱스 걸려 있어요?"라는 질문을 받고 확인하는 방법을 몰랐다면 이 글이 도움이 됩니다. 인덱스는 SQL 성능에서 가장 효과가 큰 수단이지만 가장 틀리기 쉬운 수단이기도 합니다. 맞는 인덱스는 쿼리를 약 1,900배 빠르게 했고, 잘못된 인덱스는 다른 쿼리를 10배 느리게 했습니다. 아래 숫자는 모두 정해진 공식으로 만든 100만 행 테이블에서 직접 잰 것이라 누구나 같은 데이터를 다시 만들 수 있습니다.

실행 환경: SQLite 3.51.0 (sqlite3 CLI 와 Python 3.9.6 에 포함된 SQLite, 역시 3.51.0), Apple Silicon Mac (M1 Pro), macOS. 시간은 중앙값이고, 테이블 내용은 매번 같습니다.

100만 행 주문 테이블을 1초 만에 만들기

SQLite 의 random() 은 시드(난수의 씨앗)를 지정할 수 없어서, 선형 합동 생성기(이전 값 × 1103515245 + 12345 를 2³¹ 로 나눈 나머지라는 고정된 공식)를 재귀 CTE 로 돌려 데이터를 만듭니다. make_orders.sql 로 저장하세요.

-- 1,000,000 shop orders, deterministic (no random(): an LCG on the row number)
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status      TEXT    NOT NULL,
  amount      INTEGER NOT NULL,   -- yen
  email       TEXT    NOT NULL,
  created_at  TEXT    NOT NULL    -- 'YYYY-MM-DD HH:MM:SS'
);
WITH RECURSIVE n(i, r) AS (
  SELECT 1, 42
  UNION ALL
  SELECT i + 1, (r * 1103515245 + 12345) % 2147483648 FROM n WHERE i < 1000000
)
INSERT INTO orders (id, customer_id, status, amount, email, created_at)
SELECT i,
       1 + r % 50000,
       CASE (r / 7) % 20 WHEN 0 THEN 'cancelled' WHEN 1 THEN 'refunded'
                         WHEN 2 THEN 'pending' ELSE 'paid' END,
       100 + (r / 13) % 20000,
       'User' || (1 + r % 50000) || '@example.com',
       datetime('2025-01-01', '+' || ((r / 17) % 31536000) || ' seconds')
FROM n;

$ time sqlite3 shop.db < make_orders.sql
sqlite3 shop.db < make_orders.sql  0.73s user 0.08s system 99% cpu 0.824 total
$ sqlite3 shop.db "SELECT count(*), count(DISTINCT customer_id), min(created_at), max(created_at) FROM orders;"
1000000|50000|2025-01-01 00:00:00|2025-12-31 23:59:44

고객 5만 명, 2025년 1년치, 주문 100만 건이고 파일은 62.6MB 입니다. (금액 단위는 엔이지만 원으로 바꿔 읽어도 이야기는 같습니다. 재귀 CTE 자체는 6강에서 다룹니다.)

EXPLAIN QUERY PLAN 읽는 법: SCAN 과 SEARCH

인덱스는 하나 이상의 열을 정렬해 따로 보관한 색인이고, 각 항목은 원래 행(SQLite 에서는 rowid)을 가리킵니다. 정렬되어 있으니 모든 행을 읽지 않고 이진 탐색을 할 수 있습니다. EXPLAIN QUERY PLAN(이하 EQP)은 쿼리를 실행하지 않고 "어떻게 실행할 계획인지"를 DB 에 묻는 명령입니다.

sqlite> .timer on
sqlite> EXPLAIN QUERY PLAN SELECT count(*), sum(amount) FROM orders WHERE customer_id = 12345;
QUERY PLAN
`--SCAN orders
sqlite> SELECT count(*), sum(amount) FROM orders WHERE customer_id = 12345;
17|212448
Run Time: real 0.052 user 0.036612 sys 0.013766

SCAN orders 는 "테이블을 전부 읽는다"는 뜻입니다. 인덱스를 만들고 다시 물어봅니다.

sqlite> CREATE INDEX idx_orders_customer ON orders(customer_id);
sqlite> EXPLAIN QUERY PLAN SELECT count(*), sum(amount) FROM orders WHERE customer_id = 12345;
QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
sqlite> SELECT count(*), sum(amount) FROM orders WHERE customer_id = 12345;
17|212448
Run Time: real 0.000 user 0.000018 sys 0.000006

EQP 에서 봐야 할 표현은 다음과 같습니다.

네 번째 줄을 자주 오해합니다. "COVERING INDEX"라고 나와도 앞에 SCAN 이 붙으면 전부 읽는 것입니다.

CLI 의 .timer 는 1ms 단위라 너무 거칠어서, 이후 시간은 같은 SQLite 를 쓰는 Python 의 작은 측정 함수로 쟀습니다. 몇 번 미리 돌려 페이지 캐시를 데운 뒤 중앙값을 돌려줍니다.

import sqlite3, statistics, time
con = sqlite3.connect("shop.db")
def med(sql, params_list, reps=5):
    for p in params_list[:3]:
        con.execute(sql, p).fetchall()                    # warm-up
    ts = []
    for _ in range(reps):
        for p in params_list:
            t = time.perf_counter(); con.execute(sql, p).fetchall()
            ts.append((time.perf_counter() - t) * 1000)
    return statistics.median(ts)                          # ms

random.Random(42) 로 고른 고객 ID 20개 × 5회의 중앙값입니다.

생성 0.5초와 디스크 11MB 로 약 1,900배입니다.

복합 인덱스는 열 순서가 전부다

(customer_id, created_at) 복합 인덱스는 먼저 고객 순, 같은 고객 안에서는 날짜 순으로 정렬된 명부입니다. "고객 5 의 12월 이후"를 바로 찾습니다. (created_at, customer_id) 는 날짜가 먼저라서, 같은 질문에 대해 12월의 시작으로 건너뛴 뒤 12월의 모든 항목에서 고객을 확인할 수밖에 없습니다.

(customer_id, created_at):    0.010 ms   SEARCH orders USING INDEX i (customer_id=? AND created_at>?)
(created_at, customer_id):    3.219 ms   SEARCH orders USING INDEX i (created_at>?)

둘 다 SEARCH 이므로 "인덱스 탔네"에서 멈추면 안 됩니다. 괄호 안을 읽으면 두 번째는 created_at>? 만 씁니다. 원칙은 "= 비교 열이 앞, 범위·정렬 열이 뒤" 입니다.

복합 인덱스는 왼쪽부터의 일부(leftmost prefix)만으로도 쓰이므로, customer_id 단독 인덱스는 필요 없어집니다. idx_orders_customer 를 지운 뒤의 EQP:

sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM orders WHERE customer_id = 5;
`--SEARCH orders USING COVERING INDEX idx_orders_cust_created (customer_id=?)
sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM orders WHERE created_at >= '2025-12-01';
`--SCAN orders USING COVERING INDEX idx_orders_cust_created

두 번째는 첫 열을 건너뛰었으니 스캔입니다. 같은 인덱스가 정렬도 없애 줍니다.

-- index on (customer_id) only
SELECT id, amount, created_at FROM orders WHERE customer_id = 5 ORDER BY created_at DESC LIMIT 5;
|--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
`--USE TEMP B-TREE FOR ORDER BY
-- index on (customer_id, created_at)
`--SEARCH orders USING INDEX idx_orders_cust_created (customer_id=?)

헷갈리면 CLI 의 .expert(인덱스 추천 기능)에 물어볼 수 있습니다. 추천은 정확히 이 형태였습니다.

sqlite> .expert
sqlite> SELECT sum(amount) FROM orders WHERE customer_id = 12345 AND created_at >= '2025-06-01' ORDER BY created_at;
CREATE INDEX orders_idx_30ddf7e6 ON orders(customer_id, created_at);

SEARCH orders USING INDEX orders_idx_30ddf7e6 (customer_id=? AND created_at>?)

흔한 실수: 인덱스는 있는데 못 쓰는 작성법

아래는 모두 customer_id·email·created_at 에 인덱스가 있는 상태에서 실행했습니다.

1. 열을 함수로 감싸기. date(created_at) = '2025-03-01' 이라고 썼다. 실제로 일어나는 일: 인덱스는 created_at 순서로 정렬되어 있지 date(created_at) 순서가 아니므로 스캔이 되고 86.4ms. 해결: 원래 열에 대한 범위 조건으로 바꾸면 0.069ms.

-- SCAN orders USING COVERING INDEX idx_orders_created     86.407 ms
SELECT count(*) FROM orders WHERE date(created_at) = '2025-03-01';
-- SEARCH ... (created_at>? AND created_at<?)                0.069 ms
SELECT count(*) FROM orders WHERE created_at >= '2025-03-01' AND created_at < '2025-03-02';

함수가 꼭 필요하다면(이메일을 대소문자 무시로 찾는 경우 등) 식 자체에 인덱스를 만듭니다.

sqlite> SELECT count(*), sum(amount) FROM orders WHERE lower(email) = 'user123@example.com';
`--SCAN orders
Run Time: real 0.160 user 0.138400 sys 0.013989
sqlite> CREATE INDEX idx_orders_email_lower ON orders(lower(email));
sqlite> SELECT count(*), sum(amount) FROM orders WHERE lower(email) = 'user123@example.com';
`--SEARCH orders USING INDEX idx_orders_email_lower (<expr>=?)
Run Time: real 0.000 user 0.000098 sys 0.000058

2. 일반 인덱스에 LIKE. LIKE 'User123@%' 는 앞부분 일치처럼 보이지만 스캔이었습니다. SQLite 의 LIKE 는 기본적으로 ASCII 대소문자를 구분하지 않는데, 일반 인덱스는 구분하는 순서(BINARY 정렬 규칙)로 정렬되어 있어서 쓸 수 없습니다. 대소문자를 구분하는 GLOB 이나 COLLATE NOCASE 로 만든 인덱스라면 쓸 수 있습니다(쿼리·계획·.timer 실제 시간을 한 줄로 줄인 발췌).

WHERE email LIKE 'User123@%'             `--SCAN orders                                    0.081 s
WHERE email GLOB 'User123@*'             `--SEARCH ... idx_orders_email (email>? AND email<?)
-- after CREATE INDEX idx_orders_email_nocase ON orders(email COLLATE NOCASE);
WHERE email LIKE 'user123@%'             `--SEARCH ... idx_orders_email_nocase (email>? AND email<?)
WHERE email LIKE '%123@example.com'      `--SCAN orders                                    0.103 s

마지막 줄은 넘을 수 없는 벽입니다. % 로 시작하면 문자열 앞부분 기준으로 정렬된 B-tree 인덱스는 도움이 안 됩니다. 전문 검색(SQLite 라면 FTS5)이나 문자열을 뒤집은 열이 필요합니다. 게시판 "제목에 ○○ 포함" 검색이 대표적으로 여기에 해당합니다.

3. 열을 넘나드는 OR. customer_id = 7 OR email = 'User8@example.com' 은 양쪽 모두 인덱스가 있어 MULTI-INDEX OR. customer_id = 7 OR amount = 500 은 SCAN orders 였습니다. 한쪽에 인덱스가 없으면 조건 전체가 전체 스캔이 됩니다.

4. 복합 인덱스의 첫 열을 안 쓰기. 앞 절에서 본 그대로입니다.

반대로 SQLite 에서 깨지지 않는 경우: WHERE customer_id = '7' 처럼 문자열로 비교해도 SEARCH 였습니다. INTEGER 열의 타입 친화성(affinity)이 문자열을 숫자로 바꿔 주기 때문입니다. MySQL 에서는 문자열 열을 숫자와 비교하면 인덱스가 안 타는 유명한 함정이 있지만, 여기서는 실행하지 않았습니다.

인덱스 때문에 오히려 느려질 때

가장 의외였던 측정입니다. created_at 인덱스가 있는 상태에서 기간을 넓혀 가며 amount 를 합산했습니다. NOT INDEXED / INDEXED BY 로 각 방식을 강제했고 7회 중앙값입니다.

일반 인덱스는 일치하는 항목마다 amount 를 가져오려고 테이블을 뒤죽박죽 순서로 찾아갑니다. 테이블의 약 8%(1개월)에서 이미 전체를 순서대로 읽는 것보다 2배 느렸습니다. 게다가 SQLite 는 ANALYZE 이후에도 이 느린 계획을 스스로 골랐습니다.

sqlite> EXPLAIN QUERY PLAN SELECT count(*), sum(amount) FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2025-07-01';
`--SEARCH orders USING INDEX idx_orders_created (created_at>? AND created_at<?)
497165|5022432858
Run Time: real 1.186 user 0.536726 sys 0.474209
sqlite> ANALYZE;
sqlite> SELECT * FROM sqlite_stat1;
orders|idx_orders_created|1000000 1
-- EQP again: same SEARCH ... USING INDEX idx_orders_created
Run Time: real 1.071 user 0.497285 sys 0.439523

sqlite_stat1 은 "키 하나당 몇 행인지"만 저장하고 날짜 분포는 모르기 때문에, 6개월이 테이블의 절반이라는 걸 판단할 수 없습니다(이번 SQLite 는 표본을 저장하는 SQLITE_ENABLE_STAT4 없이 빌드되어 있었습니다). 해결책은 두 가지입니다. 인덱스를 커버링으로 만들거나(22.7ms), 단항 플러스로 이 조건에 인덱스를 쓰지 말라고 알려 줍니다.

sqlite> SELECT count(*), sum(amount) FROM orders WHERE +created_at >= '2025-01-01' AND +created_at < '2025-07-01';
`--SCAN orders
Run Time: real 0.095 user 0.077709 sys 0.013404

인덱스가 쓰기에 청구하는 비용

INSERT 할 때마다 모든 인덱스에도 삽입이 일어납니다. 100만 행 테이블에 10만 행을 트랜잭션 하나로 추가했습니다(각 3회).

인덱스 3개로 삽입이 약 8배 느려졌고 파일은 2배 넘게 커졌습니다. 비싼 것은 문자열 인덱스(email, created_at)입니다. "혹시 모르니 모든 열에"는 쓰기가 많은 테이블에서 역효과입니다.

어떤 인덱스를 만들까: 판단표

연습 문제

1. 어떤 화면이 SELECT id, amount, created_at FROM orders WHERE status = 'paid' AND customer_id = ? ORDER BY created_at DESC LIMIT 3 을 실행합니다. USE TEMP B-TREE 가 나오지 않는 계획을 만드는 인덱스 하나를 답하세요.
2. WHERE substr(email, 1, 7) = 'User123' 은 email 에 인덱스가 있어도 스캔합니다. 새 인덱스를 만들지 않고 인덱스를 쓰도록 바꿔 쓰세요.

정답

1. CREATE INDEX idx_orders_cust_status_created ON orders(customer_id, status, created_at); 등호 두 열을 앞에, 정렬 열을 마지막에 둡니다. 실제 계획은 SEARCH orders USING INDEX idx_orders_cust_status_created (customer_id=? AND status=?) 이고 임시 B-tree 가 없으며, 고객 5 의 최신 결제 완료 주문 3건이 이미 정렬된 채 돌아왔습니다.
2. WHERE email >= 'User123' AND email < 'User124'. User123 으로 시작하는 문자열은 모두 이 두 값 사이에 정렬됩니다. 둘 다 2,276행을 돌려줬고, 계획은 SCAN … USING COVERING INDEX idx_orders_email 에서 SEARCH … (email>? AND email<?) 로 바뀌었습니다.

솔직한 한계와 버전 메모

• 실행한 것은 SQLite 3.51.0 뿐입니다. EQP 의 트리 모양 출력(`--)은 3.24 부터이고, 그 이전 버전은 평평한 표로 출력합니다. .expert 는 CLI 기능이라 최소 구성 빌드에는 없을 수 있습니다.
• 식 인덱스는 SQLite 3.9 이상입니다. NOT INDEXED·INDEXED BY·단항 플러스는 SQLite 전용이며, MySQL 에는 USE INDEX / IGNORE INDEX 가 있고 PostgreSQL 에는 표준 인덱스 힌트가 없습니다(여기서는 실행하지 않음).
• PostgreSQL 과 MySQL 은 히스토그램을 가지므로 6개월 사례에서 일반 인덱스를 피할 가능성이 높지만 시험하지 않았습니다. 약 8% 라는 분기점도 행 너비·캐시·저장 장치에 따라 달라지니 자기 테이블에서 재 보세요.
• 데이터는 합성입니다. 선형 합동 생성기는 좋은 난수가 아니고 같은 고객의 금액이 반복됩니다. 인덱스 동작에는 영향이 없지만 통계 용도로는 쓰지 마세요.
• 시간은 다른 작업도 돌고 있던 한 대의 기계에서 잰 중앙값입니다. 비율은 믿어도 되고, 밀리초 절댓값은 참고치입니다.

인덱스만큼 중요한 파라미터 바인딩 쿼리는 PDO + SQLite 와 SQL 인젝션을, 학습 전체에서 SQL 의 위치는 프로그래밍 학습 로드맵을 참고하세요.

이전: 3강: JOIN·GROUP BY·서브쿼리 · 다음: 5강: 트랜잭션·격리 수준·잠금