实战SOP:SQLite3开WAL,个人项目告别MySQL

背景:个人项目的数据库,别急着上 MySQL

个人项目最容易被过度设计的地方,就是数据库。一个日活几十人的小工具、一个自用的记账服务、一个爬虫的落库端——上来就装 MySQL:服务器要配、账号要建、备份要写脚本、迁移要导数据,连不上还得排查半天。等这套运维跑通,写业务代码的兴致已经没了一半。

SQLite 本来是更好的答案:一个文件、零运维、随程序走。但很多人对它有挥之不去的偏见——“并发差,写的时候整个库都锁住”。这个印象来自它的默认模式:rollback journal。那个模式下,写事务进行中,读会被阻塞;读事务进行中,写要排队。单用户场景无所谓,一旦有个后台任务在写、前台还要读,体验立刻崩。

解法不是换 MySQL,而是把 SQLite 切到 WAL 模式——写不阻塞读、读不阻塞写,再配一组参数,个人项目的读写性能与体验,足够顶替一个 MySQL 实例。本文记录这次调优的完整过程与实测。

方案:一条 PRAGMA 加六组参数

1. 核心开关:

PRAGMA journal_mode = WAL;

这一条把日志模式从 rollback journal 切成 write-ahead log。原理不复杂:写操作先追加到独立的 WAL 文件,读操作照常读主库文件,二者互不阻塞;WAL 攒到一定量,再统一回写主库(checkpoint)。代价是多出两个伴随文件:-wal 和 -shm。

2. 配套参数(一次配好,长期有效):

3. 三种常用栈的设置方式:

命令行:

sqlite3 app.db "PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL;"

Python:

import sqlite3

conn = sqlite3.connect("app.db")
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA synchronous=NORMAL")
conn.execute("PRAGMA busy_timeout=5000")
conn.execute("PRAGMA cache_size=-64000")

Go(database/sql 驱动同理,连接串里带上参数,或每条连接初始化时执行):

db, _ := sql.Open("sqlite3", "app.db?_journal_mode=WAL&_synchronous=NORMAL&_busy_timeout=5000&_cache_size=-64000")
db.SetMaxOpenConns(8)   // 读连接可并发,写连接由驱动串行化

注意:这些 PRAGMA 是「按连接生效」的,新建连接不执行就回到默认值。所以要么写进连接串,要么在连接池的初始化钩子里统一设置,不能只在某个脚本里执行一次。

实测:数字比感觉可靠

同一台机器、同一个库(约 40 万行,单表)、同一套查询与写入脚本,前后对比:

最亮眼的是最后一行:默认模式下,一个批量写事务跑着,前台读请求全部排队等锁;WAL 之后,写事务进行中,读照常走,只是读到的是事务开始前的快照。对个人项目来说,这一条就值回票价。

实践要点

1. 备份必须带上 -wal 和 -shm。 WAL 模式下,最近一次 checkpoint 之后的改动都在 -wal 文件里。只拷主库文件,等于丢一部分数据。最稳的做法是用 SQLite 的在线备份接口,或者 VACUUM INTO 'backup.db' 生成一份一致的快照。
2. SQLite 是单写者模型,WAL 不改变这一点。 无论怎么调,同一时刻只有一个写事务。写请求多的时候,正确做法是让写操作走单条队列(单协程/单线程消费),而不是多个连接抢锁。读可以并发,写要排队——把这条当成设计约束,而不是缺陷。
3. synchronous=NORMAL 是有代价的安全折中。 它保证数据库文件不会损坏,但断电时可能丢失最近若干次已提交的事务。个人工具、可重建的数据,这个折中完全值得;涉及钱的场景,退回 FULL。
4. busy_timeout 一定要设。 默认 0 意味着"遇到锁立刻返回 SQLITE_BUSY"。设成 5 秒后,偶发的锁竞争变成短暂等待,用户侧感知不到。这是性价比最高的一行配置。
5. 不要把 SQLite 放在网络盘上。 NFS、SMB 上的文件锁语义不可靠,WAL 模式尤其依赖共享内存(-shm)。库文件必须在本地磁盘,这条没有例外。
6. 留着只读连接会让 WAL 文件膨胀。 checkpoint 需要没有活跃读事务才能推进;一个长期挂着的事务(比如忘了提交的只读连接)会把 WAL 文件撑到几百兆。定期检查 -wal 文件大小,发现异常先看有没有长事务。
7. 什么时候仍然该上 MySQL。 多机部署、写并发高(每秒几十上百个写事务)、需要独立的账号权限体系、需要跨服务的强一致——这四类场景,SQLite 解决不了,别硬撑。个人项目常见的那点负载,它绰绰有余。

小结

这次调优之后,我把手上三个小服务的库全切到了 WAL,运维动作从"维护一个 MySQL 实例"变成"记得备份时带上两个附属文件"。对个人开发者而言,这不是"凑合能用",而是恰好够用:单机、单写者、读多写少——SQLite 的设计目标本来就是为这种场景优化的。

数据库选型的关键从来不是"哪个更强",而是"我的负载长什么样"。想清楚这一点,WAL 那一条 PRAGMA,就能省掉一整套 MySQL 的部署与维护。