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

本文是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加密
iOS启动优化之二进制重排
utf8mb4:MYSQL中使用Emoji
iOS Asset Catalog and Bundle