SQLite 运维核心：PRAGMA 命令详解与最佳实践

SQLite 虽然是一个小型的数据库产品，但其运维命令并不像想象中那么简单。核心的运维命令是 PRAGMA。

PRAGMA 语句是特定于 SQLite 的 SQL 扩展，用于修改 SQLite 库的操作或查询 SQLite 库的内部（非表）数据。

⚠️ 注意事项
PRAGMA 命令本身会随着版本更迭。最重要的一点是：在系统中使用错误的 PRAGMA 命令通常不会报错，只会静默失败，这一点在运维中需要格外注意。

---

一、 性能与安全性的平衡：Synchronous 模式

在 SQLite 运维中，平衡系统性能与数据安全性是关键。synchronous 参数决定了 SQLite 写入磁盘的策略。

1. 模式对比表

模式	数值	性能	安全性	特点说明
OFF	0	★★★★★ (最快)	★☆☆☆☆ (最低)	不调用 fsync；掉电可能丢大量数据；适合批处理、缓存、不重要数据。
NORMAL	1	★★★★☆	★★☆☆☆	WAL 层 fsync 延迟到后台；掉电可能丢“最后一次提交”；推荐一般业务。
FULL	2	★★★☆☆	★★★★☆	每次事务提交都会 fsync；传统、稳健；关键/TP 型业务推荐。
EXTRA	3	★★☆☆☆ (最慢)	★★★★★ (最安全)	FULL + metadata fsync；确保文件元数据一致；几乎坚不可摧。

2. 设置示例

在进入 SQLite 时，需要根据业务模型选择合适的模式。对于关键业务，我们推荐 FULL 模式。

[root@localhost data]# sqlite3 test.db
SQLite version 3.47.1 2024-11-25 12:07:48
Enter ".help" for usage hints.

sqlite> PRAGMA synchronous = FULL;
sqlite> PRAGMA synchronous;
2

---

二、 WAL 日志刷新策略 (Checkpoint)

WAL（Write-Ahead Logging）日志刷新是 SQLite 运维的关键项目。我们需要管控在断电后数据库恢复工作所需的时间。

这涉及到数据页面的刷新率：产生多少数据页面后，进行数据页面刷新（Checkpoint）到磁盘？

1. 关键命令说明

• PRAGMA wal_autocheckpoint;
  查看当前配置，即产生多少个 WAL 页面后自动触发 Checkpoint。默认通常是 1000。
• PRAGMA wal_autocheckpoint = 200;
  修改配置，设置为产生 200 个页面就进行数据刷新。
• PRAGMA wal_checkpoint;
  手动触发 Checkpoint。

2. 操作示例

sqlite> PRAGMA synchronous = FULL;
sqlite> PRAGMA synchronous;
2

-- 查看默认自动检查点阈值
sqlite> PRAGMA wal_autocheckpoint;
1000

-- 修改为 200 页
sqlite> PRAGMA wal_autocheckpoint = 200;
200

-- 再次确认
sqlite> PRAGMA wal_autocheckpoint;
200

-- 手动执行 Checkpoint
sqlite> PRAGMA wal_checkpoint;
0|10179839|10179839

-- 使用 TRUNCATE 模式执行 Checkpoint (清空 WAL 文件)
sqlite> PRAGMA wal_checkpoint(TRUNCATE);
0|0|0

3. 启动配置建议

注意： 每次启动数据库连接时，SQLite 不会自动加载所有预设配置（部分配置不持久化）。因此，你的 Java 程序或启动脚本应该显式执行以下配置：

PRAGMA journal_mode = WAL;
PRAGMA synchronous = FULL;
PRAGMA wal_autocheckpoint = 200;

---

三、 PRAGMA 的持久化 vs 非持久化

了解哪些配置会写入数据库文件（持久化），哪些只在当前连接有效（会话级），至关重要。

1. 持久化 PRAGMA (Persistent)

这些设置会写入数据库文件头，对所有连接永久生效。

PRAGMA	持久化	说明
journal_mode	✔	改变数据库日志格式（如 WAL 会生成 -wal/-shm 文件）
auto_vacuum	✔	改变数据库文件结构，需要重写文件
encoding	✔	仅新库可设，写入文件头，建库后不能修改
application_id	✔	写入文件头 32-bit 标识，用于应用识别
page_size	✔	修改数据库页大小
user_version	✔	写入文件头，用于 Schema 版本控制

2. 会话级 PRAGMA (Non-persistent)

这些只影响当前连接或事务，不会写入数据库文件，重启连接后失效。

PRAGMA	持久化	说明
synchronous	✘	仅本连接的 IO 策略
wal_autocheckpoint	✘	WAL checkpoint 设置不写入文件
checkpoint_fullfsync	✘	仅本连接有效
analysis_limit	✘	影响 ANALYZE，本连接有效
cache_size	✘	缓存页数，内存设置
busy_timeout	✘	锁等待时间
foreign_keys	✘	每次连接都需要重新 ON
foreign_key_check	✘	检查型命令，不保存状态
integrity_check	✘	检查型命令，不保存状态
defer_foreign_keys	✘	仅当前事务有效
incremental_vacuum	✘	动作执行，不改变配置
data_version	✘	查询 DB 是否被修改
database_list	✘	查询类
index_list / _info	✘	查询类
所有 deprecated PRAGMA	✘	均不持久化

