SQLite ANALYZE 和 VACUUM：统计信息与空间回收

两种不同的维护工作

ANALYZE 和 VACUUM 经常被一起提起，但它们解决的是不同的问题。

• ANALYZE 收集关于数据的统计信息，让查询规划器做出更聪明的选择。它写入一张名为 sqlite_stat1 的表，不会动你的实际数据行。
• VACUUM 重建文件本身，回收未使用的页并整理碎片。它不会直接改变查询计划。

如果查询选错了索引，你需要的是 ANALYZE。如果大量删除之后文件比应有的大，你需要的是 VACUUM。把两者搞混，会在维护上浪费大量时间。

ANALYZE 到底做了什么

查询规划器只能靠猜。当它看到 WHERE status = 'active' 时，需要估计有多少行匹配（一行？一百万行？），才能决定是用索引还是扫描整张表。没有统计信息时，它只能退回到粗略的经验规则。

ANALYZE 会遍历每个索引，记录值的分布情况的汇总信息：

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  status TEXT,
  customer_id INTEGER
);

CREATE INDEX idx_orders_status ON orders(status);

INSERT INTO orders(status, customer_id) VALUES
  ('shipped', 1), ('shipped', 2), ('shipped', 3),
  ('shipped', 4), ('pending', 5);

ANALYZE;

SELECT * FROM sqlite_stat1;

sqlite_stat1 中的这一行告诉规划器，这个索引大约有多少行，以及一个典型的键有多少重复值。下次你查询 WHERE status = 'pending' 时，它知道 pending 很少见，就会使用索引；而对于 WHERE status = 'shipped'，它可能会判断扫描全表更划算。

你可以只分析一张表或一个索引，而不是整个数据库：

ANALYZE orders;
ANALYZE idx_orders_status;

在批量导入数据之后、在大的表结构变更之后，或者当你发现某些表的数据分布变化后规划器选出了糟糕的计划时，运行 ANALYZE。

PRAGMA optimize：现在的默认做法

每次关闭连接都盲目运行 ANALYZE 是一种浪费，大多数时候并没有什么变化大到值得重新分析。SQLite 提供了一个更聪明的封装：

PRAGMA optimize;

PRAGMA optimize 会检查自上次分析以来数据库发生了哪些变化，只对需要的表运行 ANALYZE。官方建议是：每个长期存在的连接在关闭前调用一次，保持打开好几个小时的连接则定期调用。

没有变化时它开销很小，有变化时它很有效。优先用 optimize；只有需要强制刷新时，才直接用 ANALYZE。

VACUUM 到底做了什么

当你删除行或删除一张表时，SQLite 会把那些页标记为空闲，但不会缩小文件。空闲页会被之后的插入重新使用，所以大多数时候这没什么问题。但在长期的数据变动中，会积累两样东西：

1. 操作系统看不到的空闲空间。 你的 .db 文件一直是 2 GB，尽管实际有效的数据只有 800 MB。
2. 碎片。 同一张表的行散落在不相邻的页上，影响扫描性能。

VACUUM 通过把整个数据库复制到一个新文件、紧凑地排列好，再替换原文件，同时解决这两个问题：

CREATE TABLE logs (id INTEGER PRIMARY KEY, body TEXT);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO logs(body) SELECT 'entry ' || value FROM n;
DELETE FROM logs WHERE id < 900;

VACUUM;

SELECT count(*) FROM logs;

VACUUM 之后，文件的大小就和你从头只插入剩下的 100 行时一样。附带的效果是，所有 rowid 保持不变，但磁盘上的布局又变得连续了。

运行它之前需要知道几件事：

• 运行期间它需要数据库的排他锁。其他连接都不能写入。
• 它需要大约相当于数据库两倍大小的磁盘空闲空间，因为它会在旧文件旁边构建新文件。
• 它不能在事务中运行，如果有活动的事务打开，它会报错。
• 对于好几 GB 的数据库，它可能要运行很久。要提前做好安排。

什么时候真正需要运行 VACUUM

对大多数应用来说：除非发生了特定的变化，否则不需要。

运行 VACUUM 的合理理由：

