7.3 应用层协作与批量写入

7.3 应用层协作：预编译复用与批量写入

本节摘要：内核给足了武器（缓存、日志、编译器），应用层的用法决定它们的兑现率。本节用可复现的实验对比"逐条提交、批量提交、预编译复用、多值合并"四种写入方式的吞吐，解释每个差距对应的内核机制，并交代 temp_store 与内存告警的应对策略。

一组可以亲手复现的实验

同样的十万行插入，四种写法，在普通 SSD 笔记本上的典型结果（数字会随硬件浮动，量级关系稳定）：

-- 写法一：逐条自动提交（新手默认）
INSERT INTO events(device, value, ts) VALUES(?, ?, ?);   -- 执行十万次
-- 典型吞吐：每秒 500 至 3000 条 —— 每条都走完整事务账本

-- 写法二：一个大事务包裹
BEGIN;
-- 同一条预编译语句执行十万次
COMMIT;
-- 典型吞吐：每秒 20 万至 80 万条 —— 账本只走一次

-- 写法三：批量提交（每 5000 条一提）
BEGIN;  -- 每 5000 条 COMMIT 一次再 BEGIN
-- 典型吞吐：接近写法二，且单事务内存峰值更低

-- 写法四：多值合并（减少语句执行次数）
INSERT INTO events VALUES(?,?,?),(?,?,?),... ;  -- 每语句 50 行
-- 典型吞吐：比写法二再快 10% 至 30% —— 指令循环次数减少
​

三个数量级的差距来自哪里？写法一的每一笔都要：取写锁、创建 journal、旧页落盘、新页落盘、删 journal——第 4 章的账本走五遍流程只为存一行。写法二把这五步摊到十万行头上，中间九万九千次修改全在页缓存里完成。第 1 章 1.3 节的"成本结构"论断在这里兑现：进程内引擎的性能问题，九成出在事务粒度，不出在引擎本身。

预编译复用：不花第二次编译钱

第 3 章讲过 prepare 与 step 的分离，这里算清它省的钱：

/* 反面教材：循环里 prepare */
for (int i = 0; i < 100000; i++) {
    sqlite3_prepare_v2(db, "INSERT INTO events VALUES(?,?,?)", -1, &stmt, 0);
    /* bind ... */
    sqlite3_step(stmt);
    sqlite3_finalize(stmt);
}
/* 每次都付：词法加语法加代码生成的全套成本，外加对象分配 */

/* 正确姿势：prepare 一次，循环里只 bind、step、reset */
sqlite3_prepare_v2(db, "INSERT INTO events VALUES(?,?,?)", -1, &stmt, 0);
for (int i = 0; i < 100000; i++) {
    sqlite3_bind_text(stmt, 1, dev, -1, SQLITE_TRANSIENT);
    sqlite3_bind_double(stmt, 2, vals[i]);
    sqlite3_bind_int64(stmt, 3, ts[i]);
    sqlite3_step(stmt);
    sqlite3_reset(stmt);
}
sqlite3_finalize(stmt);
​

编译一条 INSERT 约几十微秒，十万次就是数秒——在批量写入场景里，重复编译的开销与事务粒度开销同处一个量级，两条都要治。没法改 C 代码的 ORM 用户检查两件事：驱动层的语句缓存（statement cache）是否开启、批量操作是否绕过了 ORM 的逐对象提交。MySQL 与 PostgreSQL 的用户对应检查的是连接池的预编译缓存——同一原理在不同架构里的落点不同，第 3.3 节的生命周期对照表可以直接拿来对号。

temp_store 与排序峰值

ORDER BY、GROUP BY、DISTINCT 在无索引可用时动用临时存储，PRAGMA temp_store 三档：

排序峰值 = 一次性排序的数据量（物化全行，不是只排键）。百万行宽行排序选 MEMORY 可能把堆吃穿——这就是 7.2 节预算公式里"temp_store 峰值"那一项的来源。策略上宁可让个别大排序落盘，也别为它把常驻缓存预算砍半：排序是一次性的，缓存是全程的。

内存告警与优雅降级

移动端收到系统内存告警时的标准动作序列：

/* 1. 释放可释放的页缓存 */
sqlite3_db_release_memory(db);
/* 2. 收缩软上限，让后续大分配自然失败而不是拖垮进程 */
sqlite3_soft_heap_limit64(16 * 1024 * 1024);
/* 3. 暂停大事务，把在途批量操作收尾提交（小事务先行） */
​

第三步是关键纪律：内存告警时刻恰好持有一个巨型写事务的应用，比不写数据库的应用更危险——脏页多、缓存涨、回收不动。批量写入的"分块提交"（写法三）在故障与内存两个维度都更稳，代价只是吞吐的个位数折让。服务端世界的对应常识是"长事务是万恶之源"；SQLite 世界把它具体化成"巨型事务是内存与恢复的双重风险"。

常见问题速答

**事务多大算"太大"？**三个水位任一触顶即算太大：脏页预计量超过 cache_size 的一半（ spill 开始出现）；WAL 或 journal 因单个事务的增长超过百 MB 级（检查点与恢复时间开始难看）；事务持续时间进入分钟级（长读长写在三库里都是公敌）。批量任务按"几千到几万行或几十 MB"切分块是稳妥默认，具体数字用你的行宽代入 7.1 的预算算式算出来。

**预编译语句要缓存多少个？**按业务语句形状的数量定，不是按并发。SQLite 的语句对象很轻（KB 级），缓存几十上百个毫无压力；真正要防的是"无上限的动态语句"——拼接表名或动态条件的 ORM 会生成无限形状的语句，任何缓存都会被击穿。治理办法是把动态维度收敛（枚举化、白名单化），让语句形状可数。JDBC 等驱动层的连接池缓存同理，设上限加淘汰即可。

本节要点回顾

• 逐条自动提交与事务包裹的吞吐差两到三个数量级，差距全部来自事务账本的重复支付。
• 预编译复用在批量场景与事务粒度同量级重要；ORM 用户要核对语句缓存与批量绕行。
• temp_store 决定排序峰值的位置；宁可个别排序落盘，不砍常驻缓存。
• 内存告警三步：释放缓存、收缩上限、小事务收尾——巨型事务在告警时刻最危险。

内核与应用的协作讲完。最后一章处理交付前的最后一公里：备份、诊断、安全与部署。