数据库原理与选型 / 系统课时

使用 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 也不能直接证明查询已经足够快。

用同一组查询观察计划

下面先创建订单表和一个复合索引,再观察四种典型计划。示例只关注访问方式,不依赖具体的查询计划节点编号。

sql
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 列;实际客户端可能附带节点编号或树形前缀。

text
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 就无条件建索引。

sql
CREATE INDEX idx_orders_amount_desc
ON orders(amount DESC);

EXPLAIN QUERY PLAN
SELECT id, amount
FROM orders
ORDER BY amount DESC;

预期输出

text
SCAN orders USING COVERING INDEX idx_orders_amount_desc

这次仍然出现 SCAN,但扫描的是已经按目标顺序组织的索引,不再需要 USE TEMP B-TREE FOR ORDER BY。因此判断计划时要看完整描述,而不是只盯某一个单词。

结合统计信息和真实耗时判断

查询计划是优化器的执行选择,不是性能计时器。它不会告诉你实际读取了多少页、耗时多少毫秒,也不能替代真实数据量下的基准测试。数据分布变化较大时,可在维护窗口执行 ANALYZE 更新统计信息,再重新比较计划。

SQLite 不保证 EXPLAIN QUERY PLAN 的文本格式在不同版本间完全稳定,因此自动化系统不应依赖固定节点编号或整行字符串解析。人工排查更应关注访问对象、索引名、约束条件以及是否出现临时结构。

按固定顺序排查慢查询

  1. 先确认计划中的 SCAN 是否发生在大表和高频路径上,并判断查询本身是否必须读取大量数据。
  2. 再检查 SEARCH 使用的索引是否匹配真实过滤条件,尤其是复合索引的列顺序。
  3. 看到 USE TEMP B-TREE FOR ORDER BY、GROUP BY 或 DISTINCT 时,判断能否通过更合适的索引顺序消除临时结构。
  4. 对极高频查询再考虑覆盖索引,避免为了少一次回表而制造过宽索引和更高写入成本。
  5. 修改索引后重新执行同一条 EXPLAIN QUERY PLAN,并在接近生产的数据量上比较实际耗时。

能稳定复现“计划变化”和“耗时改善”才算完成优化;只看到 SEARCH 或索引名称出现,并不足以证明问题已经解决。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

使用 EXPLAIN QUERY PLAN

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通