SQLite EXPLAIN QUERY PLAN：读懂输出，找出慢查询

EXPLAIN QUERY PLAN 告诉你查询会怎么执行

在优化慢查询之前，你需要知道 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 表上做的是 SEARCH（而不是扫描），使用的是为 email 自动创建的唯一索引，查找键是 email。正是你希望看到的结果。

SCAN 与 SEARCH：首先要看的东西

执行计划中的每一行都以 SCAN 或 SEARCH 开头。这个区别是整个输出中最重要的信号。

• 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 会读取每一行。在小表上你察觉不到差别；在百万行的表上，这就是毫秒和秒的区别。

SCAN 并不总是错的。对于很小的查找表，或者确实会返回大部分行的查询，扫描就是正确的计划。但在带有高选择性过滤条件的大表上，看到 SCAN 就该考虑加索引了。

确认索引被用上了

要找的关键短语是 USING INDEX <name>（或 USING COVERING INDEX <name>，下面会讲）。如果你建了一个索引，希望查询规划器会用它，可以这样检查：

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 根本不用读底层的表。对于查找操作，覆盖索引是你能得到的最快的计划，在决定哪些列一起建索引时值得考虑。

阅读连接的执行计划

连接是执行计划变得有意思的地方。计划中的每一行对应连接中的一张表，行的顺序就是 SQLite 访问这些表的顺序。第一张表是外层表；后面的表会针对外层的每一行各查找一次。

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，没有全表扫描，这正是你想要的。

如果第二行显示的是 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。如果你在调试引擎本身，这很有用；做查询优化时几乎用不上。

EXPLAIN QUERY PLAN 才是你真正需要的易读摘要。拿不准的时候，就用 EXPLAIN QUERY PLAN。

优化慢查询的流程

当一个查询很慢时，可以按这样的循环来处理：

1. 对它运行 EXPLAIN QUERY PLAN。
2. 对每一行表信息问一句：这是 SCAN 还是 SEARCH？在大表上，SCAN 就是嫌疑对象。
3. 如果某个 SCAN 在按某一列过滤，考虑给这一列加索引。
4. 对于连接，确认内层循环的表在连接列上使用了 SEARCH USING INDEX。
5. 加完索引后重新运行 EXPLAIN QUERY PLAN。计划应该会变化。如果没有变，说明规划器认为这个索引不值得用，通常是因为表太小，或者过滤条件的选择性不够。

第 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 描述的是策略，而不是代价。它不会告诉你查询花了 800 毫秒，或者返回了 50,000 行。要知道这些，你需要计时（命令行工具中的 .timer on）和行数统计。执行计划和计时是互补的：计划告诉你查询为什么慢，计时告诉你它是不是真的慢。

还有两个值得了解的局限：

• 执行计划会随着数据增长而变化。一个在 100 行的表上扫描得很愉快的查询，当表增长到一百万行时就需要索引了。要在生产规模的数据上重新检查计划，而不是在开发用的测试数据上。
• 查询规划器使用 ANALYZE 收集的统计信息。没有统计信息时，它会退回到默认值，而默认值并不总是好的。统计信息过期或缺失，是出现意外计划的常见原因。

下一步：ANALYZE 和 VACUUM

查询规划器根据表和索引的统计信息做出决策。如果这些统计信息缺失或过期，即使索引设计得再完美，也可能得到糟糕的计划。ANALYZE 就是让统计信息保持最新的方法；而 VACUUM 是与之配套的命令，用来回收空间、整理数据库文件的碎片。这就是下一页的内容。