数据库原理与选型 / 系统课时
使用 EXPLAIN QUERY PLAN
使用 EXPLAIN QUERY PLAN
EXPLAIN QUERY PLAN 用来观察 SQLite 打算怎样访问表和索引。本文通过同一组查询辨认 SCAN、SEARCH、覆盖索引和临时 B-tree,并把计划中的信号转换成可验证的索引优化动作。
学习目标
- 能读懂查询计划中常见的访问方式
- 能判断查询是否使用覆盖索引或额外排序
- 能根据计划提出并验证最小化优化方案
先读懂四类关键信号
EXPLAIN QUERY PLAN 最重要的是 detail 列。它描述访问对象、使用的索引以及部分约束方式,足以回答“有没有全扫”“用了哪个索引”“是否需要临时排序”等问题。
| 关键词 | 含义 | 常见判断 |
|---|---|---|
SCAN |
顺序扫描表或索引 | 大表高频查询需确认是否合理 |
SEARCH |
按索引约束定位部分记录 | 通常比无条件全表扫描更有针对性 |
COVERING INDEX |
所需列都能从索引取得 | 可省去回表读取 |
USE TEMP B-TREE |
为排序、分组或去重建立临时结构 | 可能存在可优化的排序成本 |
SCAN 不等于错误,小表、必须读取大部分行的查询或合适的索引顺序扫描都可能合理;同样,看到 SEARCH 也不能直接证明查询已经足够快。
用同一组查询观察计划
下面先创建订单表和一个复合索引,再观察四种典型计划。示例只关注访问方式,不依赖具体的查询计划节点编号。
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
status TEXT NOT NULL,
amount REAL NOT NULL,
created_at TEXT NOT NULL
);
INSERT INTO orders (customer_id, status, amount, created_at) VALUES
(1, 'paid', 120.0, '2026-09-01 09:00:00'),
(1, 'paid', 80.0, '2026-09-01 10:00:00'),
(1, 'pending', 200.0, '2026-09-01 11:00:00'),
(2, 'paid', 150.0, '2026-09-01 12:00:00');
CREATE INDEX idx_orders_customer_status_created
ON orders(customer_id, status, created_at);
EXPLAIN QUERY PLAN
SELECT id, amount
FROM orders
WHERE amount > 100;
EXPLAIN QUERY PLAN
SELECT id, created_at
FROM orders
WHERE customer_id = 1 AND status = 'paid';
EXPLAIN QUERY PLAN
SELECT id, amount
FROM orders
WHERE customer_id = 1 AND status = 'paid';
EXPLAIN QUERY PLAN
SELECT id, amount
FROM orders
ORDER BY amount DESC;预期输出
下面只摘录 detail 列;实际客户端可能附带节点编号或树形前缀。
SCAN orders
SEARCH orders USING COVERING INDEX idx_orders_customer_status_created (customer_id=? AND status=?)
SEARCH orders USING INDEX idx_orders_customer_status_created (customer_id=? AND status=?)
SCAN orders
USE TEMP B-TREE FOR ORDER BY第一条没有 amount 索引,只能扫描。第二条只读取 id 和 created_at,而这里的 id INTEGER PRIMARY KEY 是 rowid 的别名,普通二级索引同时保存 rowid,因此现有索引即可覆盖结果。第三条还要读取 amount,需要根据索引找到记录后再访问表。第四条既无可用于排序的索引又要按 amount 排序,所以额外出现临时 B-tree。
把计划转换为优化动作
如果“按金额倒序取订单”是高频路径,可以增加与排序一致的索引,再重新查看计划。优化必须以真实业务频率为前提,不能因为看到临时 B-tree 就无条件建索引。
CREATE INDEX idx_orders_amount_desc
ON orders(amount DESC);
EXPLAIN QUERY PLAN
SELECT id, amount
FROM orders
ORDER BY amount DESC;预期输出
SCAN orders USING COVERING INDEX idx_orders_amount_desc这次仍然出现 SCAN,但扫描的是已经按目标顺序组织的索引,不再需要 USE TEMP B-TREE FOR ORDER BY。因此判断计划时要看完整描述,而不是只盯某一个单词。
结合统计信息和真实耗时判断
查询计划是优化器的执行选择,不是性能计时器。它不会告诉你实际读取了多少页、耗时多少毫秒,也不能替代真实数据量下的基准测试。数据分布变化较大时,可在维护窗口执行 ANALYZE 更新统计信息,再重新比较计划。
SQLite 不保证 EXPLAIN QUERY PLAN 的文本格式在不同版本间完全稳定,因此自动化系统不应依赖固定节点编号或整行字符串解析。人工排查更应关注访问对象、索引名、约束条件以及是否出现临时结构。
按固定顺序排查慢查询
- 先确认计划中的
SCAN是否发生在大表和高频路径上,并判断查询本身是否必须读取大量数据。 - 再检查
SEARCH使用的索引是否匹配真实过滤条件,尤其是复合索引的列顺序。 - 看到
USE TEMP B-TREE FOR ORDER BY、GROUP BY或DISTINCT时,判断能否通过更合适的索引顺序消除临时结构。 - 对极高频查询再考虑覆盖索引,避免为了少一次回表而制造过宽索引和更高写入成本。
- 修改索引后重新执行同一条
EXPLAIN QUERY PLAN,并在接近生产的数据量上比较实际耗时。
能稳定复现“计划变化”和“耗时改善”才算完成优化;只看到 SEARCH 或索引名称出现,并不足以证明问题已经解决。