SQLite 常用性能优化配置

红太狼 2022-09-06 10:16 278阅读 0赞

### 文章目录 ###

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

大家好，我是只谈技术不剪发的 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 无法执行[检查点操作][Link 1]。缓解这一问题的方法有几个：

*  减少 [wal\_autocheckpoint][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 可能导致数据库损坏，虽然我们极少遇到这种问题。更多信息可以参考[官方文档][Link 2]。

## 临时文件 ##

pragma temp_store = memory;

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

## 内存映射 ##

pragma mmap_size = 30000000000;

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

对于 32 位操作系统，可能只能设置小于 4 GB 的 mmap\_size。

内存映射还会在出现 I/O 错误时产生影响，具体可以参考[官方文档][Link 4]。

## 页面大小 ##

pragma page_size = 32768;

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

## 其他操作 ##

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

首先，我们可以定期重新组织数据库：

pragma vacuum;

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

其次，我可以定期重新分析数据库：

pragma optimize;

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

另外，我们还可以定期清理数据库：

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

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

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

[Link 1]: https://www.sqlite.org/wal.html#ckpt
[wal_autocheckpoint]: https://www.sqlite.org/pragma.html#pragma_wal_autocheckpoint
[Link 2]: https://www.sqlite.org/pragma.html#pragma_synchronous
[Link 3]: https://www.sqlite.org/tempfiles.html#transient_indices
[Link 4]: https://sqlite.org/mmap.html
[Link 5]: https://www.sqlite.org/pragma.html#pragma_optimize
[Link 6]: https://www.sqlite.org/pragma.html#pragma_incremental_vacuum