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 Pro2.4 GHz 四核 i58GB 内存256GB 固态硬盘Big Sur 11.1。我愿意在这些方面做出妥协我不需要持久性保证。也就是说即使进程崩溃且所有数据丢失也没关系。我可以重新运行脚本。它可以充分利用我的机器资源100% CPU、8GB 内存和数 GB 的固态硬盘空间。无需使用真正的随机方法标准库中的伪随机方法就足够了。Python 原型Python 是我进行任何脚本编写的首选语言。标准库提供了一个不错的 SQLite 模块我用它编写了第一个版本。这是完整代码。在这个脚本中我尝试在一个 for 循环中逐行插入 1000 万行数据。这个版本耗时接近 15 分钟这激发了我的好奇心促使我进一步探索以减少时间。在 SQLite 中每次插入都是原子操作并且是一个事务。每个事务都保证会写入磁盘因此可能会很慢。我尝试了不同大小的批量插入发现 10 万行是一个最佳点。通过这个简单的更改运行时间减少到了 10 分钟。这是完整代码。SQLite 优化我编写的脚本非常简单所以我认为优化空间不大。其次我希望代码保持简单接近日常使用的版本。合乎逻辑的下一步是寻找数据库优化方法于是我开始深入研究 SQLite 的神奇世界。互联网上有很多关于 SQLite 优化的文章。基于这些文章我做了以下更改PRAGMA journal_modeOFF;PRAGMA synchronous0;PRAGMA cache_size1000000;PRAGMA locking_modeEXCLUSIVE;PRAGMA temp_storeMEMORY;这些设置是做什么的关闭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% 的时间都花在了生成和添加行上。排行榜截至撰写本文时。仓库中有最新的数据变体时间Rust33 秒PyPy150 秒CPython510 秒关键要点尽可能使用 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 日在标题前添加了“致力于”一词以使意图更明确。