附录 PRAGMA 速查与三库决策卡
附录 · PRAGMA 速查与三库决策卡
本附录定位：全册机制的浓缩对照。前半是按场景分组的 PRAGMA 速查表（含正反用法提示），后半是三库关键能力的决策卡，供日常工作中直接翻查。所有条目均可回溯到对应章节的机制讲解。
一、PRAGMA 速查：按场景分组
场景 A：新建数据库（建库前一次性设置，之后难以更改）
| PRAGMA | 建议值 | 说明 | 详见 |
|---|---|---|---|
| page_size | 4096（默认）或 8192 | 大字段多、追求更矮的树时取 8192；普通场景用默认 | 第 1.4、2.2 节 |
| auto_vacuum | NONE（默认）或 INCREMENTAL | 增量模式可用 incremental_vacuum 逐步归还空间，避免整库重写 | 第 8.2 节 |
| encoding | UTF-8（默认） | 建库后不可改 | 第 1.4 节 |
场景 B：运行期性能与并发（每个连接初始化时执行）
| PRAGMA | 建议值 | 说明 | 详见 |
|---|---|---|---|
| journal_mode | WAL | 读写并行的唯一选项；持久写入文件头 | 第 4.2 节 |
| synchronous | NORMAL | WAL 下的安全快档；FULL 只在换回回滚模式时需要 | 第 4.2 节 |
| busy_timeout | 5000 | 撞写锁的等待毫秒数；零值等于不等待直接报错 | 第 4.4 节 |
| cache_size | -65536 | 负数按 KB；总预算 = 此值乘连接数 | 第 7.1 节 |
| temp_store | MEMORY 或默认 | 排序峰值可控且内存富余时 MEMORY | 第 7.3 节 |
| mmap_size | 0 到 268435456 | 只加速读路径；网络文件系统上必须为 0 | 第 7.2 节 |
| wal_autocheckpoint | 1000（默认） | WAL 页数阈值；写入密集可调大并配合手动检查点 | 第 4.3 节 |
场景 C：正确性与安全（按需开启）
| PRAGMA | 建议值 | 说明 | 详见 |
|---|---|---|---|
| foreign_keys | ON | 每连接默认关闭，需显式打开才生效 | 第 8.4 节 |
| trusted_schema | OFF | 禁止 schema 对象调用任意应用函数 | 第 8.3 节 |
| defensive | ON | 阻止直接写 sqlite_schema 等内部表 | 第 8.3 节 |
| ignore_check_constraints | 保持 OFF | 临时放行校验用，用完立即关闭 | — |
场景 D：诊断与维护（随时查询，无副作用）
| PRAGMA | 作用 | 详见 |
|---|---|---|
| integrity_check / quick_check | 全量或快速结构校验 | 第 8.2 节 |
| freelist_count、page_count | 碎片与占用规模 | 第 8.2 节 |
| wal_checkpoint(PASSIVE 或 TRUNCATE) | 手动检查点，返回忙标记与页数统计 | 第 4.3 节 |
| optimize | 按需执行 ANALYZE 与清理，建议连接关闭前调用 | 第 5.2 节 |
⚠️ 两个最常见的 PRAGMA 误用：把 cache_size 的正负号搞反（正数是页数，负数是 KB，差四倍）；在 WAL 模式下把 synchronous 设为 OFF 以求性能——WAL 加 NORMAL 才是安全与速度的正确交点，OFF 留给可丢弃数据。
二、一次写入的完整旅程（全册机制串烧）
以一条 INSERT INTO events VALUES(...) 在 WAL 模式下的完整旅程收束全册，每一步标注对应的章节：
1. 语句进入 prepare：词法与 LALR 解析，产出表达式树 （第 3.1 节） 2. 代码生成：表达式树变成 VDBE 字节码，占位符登记参数槽 （第 3.2 节） 3. step 执行：参数绑定进寄存器，字节码推动游标在表树上定位 （第 3.2 节） 4. 页修改：目标叶子页在页缓存中变脏；若无索引则只动一棵树 （第 2.1、5.1 节） 5. 事务提交：新页追加进 WAL 帧，帧头带提交易，校验和累计 （第 4.3 节） 6. 并发面：写事务独占写者位，读者继续读主文件旧世界快照 （第 4.4 节） 7. 检查点：WAL 攒到阈值后由 PASSIVE 检查点批量回写主文件 （第 4.3 节） 8. 内存面：脏页留在 cache_size 预算内，mmap 页只服务读者 （第 7.1、7.2 节）
这八步对应八章的知识主线——面试或故障复盘时，能顺着这条线讲清任何一环的机制与调优点，就是本册读完的检验标准。
三、三库决策卡
卡 1：什么时候选 SQLite
- 数据与单设备或单应用绑定：本地存储、配置缓存、离线数据、边缘设备。
- 读多写少或写入可批量：内容库、日志缓冲、定期归并的采集数据。
- 要求数据"跟文件走"：拷贝即迁移、随应用打包、用户粒度隔离。
- 微秒级延迟诉求：高频点查、进程内实时数据面板。
卡 2：什么时候选 MySQL 或 PostgreSQL
- 多机多实例共享写入，或写入吞吐超出单写者模型（持续数万写 QPS 以上）。
- 细粒度权限与审计：多角色访问、行级安全、操作合规留痕。
- 强分析型负载：复杂窗口函数、并行查询、大表连接（PG 与 MySQL 的优化器与并行能力更适配）。
- 独立扩缩容：数据库需要独立于应用扩容、主从复制、时间点恢复的完整生态。
卡 3：关键能力对照（浓缩版）
| 能力 | SQLite | MySQL InnoDB | PostgreSQL |
|---|---|---|---|
| 行存储形态 | 表即主键 B-Tree | 主键聚簇 | 堆表加全二级索引 |
| 日志架构 | 回滚日志或 WAL 整页追加 | redo 加 undo 双日志 | WAL 记录流加多版本堆 |
| 隔离与并发 | 快照隔离，单写者 | RR 默认，行锁加间隙锁 | RC 默认，MVCC 加 SSI |
| 统计与优化 | stat1 平均数，无直方图 | 持久统计加直方图 | MCV 直方图最全 |
| 全文检索 | FTS5 影子表加倒排 | FULLTEXT 加 ngram | tsvector 加 GIN |
| 备份 | backup API 或 VACUUM INTO | 逻辑导出或物理热备 | 基础备份加 WAL 归档 |
| 权限模型 | 文件系统权限 | 库表列级授权 | 角色加对象 ACL |
| 内存观 | 进程内合租，按连接预算 | 独占缓冲池 | 双层，信任系统页缓存 |
卡 4：从服务端迁移到 SQLite 的五步
- 主键策略审查：TEXT 主键改 INTEGER 或用 WITHOUT ROWID（第 2.3、5.1 节）。
- 索引瘦身：按写入 QPS 反推索引数量，删掉只为查询偶发服务的树（第 2.2 节）。
- 事务粒度改造：逐条自动提交改批量包裹，预编译复用（第 7.3 节）。
- 权限模型换位：数据库账号换成文件权限加只读连接（第 8.3 节）。
- 运维脚本重写：备份改三招，监控改应用内采样，慢查询告警自建（第 8.1、8.2 节）。
四、延伸阅读指引
本册刻意避开了三类内容，指一条继续深入的路。源码层面：pager 与 WAL 的实现细节在官方源码树的说明文档中有两篇规格书级别的长文（fileformat 与 walformat），读完本册第 1、4 章即可无障碍阅读。对比层面：若工作横跨三库，建议把本册的三库对照表扩展成团队自己的"方言对照维基"，收录错误码、类型亲和性、日期函数三块本册未展开的差异。实践层面：官方测试覆盖率报告与短小精悍的"how to corrupt"文档，展示了数据库损坏的所有可能路径——把每一页读成排错经验，是内核学习最扎实的收尾方式。
全册终：回头看导读的知识地图——八章的箭头最终都指向"用机制的确定性对抗系统的复杂性"。愿你在下一次面对慢查询、备份窗口或内存告警时，手里有图，心中有账。