嵌入式数据库的并发与性能最佳实践

零配置嵌入式数据库的权威最佳实践指南，涵盖并发控制、WAL模式、外键约束与性能调优，规避常见陷阱

基本信息

• 技能名称?SQLite
• 中文名称?嵌入式数据库的并发与性能最佳实践
• 作者?ivangdavila
• 分类?专业技能
• 版本?1.0.0
• 标签?sqlite, embedded-database, concurrency, wal-mode, performance-tuning, database-maintenance, sql

使用方法

使用说明
核心用法
SQLite 是一个零配置、服务器端的嵌入式关系数据库，无需独立进程即可运行。本技能文档系统梳理了生产环境必备的核心技术点：
并发与WAL模式 — SQLite 最大痛点。仅支持单写多读，默认模式会阻塞读操作。启用 PRAGMA journal_mode=WAL 可实现读写并发，配合 PRAGMA busy_timeout=5000 避免 SQLITE_BUSY 错误。注意 WAL 会生成 -wal 和 -shm 辅助文件，备份时必须同步复制。
数据完整性 — 外键约束默认关闭！每个连接必须执行 PRAGMA foreign_keys=ON ，否则级联删除等约束完全失效。建议连接后立即检查返回值确认开启。
类型与Schema — 传统表为"类型亲和"机制，INTEGER 列可存入字符串而不报错。SQLite 3.37+ 支持 STRICT 严格表模式强制类型校验。无原生布尔/日期类型，需用 INTEGER 0/1 或 ISO8601 文本存储。
性能调优 — 关键配置组合： cache_size 调至 64MB 以上、 synchronous=NORMAL 配合 WAL 平衡安全与速度、 temp_store=MEMORY 加速排序。批量操作务必显式包裹 BEGIN...COMMIT ，速度提升 10-100 倍。长期连接关闭前执行 PRAGMA optimize 更新查询规划器统计信息。
维护操作 — DELETE 不会释放文件空间，需定期 VACUUM 重写数据库（需 2 倍磁盘空间）。3.27+ 版本可用 VACUUM INTO 直接生成独立备份副本。
显著优点
零运维成本 ：无服务进程、无权限配置、单文件即数据库
WAL 模式突破 ：读写并发能力满足大多数桌面/移动端场景
现代特性演进 ：3.35+ 支持 DROP COLUMN ，3.37+ 支持 STRICT 类型，持续活跃开发
高级索引 ：表达式索引、部分索引、覆盖索引完整支持
潜在缺点与局限
写并发瓶颈 ：单写锁设计天然不适合高并发写入场景（如多用户 Web 后端），应果断迁移至 PostgreSQL
有限 ALTER 能力 ：修改列类型、添加约束仍需重建表流程
类型系统混乱 ：历史遗留的类型亲和机制易导致隐式转换 Bug
ROWID 不可靠 ： VACUUM 会重新分配，依赖它做主键会导致数据关联断裂
适合人群
桌面应用、移动 App（iOS/Android）、IoT 设备本地存储开发者
中小型数据分析、测试环境、嵌入式系统工程师
需要嵌入数据库但无 DBA 资源的独立开发者和小团队
常规风险
并发陷阱 ：未启用 WAL + busy_timeout 的生产环境必现随机失败
静默数据损坏 ：外键关闭时违反约束无警告、类型亲和导致脏数据入库
备份失误 ：直接复制打开中的数据库文件或遗漏 WAL 文件导致不可恢复损坏
内存数据库误用 ： :memory: 每个连接隔离，共享内存需用 file::memory:?cache=shared 语法