• 你刚删除了一张大表或一大批行，想拿回磁盘空间。
• 数据库已经频繁变动了好几年，扫描表的查询感觉比以前慢了。
• 你要把数据库文件作为发布内容的一部分交付，希望它尽可能小。

不合理的理由：

• “只是为了保险起见。”它每次都会重写整个文件。在线上系统上这样做，一点都不保险。
• 每批删除之后都运行一次。释放出来的页本来也会被重新使用。

auto_vacuum 和增量 VACUUM

如果你想让 SQLite 自动管理空闲页，需要在创建数据库时设置 auto_vacuum，之后要改就只能做一次完整的 vacuum：

PRAGMA auto_vacuum = INCREMENTAL;

有三种模式：

• NONE（默认）：空闲页留在文件中，供之后的插入重新使用。
• FULL：每次释放了页的提交都会同时截断文件。很方便，但每个事务都要承担这份开销。
• INCREMENTAL：SQLite 会记录空闲页，但只在你要求时才释放它们：

PRAGMA auto_vacuum = INCREMENTAL;
CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t;

PRAGMA incremental_vacuum(100);
PRAGMA freelist_count;

PRAGMA incremental_vacuum(N) 把最多 N 个空闲页还给操作系统。它很快，不会长时间占用排他锁，而且你可以定时运行它。对于写入频繁、又需要保持紧凑而不想承担完整 VACUUM 开销的数据库来说，这是最合适的方案。

VACUUM INTO：导出一份紧凑的副本

VACUUM INTO 把一份全新、紧凑的副本写入新文件，完全不动原文件：

VACUUM INTO 'backup.db';

它非常实用：

• 备份。 输出是一份一致的、完全 vacuum 过的快照：没有写了一半的页，也不用担心 .wal 文件。比用 cp 复制文件更好。
• 缩小文件，又不长时间阻塞写入。 先 vacuum 到一个旁路文件，再以原子方式替换。写入方不会在整个 vacuum 过程中都被阻塞。
• 分发。 交付一份小巧、无碎片的开发数据库副本。

目标文件必须不存在。如果已经存在，就会报错。

一份实用的维护方案

对于一个典型的应用数据库：

-- On each long-lived connection, before close:
PRAGMA optimize;

-- After a large bulk load or schema change:
ANALYZE;

-- After you delete a lot of data and want disk back:
VACUUM;

-- For backups:
VACUUM INTO '/backups/app-2026-04-23.db';

如果数据库以大量写入和删除为主、并且全天候在线，就在创建时设置 auto_vacuum = INCREMENTAL，再定期运行 PRAGMA incremental_vacuum(N)，比如每天在流量低谷时运行一次。

排查“为什么我的文件这么大？”

两个 pragma 能告诉你发生了什么：

CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 10000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t WHERE x > 100;

PRAGMA page_count;
PRAGMA freelist_count;
PRAGMA page_size;

• page_count × page_size = 当前文件大小。
• freelist_count × page_size = 浪费在未使用页上的字节数。

如果 freelist_count 占 page_count 的比例很大，运行一次 VACUUM（或 incremental_vacuum）会让文件明显变小。如果比例很小，说明文件已经排列得很紧凑，VACUUM 帮不上忙。

常见陷阱

• 在事务中运行 VACUUM。 这是不行的，先提交。
• 忘了 VACUUM 需要磁盘空闲空间。 一个 10 GB 的数据库，需要另外约 10 GB 的空闲空间才能 vacuum。
• 在已有数据之后才设置 auto_vacuum。 在下一次完整的 VACUUM 之前，它不会起作用。如果需要它，请在创建数据库时就设置好。
• 运行 ANALYZE 却期望文件变小。 那是 VACUUM 的工作。
• 运行 VACUUM 却期望得到更好的查询计划。 那是 ANALYZE 的工作。

这两个命令相辅相成，谁也替代不了谁。

下一步：事务

VACUUM 这样的维护命令凸显了一件我们一直视为理所当然的事：SQLite 的事务模型，以及它在什么时候锁住什么。下一章就从这里开始：事务如何工作，BEGIN / COMMIT / ROLLBACK 到底保证了什么，以及如何用它们让多条语句的操作保持原子性。