用pymysql实现对多行数据的insert和update加速

100人浏览 2025-03-31 00:01:20

摘要

本文对pymysql操作MySQL，insert和update的速度优化测试对比。对于insert操作来说，“一个事务处理多行的方式”比“一个事务处理一行的方式”插入相同的数据快了313倍，即插入2000个记录，一个事务一行的方式需要30.99秒，优化方式仅需要0.099秒。MySQL原始语法是支持对insert语句一次插入多行记录，即insert into table_name values ，后面跟着多行值。

但是对于update语句来说，MySQL并没有原始的语法支持一次update处理多行记录，update原始语句仅支持一次处理多个字段。本文将update语法和case when 语法结合起来，可以实现一个事务update更新多行记录，经测试这种方式比常规的方式快了283倍，即处理2000个记录，常规方式需要31.19秒，优化方式仅需要0.11秒。

pymysql包操作MySQL数据库

我们可以用Python封装一个MySQL连接的类，在类中实现对MySQL操作的类方法。

例如，我们可以新建一个MySqlConnector的类，实现初始化类方法如下：

这个方法中，首先从mysql_config.ini配置文件中，加载MySQL的配置数据，如host、port、user、pwd、db_name等；然后利用pymysql包的连接方法，创建一个MySQL的连接。

然后，我们定义MySqlConnector类的查询方法：

这个方法可以执行对mysql的查询操作。

其次，我们再定义MySqlConnector类的update方法：

这个update方法，可以执行对mysql的插入、更新、删除操作。

最后，定义MySqlConnector类的close方法：

对MySQL查询或者其他操作执行完毕之后，一定要执行该方法，关闭mysql的连接。因为单位时间里MySQL的连接数有最大的上限，当Mysql的连接数太多时，会造成MySQL的性能下，甚至会出现事务进程卡死的情况。

对insert 多行数据的性能优化

我们先定义一个函数来计算某个函数的执行时间：

我们网上任意找2000个文字，来做这个优化对比测试：

所以现在的任务是需要将这2000行的文本，插入mysql中。

1、我们先定义一种插入方式：一个事务插入一行数据的方式

执行看看需要多少时间：

我们得到，一个事务插入一行的方式，将2000个文本全部插入，需要30.99秒。

2、我们定义另一种方式：一个事务插入多行记录值

看看这种方式的执行时间：

我们发现这种方式，插入2000行数据，仅需要0.099秒，比之前的方式，速度快了313倍。

对update多行数据的性能优化

同样地，使用如上的2000行文本数据，现在需要根据 id 将mysql中的某个字段更新成这个2000行文本。

1、常规的方式是

执行耗时：

这种一个事务更新一行记录的方式，处理2000行数据，需要耗时31.19秒。

2、能不能写成一个事务更新多行的方式

虽然MySQL原生语法，没有一次update更新多行记录的语法。但是，我们可以结合case when 语法，很优雅的实现这个需求。

执行时间：

发现，采用这种方式，更新2000行文本的时间，仅需要0.11秒，比常规方式加速了283倍。

小结

对于MySQL的insert和update的操作，尽可能的使用一个事务处理多行记录值的方式，这样可以大大提升对MySQL的操作性能。另外需要注意的时，当使用一个事务处理多行记录值时，可能会出现如下的报错信息："Got a packet bigger than 'max_allowed_packet' bytes"。

一个事务处理多行虽好，但是事务处理多行也是有最大上限的。当出现这个报错信息时，需要将数据分成几个batch，然后每个batch来使用一个事务处理多行的方式即可。