查询计划基础：SQLite 查询计划概述、EXPLAIN QUERY PLAN 输出、扫描方式

概念与原理

SQLite 在执行 SQL 查询前会生成一个查询计划（Query Plan），该计划描述了如何访问表、使用哪些索引、以什么顺序执行连接等操作。查询计划由优化器根据表统计信息、索引可用性和查询条件综合决定。理解查询计划是诊断性能瓶颈、正确使用索引的第一步。

查询计划可以看作是一棵操作符树，每个节点代表一个基本操作（如扫描、排序、连接）。叶子节点通常是数据访问操作，即从表中读取行的方式。数据访问方式决定了磁盘 I/O 和 CPU 开销，是整个查询性能的核心。

EXPLAIN QUERY PLAN 命令

SQLite 提供 EXPLAIN QUERY PLAN 命令来输出查询计划的文本表示，用于调试和优化。该命令不会实际执行查询，只生成计划描述。

-- 基本语法
EXPLAIN QUERY PLAN SELECT ...;

-- 示例：查询 orders 表，id 为主键
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE id = 1001;

-- 输出示例：
-- QUERY PLAN
-- |--SEARCH orders USING INDEX sqlite_autoindex_orders_1 (id=?)

输出中的关键词含义：

  • SCAN：全表扫描（遍历整个表）
  • SEARCH：使用索引或表的主键进行范围/等值查找
  • USING INDEX：使用的索引名称
  • USING COVERING INDEX：覆盖索引扫描（索引已经包含所有需要返回的列，无需回表）
  • ~（波浪线后的数字）：估计的行数或百分比
实践提示：开发阶段频繁使用 EXPLAIN QUERY PLAN 检查是否命中期望的索引。发现 SCAN 操作符出现在大表上时，通常意味着缺少合适的索引。

三种扫描方式详解

根据索引的使用程度，SQLite 的叶子节点访问策略分为以下三种：

扫描方式 含义 I/O 代价 适用场景 EXPLAIN 关键词
全表扫描 顺序读取表中每一行，逐行检查条件 ≈ 表的总页数 表很小、无索引可用、必须返回大部分行 SCAN table
索引扫描 先遍历索引树找到满足条件的行 ID，再根据行 ID 回表读取完整数据行 ≈ 索引树深度 + 结果行数 × 回表代价 查询条件在索引列上，且需要访问非索引列 SEARCH table USING INDEX
覆盖索引扫描 索引中包含所有查询所需要的列（SELECT、WHERE、ORDER BY），无需回表 ≈ 索引树深度 + 结果行数（无回表） 查询列全部在复合索引中 SEARCH table USING COVERING INDEX
常见误区：并非所有带索引的查询都会使用索引扫描。如果优化器认为全表扫描比索引扫描更便宜（例如表很小、索引选择性差、需要读取大部分行），仍然会选择全表扫描。因此需要监控实际计划，而非仅仅相信索引存在。

查询计划选择流程（SVG 图）

查询计划生成与扫描方式决策流程 SQL Query 查询优化器 + 统计信息 有可用索引？ 且成本更低？ 全表扫描 覆盖所有 返回列？ 索引扫描（回表） 覆盖索引扫描 执行查询计划 说明框 优化器基于统计信息估算成本 选择最优扫描策略

调试与部署建议

  • 快速验证：在 Room 中，获取 SupportSQLiteDatabase 对象并执行 EXPLAIN QUERY PLAN，或通过 Android Studio Database Inspector 查看查询计划。
  • 日志化：在 DAO 方法中添加日志，输出 SQL 和绑定参数，然后复制到 sqlite3 命令行中执行 EXPLAIN QUERY PLAN。
  • 部署前检查：在 CI 流程中添加单元测试，对关键查询执行 EXPLAIN QUERY PLAN，检查输出中不包含 SCAN（除非预期全表扫描）。
  • 常见错误：
    • 创建了索引但查询中使用了 LIKE '%xxx' 或函数包裹索引列（如 UPPER(column)），导致索引无法使用，退化为全表扫描。
    • 复合索引的列顺序与查询条件顺序不匹配，导致最左前缀原则失效。
    • 覆盖索引中包含了不必要的宽列（如 BLOB），导致索引体积过大，反而降低扫描速度。
性能标杆：对于百万级表，全表扫描通常需要 100ms～数秒；索引扫描可降至 1ms～10ms；覆盖索引扫描最快，可达到亚毫秒级。优化目标是将所有高频查询的扫描方式从全表扫描提升到索引扫描或覆盖索引扫描。

思考练习

假设有一个 users 表：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT,
    age INTEGER
);
CREATE INDEX idx_name ON users(name);

执行以下查询：

EXPLAIN QUERY PLAN SELECT id, name FROM users WHERE name = 'Alice';
  • 输出中的扫描方式是什么？
  • 如果我们将索引改为 CREATE INDEX idx_name_cover ON users(name, id);，查询计划会有什么变化？
  • 如果查询变为 SELECT *（即返回所有列），还能使用覆盖索引吗？为什么？

请实际在 SQLite 实验环境中验证你的猜想。

← 上一章 📖 返回目录 下一章 →