在SQLite中插入10亿条Python VS Rust

在SQLite中插入10亿条Python VS Rust

在实际生活中，市场有这样的案例：写脚本来进行数据处理，比如说给数据库导入导出数据，这种任务一般来说最方便的方法是用python脚本，但是如果数据量比较大时候(比如上亿条)时候Python就会超级慢，看到无法忍受。在这种案例时候该怎么做呢，有一个外国老哥分享了自己的实践经历，并且对比了Python和Rust语言给SQLite插入十一条数据的情况，最后用Rust实现了在一分钟来完成任务。我们在此分享一下该实践过程，希望能对大家有所启迪，大家也可以尝试自己最拿手方法来实现该例子，并对比一下具体性能。

概述

案例中的任务是SQLite数据库插入10亿条的数据。表(user)数据结构和约束如下：

create table IF NOT EXISTS user  (  id INTEGER not null primary key,  area CHAR(6),  age INTEGER not null,  active INTEGER not null  );

随机生成数据。其中are列为六位数的区号(任何六位数字)。age将是5、10 或15中的一个数字。Active为0或1。

实验环境硬件配置为：MacBook Pro，2019(2.4 GHz 四核i5，8GB内存，256GB ***硬盘，Big Sur 11.1)。 任务前提：任务无需保持程序稳健性，如果进程崩溃并且所有数据都丢失了也没关系。可以再次运行脚本。 需要充分利用我的机器资源：100% CPU、8GB 内存和千兆字节的***空间。

无需使用真正的随机方法，stdlib伪随机方法即可。

Python

首先是原始版本的Python方法。Python标准库提供了一个SQLite模块，首先使用它编写了第一个版本。代码如下：

在该脚本中，通for循环中一一插入1000万条数据。执行花了将近15分钟。基于此进行优化迭代，提高性能。

SQLite中，每次插入都是原子性的并且为一个事务。每个事务都需要保证写入磁盘(涉及IO操作)，因此可能会很慢。为了优化，可以尝试通过不同大小的批量插入，对比发现，100000是最佳选择。通过这个简单的更改，运行时间减少到了10分钟，优化了3分之一，但是仍然非常耗时。优化后，批量插入版本源码：

SQLite库优化

除了在代码层优化外，如果对于单纯的数据写入，对数据库本身搞的优化也是非常重要的。对于SQLite优化，可以做如下配置：

PRAGMA journal_mode = OFF;  PRAGMA synchronous = 0;  PRAGMA cache_size = 1000000;  PRAGMA locking_mode = EXCLUSIVE;  PRAGMA temp_store = MEMORY;

具体解释：

首先，journal_mode设置为OFF，将会关闭回滚日志，禁用 SQLite 的原子提交和回滚功能，这样在事务失败情况下，无法恢复，基于例子实例稳健性要求可以设置，但是严禁在生产环境中使用。

其次，关闭synchronous，SQLite可以不再校验磁盘写入的数据可靠性。写入SQLite可能并不意味着它已刷新到磁盘。同样，严禁在生产环境中启用。

cache_size用户指定SQLite允许在内存中保留多少内存页。不要在生产中分配太高的的数值。

使用在EXCLUSIVE锁定模式，SQLite连接持有的锁永远不会被释放。

设置temp_store到MEMOR将使其表现得像一个内存数据库。

优化性能

对上面的两个脚本，添加 SQLite优化参数，然后重新运行：

def main():      con = sqlite3.connect(DB_NAME, isolation_level=None)      con.execute('PRAGMA journal_mode = OFF;')      con.execute('PRAGMA synchronous = 0;')      con.execute('PRAGMA cache_size = 1000000;') # give it a GB      con.execute('PRAGMA locking_mode = EXCLUSIVE;')      con.execute('PRAGMA temp_store = MEMORY;')      create_table(con)

faker(con, count=100_000_000)

优化后版本，原始版本，插入1亿行数据，大概花了10分钟;对比批量插入版本大概花了8.5分钟。

pypy版本

对比CPython PyPy在数据处理中可以提高性能，据说可以提高4倍以上的性能。本实验中也尝试编译PyPy解释器，运行脚本(代码无需修改)。

使用pypy解释器，批处理版本，插入1亿行数据只需2.5分钟。性能大概是Cpython的3.5倍，可见传说的4倍性能提高确实是真的，诚不我欺也!。同时，为了测试在纯循环插入中消耗的时间，在脚本中删除SQL指令并运行：

以上脚本在CPython中耗时5.5分钟 。PyPy执行耗时1.5分钟(同样提高了3.5倍)。

Rust

在完成Python各种优化折腾。又尝试了Rust版本的插入，对比也有个原始版本和批量插入版本。原始版本，也是每行插入：

该版执行，大概用时3分钟。然后我做了进一步的实验：

将rusqlite，换成sqlx异步运行。

这个版本花了大约14分钟。性能反而下降下降了。比Python版本还要差(原因值得深析)。

对执行的原始SQL语句，切换到准备好的语句并在循环中插入行，但重用了准备好的语句。该版本只用了大约一分钟。

使用准备好的语句并将它们插入到50行的批次中，插入10亿条，耗时34.3 秒。

这是性能最好的版本，耗时约32.37秒。

基准测试对比：

总结

通过案例不同任务实验，总体上可以得到：

通过SQLite PRAGMA语句优化设置可以提高插入性能。 使用准备好的语句可以提高性能 进行批量插入可以提高性能。 PyPy 实际上比CPython快4倍 线程/异步不一定能提高性能。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。