sqlite journal恢复数据_sqlite 性能优化

测试环境  CPU：Inter(R) Core(TIM) i3-2120 CPU@3.3GHz 内存：8GB 操作系统：Deepin

显示调用事务

利用事务的互斥性，在批量操作时显示开启事务，操作结束后提交事务，那么所有操作将只执行一次事务，大大提高IO效率。

sqlite3_exec(db, "BEGIN;", 0, 0, NULL);for( i = 0; i 10000; i++)
{
   ...
   sqlite3_exec(db, sqlcmd, NULL, NULL, &zErr);
}
sqlite3_exec(db, "COMMIT;", 0, 0, NULL);

磁盘同步 (synchronous)

synchronous 获取或者设置当前磁盘同步模式，该模式用于控制SQlite写入磁盘的时机。

Pragma 值	说明
0或者OFF	不进行同步。写入数据后传递给操作系统则完成操作，类似mmap,剩下的交给操作系统完成
1 或者NORMAL	sqlite2 的默认模式，在关键磁盘的每个序列后同步。不像FULL模式那么频繁刷盘，有小几率在电源故障或者磁盘不可用时导致数据库损坏
2 或者 FULL	sqlite3 默认模式，在每个关键磁盘操作后同步，性能差。数据库在紧急时刻暂停以确定数据写入磁盘，使得系统崩溃或者电源出问题时，确保数据库重启不会损坏

当 我们对数据丢失情况不太敏感，可将 synchronous 设置为 off,写入性能可提高3倍。

内存模式(temp_store)

Pragma 值	说明
0 或者DEFAULT	使用编译时的C预处理宏 TEMP_STORE来定义储存临时表和临时索引的位置
1 或者FILE	则存放于文件中。temp_store_directory pragma 可用于指定存放该文件的目录
2 或者 MEMORY	临时表和索引则存放于内存中

journal_model 设置

journal_mode 获取或者设置控制日志文件如何存储和处理的日志模式。journal 为的是数据库事务的rollback操作，数据库begin trans写入时，首先写入journal 文件中，commit 操作时，根据journal-model来处理日志文件。如果在commit之前由于断电等原因造成无法commit，当再次启动时，可通过journal文档做回滚操作，保证数据库的完整性和一致性。

Pragma 值	说明
DELETE	默认模式。事务结束时，日志文件删除
TRUNCATE	日志文件被截断为0字节长度
PRESIST	日志文件保留在原地，但头部被重写，表明日志不再有效
MEMORY	日志文件记录在内存中，而不是磁盘
OFF	不保留任务日志
WAL	write ahead log。

• DELETE：读写操作时DEL模式要处理各种锁。写操作是独享的，写阻塞读；读完成时才能写，读阻塞写。
• WAL：修改不直接写入数据库文件中，而是直接一个WAL文件中，若事务失败，WAL记录被忽略；若事务成功，随后在某个checkpoint时间点写回数据。若要继续提升性能，可修改checkpoint.

page_size 和 cache_size

• page_size：分页大小。默认page_size = 4096。其值为512、1024、2048、4096、8192、16384、32768、65536.
• cache_size：表示在缓存中的页面数，内置页面缓存的默认大小为 2,000 页，最小尺寸为 10 页。

注意:

• 通 PRAGMA page_size/cache_size 能够查询当前页大小和缓存size；
• PRAGMA cache_size = xxx ，可动态设置缓存大小，仅当前数据库链接有效；
• page_size：在创建数据库表时设置即可生效。sqilte3.5.8 以后，通过PRAGMA cache_size =xxx 后，执行 VACUUM, 也可动态修改页大小。如果未执行VACCUM，页大小修改无效。
• page_size 和 cache_size 不是越大越好。具体要结合硬件缓存以及数据库记录。
• page_size 决定着数据库最小储存单元page的大小。当查询记录大小大于page_size 时，此时需要多次寻址才能完成本次操作，如果page_size 大于 查询记录大小，一次寻址即可。
• 理论上page_size 和 cache_size 越大越好，实际根据应用场景和硬件资源来调整这两个参数。当缓存足够大的时候，一次查询能够直接命中数据库最后一条记录，在向上调整就没有效果了；当缓存和内存资源紧张，特别是嵌入设备，根据实际场景调整，page_size 设置尽量接近需要频繁查询的记录大小。然后调整cache_size，这样也可以一定程度控制数据库大小。

以下对轻量级数据库做了一个简要测试：

mmap_size

mmap对I/O性能的提升无需赘言，尤其是对于读操作。SQLite也在OS层封装了mmap的接口，可以无缝地切换mmap和普通的I/O接口。只需配置PRAGMA mmap_size=XXX即可开启mmap。

其他

• 优化表的结构：比如将 blob 字段，放在数据库末尾，尽量降低blob大小；
• 提升 CPU 和 DDR 频率，对于嵌入式平台，CPU频率对数据库查询速率影响较大。适当的提高CPU频率会大大提升查询性能；
• 优化应用程序负载，降低CPU 和 DDR 负担，在测试时发现，自己写的测试demo速度优化很明显，一加入到项目中，发现查询速度又降下来了，排查发现有个线程对CPU的资源消耗非常大，适当的降低其占用CPU时间，查询速度立马起来了。

总结

本文介绍了sqlite 常见的几种优化性能的方式：

• 事务
• 磁盘同步
• temp_store
• journal_mode
• page_size 和 cache_size
• mmap_size

结合自身需求，选择适合自己的方式！最后放一个sqlite c源码链接，感兴趣的自行下载：http://www3.sqlite.org/index.html