数据库原理与选型 / 系统课时

分析查询计划并优化索引

分析查询计划并优化索引

本课使用 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 BY

SCAN 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 会受测试库已有数据影响,因此以排序关系和行数为准。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

分析查询计划并优化索引

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通