索引与执行计划

学习前提

理解WHERE、ORDER BY与连接；知道索引会增加写入成本。

本课完成后你能做什么

根据查询条件设计索引，区分结果正确性与性能。

先理解机制

索引提供额外的检索结构，以存储空间和写入维护成本换取部分查询的更快定位。并不是索引越多越好，也不保证小表一定使用索引。

本查询先按course_id等值筛选，再按minutes和id排序，因此设计(course_id,minutes,id)复合索引。列顺序对应访问方式，不能随意调换后假设效果相同。

EXPLAIN QUERY PLAN描述计划，不执行同样的结果读取。观察SEARCH、SCAN和临时排序等信息；计划文本会随SQLite版本与统计信息变化，不能把某段文字当成永久测试契约。

可运行示例

本系列使用SQLite 3方言。打开一个全新的内存练习数据库，例如终端执行 sqlite3 :memory:，再执行本课整段SQL。每章都自带建表和样例数据，不连接网站业务数据库。

CREATE TABLE sessions(id INTEGER PRIMARY KEY,course_id INTEGER,minutes INTEGER);
INSERT INTO sessions VALUES(1,1,25),(2,1,35),(3,2,20),(4,1,25);
CREATE INDEX idx_sessions_course_minutes ON sessions(course_id,minutes,id);
EXPLAIN QUERY PLAN SELECT id,minutes FROM sessions
 WHERE course_id=1 ORDER BY minutes,id;
SELECT id,minutes FROM sessions WHERE course_id=1 ORDER BY minutes,id;

推演与验证

最后的查询固定返回(1,25)、(4,25)、(2,35)。前面的执行计划通常可见覆盖索引访问，但以当前数据库实际输出为准。

常见错误与边界

用四行数据测出的耗时不能证明生产性能。应在代表性数据量和读写比例下测量，索引也不能修复错误的连接与统计逻辑。

动手练习

在新的练习库中去掉CREATE INDEX，比较计划和最终结果。

参考解法与理由

最终三行应完全一致；执行策略可能由索引检索变为表扫描与临时排序。这说明索引改变访问成本，而不是查询语义。

深入实验：为查询设计索引，而不是为每列加索引

一个查询先按课程等值过滤，再按时间倒序取最近记录。单独给时间和课程各加索引，不保证优化器会自动组合成最合适的访问路径。下面比较建索引前后的EXPLAIN QUERY PLAN。它描述计划，不是实际耗时成绩。

CREATE TABLE session(id INTEGER PRIMARY KEY, course_id INTEGER, started TEXT, minutes INTEGER);
INSERT INTO session VALUES
(1,7,'2026-09-01T09:00:00Z',20),
(2,7,'2026-09-02T09:00:00Z',30),
(3,8,'2026-09-03T09:00:00Z',40);
EXPLAIN QUERY PLAN SELECT id, minutes FROM session
WHERE course_id=7 ORDER BY started DESC, id DESC LIMIT 2;
CREATE INDEX session_recent ON session(course_id, started DESC, id DESC);
EXPLAIN QUERY PLAN SELECT id, minutes FROM session
WHERE course_id=7 ORDER BY started DESC, id DESC LIMIT 2;
SELECT id, minutes FROM session
WHERE course_id=7 ORDER BY started DESC, id DESC LIMIT 2;

预期观察

最后返回2|30和1|20。常见SQLite版本中，原计划含SCAN和临时排序，建索引后出现USING INDEX session_recent；具体计划文字不作为跨版本固定输出。

原因、取舍与适用边界

course_id放在前面定位等值范围，后续两列提供范围内所需顺序。时间示例统一为固定格式UTC字符串，使字典序与时间序一致；混合时区偏移或格式会破坏这个前提。索引不含minutes，读取结果仍可能回表；把minutes也放进去可能形成覆盖索引，但增加空间和写入维护成本。只有三行不能证明性能提升，需用代表性数据和读写负载测量。

进阶练习

如果新查询只按started过滤所有课程，原索引一定是最佳选择吗？

解法与推理

不是。缺少前导course_id的约束时，访问路径可能不同，优化器也可能选择扫描或其他策略。先看新查询计划和真实成本，再决定是否需要以started为前导的另一索引；别因索引名字“看起来相关”就认定命中有效。

进一步阅读

SQLite SQL reference