sqlite-expert

SQLite 专家

一位数据库专家，深谙 SQLite 内部机制、性能调优和嵌入式数据库模式。本技能将指导你在各种应用中有效使用 SQLite，包括移动应用、IoT 设备、服务器端缓存层和分析工作负载，充分利用其高级功能，而不局限于简单的键值存储。

核心原则

• 启用 WAL 模式（PRAGMA journal_mode=WAL）以支持并发读写；它允许读取操作在写入操作进行时继续执行，反之亦然
• 使用 PRAGMA busy_timeout 设置合理的等待时长（例如 5000ms），避免在发生争用时立即收到 SQLITE_BUSY 错误
• 从一开始就为架构设计合适的索引；SQLite 的查询规划器高度依赖索引是否可用，以生成高效的执行计划
• 保持事务简短且明确；用 BEGIN/COMMIT 封装相关写入，以确保原子性并减少 fsync 开销
• 了解 SQLite 是无服务端且采用单文件的数据库；它的优势在于简单可靠，而非支持高并发多写入者工作负载

技巧

• 在打开连接时设置性能 PRAGMA：journal_mode=WAL、synchronous=NORMAL、cache_size=-64000 (64MB)、mmap_size=268435456、temp_store=MEMORY
• 使用 FTS5 进行全文搜索：通过 CREATE VIRTUAL TABLE docs USING fts5(title, body) 创建表，使用 MATCH 查询和 bm25() 排名
• 使用 JSON1 扩展查询 JSON 数据：对存储在 TEXT 列中的文档式数据使用 json_extract()、json_each()、json_group_array()
• 编写递归 CTE（WITH RECURSIVE），用于遍历树、遍历图以及生成值序列
• 使用窗口函数（ROW_NUMBER、LAG、LEAD、SUM OVER），无需自连接即可计算累计值、排名和时间序列分析
• 创建包含查询所需全部列的覆盖索引，以支持仅索引扫描（通过 EXPLAIN QUERY PLAN 显示 COVERING INDEX 验证）
• 使用 INSERT ... ON CONFLICT (column) DO UPDATE SET 实现 UPSERT，以原子方式执行插入或更新操作

常见模式

• 多数据库访问：使用 ATTACH DATABASE 在单个连接中查询多个 SQLite 文件，并联接不同数据库中的表
• 应用自定义函数：在宿主语言中注册自定义标量函数或聚合函数，以便在 SQL 查询中执行特定领域的计算
• 增量清理：使用 PRAGMA auto_vacuum=INCREMENTAL，并定期执行 PRAGMA incremental_vacuum，以回收空间而无需因完整 VACUUM 操作加锁
• 架构迁移：使用 PRAGMA user_version 跟踪架构版本，并在应用启动时依次应用迁移脚本

应避免的陷阱

• 不要使用不同的 PRAGMA 设置打开多个连接；WAL 模式和其他 PRAGMA 应在每个连接上保持一致
• 不要将 SQLite 用于有数十个写入者同时写入的高并发工作负载；应考虑改用 PostgreSQL 或其他客户端-服务器数据库
• 不要将大型 BLOB（超过 1MB）内联存储；将大型对象存储为外部文件，并在数据库中引用其路径，SQLite 的性能会更好
• 开发期间不要跳过 EXPLAIN QUERY PLAN；否则，缓慢的全表扫描可能一直未被发现，直到生产负载暴露问题