一听到“SQLite 调优”很多人的第一反应是一个嵌入式数据库还调什么优但真把几十万行数据丢进去、再挂上几个并发读连接你就会发现初始化参数没设对再怎么优化 SQL 都白搭。SQLite 没有像 MySQL 那样的 my.cnf它的“初始化参数”分散在编译宏、sqlite3_config() 和每次连接时执行的 PRAGMA 里如果你把这些当成可有可无的开场白数据库通常在 1 万行内看着挺快到 10 万行就开始给你脸色看。这篇文章我会从初始化参数这个角度把 SQLite 从“能用”调到“好用”。后面要聊的很多内容是踩过坑之后才想明白的。比如我最开始接手一个用 SQLite 做数据采集的小项目单 db 文件跑了半年后写入一次要从几十毫秒变成几秒后来发现根子不在 SQL 语句而是在建立连接时没人设置过任何初始化参数。如果你也正被 SQLite 的性能或并发问题折腾这篇值得你花十分钟从头看完。1. 先把它说清楚SQLite 的“初始化参数”到底是什么1.1 为什么你找不到像 MySQL 那样的 my.cnfMySQL、PostgreSQL 这种服务型数据库启动时读一个配置文件里面写着 buffer pool、连接数、日志级别等参数改完重启生效。SQLite 是嵌入式库它没有独立进程也没有全局配置文件你的程序直接链接它所以“配置”只能通过 API 和 SQL 语句传递。这意味着两件事第一不同连接可以有不同的参数第二参数必须在合适的时机设置比如打开数据库连接之后立刻执行或者通过 sqlite3_config() 在第一个连接打开之前设置。很多人不习惯这种模式以为“连上库就能跑”结果 busylock、磁盘同步、缓存不足这些坑全踩一遍。所以“SQLite 需要初始化参数”这句话严格说并不准确准确说是“你在初始化 SQLite 环境时需要主动告诉它怎么工作”。你不说它就按编译时的默认值来而默认值是为兼容性设计的不是为性能设计的。1.2 三个层面的“初始化参数”编译期、全局期、连接期我习惯把 SQLite 的参数分成三层调优时逐层排查层面设置方式典型选项生命周期编译期编译 SQLite 源码时定义宏SQLITE_THREADSAFE、SQLITE_DEFAULT_CACHE_SIZE、SQLITE_TEMP_STORE库编译后基本固定全局期第一次打开数据库前调用 sqlite3_config()SQLITE_CONFIG_MULTITHREAD、SQLITE_CONFIG_MEMSTATUS进程生命周期连接期每个连接打开后执行 PRAGMAjournal_mode、synchronous、cache_size、busy_timeout当前数据库连接编译期参数决定了一个 SQLite 库的“底子”。比如 SQLITE_THREADSAFE 如果编译成 0就算你代码里用 sqlite3_open 打开一百个连接并发安全也没有保障。全局期参数影响线程模式、内存统计、互斥锁策略通常在程序启动时调用一次。连接期参数最常用就是每次拿到连接后执行的那一串 PRAGMA。实际调优时大多数性能问题都能通过连接期参数解决但如果你二三十个线程同时写同一个库那就必须回头检查编译期线程模式有没有开对。1.3 影响调优决策的核心默认值SQLite 的默认值不是乱给的但也绝不是性能最优解。几个关键的默认值先记在脑子里journal_mode 默认是 delete对应回滚日志模式读并发很差。synchronous 默认是 FULL每次事务提交都要把日志和数据库文件都刷到磁盘。cache_size 默认是 2000 页按默认 4096 字节页大小算总共才 8MB 缓存。busy_timeout 默认是 0遇到锁直接返回 SQLITE_BUSY不会等。mmap_size 默认是 0不使用内存映射。foreign_keys 默认是 OFF外键约束不检查。这些默认值组合在一起就是一个“绝对不会出错但性能平庸”的数据库。你要做的就是根据自己的读写场景把这些默认值改掉。2. 全局配置sqlite3_config 和编译开关动手前先看一眼2.1 sqlite3_config 能管什么如果你是写 C/C 程序直接调用 SQLite C API那么 sqlite3_config() 是绕不开的初始化入口。它必须在任何数据库连接打开之前调用否则会返回 SQLITE_MISUSE。常用的配置项有这么几类SQLITE_CONFIG_MULTITHREAD使用多线程模式SQLite 内部会加锁但同一连接同一时间只允许一个线程使用。SQLITE_CONFIG_SERIALIZED串行化模式更安全但多线程竞争时性能会下降。SQLITE_CONFIG_MEMSTATUS控制是否统计内存分配情况统计本身有开销追求极致性能可以关掉。SQLITE_CONFIG_PAGECACHE指定页缓存内存池能减少 malloc 调用。不是所有程序都需要调 sqlite3_config但如果你在多线程环境下打开同一个 SQLite 文件至少要确认线程模式是合理的。默认情况下SQLite 编译时如果开了 SQLITE_THREADSAFE1相当于 SERIALIZED安全但锁竞争存在。如果你能保证每个连接只被一个线程使用可以设置成 MULTITHREAD减少锁开销。用 Python、Java、Go 这类语言时一般接触不到 sqlite3_config语言绑定已经把这一步封装好了。这时候你要关注的更多是连接期 PRAGMA。2.2 线程模式对并发调优的直接影响很多人问“SQLite 能不能多线程并发写”答案是能但同一时刻只能有一个连接写成功其他连接要么等到 busy_timeout 超时要么立即报 SQLITE_BUSY。SQLite 的锁粒度是整库级别的不是行级或页级。所以并发调优的目标不是“让多个写同时进行”而是“让写尽快完成把锁占用时间压到最短”。在这个前提下线程模式如果设置成 SERIALIZEDSQLite 内部所有 API 调用都加一把大锁多个线程同时读也可能被串行化读并发就废了。我见过不少桌面应用默认就是 SERIALIZED一开多窗口查询就卡其实是全局锁在拖后腿。走 MULTITHREAD 每个连接单线程使用读并发会好很多。2.3 编译开关怎么查不用重编译也能确认如果你用的是别人编译好的 SQLite 库或系统自带版本怎么知道编译了哪些宏很简单SQLite 命令行工具里执行 .compile_options 就能看到。sqlite3 app.db .compile_options执行结果里会列出一堆编译选项重点关注SQLITE_THREADSAFE输出 0、1、2 分别对应禁用、串行化、多线程。SQLITE_WAL是否支持 WAL 模式。SQLITE_DEFAULT_PAGE_SIZE默认页大小。SQLITE_DEFAULT_CACHE_SIZE默认缓存页数。SQLITE_TEMP_STORE临时表的存储位置策略。如果你的 SQLite 库不支持 WAL老版本编译时没开这个宏那后面说的 WAL 模式初始化参数怎么设都没用只能换一个编译版本。所以第一步永远是查看自己用的库支持哪些特性。3. 连接级初始化的标准配方这些 PRAGMA 每次打开都要执行3.1 一套经得起实测的初始化序列我现在的习惯是建立连接后立刻执行一段固定的初始化 SQL。以 Python 的 sqlite3 模块为例import sqlite3 conn sqlite3.connect(app.db, timeout5) cur conn.cursor() cur.execute(PRAGMA journal_modeWAL;) cur.execute(PRAGMA synchronousNORMAL;) cur.execute(PRAGMA busy_timeout5000;) cur.execute(PRAGMA cache_size-20000;) cur.execute(PRAGMA temp_storeMEMORY;) cur.execute(PRAGMA mmap_size268435456;) cur.execute(PRAGMA foreign_keysON;) cur.execute(PRAGMA wal_autocheckpoint1000;)这套组合覆盖了读并发、写性能、内存使用和锁等待一般业务项目直接拿去用没问题。但注意Python 的 sqlite3.connect 默认 timeout 参数也会影响 busy_timeoutPRAGMA busy_timeout 的设置是连接级的两者会有叠加你需要自己验证最终生效值。如果你用 C 语言那就是在 sqlite3_open_v2 成功后执行同样的 sqlite3_exec。sqlite3_exec(db, PRAGMA journal_modeWAL;, NULL, NULL, NULL); sqlite3_exec(db, PRAGMA synchronousNORMAL;, NULL, NULL, NULL); sqlite3_exec(db, PRAGMA busy_timeout5000;, NULL, NULL, NULL);3.2 每个参数的“为什么”和典型取值这组参数不是随手拍的每个都有明确取舍。journal_modeWAL是把默认的回滚日志模式改成预写日志模式。WAL 模式下读和写可以并行不会出现“有人在写所有读全被阻塞”的尴尬情况。代价是数据库目录里多出 -wal 和 -shm 两个文件备份时要一起处理。synchronousNORMAL配合 WAL 使用时只有 checkpoint 时才把 WAL 文件刷到磁盘每次事务提交不会强制 fsync性能提升非常明显。如果你还是默认的 FULLWAL 的优势会少一半。busy_timeout5000是让 SQLite 在遇到锁时最多等 5 秒。默认值是 0也就是说一旦发现库被其他连接锁住立刻报 SQLITE_BUSY。很多并发连接下的“database is locked”错误就是没设置这个初始化参数导致的。cache_size-20000不是 20000 页负号表示单位是 KB也就是分配 20MB 作为页缓存。如果写正数 20000会被当成 20000 页在 4096 字节页大小下是 80MB容易超内存。用负号更可控。temp_storeMEMORY表示临时表和临时索引放到内存中。如果你的 SQL 里有临时排序、GROUP BY、子查询中间结果这个参数能减少磁盘 I/O。mmap_size268435456表示允许 SQLite 用 256MB 的内存映射来读取数据库文件。读多写少场景下能减少系统调用但高并发写时要小心后面会专门说。foreign_keysON是让外键约束真的生效。它不是性能参数但属于“初始化时该做没做”的典型例子。很多项目默认 OFF等到数据脏了才发现外键从没检查过。3.3 不同场景下的配方差异上面的组合适合大多数“读写混合”的业务系统。如果你的场景特殊需要微调只读分析场景可以把 journal_mode 保持在 WALsynchronous 降到 OFFmmap_size 适当调大cache_size 也调大因为基本没有写竞争。高并发写场景WAL 反而是双刃剑写并发到一定程度时WAL 的锁竞争也会出现此时重点是把每次写的事务变小、commit 变快而不是无限调大缓存。数据安全第一场景比如交易记录、日志审计synchronous 保持 FULLbusy_timeout 调大宁可写入慢一点也不能丢数据。配方是死的场景是活的。我记得自己在一次实际项目里把 cache_size 调太大结果低内存嵌入式设备直接 OOM后来才知道那台机器总内存才 128MB20MB 的页缓存加一堆连接池连接直接把内存吃光了。所以初始化参数的“合理值”必须结合运行环境重新评估。4. page_size / cache_size / mmap_size三个容易调反的参数4.1 page_size建库之前定终身page_size 是 SQLite 数据库中页的大小默认通常是 4096 字节。页大小影响 B-Tree 的深度和磁盘 I/O 粒度但很多人一上来就调成 8192 或 16384以为越大性能越好结果反而浪费内存。关键点在于page_size 最好在建库之前或库为空时设置。对一个已有大量数据的数据库直接执行PRAGMA page_size8192;不会立刻生效必须跟着执行一次VACUUM;重建整个数据库文件这个过程会复制全部数据慢而且临时占用空间。PRAGMA page_size8192; VACUUM;在 SSD 上4096 和 8192 的差别通常不大在机械硬盘或某些嵌入式存储上更大的页能减少 I/O 次数有一定收益。但代价是页缓存按页计算页变大了同样缓存大小能缓存的页数就少了。如果你有非常宽的字段或大量大文本可以考虑 8192常规业务数据 4096 完全够用。4.2 cache_size单位不是 KB 是页我见过有人设置PRAGMA cache_size20000;后跟我抱怨内存没涨一查才知道他把 20000 当成 KB 来期待实际上那是 20000 页按默认 4096 字节/页计算约 80MB也不是立刻精确占用的SQLite 是懒加载。更要命的是对同一个连接经常只设置一次PRAGMA cache_size就忘记后续代码在多个连接里各设各的结果不同连接表现不一致。如果你希望单位更直观用负值PRAGMA cache_size-20000; -- 20MB PRAGMA cache_size-40000; -- 40MBcache_size 设置的值是每个连接独立拥有的。五个并发连接都设-20000理论上最多能占 100MB 内存实际不会瞬间用完但你要有心理准备。低内存环境宁可调小一点比如 -80008MB也不要让缓存把系统内存吃紧。4.3 mmap_size用内存换锁风险的临界点mmap_size 是很多人忽略的初始化参数。它控制 SQLite 是否把数据库文件的一部分映射到进程地址空间。开启后读操作不再走 read() 系统调用某些场景能明显降低 CPU 占用。对于“单 db 文件 大量只读查询 写不频繁”的业务非常合适。但 mmap 不是没有代价。一旦多个进程同时打开同一个数据库文件并且其中一个进程通过 mmap 读取另一个进程执行写操作触发文件扩展读进程有可能遇到 SIGBUS。官方文档也提醒过这个问题。SQLite 的默认 mmap_size 是 0也就是不启用就是为了避免这种边界问题。我的经验是只读连接可以放心设置 mmap_size比如 256MB读写混合连接谨慎一些如果你没法保证写操作频率很低宁可让 mmap_size 保持较小值甚至 0。另外网络文件系统上不要把 mmap_size 调大网络盘的文件锁和内存映射组合起来很容易出奇怪故障。5. 性能大头在写入事务和 synchronous 的配合5.1 默认的 FULL_SYNC 到底慢在哪如果你不设置任何初始化参数SQLite 默认在 delete 日志模式下每次事务提交都要做两次 fsync先刷回滚日志再刷数据库文件。机械硬盘上每次 fsync 成本可能高达几十毫秒你每执行一条 INSERT 就自动开一个事务积少成多写入自然慢得离谱。很多人说“SQLite 批量插 10 万条要半分钟”一半原因就是默认 synchronousFULL 和 autocommit。SQLite 单条插入在 autocommit 模式下等于是“每次 INSERT 都开事务、提交事务、等磁盘”。你要做的第一件事不是换数据库而是把短事务合并成长事务。5.2 WAL NORMAL 手动事务的组合拳WAL 模式下提交事务时默认把记录追加到 WAL 文件不直接改主数据库文件。如果把 synchronous 设成 NORMAL只有执行 checkpoint 时才需要刷 WAL平时事务提交就基本只是在内存里标记一下配合手动事务效果会非常好。举个例子同样是插入 5000 条数据cur.execute(BEGIN) for i in range(5000): cur.execute(INSERT INTO sensor(data) VALUES (?), (i,)) cur.execute(COMMIT)这段代码在 WAL NORMAL 下5000 条插入因为共享一个事务磁盘同步只发生一次耗时往往是 autocommit 的十分之一甚至更低。如果你的业务允许比如批量采集场景每攒够 1000 条再提交一次效果远比“边采集边提交”好。在 C 语言接口里是 sqlite3_exec(db, BEGIN, ...) 和 sqlite3_exec(db, COMMIT, ...)逻辑跟上面一致。我遇到的大部分“写入慢”问题排查到最后都不是 SQLite 本身慢而是事务边界划得太碎。5.3 什么情况下才建议 synchronousOFFsynchronousOFF是把磁盘同步完全禁用commit 返回几乎不会被磁盘瓶颈拖住写入速度可以暴力提升。但代价是如果操作系统崩溃或突然断电数据库文件很可能损坏而且损坏后基本没法用PRAGMA integrity_check救回来因为你连日志都没刷根本不知道最后哪些页是旧数据。我不会在生产环境把 synchronousOFF 用于重要数据。我只建议在以下场景用临时计算用的中间数据库丢了可以重新生成。缓存类数据重启后可重建。写入量极大、硬件掉电保护做得极好的嵌入式设备。如果你拿不准就继续用 synchronousNORMAL已经很稳了。默认的 FULL 适合强一致场景但如果你开了 WALFULL 和 NORMAL 的可靠性差异并不大性能差异却很大所以不用执着于 FULL。6. 一个从 30 秒到 200ms 的实际调优案例6.1 现象和第一轮排查有一年我帮朋友看一个数据采集网关的问题。设备是嵌入式 Linux用 C 写的采集程序每 5 秒从串口读一批传感器数据写入本地 SQLite 单 db 文件。跑了三个月后一次写入要 30 秒导致采集线程不断堆积系统负载飙升。我当时第一反应是看索引因为通常写入慢跟索引过多有关。结果用 db browser for sqlite 打开数据库一看记录不到 200 万行索引也只有一个主键SQL 更不复杂就是单表 INSERT。那就排除了“SQL 写得烂”这个原因问题几乎一定出在初始化设置上。再查源代码果然程序打开数据库后只执行了sqlite3_open_v2没设置任何 PRAGMA。也就是说它一直运行在默认配置下journal_modedeletesynchronousFULLbusy_timeout0。每条 INSERT 单独提交每次提交都等磁盘同步采集周期又短锁冲突自然越来越严重。6.2 初始化参数调整的完整过程我改的过程很简单但每一步都有针对性。第一把数据库切到 WAL 模式PRAGMA journal_modeWAL; PRAGMA wal_autocheckpoint1000;这解决了读写互相阻塞的问题。采集线程写入时查询线程不再被卡死。第二把 synchronous 改成 NORMAL。这个设备有备用电源断电概率低而且传感器数据就算丢最后几秒也能接受真正不能接受的是写入一直卡死。第三设置 busy_timeout5000让采集线程在遇到写锁时等一等而不是立刻报错退出。第四把采集程序里单条 INSERT 改成批量事务。串口读一批 200 条数据后一次性 BEGIN INSERT COMMIT。调整后的完整初始化段落sqlite3_exec(db, PRAGMA journal_modeWAL;, NULL, NULL, NULL); sqlite3_exec(db, PRAGMA synchronousNORMAL;, NULL, NULL, NULL); sqlite3_exec(db, PRAGMA busy_timeout5000;, NULL, NULL, NULL); sqlite3_exec(db, PRAGMA cache_size-8000;, NULL, NULL, NULL); sqlite3_exec(db, PRAGMA temp_storeMEMORY;, NULL, NULL, NULL);为什么 cache_size 只给了 -8000因为设备总内存只有 128MB我可不想开着开着就 OOM。给了 8MB 缓存足够应付传感器采集重点不在缓存而在免同步和批量事务。6.3 验证结果和后续教训改完之后同一台设备再次执行同样的写入逻辑一批 200 条的 INSERT 操作稳定在 200ms 级别最坏也没超过 500ms采集线程不再堆积。从“30 秒一次”到“200ms 一批”这个提升没有改任何 SQL只是把初始化参数和事务边界理顺了。后续教训有三条不要把所有性能问题都归到 SQL 优化上初始化参数往往是第一块性价比最高的地方。嵌入式设备调参要特别小心内存上限不能照搬 PC 端的大缓存配置。每次连接建立后的初始化代码必须统一最好是封装成公共函数不要在每个业务模块里写一遍还不一致。7. 如何确认初始化参数真的生效检查命令与监控指标7.1 用命令行快速核对所有配置很多人在代码里设置了 PRAGMA但根本没确认是否真的生效。用 sqlite3 命令行连接同一个数据库然后逐条查询sqlite3 app.db在 sqlite 提示符下执行PRAGMA journal_mode; PRAGMA synchronous; PRAGMA page_size; PRAGMA cache_size; PRAGMA mmap_size; PRAGMA busy_timeout; PRAGMA temp_store; PRAGMA wal_autocheckpoint;注意PRAGMA busy_timeout 和 cache_size 这类连接级参数命令行工具打开的是一个新的连接如果你没在命令行里执行设置你看到的只是命令行连接自己的默认值并不代表你的业务连接也是这个值。要真正验证最好在业务程序启动后把每个连接设置的参数打一条日志。用 db browser for sqlite、sqlitestudio 或 dbeaver 打开数据库也可以执行同样的 PRAGMA 查询。db browser for sqlite 还有图形化的数据库结构查看功能用来确认 page_size、主键、索引都很方便但它同样只是代表那个 GUI 连接看到的参数不是你的应用连接。7.2 监控 SQLite 运行时的几个状态量调优之后效果不能靠感觉要看这几个指标-- 当前 WAL 文件里有多少页还没合并回主库 PRAGMA wal_checkpoint(PASSIVE); -- 数据库总页数和空闲页数 PRAGMA page_count; PRAGMA freelist_count;freelist_count 很值得关注。频繁增删数据后SQLite 会把释放的页留在文件里数据库文件巨大但实际数据很少文件膨胀后读写性能也会下降。每隔一段时间可以用VACUUM;或者PRAGMA incremental_vacuum;清理前提是你设置了PRAGMA auto_vacuumINCREMENTAL;这也属于初始化阶段要做的决策之一。另一种方式是开 sqlite3 的 status 接口。C 接口有 sqlite3_status()可以查 SQLITE_STATUS_MEMORY_USED、SQLITE_STATUS_PAGECACHE_USED配合日志系统能看出内存页缓存占用是否符合预期。Python 里可以通过扩展模块访问但多数情况不需要那么细你只要在测试环境观察内存和 CPU 曲线就够了。7.3 初始化参数调整的边界哪些改了也没用最后说几个越调越错的场景帮大家避坑。第一page_size 对已有数据库不是“改了立刻生效”就算你执行PRAGMA page_size8192;后马上PRAGMA page_size;查出来是 8192也不代表现有表已经按 8192 页存储了必须对空库设置或在 VACUUM 时才会实际重写文件。所以建库初期就定好页大小别等到数据堆了几百万行再调。第二WAL 模式不适合网络文件系统。NFS、SMB 这类共享存储对文件锁和持久化支持不稳定WAL 的 -wal 和 -shm 文件在这种环境下容易坏库。如果 SQLite 文件放在网络盘上老老实实用默认 delete 日志模式或者干脆不要用这种部署方式。第三PRAGMA optimize;不是初始化参数。它在 SQLite 3.18 之后出现是用来在关闭连接前收集统计信息的不适合在初始化时执行。你可以把它放在应用退出、数据库连接关闭的清理阶段。另外有些语言库默认会帮你做一部分初始化。比如 Python 3.11 的 sqlite3 模块默认autocommit行为有变化同一个PRAGMA busy_timeout在不同版本里可能表现不同。跨平台的时候务必对“初始化参数最终值”做一次断言式检查而不是假设代码写了对就肯定是对的。我个人的习惯是封装一个数据库初始化函数函数最后把关键 PRAGMA 的值全部查出来打到日志里线上出问题时看一眼启动日志就知道每台设备的参数是不是一致。这个方法很土但确实是排查初始化参数类问题最高效的方式。