access update语句执行_用pymysql实现对多行数据的insert和update加速

2023年1月29日13:27:22
access update语句执行_用pymysql实现对多行数据的insert和update加速

摘要

本文对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的类,实现初始化类方法如下:

access update语句执行_用pymysql实现对多行数据的insert和update加速

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

然后,我们定义MySqlConnector类的查询方法:

access update语句执行_用pymysql实现对多行数据的insert和update加速

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

其次,我们再定义MySqlConnector类的update方法:

access update语句执行_用pymysql实现对多行数据的insert和update加速

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

最后,定义MySqlConnector类的close方法:

access update语句执行_用pymysql实现对多行数据的insert和update加速

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

对insert 多行数据的性能优化

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

access update语句执行_用pymysql实现对多行数据的insert和update加速

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

access update语句执行_用pymysql实现对多行数据的insert和update加速

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

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

access update语句执行_用pymysql实现对多行数据的insert和update加速

执行看看需要多少时间:

access update语句执行_用pymysql实现对多行数据的insert和update加速

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

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

access update语句执行_用pymysql实现对多行数据的insert和update加速

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

access update语句执行_用pymysql实现对多行数据的insert和update加速

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

对update多行数据的性能优化

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

1、常规的方式是

access update语句执行_用pymysql实现对多行数据的insert和update加速

执行耗时:

access update语句执行_用pymysql实现对多行数据的insert和update加速

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

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

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

access update语句执行_用pymysql实现对多行数据的insert和update加速

执行时间:

access update语句执行_用pymysql实现对多行数据的insert和update加速

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

小结

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

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

  • 作者:weixin_39771969
  • 原文链接:https://blog.csdn.net/weixin_39771969/article/details/110296479
    更新时间:2023年1月29日13:27:22 ,共 1565 字。