如何配置 SQLite pragma 参数优化生产环境读写性能

生产环境优化 SQLite 读写性能，最立竿见影的手段通常是调整 PRAGMA 参数，尤其是启用 WAL 模式，这能显著改善并发读写时的锁竞争问题。

先说结论：启用 WAL 模式是提升并发读写的基础，配合同步策略和缓存调整可进一步平衡性能与安全，但需注意每连接配置的特性。

命令速用版

以下是在连接建立后通常建议执行的基础配置命令，可根据实际负载微调：

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA cache_size=-20000;
PRAGMA wal_autocheckpoint=5000;

SQLite 默认使用回滚日志（DELETE 模式），写操作会锁住整个数据库文件，导致读操作阻塞。WAL（Write-Ahead Logging）模式将写入操作先记录到独立的 WAL 文件中，允许读者和写入者并发访问，这是提升并发能力的核心机制。

synchronous 参数控制数据写入磁盘的频率。FULL 最安全但慢，NORMAL 在大多数生产场景下能平衡性能与崩溃安全性，OFF 则极快但有数据丢失风险。cache_size 则决定了内存中缓存的数据库页面大小，合理增大可以减少磁盘 I/O 次数。

1. 连接时配置：PRAGMA 设置通常是针对单个连接的。在使用连接池或多线程环境时，必须确保每次获取新连接后都重新应用这些设置，或者使用初始化回调函数。

2. 启用 WAL：执行PRAGMA journal_mode=WAL;。执行成功后，数据库目录通常会新增.wal和.shm文件。

3. 调整同步策略：执行PRAGMA synchronous=NORMAL;。如果对数据持久性要求极高（如金融账本），可考虑保持 FULL，但需接受性能损耗。

4. 设置缓存：执行PRAGMA cache_size=-20000;（负数单位通常为 KB，具体视版本而定，此处示例为 20MB 左右），根据服务器内存情况调整。

5. 管理 WAL 文件：WAL 文件可能会无限增长，建议配置wal_autocheckpoint或在低峰期手动执行PRAGMA wal_checkpoint(TRUNCATE);。

1. 检查模式：执行PRAGMA journal_mode;，返回结果应为wal。

2. 观察文件：查看数据库文件所在目录，确认是否存在-wal和-shm后缀的文件。

3. 监控大小：在生产运行一段时间后，观察.wal文件大小是否稳定。如果持续增大且不回落，可能需要检查 checkpoint 策略是否生效。

4. 压力测试：在高并发读写场景下，观察是否仍频繁出现“database is locked”错误，若显著减少则说明优化有效。

1. 连接失效：很多开发者只在主连接设置了 PRAGMA，但连接池中的新连接未继承配置，导致部分请求仍运行在默认模式下。务必在每次打开连接时执行配置。

2. 文件系统兼容：WAL 模式在某些网络文件系统（如 NFS）或旧版文件系统上可能不支持或表现异常，生产环境建议使用本地磁盘（ext4, xfs, NTFS 等）。

3. 数据安全风险：不要随意将synchronous设置为OFF，除非你能接受断电时最近的事务数据丢失。

4. WAL 文件残留：如果数据库非正常关闭，WAL 文件可能不会合并回主数据库文件。迁移或备份数据库时，需确保所有连接已关闭或使用完整备份工具。