실행 가능한 SQLite 문서: EXPLAIN QUERY PLAN

Q: 출력에 나오는 SCAN과 SEARCH는 어떻게 다른가요?

SCAN은 테이블이나 인덱스를 처음부터 끝까지 전부 훑는다는 뜻입니다. 작은 테이블이라면 문제없지만, 데이터가 많아지면 그만큼 느려집니다. 반대로 SEARCH는 인덱스나 기본 키를 이용해 일치하는 행으로 바로 점프하는 방식이라 훨씬 빠릅니다. 큰 테이블에서 필터링 컬럼은 거의 항상 SEARCH로 풀려야 정상입니다.

Q: 내 쿼리가 인덱스를 타고 있는지 어떻게 확인하나요?

쿼리에 EXPLAIN QUERY PLAN을 붙여 실행한 뒤, 출력에 USING INDEX 또는 USING COVERING INDEX 이 보이는지 확인하면 됩니다. 인덱스 언급 없이 SCAN 만 떠 있다면 풀 테이블 스캔을 하고 있다는 뜻이고, 이 경우엔 적절한 인덱스를 추가하면 거의 확실히 빨라집니다.

Q: EXPLAIN과 EXPLAIN QUERY PLAN의 차이는 뭔가요?

EXPLAIN은 SQLite가 생성한 가상 머신 바이트코드를 그대로 보여줍니다. 엔진 내부를 분석할 때나 쓰지, 일반적인 쿼리 튜닝에는 별로 도움이 되지 않습니다. 반면 EXPLAIN QUERY PLAN은 테이블 접근 방식과 인덱스 사용 여부를 사람이 읽기 좋은 형태로 요약해 줍니다. 성능 튜닝 목적이라면 EXPLAIN QUERY PLAN을 쓰는 게 맞습니다.

EXPLAIN QUERY PLAN으로 SQLite 실행 계획 확인하기

느린 쿼리를 손보기 전에, SQLite가 실제로 어떻게 동작하는지부터 들여다봐야 합니다. EXPLAIN QUERY PLAN은 옵티마이저가 선택한 전략을 짧게 요약해서 보여줍니다. 어떤 테이블을 어떤 순서로 읽는지, 인덱스를 쓰는지 안 쓰는지까지 한눈에 드러나죠. 쿼리가 실제로 실행되는 건 아니고, 말 그대로 실행 계획만 받아볼 수 있습니다.

아무 쿼리 앞에 이 키워드만 붙이면 됩니다:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    country TEXT
);

INSERT INTO users (email, country) VALUES
    ('a@x.com', 'US'),
    ('b@x.com', 'UK'),
    ('c@x.com', 'US');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'a@x.com';

출력 결과는 대략 이런 모양입니다.

