优化原生 SQLite 的并发性能，尤其是在 C/C++ 多进程或多线程环境下，需要从 数据库配置、事务设计、API 使用、系统环境、错误处理 等多个维度进行系统性调优。SQLite 虽为嵌入式数据库，但通过合理配置和编程实践，其并发能力可显著提升。

以下是一份 详细、完善、可落地的 SQLite 并发性能优化方案，适用于 Linux/Windows/macOS 下的 C/C++ 项目。

---

一、核心前提：理解 SQLite 的并发模型

• SQLite 使用 文件级锁（POSIX fcntl / Windows LockFileEx）控制并发；
• 读操作：允许多个进程/线程同时读；
• 写操作：同一时间仅允许一个写者，且写期间阻塞其他所有读写（除非启用 WAL）；
• WAL 模式 是提升并发的关键：支持 1 写 + N 读 并发。

📌 结论：所有优化必须基于 WAL 模式展开。

---

二、基础配置优化（PRAGMA 设置）

在首次打开数据库后立即执行以下配置：

// C 示例：初始化数据库连接
int init_sqlite_db(sqlite3 **db, const char *path) {
    int rc = sqlite3_open(path, db);
    if (rc != SQLITE_OK) return rc;

    // 1. 启用 WAL 模式（最重要！）
    sqlite3_exec(*db, "PRAGMA journal_mode=WAL;", NULL, NULL, NULL);

    // 2. 设置 busy timeout（自动重试写锁）
    sqlite3_busy_timeout(*db, 5000); // 5秒超时

    // 3. 调整缓存大小（单位：页，默认通常 2000KB）
    sqlite3_exec(*db, "PRAGMA cache_size = -4000;", NULL, NULL, NULL); // -4000 = 4MB

    // 4. 降低同步级别（权衡性能 vs 安全性）
    //   FULL: 最安全，每次事务 fsync（慢）
    //   NORMAL: 大多数场景足够安全（推荐）
    //   OFF: 极快，但崩溃可能丢数据（仅用于临时库）
    sqlite3_exec(*db, "PRAGMA synchronous = NORMAL;", NULL, NULL, NULL);

    // 5. 调整 WAL 自动 checkpoint 频率（默认1000页）
    //    增大可减少 checkpoint 频率，适合高写负载
    sqlite3_exec(*db, "PRAGMA wal_autocheckpoint = 5000;", NULL, NULL, NULL);

    // 6. 页面大小对齐 OS（通常 4096）
    sqlite3_exec(*db, "PRAGMA page_size = 4096;", NULL, NULL, NULL);

    // 7. 启用外键（如需要）
    sqlite3_exec(*db, "PRAGMA foreign_keys = ON;", NULL, NULL, NULL);

    return SQLITE_OK;
}

PRAGMA 参数说明表：

PRAGMA	推荐值	作用
journal_mode	WAL	启用写前日志，支持读写并发
synchronous	NORMAL	平衡性能与崩溃安全性
cache_size	-4000（负数=KB）	增大内存缓存，减少 I/O
wal_autocheckpoint	3000~10000	减少 checkpoint 频率，避免写阻塞
page_size	4096	对齐磁盘块，提升 I/O 效率
temp_store	MEMORY（可选）	临时表存内存（需足够 RAM）

⚠️ 注意：PRAGMA 设置对当前连接生效，每个新连接需重新设置。

---

三、事务设计优化：最小化锁持有时间

原则：

• 事务越短越好；
• 批量写入合并为单事务；
• 避免在事务中执行非 DB 操作（如 sleep、网络请求、复杂计算）。

✅ 正确示例：批量插入（高效）

sqlite3_exec(db, "BEGIN;", NULL, NULL, NULL);
for (int i = 0; i < 10000; ++i) {
    char sql[256];
    snprintf(sql, sizeof(sql), "INSERT INTO events(ts, data) VALUES (%ld, 'data%d');", time(NULL), i);
    sqlite3_exec(db, sql, NULL, NULL, NULL);
}
sqlite3_exec(db, "COMMIT;", NULL, NULL, NULL);

❌ 错误示例：每条语句独立事务（极慢）

for (...) {
    sqlite3_exec(db, "BEGIN; INSERT ... ; COMMIT;", ...); // 锁频繁争用
}

💡 性能对比：1 万条记录，单事务 vs 1 万事务 → 速度差 100 倍以上。

---

四、API 使用最佳实践

1. 使用 Prepared Statement（预编译语句）

避免重复解析 SQL，提升性能并防止注入。

const char *sql = "INSERT INTO logs(level, msg) VALUES (?, ?);";
sqlite3_stmt *stmt;
sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);

for (int i = 0; i < N; ++i) {
    sqlite3_bind_int(stmt, 1, level[i]);
    sqlite3_bind_text(stmt, 2, msg[i], -1, SQLITE_STATIC);
    sqlite3_step(stmt);
    sqlite3_reset(stmt); // 重置绑定，复用 stmt
}

