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

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 生成并验证统计

下面的脚本创建一个小型订单表和客户索引,执行 ANALYZE 后检查 sqlite_stat1,再观察按客户查询时的访问路径。可直接保存为 SQL 文件交给 sqlite3 执行。

sql
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;

预期输出

text
1
<ID>|<ID>|<VALUE>|SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
7|17
27|37
47|57

EXPLAIN QUERY PLAN 前几列是计划节点元数据,实际 <ID> 与 <VALUE> 可能因 SQLite 版本而不同;关键是最后一列出现目标索引。PRAGMA optimize 在没有需要处理的对象时可以不产生可见输出,这属于正常情况。

用 PRAGMA optimize 做日常维护

直接运行 ANALYZE 会明确重建统计,适合初始化基线、批量装载完成后主动刷新,或排查计划问题时做受控实验。日常运行中更适合把 PRAGMA optimize 作为维护入口,让 SQLite 根据连接期间实际访问过的对象和现有统计决定是否需要分析。

对短连接,可以在连接即将关闭前运行一次 PRAGMA optimize;。对长期连接,刚建立连接时可执行 PRAGMA optimize=0x10002;,之后再周期性执行 PRAGMA optimize;;新增索引或发生明显数据分布变化后也值得执行。它通常开销很小,不需要因为每条写入都立即维护统计。

什么时候需要重新维护统计信息

当表从很小增长到很大、某些键值从均匀分布变成高度倾斜、批量导入后查询计划明显改变,或者新建了关键索引时,都应该考虑刷新统计。反过来,如果数据规模和分布基本稳定,频繁执行完整 ANALYZE 通常没有收益。

把 ANALYZE 理解为“显式采样并写统计”,把 PRAGMA optimize 理解为“按需维护入口”即可:前者适合主动建立或重建基线,后者更适合作为应用生命周期中的常规维护动作。

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

目录与阅读设置

阅读设置

17

记录一个想法

ANALYZE、统计信息与 PRAGMA optimize

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

登录哪都通