优化SQLite，让这根羽毛飞起来

SQLite优化与应用：避坑指南与性能飞跃之路

SQLite，它不是玩具，真的，太多人误解了，移动应用都在用，嵌入式系统也离不开它，甚至连航空航天，都在用，厉害吧，常见的错误，那个SQLITE_BUSY，它不是SQLite的错，是默认配置的问题，偏向嵌入式，要自己调，不然就容易出问题。

可靠性，性能，简易性，这是SQLite的三大优势，网络调用，靠不住，DNS出错，网络中断，都是坑，SQLite就不怕，读取延迟，毫秒级变微秒级，这速度，爽，单机扩展，能力很强，集群管理，也简单多了，安全，更重大，数据库不用暴露在公网，数据泄露，风险就小了。

SQLITE_BUSY，这个错误，烦人，默认情况下，SQLite只允许一个写入者，多个连接同时写入，锁冲突就来了，解决办法，调`busy_timeout`参数，让SQLite多等等再报错，给它个机会，说不定就成功了。

配置PRAGMA命令，这是优化第一步，`PRAGMA journal_mode = WAL;`，启用WAL模式，并发性能，嗖嗖的，读写互不干扰，`PRAGMA synchronous = NORMAL;`，WAL模式下，性能和数据安全，兼顾了，`PRAGMA cache_size = 1000000000;`，增加SQLite缓存，查询性能起飞，这个大小，自己看着办，`PRAGMA foreign_keys = true;`，启用外键约束，数据完整性，有保障，`PRAGMA busy_timeout = 5000;`，这个等待时间，根据应用来，别太长也别太短，刚刚好。

事务处理也很重大，`BEGIN IMMEDIATE`，立即启动事务，避免事务升级导致的锁冲突，在Django5.1以上，还有Golang，都有相应的配置，可以实现这个功能，方便，省事。

连接池管理，也不能忽略，读写分离，是个好主意，设立两个连接池，一个专门写入，限制单个连接，另一个专门读取，并发量大，Golang实现，`SetMaxOpenConns`设定连接池大小，读取池可以根据CPU核心数来扩展，充分利用资源。

批量写入，提高效率，事务包裹，把多个写入操作放在一个事务里，测试表明，每秒25W+的写入速度，不是梦，效率惊人。

数据库分片，按业务拆分，把数据库分成多个小型数据库，主数据库，队列数据库，事件数据库，各司其职，读写负载均衡，针对读写密集型数据库，特殊优化，事件数据库可以设置缓冲区和批量插入策略，针对性更强。

强类型模式，STRICT表，启用严格类型检查，避免数据类型错误，查询性能，数据纯度，都能提升，SQLite 3.37及以上版本支持，赶紧升级。

运维保障，备份，恢复，高可用，一个都不能少，Litestream，是个好工具，流式复制，后台复制WAL日志到S3等对象存储服务，时间点恢复，实现无损数据恢复，真棒。

文件系统选择，也很关键，避免网络文件系统，防止锁定机制错误导致数据库损坏，单机服务器，或者网络卷，提议使用RAID配置的裸机服务器，或者AWS EBS/Scaleway Block Storage等网络卷，靠谱。

零停机更新，SO_REUSEPORT，在一样端口上启动新版本应用，逐步替换旧版本，反向代理，使用Nginx等反向代理平滑过渡，分离存储卷，并附加到新服务器，无缝切换。

零停机故障转移，异地备份，在不同数据中心的服务器上备份数据库，分钟级切换，监控系统检测到主服务器故障时，快速启动备用服务器，并更新DNS记录，高可用承诺，提高冗余性，达到99.99%的可用性目标，不是问题。

常见坑点，也要注意，架构更新缓慢，`ALTER TABLE DROP COLUMN`，大表删除列操作，可能耗时较长，要有心理准备。

时间戳类型缺失，SQLite没有原生时间戳类型，替代方案，使用Unix毫秒时间戳(`INT`)，或者ISO-8601/RFC-3339时间戳(`TEXT`)，推荐使用Unix毫秒时间戳`Time`类型，省事。

COUNT()查询效率，SQLite不维护索引统计信息，`COUNT()`查询较慢，解决办法，触发器方案，使用触发器维护单独的计数表，避免全表扫描，提高效率。

未来展望，分布式架构，分布式SQLite，读写分离复制，单一写入数据库复制到只读副本，全球分发，副本分布在不同地区，降低访问延迟，Cloudflare D1，litefs，rqlite，mvsqlite，chiselstore，dqlite，cr-sqlite，这些项目，值得关注。

SQLite优化配置，总结一下，`PRAGMA journal_mode = WAL;`，`PRAGMA busy_timeout = 5000;`，`PRAGMA synchronous = NORMAL;`，`PRAGMA cache_size = 1000000000;`，`PRAGMA foreign_keys = true;`，`PRAGMA temp_store = memory;`，使用`BEGIN IMMEDIATE`事务，`writeDB.SetMaxOpenConns(1)`，`readDB.SetMaxOpenConns(max(4, runtime.NumCPU()))`，使用`STRICT`表，记住这些，SQLite就能飞起来了。