Kodokon kodokon.com

EXPLAIN 与查询计划：读懂数据库实际做了什么

借助 EXPLAIN QUERY PLAN，解读 SQLite 规划器真正为你的查询选择的执行计划。

10 分钟 · 3 题

在 Kodokon 中打开本课

SQL 是声明式的：你描述想要的结果，而不是获取它的方式。决定如何读取数据的是查询规划器。为了观察它的工作，SQLite 提供了两个命令：EXPLAIN QUERY PLAN 给出一个可读的高层计划，而单独的 EXPLAIN 则会转储 VDBE 虚拟机的字节码。安装 sqlite3 命令行工具，或者在浏览器中打开 sqliteonline.com，把接下来的每一个例子都重现一遍。

SQL
CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  salary INTEGER NOT NULL
);

INSERT INTO departments (id, name) VALUES
  (1, 'Engineering'),
  (2, 'Sales');

INSERT INTO employees
  (id, name, department_id, salary)
VALUES
  (1, 'Ada',    1, 65000),
  (2, 'Grace',  1, 72000),
  (3, 'Alan',   2, 58000);
在其他一切之前需要先创建的数据集。

在任意 SELECT 查询前面加上 EXPLAIN QUERY PLAN。结果的每一行都描述了一张表是如何被读取的。有两个关键词占据主导地位：SCAN 表示该表被完整地逐行扫描，而 SEARCH 则表示一次由索引引导的定向访问。如果没有合适的索引，即便是一个简单的相等比较，也已经会触发一次全表扫描。

SQL
EXPLAIN QUERY PLAN
SELECT * FROM employees
WHERE department_id = 1;
-- Result: SCAN employees
-- (no index: the whole table is read)
没有索引时，规划器会扫描整张表。

为被过滤的列创建一个索引，然后重新运行完全相同的查询：计划就从 SCAN 切换成了 SEARCH ... USING INDEX。其他一些标注也很有价值：USE TEMP B-TREE 揭示了一次在内存中物化的排序或分组（往往说明缺少一个用于排序的索引），而 USING COVERING INDEX 则表明连表本身都没有被打开。

SQL
CREATE INDEX idx_emp_dept
  ON employees (department_id);

EXPLAIN QUERY PLAN
SELECT * FROM employees
WHERE department_id = 1;
-- SEARCH employees USING INDEX
-- idx_emp_dept (department_id=?)
同一个 SELECT，但现在由索引引导。

知识检测

确认你已牢记本课的重点内容。

  1. 在 SQLite 中，与单独的 EXPLAIN 相比，EXPLAIN QUERY PLAN 返回什么？
    • VDBE 字节码，逐条指令列出
    • 一个高层计划（SCAN、SEARCH、临时排序）
    • 查询实际测得的执行时间
    • 表上已存在的索引列表
  2. 在 SQLite 的计划中，SCAN employees 这一行意味着什么？
    • 该表被完整地逐行扫描
    • 使用了一个覆盖索引
    • 通过索引进行了一次定向搜索
    • 该表被加载进了内存缓存
  3. ANALYZE 命令用来做什么？
    • 它重建数据库的所有索引
    • 它把统计信息收集进 sqlite_stat1 以引导规划器
    • 它执行查询并测量耗时
    • 它在分析期间锁住表