42. 索引优化与 EXPLAIN QUERY PLAN

还记得我们在第 34 章讲过，索引（Index）就像书本的目录，能帮 SQLite 快速定位数据、少读很多行。但”建了索引”和”查询真的用上了索引”是两回事。很多时候你辛辛苦苦建了索引，结果一跑还是慢得离谱，原因就是查询计划（Query Plan）根本没用你的索引，而是老老实实把整张表从头读到尾——也就是”全表扫描”（Full Table Scan）。

本章要解决的，就是怎么”看穿”SQLite 到底打算怎么执行你的 SQL，以及怎么让它乖乖用上索引。

为什么需要看查询计划

SQLite 是一个嵌入式、零配置的数据库（serverless/零配置），没有独立的服务进程，整个数据库就是一个文件。正因为没人在背后替你调优，查询到底快不快，几乎完全取决于你写的 SQL 和表上的索引是否匹配。当一张表只有几十条数据时，全表扫描和走索引几乎没区别；可一旦表涨到几万、几十万行，差距就能从”毫秒级”拉到”秒级”。

所以优化的第一步不是盲目加索引，而是先搞清楚：这条语句此刻是怎么执行的。这就轮到 EXPLAIN QUERY PLAN（本章里我们简称 EQB）登场了。

用 EXPLAIN QUERY PLAN 看计划

在任意 SELECT、UPDATE、DELETE、INSERT…SELECT 语句前面加上 EXPLAIN QUERY PLAN，SQLite 不会真正执行它，而是返回一份”执行方案说明”。看个例子，沿用我们全局统一的示例表：

sqlite> EXPLAIN QUERY PLAN
   ...> SELECT * FROM users WHERE name = '张三';
QUERY PLAN
`--SCAN users

这里的 SCAN users 就是全表扫描：SQLite 打算把 users 表每一行都翻一遍，逐个比对 name。因为没有索引，它别无选择。

现在给 name 建个索引再试：

sqlite> CREATE INDEX idx_users_name ON users(name);
sqlite> EXPLAIN QUERY PLAN
   ...> SELECT * FROM users WHERE name = '张三';
QUERY PLAN
`--SEARCH users USING INDEX idx_users_name (name=?)

SEARCH ... USING INDEX 出现了，说明这次 SQLite 用了索引，先通过 B 树定位到 name='张三' 的那几行，不再扫全表。这就是我们想要的结果。

索引什么时候会被用上

SQLite 的查询优化器（Query Planner）只有在 WHERE 子句里出现”索引最左列”的等值或范围条件时，才会考虑用这个索引。以复合索引为例：

CREATE INDEX idx_orders_user_amount ON orders(user_id, amount);

这张索引好比电话簿：先按 user_id 排序，同一个 user_id 内再按 amount 排序。想用书，得从最左边这列开始翻：

• WHERE user_id = 5 —— 能用上索引（最左列等值）。
• WHERE user_id = 5 AND amount > 100 —— 也能用，且 amount 上的 > 作为”范围”用在最右可用列。
• WHERE amount > 100 —— 用不上！因为跳过了最左列 user_id，SQLite 只能全表扫。
• WHERE user_id = 5 OR amount > 100 —— 用不上，因为条件之间是 OR 连接，优化器通常走全表扫（除非给 amount 也单独建索引）。

覆盖索引：连表都不用回头查

普通索引查找有个隐藏成本：SQLite 先在索引 B 树里找到符合条件的行，拿到这些行的 rowid，再拿 rowid 回原表把完整行读出来——相当于两次查找。但如果查询要的列，索引里本来就全有，SQLite 就懒得回表了，直接从索引里把结果给你，这叫”覆盖索引”（Covering Index）。

CREATE INDEX idx_orders_cover ON orders(user_id, amount);
sqlite> EXPLAIN QUERY PLAN
   ...> SELECT user_id, amount FROM orders WHERE user_id = 5;
