sqlite 性能优化

2022-03-16 15:53:24

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

显示调用事务

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

sqlite3_exec(db, "BEGIN;", , , NULL);
for( i = ; i < 10000; i++)
{
   ...
   sqlite3_exec(db, sqlcmd, NULL, NULL, &zErr);
}
sqlite3_exec(db, "COMMIT;", , , 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，这样也可以一定程度控制数据库大小。

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