SQLite Expert 是一个数据库专家技能，专注于 SQLite 内部机制、性能调优和嵌入式数据库模式。它为在移动应用、IoT 设备、服务端缓存层和分析型负载等场景中高效使用 SQLite 提供指导，帮助开发者利用其高级特性，而不仅仅把它当作简单的键值存储。
核心原则
启用 WAL 模式（PRAGMA journal_mode=WAL）以支持并发读写访问；它允许读操作在不阻塞写操作的情况下继续进行，反之亦然。
使用 PRAGMA busy_timeout 设置合理的等待时长（例如 5000ms），而不是在发生竞争时立即收到 SQLITE_BUSY 错误。
从一开始就设计带有合适索引的 schema；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 数据：json_extract()、json_each()、json_group_array()，用于存储在 TEXT 列中的文档式数据。
编写递归 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 查询内部进行领域特定计算。
增量 Vacuum：使用 PRAGMA auto_vacuum=INCREMENTAL 配合周期性的 PRAGMA incremental_vacuum，在不产生完整 VACUUM 锁的情况下回收空间。
Schema 迁移：使用 PRAGMA user_version 跟踪 schema 版本，并在应用启动时按顺序应用迁移脚本。
需要避免的陷阱
不要用不同的 PRAGMA 设置打开多个连接；WAL 模式和其他 PRAGMA 应在每个连接上保持一致设置。
不要将 SQLite 用于有数十个同时写入者的高并发写入负载；应考虑 PostgreSQL 或其他客户端-服务器数据库。
不要内联存储大型 BLOB（超过 1MB）；当大型对象作为外部文件存储并在数据库中引用路径时，SQLite 表现更好。
不要在开发过程中跳过 EXPLAIN QUERY PLAN；否则缓慢的全表扫描可能一直不被发现，直到生产负载暴露问题。