SQLite 教学工作区 · 课程 0004 · 约 25 分钟 · 前置:0003 · 2026-09-24

索引与执行计划:同一张表,两条路

为什么是这一步

0003 你已经看到过 SCAN 与 SEARCH … USING INDEX 的差别,但那时是彩蛋。 使命的验收里有一条硬要求:「说明索引为何生效(EXPLAIN QUERY PLAN 说得清)」—— 这一节把它讲透:索引不是"加了就快",而是你写的查询能不能用上它。

最小知识:三条

1. 索引是一张"排好序的旁表"。它存两样东西:被索引列的值 + 指向原行的 rowid。 所以"按索引列找行"变成了"在排好序的表里二分"——这就是它快的原因。
2. 复合索引只能从左往右用(最左前缀)。(a, b, c) 的索引: WHERE a=? 能用、a=? AND b=? 能用、b=? 单独用不能用。 因为索引是先按 a 排、a 相同再按 b 排 —— 只看 b 就没法定位。
3. 执行计划有三种"好"与一种"坏"。 SEARCH … USING INDEX(用索引定位)、USING COVERING INDEX(索引里就有全部所需列,连原表都不用回)、 SCAN(逐行扫全表)。USE TEMP B-TREE FOR ORDER BY 表示排序借不到索引,值得关注。

动手:五条查询,先猜后验

用 0001 建好的副本(/tmp/sqlite-练习/ctx.db);先自己在纸上写下预测,再对照输出:

sqlite3 -readonly /tmp/sqlite-练习/ctx.db

-- ① project_path + status
EXPLAIN QUERY PLAN SELECT * FROM memories WHERE project_path='x' AND status='active';
-- `--SEARCH memories USING INDEX idx_memories_project_status_expires (project_path=? AND status=?)

-- ② 只用 status
EXPLAIN QUERY PLAN SELECT * FROM memories WHERE status='active';
-- `--SCAN memories                    ← 没有任何索引以 status 开头

-- ③ project_path + category
EXPLAIN QUERY PLAN SELECT * FROM memories WHERE project_path='x' AND category='y';
-- `--SEARCH memories USING INDEX idx_memories_project_category_hash (project_path=? AND category=?)

-- ④ 只取索引里已有的两列
EXPLAIN QUERY PLAN SELECT project_path, status FROM memories WHERE project_path='x';
-- `--SEARCH memories USING COVERING INDEX idx_memories_project_status_expires (project_path=?)

-- ⑤ 加上排序
EXPLAIN QUERY PLAN SELECT * FROM memories WHERE project_path='x' ORDER BY status;
-- `--SEARCH memories USING INDEX idx_memories_project_status_expires (project_path=?)

对照一下你的预测:①②③ 的差别全在"索引的第一列是谁";④ 因为只要两列、而这两列都在索引里, 于是变成 COVERING INDEX(最快的形态);⑤ 排序没有额外报 TEMP B-TREE —— 说明它借到了索引的顺序。

实验:亲手加一个索引,看计划变化

在副本上做(不要动原库):

-- 先确认 ② 是全表扫
EXPLAIN QUERY PLAN SELECT * FROM memories WHERE category='ARCHITECTURE';
-- `--SCAN memories

-- 建索引(实测:耗时约 2.5 秒)
CREATE INDEX idx_cat_test ON memories(category);

-- 再看一次:变成走索引了
EXPLAIN QUERY PLAN SELECT * FROM memories WHERE category='ARCHITECTURE';
-- `--SEARCH memories USING INDEX idx_cat_test (category=?)

-- 量一下这个索引的代价(16 KB)
SELECT name, sum(pgsize)/1024 AS kb FROM dbstat WHERE name LIKE 'idx_cat%' GROUP BY name;

三个结论,请记住:① 索引换来的是"不再全表扫";② 它是有代价的 —— 占空间、拖慢写入(每次插入都要更新索引); ③ 加索引之前先问:我的查询会用到它吗? —— 索引列与查询条件对不上,加了也是白占地方。

自测(选项一样长,别从长度上猜)