Windows下多进程访问SQLite（WAL模式）的性能优化问询

DevOps 小满

2026-7-30

问题分析与优化方案

当前操作存在的问题

频繁短事务：每个单条操作单独作为事务，会导致WAL文件频繁生成小写入片段，同时每次事务都要获取、释放锁，20个进程并发场景下锁冲突概率急剧上升，这是SQL_BUSY问题频发的核心原因之一。
不合理的WAL清理策略：每小时执行PRAGMA wal_checkpoint(TRUNCATE)+VACUUM存在两个问题：一是TRUNCATE级别的checkpoint需要独占锁，高负载下可能无法顺利完成，导致WAL持续积累；二是VACUUM是IO密集型重操作，会加剧服务器负载和锁竞争，而你的数据库文件大小稳定，说明空闲页占比不高，完全没必要频繁执行。
重试策略低效：固定1-10ms的随机休眠重试，在高负载下仍过于频繁，反而加剧锁竞争；且未区分读写操作的重试逻辑，读操作无需等待写锁的场景被不必要地阻塞。
事务控制粗糙：虽然每个进程仅打开一次数据库是合理的，但如果某个进程的短事务因服务器高负载出现延迟，会长期持有锁资源，阻塞其他进程。

将多个同类型的短操作合并为一个事务，比如把10次INSERT、或一组DELETE+INSERT的覆盖操作放在同一个事务中提交。这样能大幅减少事务提交次数，降低锁竞争频率，同时减少WAL文件的写入量。示例：

BEGIN TRANSACTION;
INSERT INTO table (...) VALUES (...);
INSERT INTO table (...) VALUES (...);
COMMIT;

启用自动检查点并调整阈值：设置PRAGMA wal_autocheckpoint=5000（默认是1000页，可根据实际负载调整），让SQLite在WAL文件达到指定页数时自动执行checkpoint，避免手动定时checkpoint的僵化问题。
限制WAL文件大小：通过PRAGMA journal_size_limit=104857600（设置为100MB）强制SQLite在WAL超过该大小时自动触发checkpoint，防止WAL膨胀到千兆级别。
优化手动checkpoint逻辑：避免在高负载时段执行，且优先使用低侵入性的checkpoint模式：先尝试PRAGMA wal_checkpoint(PASSIVE)（不阻塞读写），如果WAL未有效缩小，再用PRAGMA wal_checkpoint(FULL)，最后才用TRUNCATE。同时取消每小时的VACUUM，改为每天低峰时段执行一次即可。

读操作启用未提交读：如果业务允许脏读（即可以读取未提交的写事务数据），设置PRAGMA read_uncommitted=1，读事务无需等待写事务释放锁，直接读取WAL中的数据，彻底避免读操作触发SQL_BUSY。
使用SQLite内置的busy_timeout：设置PRAGMA busy_timeout=5000（等待5秒），让SQLite内部自动处理锁冲突重试，比自定义的重试逻辑更高效，可减少大量手动重试的代码开销。
改用指数退避重试：如果必须自定义重试，不要用固定间隔，改用指数退避（比如第一次等1ms，第二次2ms，第三次4ms，最大到500ms），避免短时间内频繁重试加剧竞争。

用UPDATE替代DELETE+INSERT：覆盖操作尽量直接用UPDATE语句，而非先DELETE再INSERT，这样只需修改一行数据，减少事务开销和索引更新次数，降低锁竞争概率。
清理冗余索引：用EXPLAIN QUERY PLAN验证每个查询的索引使用情况，删除冗余索引（比如多个索引覆盖相同的字段组合），冗余索引会大幅增加INSERT/DELETE的IO开销。

确保数据库文件存储在SSD上，高负载下HDD的IO性能会成为瓶颈；
关闭Windows的磁盘索引功能（针对数据库文件所在分区），减少磁盘IO干扰；
调整SQLite的同步模式：如果业务可以接受极小的数据丢失风险（比如断电丢失最近几秒的事务），设置PRAGMA synchronous=NORMAL，比默认的FULL模式减少磁盘同步次数，提升写性能。

绝对不建议。每次打开关闭数据库会带来额外开销：文件打开/关闭、元数据读取、连接初始化等，在每秒数次操作的场景下，会导致性能显著下降。同时频繁的文件操作会加剧文件锁竞争，反而让SQL_BUSY问题更严重。保持每个进程长期持有一个数据库连接是更高效的方式，重点是优化事务的执行时长和并发逻辑。

读写分离：定期（比如每5分钟）通过sqlite3_backup API将主库备份到一个只读副本，让读操作优先访问副本，减少主库的读锁竞争。注意副本的一致性取决于备份频率，需根据业务需求调整。
进程操作错峰：如果某些进程的非核心操作可以调整执行时间，错开高峰时段的并发操作，降低锁冲突概率。
监控锁竞争情况：使用SQLite的PRAGMA lock_status查看当前锁的持有情况，定位频繁持有锁的进程或操作，针对性优化。

内容的提问来源于stack exchange，提问作者kofifus