SQLite3 性能优化

在嵌入式开发和轻量级应用场景中，SQLite3 作为轻量级数据库引擎，凭借其无需独立服务器、部署便捷等特点被广泛应用。然而，当面对大量数据的高速读写需求时，默认配置下的 SQLite3 性能往往难以满足要求。本文将从数据库配置调整、WAL 日志模式应用以及 SQL 读写语句优化等方面，分享提升 SQLite3 性能的实战经验。

一、核心配置优化：通过 Pragma 指令提升数据库性能

1. 空间释放策略：Auto-Vacuum 的使用与权衡

在 SQLite3 中，当执行数据删除操作时，数据库文件大小默认不会自动收缩，未使用的文件页会被标记以便后续添加操作重复利用。此时可通过PRAGMA auto_vacuum指令控制空间释放行为：

查询状态：使用PRAGMA auto_vacuum;可查看当前数据库的 auto-vacuum 标记状态
工作机制：若开启auto_vacuum=1，删除数据时数据库文件会自动收缩，但由于需要存储额外的支持信息，数据库文件会比未开启时略大
使用建议：除非数据库空间非常紧张，否则建议保持auto_vacuum=0。需要注意的是，该标记只能在数据库未创建任何表时修改，若在已有表的情况下尝试修改，不会报错但也不会生效

2. 缓存大小调整：利用内存提升读写效率

SQLite3 通过缓存机制提升数据读写性能，默认缓存大小为 2000 页（每页约 1.5KB）：

性能影响：当执行大量多行的 UPDATE 或 DELETE 操作时，增大缓存可减少磁盘 I/O 次数，提升操作性能。例如将缓存大小设置为PRAGMA cache_size=8000;（约 12MB）
持久化设置：使用cache_size pragma 修改的缓存大小仅对当前会话有效，若需永久修改，需使用default_cache_size pragma
内存规划：可根据系统内存情况调整缓存大小，但需注意合理分配系统资源

3. LIKE 运算符优化：大小写敏感设置

SQLite3 中 LIKE 运算符默认忽略 latin1 字符的大小写，在某些场景下可能需要调整这一行为：

指令控制：通过PRAGMA case_sensitive_like=1;可开启大小写敏感模式，此时'a' LIKE 'A'的结果为假
版本支持：SQLite3.6.22 及更早版本不支持该特性，使用时需注意版本兼容性
应用场景：在需要精确匹配大小写的场景（如用户名搜索）中，建议开启此选项

4. 操作计数追踪：助力调试的 COUNT_CHANGES

开启PRAGMA count_changes=1;后，INSERT、UPDATE 和 DELETE 语句会返回受影响的行数，便于调试：

使用示例：

PRAGMA count_changes=1;
UPDATE user_table SET status=1 WHERE age>30;
-- 执行后将返回更新的行数，方便验证操作结果

注意事项：返回的行数不包含由触发器引发的插入、修改或删除操作的行数

5. 磁盘同步策略：在安全性与性能间寻找平衡

PRAGMA synchronous参数可控制 SQLite3 的数据同步策略，影响数据可靠性和读写性能：

FULL(2)：最安全的模式，确保数据完全写入磁盘，适合对数据可靠性要求极高的场景，但性能相对较低
NORMAL(1)：折中模式，在大多数关键操作时会暂停以确保数据同步，性能和可靠性较为平衡
OFF(0)：性能最佳的模式，数据传递给系统后直接继续操作，无需等待写入磁盘，但系统崩溃或断电可能导致数据库损坏
实践建议：若有定期备份机制且可接受少量数据丢失，可使用 OFF 模式以获取更高性能

6. 临时存储优化：将临时表放入内存

临时表和临时索引的存储位置可通过PRAGMA temp_store指令调整：

存储模式：设置为MEMORY(2)时，临时表和索引将存储在内存中，可显著提升读写速度
注意事项：修改临时存储设置会立即删除所有已存在的临时表、索引、触发器及视图，建议在事务开始前进行配置
目录指定：若使用文件存储模式（FILE(1)），可通过temp_store_directory pragma 指定存储目录

