数据库原理与选型 / 系统课时
ANALYZE、统计信息与 PRAGMA optimize
ANALYZE、统计信息与 PRAGMA optimize
SQLite 的查询优化器要估计“走哪个索引更便宜”。ANALYZE 收集表与索引分布并写入统计表,PRAGMA optimize 则在合适时机按需维护统计。本课用可执行示例验证统计是否生成,并观察它如何参与计划选择。
学习目标
- 理解
sqlite_stat1中统计信息对查询计划选择的作用。 - 能运行
ANALYZE并验证目标索引已经产生统计信息。 - 能用
PRAGMA optimize建立轻量、可持续的统计维护策略。
统计信息解决什么问题
索引只描述“如何找到数据”,却不直接告诉优化器某个值常见还是稀有。面对多个候选索引时,优化器需要估算每条路径会读取多少行,再比较全表扫描、单索引扫描等方案的成本。统计信息就是这类估算的重要输入。
ANALYZE 会检查表和索引的数据分布,并把结果写入以 sqlite_stat 开头的内部表。常见的是 sqlite_stat1:其中的 tbl、idx 标识对象,stat 保存供优化器解释的统计摘要。应用代码通常只需要触发维护和检查是否存在统计,不应自行猜测并修改 stat 内容。

用 ANALYZE 生成并验证统计
下面的脚本创建一个小型订单表和客户索引,执行 ANALYZE 后检查 sqlite_stat1,再观察按客户查询时的访问路径。可直接保存为 SQL 文件交给 sqlite3 执行。
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
total INTEGER NOT NULL
);
-- 构造足够多的数据,让选择性较高的客户条件适合走索引。
WITH RECURSIVE seq(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM seq WHERE n < 200
)
INSERT INTO orders(order_id, customer_id, total)
SELECT n, ((n - 1) % 20) + 1, 10 + n
FROM seq;
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- 显式生成统计,并确认目标索引已有 sqlite_stat1 记录。
ANALYZE;
SELECT COUNT(*) AS stat_rows
FROM sqlite_stat1
WHERE tbl = 'orders' AND idx = 'idx_orders_customer';
EXPLAIN QUERY PLAN
SELECT order_id, total
FROM orders
WHERE customer_id = 7;
-- 日常维护入口;若无需处理,可能没有可见输出。
PRAGMA optimize;
SELECT order_id, total
FROM orders
WHERE customer_id = 7
ORDER BY order_id
LIMIT 3;预期输出
1
<ID>|<ID>|<VALUE>|SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
7|17
27|37
47|57EXPLAIN QUERY PLAN 前几列是计划节点元数据,实际 <ID> 与 <VALUE> 可能因 SQLite 版本而不同;关键是最后一列出现目标索引。PRAGMA optimize 在没有需要处理的对象时可以不产生可见输出,这属于正常情况。
用 PRAGMA optimize 做日常维护
直接运行 ANALYZE 会明确重建统计,适合初始化基线、批量装载完成后主动刷新,或排查计划问题时做受控实验。日常运行中更适合把 PRAGMA optimize 作为维护入口,让 SQLite 根据连接期间实际访问过的对象和现有统计决定是否需要分析。
对短连接,可以在连接即将关闭前运行一次 PRAGMA optimize;。对长期连接,刚建立连接时可执行 PRAGMA optimize=0x10002;,之后再周期性执行 PRAGMA optimize;;新增索引或发生明显数据分布变化后也值得执行。它通常开销很小,不需要因为每条写入都立即维护统计。
什么时候需要重新维护统计信息
当表从很小增长到很大、某些键值从均匀分布变成高度倾斜、批量导入后查询计划明显改变,或者新建了关键索引时,都应该考虑刷新统计。反过来,如果数据规模和分布基本稳定,频繁执行完整 ANALYZE 通常没有收益。
把 ANALYZE 理解为“显式采样并写统计”,把 PRAGMA optimize 理解为“按需维护入口”即可:前者适合主动建立或重建基线,后者更适合作为应用生命周期中的常规维护动作。