QUERY PLAN
`--SEARCH orders USING COVERING INDEX idx_orders_cover (user_id=?)

注意计划里多了 COVERING 字样。覆盖索引能显著提速，因为它少一次回表查找。实际建索引时，可以故意把查询常取的列一起放进复合索引，制造覆盖效果。

范围查询、OR 与 LIKE 的优化边界

• 范围（BETWEEN / > / <）：索引可用，但范围列右边的列就不能再借索引过滤了。比如 WHERE user_id=5 AND amount BETWEEN 10 AND 100 AND created_at > '2026-01-01'，created_at 一般用不上索引，因为它排在范围列 amount 右边。
• OR 连接：如前所述，单表内 OR 多个不同列通常导致全表扫。但如果 OR 的两边都是同一列的等值（如 user_id=5 OR user_id=6），SQLite 会智能改写成 user_id IN (5,6)，从而用上索引。
• LIKE 前缀匹配：当右侧是字符串常量且不以通配符开头时（如 name LIKE '张%'），并且列上有合适的索引与排序规则，SQLite 能把它当成一种范围扫描来用索引。但若以 % 开头（%张三），索引就无能为力了，只能全表扫。

动态类型与索引的一个小提醒

SQLite 是动态类型系统，声明的列类型只是”类型亲和性”（Type Affinity）建议，实际存储类由你存入的值决定（第 8、9 章讲过）。这点在索引上也有体现：在表达式上建的索引，匹配时也用”表达式”本身。例如：

CREATE INDEX idx_lower_name ON users(lower(name));
sqlite> SELECT * FROM users WHERE lower(name) = '张三';

这里查询条件 lower(name) 与索引里的表达式完全一致，才能用上这个索引。如果你写的是 name = '张三'（没包 lower），则匹配的不是同一个表达式，索引用不上。这再次印证了 SQLite”按值/按表达式决定如何使用存储与索引”的动态特性。

多表连接时别忘了外键的默认行为

当我们在 orders 和 users 之间做连接（JOIN）优化时，常常用到 orders.user_id 引用 users.id。如果你的表真的写了 REFERENCES 外键约束，请务必记住：SQLite 默认不强制外键，每次连接都得先 PRAGMA foreign_keys = ON 才会检查参照完整性。索引优化本身不依赖外键开关，但理解两张表”谁引用谁”的关系，能帮你决定该在 orders.user_id 上建索引（它通常是连接和过滤的高频列）。

PRAGMA foreign_keys = ON;

一个趁手的工具：CLI 的 .expert

除了手写 EXPLAIN QUERY PLAN，sqlite3 命令行还内置了 .expert 命令，它会分析你的 SELECT 并直接建议你”该建什么索引”：

sqlite> .expert
sqlite> SELECT * FROM orders WHERE user_id = 5 AND amount > 100;
-- Analyze this SELECT

CREATE INDEX orders_idx_xxxx ON orders(user_id, amount);
0|0|0|SEARCH orders USING INDEX orders_idx_xxxx (user_id=? AND amount>?)

照着它给的建索引语句加上去，再跑一次 .expert，如果提示 (no new indexes)，就说明现有索引已经够用。这对拿不准该建什么索引的新手尤其友好。

类比小结

把查询优化想象成”在图书馆找书”：

• 全表扫描 = 没有目录，只能从第一排书架一直走到最后一排；
• 走索引 = 先查书名目录，直接走到对应书架；
• 覆盖索引 = 目录上就把你要的信息（书名+作者）写全了，连书都不用抽出来看；
• EXPLAIN QUERY PLAN = 图书管理员提前告诉你”我打算这么找”，你据此判断他是不是在偷懒全馆乱转。

优化的本质，就是用 EQB 当”透视镜”，确认 SQLite 走的是索引而非全表，再针对高频查询精准补索引。下一章我们聊另一个”加速查找”的利器——FTS5 全文检索，它解决的是 LIKE 搞不定的”文章里搜词”问题。