资料来源：阿维纳什

编辑：我很困。

【新智元导读】近日，一位程序员急需在一分钟之内生成十亿行的测试数据库，然而在用Python写了脚本之后发现「大失败」。怎么办？当然是用Rust了！

最近，一位程序员告诉我，他迫切需要一个包含“仅”10亿行数据的测试数据库，并且需要在一分钟内生成它。

所以他做了所有程序员都会做的事情：换句话说，我编写了一个Python脚本来生成数据库。

然而，不幸的是这个脚本非常慢。

所以他做了每个程序员都会做的事情：了解有关SQLite、Python 以及出于某种原因的Rust 的更多信息。

该项目开源：https://github.com/avinassh/fast-sqlite3-inserts

目标

作者需要在2019 款MacBook Pro（2.4GHz 四核i5）上生成每分钟10 亿行的SQLite 数据库。

表模式

我需要：

生成的数据是随机的。区域列包含6 位区号（无需验证）。年龄列可以是5、10 或15。 active”列是0 或1。

不过作者表示，剧本的要求不必太高，还是可以妥协的。

如果进程崩溃，所有数据都将丢失，但这不是问题。只需再次运行该脚本即可充分利用计算机资源。这意味着您不需要使用100% CPU、8 GB 内存和剩余的SSD 存储。 stdlib 的伪随机方法就足够了。

Python原型

在第一个脚本中，作者尝试在for循环中逐条插入1000万条记录，耗时长达15分钟。

显然，这已经太晚了。

SQLite保证每次插入都是一个事务，并且每个事务都写入磁盘。笔者怀疑问题就出在这里。

因此，作者开始尝试不同大小的批量插入，发现100,000 是最佳选择，将执行时间减少到10 分钟。

SQLite优化

作者认为自己写的代码已经非常简洁，没有留下优化的空间。

所以他的下一个目标是数据库优化。

基于各种优化SQLite 的建议，作者做出了一些改进。

关闭“journal_mode”会禁用回滚日志记录。这意味着如果事务失败，则无法回滚。

当你关闭“sync”时，SQLite不再关心它是否能够可靠地写入磁盘，而是将这个责任交给操作系统。这意味着您可能会遇到SQLite无法成功写入磁盘的情况。

“cache_size”指定SQLite可以在内存中保留的内存页数。

如果“locking_mode”为“EXCLUSIVE”模式，则SQLite 锁定的连接将不会被释放。

将“temp_store”设置为“MEMORY”会使其表现得像内存数据库。

作者在此警告不要在生产环境中使用这些操作。

重新审视Python

作者再次重写了Python脚本。这次它包含了微调的SQLite 参数，带来了显着的改进并显着减少了执行时间。

最初的for 循环版本大约需要10 分钟。批处理版本大约需要8.5 分钟。

PyPy

PyPy 在其主页上被吹捧为比CPython 快4 倍，因此作者决定尝试一下。

令作者惊讶的是，无需对现有代码进行任何更改，只需在PyPy 中运行即可。

批处理版本仅需2.5 分钟，速度提高了近3.5 倍。

Busy Loop？

Python循环是否耗时太长？因此作者删除了SQL指令并再次运行代码。

CPython 的批处理版本花费了5.5 分钟。

批处理版本在PyPy 中花费了1.5 分钟（又加速了3.5 倍）。

然而，在Rust 中重写相同的内容时，循环只需要17 秒。

所以我决定放弃Python转而使用Rust。

Rust

与Python类似，作者首先创建了原始的Rust版本，一个执行数据行插入的循环。

然而，即使进行了所有SQLite 优化，仍然需要大约3 分钟。因此，作者进行了进一步的测试。

将“rusqlite”替换为异步运行的“sqlx”将直接将时间减少到14分钟。作者表示这比他写过的任何Python 迭代都要糟糕。执行原始SQL 语句时使用准备好的语句。这个版本只需要1分钟。

最优的版本

使用准备好的语句插入一批50 行。最终需要34.3 秒。

作者还创建了一个线程版本，其中一个线程从通道接收数据，四个线程将数据推送到通道。

这也是迄今为止性能最好的版本，最终耗时约32.37 秒。

IO时间

SQLite论坛上的一位网友想出了一个有趣的想法来测量内存数据库所需的时间。

于是作者再次运行代码，将数据库位置设置为“:memory:”。 Rust 版本的完成时间现在快了2 秒（29 秒）。

换句话说，将1 亿条记录写入磁盘需要2 秒，这看起来很合理。

这也表明，由于99% 的时间都花在生成和追加数据上，因此可能没有更多的SQLite 优化可以更快地写入磁盘。

排行榜

插入1亿行数据所需时间：Rust 33秒PyPy 126秒CPython 210秒

总结

尽可能使用SQLite PRAGMA 语句

使用准备好的语句

执行批量插入

PyPy 确实比CPython 快4 倍

异步不一定更快

目前，第二快的版本运行单线程，而笔者的计算机有四个核心，因此它可以在一分钟内检索8亿行数据。即使合并数据需要几秒钟，也可能需要不到一分钟。

审查

这位博主的研究得到了网友的一致好评。

我真的很喜欢以下内容：

了解有关PRAGMA 声明的更多信息。

PyPy的效率和灵活性可以通过即插即用来体现（这无疑是未来的一个机会）。

本文的布局非常简单，并包含指向相应源代码的链接。它既有趣又方便。

Rust 的高光时刻又来了！

参考：

https://avi.im/blag/2021/fast-sqlite-inserts/

https://github.com/avinassh/fast-sqlite3-inserts