二、WAL 日志模式：提升数据库并发性的有效手段

1. WAL 日志模式的优势

WAL（Write-Ahead Logging）日志模式是 SQLite3 在 3.7.0 版本新增的功能，主要优势包括：

并发性能：读操作不阻塞写操作，写操作也不阻塞读操作，实现真正的读写并发
性能提升：在大多数情况下，WAL 模式比默认日志模式速度更快
磁盘操作优化：减少 fsync () 操作次数，使磁盘 I/O 操作更有序，提升系统稳定性

2. WAL 日志模式的不足

环境依赖：通常要求 VFS 支持共享内存原语，且只能在同一主机的进程中使用，无法在网络文件系统上运行
空间占用：每个数据库文件会关联额外的.wal 和.shm 文件
性能影响：在读操作远多于写操作的应用中，WAL 模式可能比传统日志模式慢 1% - 2%

3. 激活 WAL 日志模式

-- 激活WAL日志模式
PRAGMA journal_mode = WAL;
-- 验证激活状态
SELECT journal_mode FROM pragma_table_info('sqlite_master');

WAL 日志模式具有持久性，设置后即使关闭并重新打开数据库，仍会保持 WAL 模式，而其他日志模式（如 TRUNCATE）在重新打开数据库时会恢复为默认的 DELETE 模式。

三、SQL 读写语句优化：提升数据操作效率

1. 插入语句优化

（1）使用事务批量插入

将多条插入记录合并到一个事务中，可减少日志写入次数，提升插入效率：

char* errorMessage;
// 开始事务
sqlite3_exec(mDb, "BEGIN TRANSACTION", NULL, NULL, &errorMessage);
// 执行插入记录语句
...
// 提交事务
sqlite3_exec(mDb, "COMMIT TRANSACTION", NULL, NULL, &errorMessage);

通过事务批量插入，可大幅减少数据库操作的开销，提升插入性能。

（2）使用预解析 Statement

预解析 Statement 是一种高效的查询方式，只需对批量查询语句进行一次解析：

sqlite3_stmt *stmt;
// 预解析SQL语句
sqlite3_prepare_v2(db, "INSERT INTO user(name, age) VALUES(?, ?)", -1, &stmt, NULL);for (int i=0; i<1000; i++) {// 绑定参数sqlite3_bind_text(stmt, 1, "user_xxx", -1, SQLITE_STATIC);sqlite3_bind_int(stmt, 2, 25+i);// 执行语句sqlite3_step(stmt);// 重置Statement以便重复使用sqlite3_reset(stmt);
}// 释放Statement
sqlite3_finalize(stmt);

预解析查询方式可避免重复解析 SQL 语句的开销，是批量操作的首选方法。

2. 查询语句优化

（1）合理设计主键

在创建表时，应明确存储字段并设计合适的主键：

优先使用自增整数作为主键（INTEGER PRIMARY KEY AUTOINCREMENT）
除非业务逻辑必需，否则避免使用复合主键
合理的主键设计可显著提升查询效率

（2）为查询列创建索引

在需要执行查询的列上创建索引，可提升查询性能：

-- 为age列创建索引
CREATE INDEX idx_user_age ON user(age);
-- 为多列创建复合索引，适用于多条件查询
CREATE INDEX idx_user_name_age ON user(name, age);

创建索引时，应只为查询频繁、过滤性强的列创建，避免过度创建索引导致存储空间浪费和写入性能下降。

四、优化组合与实践建议

1. 性能优化配置组合

-- 性能优化配置组合
PRAGMA synchronous=OFF;         -- 提升性能，需注意数据备份
PRAGMA temp_store=MEMORY;        -- 临时表存储在内存中
PRAGMA cache_size=8000;          -- 增大缓存大小
PRAGMA journal_mode=WAL;         -- 开启WAL日志模式，提升并发性
PRAGMA case_sensitive_like=1;    -- 开启大小写敏感
PRAGMA count_changes=1;          -- 开启操作计数，便于调试