QUERY PLAN
`--SEARCH users USING INDEX sqlite_autoindex_users_1 (email=?)

이 한 줄에 꽤 많은 정보가 담겨 있습니다. SQLite는 users 테이블에 대해 SCAN이 아닌 SEARCH를 수행하고 있고, email 컬럼에 자동 생성된 유니크 인덱스를 사용하며, 조회 키는 email입니다. 딱 기대했던 그림이죠.

SCAN vs SEARCH: 실행 계획에서 가장 먼저 봐야 할 것

쿼리 플랜의 모든 줄은 SCAN 아니면 SEARCH로 시작합니다. 이 둘을 구분하는 것이 EXPLAIN QUERY PLAN 출력에서 가장 중요한 신호입니다.

SCAN <table> — SQLite가 테이블의 모든 행(또는 인덱스의 모든 엔트리)을 훑습니다. 비용은 테이블 크기에 비례해서 커집니다.
SEARCH <table> USING ... — SQLite가 인덱스나 기본 키를 통해 일치하는 행으로 바로 점프합니다. 비용은 테이블 크기가 아니라 결과 집합의 크기에 비례합니다.

비교를 위해 한쪽 컬럼에는 인덱스를 걸고, 다른 한쪽에는 걸지 않은 예시를 보겠습니다.

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    status TEXT
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

INSERT INTO orders (customer_id, status) VALUES
    (1, 'paid'), (2, 'pending'), (1, 'paid'), (3, 'shipped');

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'paid';

첫 번째 플랜에는 SEARCH orders USING INDEX idx_orders_customer라고 찍히고, 두 번째 플랜에는 SCAN orders라고 나옵니다. status 컬럼에 인덱스가 없으니 SQLite가 모든 행을 훑는 거죠. 행 수가 적은 테이블에서는 티가 안 나지만, 100만 행짜리 테이블이라면 밀리초와 초 단위의 차이로 벌어집니다.

그렇다고 SCAN이 무조건 나쁜 건 아닙니다. 작은 룩업 테이블이나 실제로 대부분의 행을 돌려줘야 하는 쿼리라면 스캔이 오히려 정답입니다. 다만 큰 테이블에서 선택도 높은 필터를 걸었는데도 SCAN이 나온다면, 그건 인덱스를 추가하라는 신호로 보면 됩니다.

sqlite 인덱스 사용 확인하기

확인할 키워드는 USING INDEX <이름>입니다(아래에서 다룰 USING COVERING INDEX <이름>도 마찬가지). 인덱스를 만들어 두고 옵티마이저가 잘 골라 쓰는지 궁금하다면, 이렇게 확인하면 됩니다:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    created_at TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42;

SEARCH events USING INDEX idx_events_user (user_id=?) 같은 결과가 나오면 정상입니다. 만약 SCAN events로 나온다면 옵티마이저가 인덱스를 못 쓰고 있다는 뜻인데요, 흔한 원인은 컬럼을 함수로 감싸는 경우(WHERE lower(user_id) = ...), 타입이 서로 다른 값과 비교하는 경우, 그리고 LIKE '%foo%'처럼 와일드카드를 앞에 붙여서 검색하는 경우입니다.

간단히 확인해 봅시다:

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

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id + 0 = 42;

+ 0을 붙이는 순간 인덱스가 무력화돼서 실행 계획이 SCAN events로 떨어집니다. 인덱스 컬럼에 어떤 식으로든 표현식을 씌우면 결과는 똑같습니다.

커버링 인덱스는 실행 계획에 다르게 표시됩니다

쿼리에 필요한 모든 컬럼이 인덱스 안에 들어 있으면, SQLite는 테이블을 들여다볼 필요 없이 인덱스만으로 결과를 만들어 낼 수 있습니다. 이때 실행 계획에는 USING COVERING INDEX라고 찍힙니다:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT,
    name TEXT,
    price REAL
);

CREATE INDEX idx_products_sku_price ON products(sku, price);

EXPLAIN QUERY PLAN
SELECT price FROM products WHERE sku = 'ABC-123';

실행 계획을 보면 SEARCH products USING COVERING INDEX idx_products_sku_price (sku=?). 쿼리는 price를 요청했고, 인덱스에 이미 sku와 price가 함께 들어 있으니 SQLite는 원본 테이블을 아예 읽지 않습니다. 커버링 인덱스(covering index) 는 단일 조회에서 얻을 수 있는 가장 빠른 실행 계획이고, 어떤 컬럼들을 묶어서 인덱스로 만들지 결정할 때 꼭 알아둘 가치가 있는 기법입니다.

조인 실행 계획 읽는 법

조인이 들어가면 실행 계획이 본격적으로 재미있어집니다. 플랜의 각 줄이 조인에 참여하는 테이블 하나에 대응하고, 줄의 순서가 곧 SQLite가 테이블을 방문하는 순서입니다. 맨 처음 줄에 나오는 테이블이 바깥쪽(outer) 테이블이고, 이후 테이블들은 바깥쪽 테이블의 행 하나마다 한 번씩 조회됩니다.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 7;

일반적인 실행 계획의 예시는 다음과 같습니다:

QUERY PLAN
|--SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH o USING INDEX idx_orders_customer (customer_id=?)

위에서부터 차례대로 읽으면 됩니다. SQLite는 먼저 기본 키로 해당 고객 한 명을 찾고, 그 고객에 대해 customer_id 인덱스를 타고 매칭되는 주문을 조회합니다. 두 줄 모두 SEARCH로 표시되어 있고 풀 스캔이 없죠. 우리가 원하던 그림입니다.

만약 두 번째 줄이 SEARCH가 아니라 SCAN o로 나왔다면, 고객을 한 명 찾을 때마다 orders 테이블 전체를 훑게 됩니다. 테이블이 커질수록 치명적인 성능 저하로 이어지죠. 해법은 거의 항상 동일합니다. 조인 컬럼에 인덱스를 걸어 주세요.

복합 쿼리와 서브쿼리 실행 계획

UNION, EXCEPT, 서브쿼리의 실행 계획은 중첩 구조로 표시됩니다. 각 분기가 부모 아래에 들여쓰기된 형태로 나타나죠:

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT x FROM a
UNION ALL
SELECT x FROM b;

COMPOUND QUERY 노드 아래에 각 분기별로 자식 행이 두 개 표시됩니다. 서브쿼리나 CTE도 마찬가지인데, 각각 들여쓰기된 플랜 노드로 따로 잡히기 때문에 똑같이 SCAN이냐 SEARCH냐를 기준으로 읽어주면 됩니다.

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT * FROM a
WHERE x IN (SELECT x FROM b WHERE x > 10);

서브쿼리는 별도의 플랜 노드("LIST SUBQUERY" 같은 형태)로 표시되며, 자체적인 접근 전략을 가집니다. 모든 레벨에서 동일한 체크를 반복해서 적용하면 됩니다.

EXPLAIN과 EXPLAIN QUERY PLAN의 차이

이 둘은 완전히 다른 도구인데, 헷갈려서 섞어 쓰는 분들이 많습니다.

CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT);

EXPLAIN
SELECT * FROM t WHERE id = 1;

EXPLAIN만 단독으로 쓰면 (QUERY PLAN 없이) SQLite 가상 머신이 실행할 바이트코드가 그대로 쏟아집니다. OpenRead, SeekRowid, Column, ResultRow 같은 저수준 opcode가 수십 줄씩 나오죠. 엔진 자체를 디버깅할 때나 쓸모 있지, 쿼리 튜닝 용도로는 거의 도움이 안 됩니다.

반면 EXPLAIN QUERY PLAN은 사람이 읽으라고 만들어진 요약본이고, 우리가 실제로 원하는 게 바로 이겁니다. 헷갈릴 땐 무조건 EXPLAIN QUERY PLAN부터 찾으세요.

느린 쿼리를 다루는 작업 흐름

쿼리가 느릴 때는 보통 다음 순서로 접근합니다.

해당 쿼리에 EXPLAIN QUERY PLAN을 돌려본다.
테이블별로 출력된 줄을 보면서 SCAN인지 SEARCH인지 확인한다. 큰 테이블에서 SCAN이 보이면 일단 의심 대상이다.
SCAN이 특정 컬럼으로 필터링하고 있다면, 그 컬럼에 인덱스를 걸 수 있는지 검토한다.
조인 쿼리라면, 안쪽 루프에 해당하는 테이블들이 조인 컬럼으로 SEARCH USING INDEX를 타고 있는지 확인한다.
인덱스를 추가한 뒤 EXPLAIN QUERY PLAN을 다시 돌려본다. 실행 계획이 바뀌어야 정상이다. 안 바뀌었다면 플래너가 그 인덱스를 쓸 가치가 없다고 판단한 것이다 — 보통 테이블이 너무 작거나 필터의 선택도(selectivity)가 낮을 때 그렇다.

5번 단계를 직접 보여주는 예제는 다음과 같습니다.

CREATE TABLE logs (
    id INTEGER PRIMARY KEY,
    level TEXT,
    message TEXT
);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SCAN logs

CREATE INDEX idx_logs_level ON logs(level);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SEARCH logs USING INDEX idx_logs_level (level=?)

실행 계획이 SCAN에서 SEARCH로 바뀌었습니다. 인덱스가 제대로 동작하고 있다는 신호죠. (단, 테이블이 비어 있거나 데이터가 거의 없을 때는 플래너가 여전히 스캔을 선택할 수 있습니다. 인덱스를 쓸 만큼 데이터가 충분하지 않다고 판단하기 때문인데요, 데이터를 채우거나 ANALYZE를 돌리면 대개 선택이 바뀝니다.)

실행 계획이 알려주지 않는 것

EXPLAIN QUERY PLAN은 _전략_을 보여줄 뿐, _비용_은 알려주지 않습니다. 쿼리가 800ms 걸렸는지, 결과가 5만 행이었는지는 여기서 알 수 없죠. 그건 실행 시간(CLI에서 .timer on)과 행 수로 따로 확인해야 합니다. 실행 계획과 타이머는 서로 보완 관계입니다. 플랜은 쿼리가 왜 느린지를, 타이머는 실제로 느린지를 알려줍니다.

알아두면 좋은 두 가지 한계가 더 있습니다:

데이터가 늘어나면 플랜도 바뀝니다. 100행짜리 테이블에서는 풀 스캔으로도 충분했던 쿼리가, 100만 행이 되면 인덱스가 필요해집니다. 개발용 더미 데이터가 아니라 운영 규모의 데이터에서 실행 계획을 다시 확인하세요.
플래너는 ANALYZE가 수집한 통계에 의존합니다. 통계가 없으면 기본값으로 추정하는데, 이게 항상 좋은 선택은 아닙니다. 통계가 오래됐거나 아예 없으면 예상 밖의 플랜이 나오는 흔한 원인이 됩니다.

다음 주제: ANALYZE와 VACUUM

쿼리 플래너의 모든 판단은 테이블과 인덱스의 통계에 기반합니다. 통계가 누락되거나 낡았다면, 인덱스를 아무리 잘 걸어둬도 엉뚱한 플랜이 나올 수 있습니다. ANALYZE는 이 통계를 최신 상태로 유지하는 명령이고, VACUUM은 데이터베이스 파일의 빈 공간을 회수하고 단편화를 정리해주는 짝꿍 명령입니다. 다음 글에서 이어서 다뤄보겠습니다.

자주 묻는 질문

SQLite의 EXPLAIN QUERY PLAN은 어떤 역할을 하나요?

쿼리를 실제로 실행하지 않고, SQLite가 그 쿼리를 어떻게 처리할지 미리 보여주는 명령입니다. 어떤 테이블을 읽는지, 어떤 인덱스를 쓰는지, 조인 순서는 어떻게 잡히는지가 출력됩니다. SELECT, INSERT, UPDATE, DELETE 앞에 EXPLAIN QUERY PLAN만 붙이면 바로 확인할 수 있습니다.

출력에 나오는 SCAN과 SEARCH는 어떻게 다른가요?

SCAN은 테이블이나 인덱스를 처음부터 끝까지 전부 훑는다는 뜻입니다. 작은 테이블이라면 문제없지만, 데이터가 많아지면 그만큼 느려집니다. 반대로 SEARCH는 인덱스나 기본 키를 이용해 일치하는 행으로 바로 점프하는 방식이라 훨씬 빠릅니다. 큰 테이블에서 필터링 컬럼은 거의 항상 SEARCH로 풀려야 정상입니다.

내 쿼리가 인덱스를 타고 있는지 어떻게 확인하나요?

쿼리에 EXPLAIN QUERY PLAN을 붙여 실행한 뒤, 출력에 USING INDEX <이름> 또는 USING COVERING INDEX <이름>이 보이는지 확인하면 됩니다. 인덱스 언급 없이 SCAN <테이블>만 떠 있다면 풀 테이블 스캔을 하고 있다는 뜻이고, 이 경우엔 적절한 인덱스를 추가하면 거의 확실히 빨라집니다.

EXPLAIN과 EXPLAIN QUERY PLAN의 차이는 뭔가요?

EXPLAIN은 SQLite가 생성한 가상 머신 바이트코드를 그대로 보여줍니다. 엔진 내부를 분석할 때나 쓰지, 일반적인 쿼리 튜닝에는 별로 도움이 되지 않습니다. 반면 EXPLAIN QUERY PLAN은 테이블 접근 방식과 인덱스 사용 여부를 사람이 읽기 좋은 형태로 요약해 줍니다. 성능 튜닝 목적이라면 EXPLAIN QUERY PLAN을 쓰는 게 맞습니다.

SQLite EXPLAIN QUERY PLAN으로 느린 쿼리 잡는 법