DeepSeek总结的致力于在一分钟内将十亿行数据插入 SQLite
[原文链接]：https://avi.im/blag/2021/fast-sqlite-inserts/
致力于在一分钟内将十亿行数据插入 SQLite
发布日期：2021 年 7 月 17 日
当前最佳成绩：3300 万行数据插入耗时 33 秒。（你可以在 GitHub 上查看源代码：https://github.com/avinassh/fast-sqlite3-inserts）
最近，我遇到一个需求：需要一个包含大量行数的测试数据库，而且需要快速生成。于是我做了任何一个程序员都会做的事：写了一个 Python 脚本来生成数据库。不幸的是，它很慢，非常慢。于是我又做了任何一个程序员都会做的事：开始深入研究 SQLite、Python，并最终涉及 Rust……这一切都是为了实现在一分钟内生成一个 10 亿行数据库的目标。这篇博客就是这次有趣且有教育意义的实践的总结。
目标
本实验的目标是，在我的机器上，在一分钟内生成为一个 SQLite 数据库，其中包含十亿行数据，并且表结构如下：
createtableIFNOTEXISTSuser(idINTEGERnotnullprimarykey,areaCHAR(6),ageINTEGERnotnull,activeINTEGERnotnull);生成的数据将是随机的，并满足以下约束：area列包含六位数字的区域代码（任意六位数字即可，无需验证）。age只能是 5、10 或 15。active列是 0 或 1。
我使用的机器是 2019 款 MacBook Pro（2.4 GHz 四核 i5，8GB 内存，256GB 固态硬盘，Big Sur 11.1）。
我愿意在这些方面做出妥协：
- 我不需要持久性保证。也就是说，即使进程崩溃且所有数据丢失也没关系。我可以重新运行脚本。
- 它可以充分利用我的机器资源：100% CPU、8GB 内存和数 GB 的固态硬盘空间。
- 无需使用真正的随机方法，标准库中的伪随机方法就足够了。
Python 原型
Python 是我进行任何脚本编写的首选语言。标准库提供了一个不错的 SQLite 模块，我用它编写了第一个版本。这是完整代码。在这个脚本中，我尝试在一个 for 循环中逐行插入 1000 万行数据。这个版本耗时接近 15 分钟，这激发了我的好奇心，促使我进一步探索以减少时间。
在 SQLite 中，每次插入都是原子操作，并且是一个事务。每个事务都保证会写入磁盘，因此可能会很慢。我尝试了不同大小的批量插入，发现 10 万行是一个最佳点。通过这个简单的更改，运行时间减少到了 10 分钟。这是完整代码。
SQLite 优化
我编写的脚本非常简单，所以我认为优化空间不大。其次，我希望代码保持简单，接近日常使用的版本。合乎逻辑的下一步是寻找数据库优化方法，于是我开始深入研究 SQLite 的神奇世界。
互联网上有很多关于 SQLite 优化的文章。基于这些文章，我做了以下更改：
PRAGMA journal_mode=OFF;PRAGMA synchronous=0;PRAGMA cache_size=1000000;PRAGMA locking_mode=EXCLUSIVE;PRAGMA temp_store=MEMORY;这些设置是做什么的？
- 关闭
journal_mode将导致没有回滚日志，因此如果任何事务失败，我们将无法回滚。这会禁用 SQLite 的原子提交和回滚功能。请勿在生产环境中使用。 - 关闭
synchronous后，SQLite 不再关心可靠地写入磁盘，而是将这一责任交给操作系统。写入 SQLite 可能并不意味着数据已刷新到磁盘。请勿在生产环境中使用。 cache_size指定 SQLite 允许在内存中保存多少内存页。请勿在生产环境中将此值设置得过高。- 在
EXCLUSIVE锁定模式下，SQLite 连接持有的锁永远不会被释放。 - 将
temp_store设置为MEMORY将使其表现得像一个内存数据库。
SQLite 文档有一个专门页面介绍这些参数，还列出了其他一些参数。我还没有尝试所有参数，我选择的这些参数提供了相当不错的运行时间。
以下是我在互联网上阅读的一些文章，它们帮助我了解了这些优化参数：1, 2, 3, 4, 5。
重新审视 Python
我再次重写了 Python 脚本，这次加入了经过微调的 SQLite 参数，这带来了巨大的提升，运行时间大幅减少。
- 朴素的 for 循环版本插入 1 亿行数据大约需要 10 分钟。
- 批处理版本插入 1 亿行数据大约需要 8.5 分钟。
PyPy
我之前从未使用过 PyPy。PyPy 官网强调它比 CPython 快 4 倍，我觉得这是个尝试它并验证其说法的好机会。我也想知道是否需要修改代码才能运行，然而，我现有的代码运行得很流畅。
我所要做的就是使用 PyPy 运行我现有的代码，无需任何更改。它确实有效，而且速度提升非常显著。批处理版本插入 1 亿行数据仅用了 2.5 分钟。我获得了接近 3.5 倍的速度提升 😃
（我与 PyPy 没有关联，但我恳请您考虑向 PyPy 捐款以支持他们的努力。）
忙碌的循环（？）
我想大致了解 Python 在循环中花费了多少时间。于是我移除了 SQL 指令，只运行代码：
- 在 CPython 中，批处理版本耗时 5.5 分钟。
- 在 PyPy 中，批处理版本耗时 1.5 分钟（再次获得 3.5 倍的速度提升）。
我用 Rust 重写了同样的逻辑，循环仅用了 17 秒。我决定从 Python 转向 Rust 进行进一步的实验。
（注意：这不是Python 和 Rust 之间的速度比较文章。两者在你的工具集中有着截然不同的目标和定位。）
Rust
就像 Python 一样，我编写了一个朴素的 Rust 版本，在循环中逐行插入。但是，我加入了所有的 SQLite 优化。这个版本耗时约 3 分钟。然后我做了进一步的实验：
- 之前的版本使用了
rusqlite，我切换到了异步运行的sqlx。这个版本耗时约 14 分钟。我预料到了性能会下降。但值得注意的是，它的表现比我之前提出的任何 Python 迭代都要差。 - 我之前在执行原始 SQL 语句，后来切换到了预处理语句（prepared statements），并在循环中插入行，但重用了预处理语句。这个版本仅用了大约一分钟。
- 也曾尝试创建一个包含 insert 语句的长字符串，我认为这并没有带来更好的性能。仓库中还有其他几个版本。
（当前）最佳版本
我使用了预处理语句，并以每批 50 行的方式进行批量插入。插入 1 亿行数据，耗时34.3 秒。源代码链接
我创建了一个多线程版本，其中有一个写入线程从通道接收数据，另外四个线程将数据推送到通道。这是当前最佳版本，耗时约32.37 秒。源代码链接
I/O 时间
SQLite 论坛上的好心人给了我一个有趣的想法：测量内存数据库所需的时间。我再次运行代码，将数据库位置指定为:memory:，Rust 版本完成时间减少了 2 秒（29 秒）。我想可以合理地假设，将 1 亿行数据刷新到磁盘需要大约 2 秒。这也表明，可能没有更快的 SQLite 优化方法可以写入磁盘，因为 99% 的时间都花在了生成和添加行上。
排行榜
（截至撰写本文时。仓库中有最新的数据）
| 变体 | 时间 |
|---|---|
| Rust | 33 秒 |
| PyPy | 150 秒 |
| CPython | 510 秒 |
关键要点
- 尽可能使用 SQLite 的
PRAGMA语句。 - 使用预处理语句。
- 进行批量插入。
- PyPy 确实比 CPython 快 4 倍。
- 多线程/异步并不总是更快。
后续想法
我计划接下来探索以下几个方向以进一步提高性能：
- 我还没有对代码进行分析。分析可能会提示我们哪些部分是慢的，并帮助我们进一步优化代码。
- 第二快的版本是单线程、单进程运行的。由于我有一台四核机器，我可以启动 4 个进程，在一分钟内获得高达 8 亿行数据。然后我需要在几秒钟内合并这些数据，以便总耗时仍然少于一分钟。
- 编写一个完全禁用垃圾回收器的 Go 版本。
- Rust 编译器很可能优化了忙碌循环的代码，并消除了内存分配和对随机函数的调用，因为它没有副作用。对生成的二进制文件进行分析可能会提供更多信息。
- 这里有一个非常疯狂的想法：学习 SQLite 文件格式，然后直接生成页面并写入磁盘。
我期待着与好奇的灵魂们讨论和/或合作，以实现在我的追求中快速生成包含十亿条记录的 SQLite 数据库。如果你对此感兴趣，可以通过 Twitter 联系我或提交 PR。
感谢 Bhargav、Rishi、Saad、Sumesh、Archana 和 Aarush 阅读本文草稿。
- 为什么会有这个需求？在我写的一个 Telegram 机器人中，有一个 SQL 查询需要部分索引。我之前在 Postgres/Mongo 中使用过部分索引，但很高兴地发现 SQLite 也支持它们。我决定写一篇博客文章（剧透：最终没写），用数据展示部分索引的有效性。我写了一个快速脚本来生成数据库，但数据量太小，无法展示部分索引的威力，没有它们查询也很快。生成更大的数据库需要 30 多分钟。所以我花了 30 多个小时来减少这 30 分钟的运行时间 :p
- 如果你喜欢这篇文章，那么你可能也会喜欢我做的关于 MongoDB 的实验，我在一个具有唯一索引的集合中插入了重复记录 - 链接。
更新（7 月 19 日）：在标题前添加了“致力于”一词，以使意图更明确。