数据库原理与选型 / 系统课时
分析查询计划并优化索引
分析查询计划并优化索引
本课使用 EXPLAIN QUERY PLAN 找出状态筛选和时间排序中的全表扫描,再通过复合索引消除临时排序,并验证优化是否真正生效。
学习目标
- 识别查询计划中的
SCAN、SEARCH和临时排序 - 根据筛选列与排序列设计复合索引
- 比较优化前后的计划,而不是只凭感觉加索引
准备独立测试库
小表可能让优化器直接选择全表扫描,因此先复制数据库并补充测试数据,避免污染项目主库。
bash
cp notes.db notes-optimize.db
sqlite3 notes-optimize.db "
WITH RECURSIVE seq(x) AS (
SELECT 1
UNION ALL
SELECT x + 1 FROM seq WHERE x < 2000
)
INSERT INTO notes(title, content, status, created_at, updated_at)
SELECT
'批量笔记-' || x,
'用于查询计划测试',
CASE WHEN x % 5 = 0 THEN 'archived' ELSE 'active' END,
datetime('2026-07-01', '+' || x || ' minutes'),
datetime('2026-07-01', '+' || x || ' minutes')
FROM seq;
ANALYZE;
SELECT COUNT(*) FROM notes;
"预期输出:
text
2002查看优化前计划
先删除可能影响结果的复合索引,再查看查询计划。
bash
sqlite3 notes-optimize.db "
DROP INDEX IF EXISTS idx_notes_status_updated_at;
EXPLAIN QUERY PLAN
SELECT id, title, updated_at
FROM notes
WHERE status = 'active'
ORDER BY updated_at DESC
LIMIT 20;
"预期输出的关键结构:
text
QUERY PLAN
|--SCAN notes
`--USE TEMP B-TREE FOR ORDER BYSCAN notes 表示需要检查大量记录;USE TEMP B-TREE FOR ORDER BY 表示结果还要额外排序。不同 SQLite 版本的缩进和编号可能不同,但这两个关键词是判断重点。
创建复合索引
等值筛选列放在前面,排序列放在后面,使同一状态的数据在索引中已经按更新时间排列。
sql
CREATE INDEX idx_notes_status_updated_at
ON notes(status, updated_at DESC);
ANALYZE;
EXPLAIN QUERY PLAN
SELECT id, title, updated_at
FROM notes
WHERE status = 'active'
ORDER BY updated_at DESC
LIMIT 20;预期输出的关键结构:
text
QUERY PLAN
`--SEARCH notes USING INDEX idx_notes_status_updated_at (status=?)计划不再出现临时 B-Tree,说明索引同时承担了筛选和排序。这里仍需回表读取 title,但只取 20 行,通常比把大文本列塞进索引更合理。
理解最左前缀
索引 (status, updated_at) 适合按 status 查询,也适合按 status 筛选后按 updated_at 排序;它不能高效支持只按 updated_at 过滤的所有场景。设计复合索引时应围绕真实 SQL,而不是把所有常用列任意拼接。
不要为每个查询都增加索引。索引会占用磁盘,并增加新增、修改和删除成本。上线前应保留高频且收益明确的索引,移除重复或长期未使用的索引。
验证结果一致性
优化只改变访问路径,不能改变业务结果。对比前后查询时应同时核对返回行数、排序和首尾记录,再考虑执行时间。
bash
sqlite3 -header -column notes-optimize.db "
SELECT id, title, updated_at
FROM notes
WHERE status = 'active'
ORDER BY updated_at DESC
LIMIT 3;
"预期结果为 3 行有效笔记,并按 updated_at 从新到旧排列;具体 id 会受测试库已有数据影响,因此以排序关系和行数为准。
按自己的节奏,确认真正理解。