---

四、 新库初始化设置指南

对一个新创建的 SQLite 数据库，建议进行以下 6 项初始化设置：

1. 数据库存储文本的编码
2. 数据库页面的大小
3. 设置文件身份标识
4. 设置数据文件的版本
5. 控制 SQLite 是否进行磁盘空间的回收
6. 设置数据库运行的模式

Linux 与 Windows 下的关键差异

以下是这六项设置在不同操作系统下的表现对比：

1. 数据库存储文本的编码 (PRAGMA encoding)

结论：SQLite 行为完全一致，主要差异来自 Windows 终端默认可能不是 UTF-8。

项目	Linux	Windows	说明
默认编码	UTF-8	UTF-8	SQLite 默认 UTF-8，与 OS 无关
可设置	✔ (仅空库)	✔ (仅空库)	只能在创建 DB 后、建表前设
受终端影响	✔	✔	Windows CMD 默认 GBK，需 chcp 65001

2. 数据库页面大小 (PRAGMA page_size)

结论：两平台行为一致，4K 页通常最佳。

项目	Linux	Windows	说明
默认页大小	4096	4096	SQLite 默认值一致
持久化	✔	✔	修改文件结构
文件系统	EXT4 (通常 4K)	NTFS (4K)	两平台默认都合适

3. 文件身份标识与版本 (application_id / user_version)

结论：完全跨平台，写入文件头。

项目	Linux	Windows	说明
application_id	✔	✔	写入 4 字节，用于应用识别 DB 类型
user_version	✔	✔	写入 32-bit 整数，用于 Schema 版本管理

4. 自动回收空间 (PRAGMA auto_vacuum)

结论：功能一致，但文件系统层面的性能有差异。

模式	Linux	Windows	说明
NONE	默认	默认	不自动回收
FULL	✔	✔	删除数据立即收缩文件
INCREMENTAL	✔	✔	需手动执行 PRAGMA incremental_vacuum
FS 影响	EXT4 更快	NTFS 较慢	仅 OS 层差异

5. 运行模式与锁机制 (journal_mode)

结论：最大差异在于锁的实现。

关键项	Linux	Windows	差异说明
WAL 模式	⭐ 强	⭐ 弱一点	Windows 下并发性能稍弱
文件锁	POSIX fcntl	LockFileEx	Windows 锁开销较大
SHM 文件	.db-shm	.db-shm	一致

6. 同步写盘 (synchronous)

结论：Windows 的写盘（Flush）通常比 Linux 慢。

值	Linux 行为	Windows 行为	说明
OFF	无 fsync	无 FlushFileBuffers	最快最不安全
NORMAL	推荐 (WAL)	同 Linux	性能 + 数据安全折中
FULL	fsync()	FlushFileBuffers()	Windows 写盘更慢

---

五、 标准化初始化脚本

为了规范化数据库设置，可以将以下命令集合保存为一个 .sql 文件，或集成到数据库连接池的初始化代码中。

推荐配置清单

-- 1) 设置数据库编码（仅空库且未建表时有效）
PRAGMA encoding = 'UTF-8';

-- 2) 设置数据库页面大小（仅空库有效，推荐 4096）
PRAGMA page_size = 4096;

-- 3) 设置应用 ID（用于程序识别文件类型，可选）
PRAGMA application_id = 0x1234ABCD;

-- 4) 设置 Schema 版本（供程序版本控制使用）
PRAGMA user_version = 20240101;

-- 5) 设置自动回收空间模式 (建议 FULL 或 INCREMENTAL)
PRAGMA auto_vacuum = FULL;

-- 6) 设置日志模式 (推荐 WAL 模式以提升并发)
PRAGMA journal_mode = WAL;

-- 7) 设置同步级别 (平衡性能与安全，WAL 模式下推荐 NORMAL)
PRAGMA synchronous = NORMAL;

-- 8) WAL 模式下自动 checkpoint 间隔 (不持久化，每次连接需设置)
PRAGMA wal_autocheckpoint = 200;

-- 9) 可选：启用外键约束 (不持久化，每次连接需设置)
PRAGMA foreign_keys = ON;

-- 10) 可选：快速收缩空间 (配合 INCREMENTAL 模式使用)
-- PRAGMA incremental_vacuum;

提示：对于新特性，可以考虑使用 STRICT 表（类似强类型）：
CREATE TABLE t(a INTEGER, b TEXT) STRICT;

---

作者介绍

刘华阳
20年经历风霜雨打的 DBA，5年的 DBA 架构和团队管理经验。PostgreSQL ACE，MongoDB 狂热者，10年的 MYSQL 工作经验，现在在玩 POLARDB，始终与时俱进。