SQLite常用性能优化配置

作者: 不剪发的Tony老师
毕业于北京航空航天大学,十多年数据库管理与开发经验,目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net



文章目录

        日志模式
        同步提交
        临时文件
        内存映射
        页面大小
        其他操作

大家好,我是只谈技术不剪发的 Tony 老师。

SQLite 是一个嵌入式数据库引擎,通常以代码库的形式运行在其他程序中,因此我们不需要进行任何服务器配置。很多人都认为 SQLite 只能用于没有性能要求的小型数据库,但是实际情况并非如此。虽然 SQLite 只支持一个并发的写入操作,但是多个进程可以同时连接和查询相同的数据库。通过一些简单的配置和操作,我们完全可以使用 SQLite 创建 GB 级别的数据库并且支持高达每秒 10 万次的并发查询。

简单来说,优化 SQLite 性能的配置如下:

pragma journal_mode = WAL;
pragma synchronous = normal;
pragma temp_store = memory;
pragma mmap_size = 30000000000;

 
以上配置中的部分内容可以永久生效,但另一部分在每次连接时都会重置为默认值,因此推荐每次连接数据库之后都执行这些配置命令。

接下来我们具体解释一下每个参数的作用。
日志模式

pragma journal_mode = WAL;


I通过以上配置,SQLite 不再直接写入数据库文件(随机写入),而是先写入预写式日志(顺序写入)并定期提交到数据库文件。这种模式可以在写入事务进行的同时支持并发的读取操作,同时可以极大地改善性能。

注意:在某些写入压力下,WAL 模式可能存在一些问题,导致 WAL 文件无限增长,从而大大降低性能。通常是因为写入过于频繁,导致 SQLite 无法执行检查点操作。缓解这一问题的方法有几个:

    减少 wal_autocheckpoint 间隔。由于自动检查点是被动触发的操作,因此这种方法不能确保一定有效。
    定期执行 pragma wal_checkpoint(full) 或者 pragma wal_checkpoint(truncate) 命令。对于 full 方式,如果其他进程正在打开文件,WAL 大小不会发生改变,但是仍然会提交所有内容,确保新的数据不会导致 WAL 文件增长。对于 truncate 方式,SQLite 会阻塞其他进程并将 WAL 文件重置为空。我们可以通过一个单独的进程执行以上命令。

同步提交

pragma synchronous = normal;

pragma synchronous = off;

   

synchronous 的默认值为 full,意味着每个更新操作都需要等待 FSYNC 完成内存数据到磁盘文件的同步。在 WAL 模式下,normal 方式仍然是完全安全的,同时意味着只有 WAL 检查点操作需要等待 FSYNC 完成。off 可能导致数据库损坏,虽然我们极少遇到这种问题。更多信息可以参考官方文档。
临时文件

pragma temp_store = memory;



以上指令表示使用内存存储临时索引和表。SQLite 可以自动为某些查询语句创建临时索引。无法确定这种操作可以提供多少性能帮助,如果 SQLite 创建了临时索引(通过 EXPLAIN QUERY PLAN 命令查看),我们应该主动创建相应的索引。
内存映射

pragma mmap_size = 30000000000;



当数据库的大小小于 mmap_size 字节时使用内存映射替代读/写调用。操作系统管理的 syscall、页面以及缓存将会更少,此时的性能取决于使用的操作系统。注意,该设置不会使用物理内存,而是保留虚拟内存。然后,操作系统将根据常用的“磁盘缓存”逻辑决定哪些页面被逐出,哪些页面留在内存中。至少是在 Linux 上,如果为 SQLite 进程分配了足够的内存,性能将会得到明显提升。如果数据库的大小比指定的 mmap_size 更大,数据库的一部分将会使用内存映射,其他仍然使用 read() / write() 系统调用。

对于 32 位操作系统,可能只能设置小于 4 GB 的 mmap_size。

内存映射还会在出现 I/O 错误时产生影响,具体可以参考官方文档。
页面大小

pragma page_size = 32768;



对于存储大型 BLOB 数据而言,增加页面大小可以改进数据库的性能;但是对于一般的应用场景而言,这种方法可能并不适用。对于写入操作,SQLite 总是替换整个页面,因此增加页面大小会增加写入操作的开销。
其他操作

除了以上配置之外,我们还可以通过定期执行某些操作改善数据库的性能。

首先,我们可以定期重新组织数据库:

pragma vacuum;



每次执行以上命令都会重新写入数据库文件,如果数据库大小超过 100 MB 将会非常耗时。

其次,我可以定期重新分析数据库:

pragma optimize;



为了在不需要对应用程序模式和 SQL 进行详细分析的情况下获得最佳的长期查询性能,推荐应用程序每次关闭数据库连接时运行以上命令。对于长期运行的应用程序,也可以通过几个小时一次定期运行以上命令获得性能提升。相信信息可以参考官方文档。

另外,我们还可以定期清理数据库:

pragma auto_vacuum = incremental; -- 创建数据库之后
pragma incremental_vacuum; -- 定期


以上命令只有当数据库的大小会定期显著缩减时才会明显提升性能。

执行清理命令时,数据库文件中的空闲列表页会被移动到文件结尾并且被删除。需要注意,自动清理只会截断空闲列表页,而不会执行 VACUUM 命令的文件碎片整理和页面修复。实际上,由于它会移动数据页,自动清理反而会增加碎片化。相信内容可以参考官方文档。