sqlite3_finalize(stmt);

2. 连接管理

• 每个线程/进程应拥有独立的 sqlite3* 连接；
• 不要在线程间共享连接（SQLite 连接非线程安全）；
• 避免频繁 open/close，保持长连接。

3. 只读连接显式声明（可选）

sqlite3_open_v2("test.db", &db, SQLITE_OPEN_READONLY, NULL);

• 减少不必要的锁尝试；
• 提升只读场景并发度。

---

五、WAL 模式深度优化

1. 控制 Checkpoint 行为

自动 checkpoint 可能阻塞写者。建议：

• 关闭自动 checkpoint：

  PRAGMA wal_autocheckpoint = 0;

• 在应用空闲时手动触发非阻塞 checkpoint：

  sqlite3_wal_checkpoint_v2(db, NULL, SQLITE_CHECKPOINT_PASSIVE, NULL, NULL);

2. 监控 WAL 文件大小

• .db-wal 文件过大 → 内存/磁盘压力；
• 可定期执行 RESTART checkpoint（需停写）：

  sqlite3_wal_checkpoint_v2(db, NULL, SQLITE_CHECKPOINT_RESTART, NULL, NULL);

3. 避免“写者饥饿”

WAL 模式下，持续读可能导致写者无法获取锁（罕见）。可通过：

• 限制长时间运行的查询；
• 使用 PRAGMA wal_checkpoint(TRUNCATE) 清理。

---

六、系统与部署环境优化

优化项	说明
使用本地 SSD	避免机械硬盘、USB、网络存储（NFS/SMB 不支持可靠字节锁）
禁用杀毒软件实时扫描	对 .db, .db-wal, .db-shm 添加排除，否则可能锁冲突或损坏
确保文件系统支持 fcntl 锁	ext4、XFS、NTFS ✅；FAT32、exFAT、NFS ❌
关闭磁盘写缓存（可选）	若使用 synchronous=NORMAL，依赖 OS 缓存，一般无需额外设置
内存充足	增大 cache_size 需足够 RAM

---

七、错误处理与重试机制

1. 处理 SQLITE_BUSY

即使有 busy_timeout，仍可能超时失败。建议：

2. 日志记录

记录 SQLITE_BUSY 频率，用于评估是否需架构调整（如引入队列或代理）。

---

八、高级场景优化策略

场景 1：高频写入（如日志系统）

• 使用 单写者进程 + 多读者 架构；
• 写者接收 IPC 消息（如 Unix Socket），批量写入；
• 读者直接读 WAL 数据库（WAL 支持读写并发）。

场景 2：读远多于写

• 主库写入；
• 定期 VACUUM INTO 'snapshot.db' 创建只读副本；
• 读请求分流到只读副本（完全无锁）。

场景 3：多进程写入竞争激烈

• 引入轻量级消息队列（如 ZeroMQ、Unix Domain Socket）；
• 所有写请求发送至单一“写进程”；
• 实现逻辑串行化，避免锁竞争。

---

九、性能监控与诊断工具

1. 查看当前状态

PRAGMA wal_checkpoint(PASSIVE); -- 返回 checkpoint 状态
SELECT * FROM pragma_wal_checkpoint(); -- SQLite 3.31+

2. 统计指标

• 记录 SQLITE_BUSY 次数；
• 监控 .db-wal 文件大小；
• 使用 strace / lsof 观察文件锁行为（Linux）。

3. 压测工具

• 自研多进程压测程序；
• 使用 sqlite3 CLI + shell 脚本模拟并发。

---

十、完整优化 Checklist

✅ 启用 WAL 模式
✅ 设置 busy_timeout（2000~5000ms）
✅ 事务尽可能短，批量操作合并
✅ 使用 sqlite3_prepare_v2 + reset 复用语句
✅ 每个线程/进程独立连接
✅ 调整 cache_size（4MB+）、wal_autocheckpoint（5000+）
✅ synchronous = NORMAL（非金融级应用）
✅ 部署在本地 SSD，禁用杀毒扫描
✅ 避免在事务中做非 DB 操作
✅ 监控 SQLITE_BUSY 和 WAL 文件增长

---

十一、何时考虑放弃 SQLite？

若出现以下情况，说明已超出 SQLite 适用边界：

• 持续 > 100 写/秒 且延迟敏感；
• 多节点分布式访问；
• 需要复杂权限、复制、高可用；
• 数据库文件 > 10GB 且频繁更新。

→ 考虑迁移到 PostgreSQL、MySQL 或 嵌入式替代品如 DuckDB（分析型）。

---

结语

通过上述 配置 + 编程 + 系统 三位一体的优化，SQLite 在多进程环境下可稳定支撑 数百 QPS 的混合读写负载，满足绝大多数本地应用需求。关键在于 启用 WAL、缩短事务、合理重试、环境适配。

 欢迎扫描关注，持续交流学习！！ 

代码之外的风景：程序员如何平衡工作与生活的艺术