SQLite 单表十亿行实现 10 万 TPS：WAL 模式与 PRAGMA 优化详解

SQLite 常被视为手机和嵌入式应用的 “玩具” 数据库，但 Anders Murphy 的基准测试颠覆了这一认知：在 M1 Pro MacBook（16GB 内存）上，单一 account 表（id INT 主键、balance INT，10 亿行数据），通过 WAL 模式、读写分离和批处理，轻松达到 10 万 TPS 以上混合读写吞吐，远超网络数据库如 Postgres。这得益于嵌入式零网络延迟、单写者高效批处理，避免 Amdahl 定律下锁竞争放大效应。

基准场景模拟真实 web 应用：交互式事务（UPDATE + 应用逻辑 + UPDATE），Pareto 用户分布（99.95% 事务集中 0.05% 热点用户，约 10 万活跃）。虚拟线程高并发突发测试，Postgres 无网 13k TPS，10ms 延迟 + 可序列化仅 348 TPS；SQLite 基线 44k TPS，批处理纯写 186k TPS，SAVEPOINT 隔离 121k TPS，3 读 + 1 写仍 102k TPS。

1. PRAGMA 参数核心配置（连接初始化执行）

这些参数是性能基石，直接影响缓存、日志和同步：

参数	值	作用	注意
journal_mode	WAL	读写并发，写日志不锁主文件	WAL 文件监控 <1GB
synchronous	NORMAL	减少 fsync，平衡安全 / 性能	生产 OFF 风险数据损
cache_size	15625	~64MB 页缓存（页数，负值 KB）	内存 *0.1，避免 OOM
page_size	4096	匹配 FS 页，I/O 高效	建库前设，VACUUM 生效
temp_store	MEMORY	临时表 / 排序内存化	内存充足场景
busy_timeout	5000	写锁等待 5s	避免忙等死锁
mmap_size	0 或大值	内存映射 DB，提升随机读	SSD 优先

示例（Python/sqlite3）：

import sqlite3
conn.execute("PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL;")
conn.execute("PRAGMA cache_size=15625; PRAGMA page_size=4096;")
conn.execute("PRAGMA temp_store=MEMORY; PRAGMA busy_timeout=5000;")

2. 索引与统计优化

• 主键 id 已聚簇索引；热点如 balance 加 CREATE INDEX idx_balance ON account(balance);（覆盖查询）。
• ANALYZE; 更新规划器统计，大表必备，避免全扫。
• 禁 SELECT COUNT(*)（O (n)），维护影子计数或用 sqlite_stat1。

填充 10 亿行：事务批 100k INSERT，耗时合理。

3. 连接池与读写分离

• writer：单连接，CPU 线程池异步批写（非 IO）。
• readers：核心数（如 8）池，并发读 WAL。
• 批处理：动态队列（如 32k 阈值），SAVEPOINT inner_tx; ... ROLLBACK/RELEASE; 细粒隔离，失败不影响批。

伪代码：

;; Writer 批
@(tx! (fn [tx] (SAVEPOINT; UPDATE; UPDATE; RELEASE;)))

;; Read
(q reader "SELECT * FROM account WHERE id=? LIMIT 1" user)

避 ORM：ORM Prepare / 反射开销大，直用原生驱动（如 sqlite3/JDBC）。

4. 落地 Checklist 与监控

1. 空库：PRAGMA page_size=4096; CREATE TABLE; VACUUM;
2. 填充：批 INSERT 1B 行。
3. 优化：索引 + ANALYZE;
4. 池化：1 writer + N readers。
5. 测试：1M 虚拟线程，Pareto 负载，TPS >100k。
6. 生产：litestream 备份；cron OPTIMIZE (VACUUM+REINDEX+ANALYZE)；警报 WAL 大小 / CPU。

风险与限界

• 单写者：纯写 >10k TPS 瓶颈，批缓解。
• 无 MVCC：长事务序列化隔离慎用。
• 崩溃：synchronous=OFF 损数据，用 WAL checkpoint。
• 扩展：多机 rqlite 或 projections。

此配置参数经基准实测，单机高吞吐杀手锏，日志 / 缓存 / 分析首选。

资料来源

• Anders Murphy: 100000 TPS over a billion rows “SQLite batch + SAVEPOINT 121k TPS。”
• HN: 42217092
• SQLite 文档：PRAGMA/WAL。

（字数：1028）