SQLite开发文档：PRAGMA配置、性能优化

原创 2021-05-29

本文是easeapi.com整理的关于SQLite的第三篇文章，主要介绍SQLite的PRAGMA配置参数及性能优化的一般方法。

journal_mode：回滚模式

默认情况下SQLite使用的是rollback journal回滚机制，会自动创建journal后缀的文件。

rollback journal机制原理：在写数据库之前，先将修改所在页的数据备份到journal日志文件中，然后才将修改写入到数据库文件中；如果事务失败，则将journal中的备份数据还原回去，即回滚；如果事务成功，则删除备份数据，提交修改。这样能保证数据库的完整性和一致性。

通过journal_mode设置日志文件存储方式。示例：

PRAGMA journal_mode = DELETE;

journal_mode有如下选择：

• OFF：不保留日志；
• DELETE：事务结束，文件删除（默认采取DELETE模式）；
• WAL：write ahead log；
• MEMORY：日志文件存储在内存中；
• TRUNCATE:
• PERSIST:

其中，这里的WAL模式和journal有很大不同。WAL模式会生成wal后缀文件，并不直接将修改写入到数据库文件中，而是先写入到WAL文件中；如果事务失败，WAL中的记录会被忽略，撤销修改；如果事务成功，它将在随后的某个时间被写回到数据库文件中，提交修改。这样就需要在后续的某个时间点（文件达到SQLITE_DEFAULT_WAL_AUTOCHECKPOINT页或通过sqlite3_wal_autocheckpoint设定的值）将WAL文件数据同步到数据库文件中，这个过程称为checkpoint。执行checkpoint之后，WAL文件会被清空。

和journal模式相比，WAL模式在操作顺利的情况下，只需要一次写入文件操作，不会互相阻塞（写操作并不是直接操作数据库文件，但还是不能同时写）。使用WAL模式可以获取更好的并发性能。

synchronous：文件同步方式

写数据库时，最终调用的是系统的write接口，为了提升性能，write操作往往会先写入缓存中，直到调用sync才将缓存中的数据flush到磁盘。如果缓存中的数据还没有被sync系统就断电或异常退出了，文件就可能被损坏。synchronous的可选值如下：

• OFF：交由操作系统处理，性能最高，但在崩溃或断电时数据库很可能会损坏；
• NORMAL：不像FULL那么频繁操作sync，有小概率会损坏数据库；
• FULL：在关键磁盘操作后sync，性能差，到可以确保在崩溃或断电时数据库不会被损坏。

locking_mode：文件锁

PRAGMA locking_mode = NORMAL | EXCLUSIVE

在缺省NORMAL模式下，一个Connection会在每一次读事务开始时获取共享锁，写事务开始时获取排它锁。每一次读写事务完成时释放文件锁。

在EXCLUSIVE模式下，一个Connection会始终持有文件锁，直到Connection结束。这样会阻止其它进程访问数据库文件。在EXCLUSIVE模式下，读写减少了对文件锁的持有，性能会稍好一些。

auto_vacuum

PRAGMA auto_vacuum = 0 | 1;

当尝试删除一部分表记录之后，我们会发现SQLite文件大小并没有减小。这是SQLite的一种优化机制：数据库记录删除后，该部分的文件页会被打标记，后续的写操作会再次使用空置的文件页。可以使用VACUUM命令强制释放记录后的文件，也可以通过设置auto_vacuum=1，以在提交删除操作的事务时，数据库文件自动收缩。但VACUUM操作很耗时，慎重开启。

temp_store

PRAGMA temp_store = 2;

回滚日志、主数据库日志在需要的时候都会写入磁盘文件，但是对于其它类型的临时文件，SQLite是可以将它们存放在内存中以取代磁盘文件的，这样在执行的过程中就可以减少大量的IO操作了：

• 编译参数SQLITE_TEMP_STORE：

0：临时文件总是存储在磁盘上，而不会考虑temp_store指令的设置。
1：临时文件缺省存储在磁盘上，但是该值可以被temp_store指令覆盖。
2：临时文件缺省存储在内存中，但是该值可以被temp_store指令覆盖。
3：临时文件总是存储在内存中，而不会考虑temp_store指令的设置。

• temp_store指令：

0：临时文件的存储行为完全由SQLITE_TEMP_STORE编译期参数确定；
1：使用磁盘存储；
2：使用内存存储。

SQLite性能优化最佳实践

多线程配置

• 当可以保证数据库读写都在单线程中操作时，推荐选择Single-thread模式；
• 当存在多线程访问的可能时，推荐使用Serialized模式；
• Multi-thread模式仅能保证在多线程中持有各自的Connection是安全的，并不能保证Connection中多线程读写是安全的。

推荐配置

PRAGMA synchronous = OFF;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA journal_mode = OFF;

如果对数据的一致性要求不是特别高，可以关闭回滚日志文件。

缓存sqlite3_stmt结构

对于SQL语句固定却频繁操作的查询，推荐将对应的sqlite3_stmt结构缓存使用。

显式使用事务

尽管sqlite3_exec有隐式的事务操作，我们仍然可以显式地将多条记录放在一个事务中处理提升效率：

sqlite3_exec(sqliteObj, "BEGIN", 0, NULL, NULL);
...
sqlite3_exec(sqliteObj, "COMMIT", 0, NULL, NULL);

相关文章：

SQLite开发文档：数据类型、文件锁状态、多线程
SQLite开发文档：SQLCipher加密