![70a65b976df9b70bd64c0354939f803b.png](https://i-blog.csdnimg.cn/blog_migrate/2384d6b76952ecec13e0064840a94272.jpeg)
摘要
本文对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的类,实现初始化类方法如下:
![6cc09f8b40fff57438c90d6b154a2c51.png](https://i-blog.csdnimg.cn/blog_migrate/904db3d9dbef77779171b696cce8f450.jpeg)
这个方法中,首先从mysql_config.ini配置文件中,加载MySQL的配置数据,如host、port、user、pwd、db_name等;然后利用pymysql包的连接方法,创建一个MySQL的连接。
然后,我们定义MySqlConnector类的查询方法:
![0ce4d2490c774d4b24e859c5124dd058.png](https://i-blog.csdnimg.cn/blog_migrate/89b2d57b7086f2389715f4b2c5447106.jpeg)
这个方法可以执行对mysql的查询操作。
其次,我们再定义MySqlConnector类的update方法:
![f5f2f256e49b9acb7244c7a8a9745a25.png](https://i-blog.csdnimg.cn/blog_migrate/a9fc1e907bfca2c21bdef6c04535ad67.jpeg)
这个update方法,可以执行对mysql的插入、更新、删除操作。
最后,定义MySqlConnector类的close方法:
![41eb63472d4368124681db516e3c558d.png](https://i-blog.csdnimg.cn/blog_migrate/125bbadb3d88289246f0eded43cf813f.jpeg)
对MySQL查询或者其他操作执行完毕之后,一定要执行该方法,关闭mysql的连接。因为单位时间里MySQL的连接数有最大的上限,当Mysql的连接数太多时,会造成MySQL的性能下,甚至会出现事务进程卡死的情况。
对insert 多行数据的性能优化
我们先定义一个函数来计算某个函数的执行时间:
![727b5961f10253de0e153453c798136b.png](https://i-blog.csdnimg.cn/blog_migrate/46e45e9ffda69254963604e2d3249893.jpeg)
我们网上任意找2000个文字,来做这个优化对比测试:
![514cffd8807abf9c2bed38f40636cec2.png](https://i-blog.csdnimg.cn/blog_migrate/d7d62a91b48a4f4e50edd0215b43b850.jpeg)
所以现在的任务是需要将这2000行的文本,插入mysql中。
1、我们先定义一种插入方式:一个事务插入一行数据的方式
![c42c872b42cd3702c8eedff75b7ddec8.png](https://i-blog.csdnimg.cn/blog_migrate/45b9f87531257b625ab06b115f8cec7c.jpeg)
执行看看需要多少时间:
![1f00f37875d3a008e372b4c036f07fe5.png](https://i-blog.csdnimg.cn/blog_migrate/4fae3cb37c5dd89261d1ab8ffa96251c.jpeg)
我们得到,一个事务插入一行的方式,将2000个文本全部插入,需要30.99秒。
2、我们定义另一种方式:一个事务插入多行记录值
![eda71f9f01fdcd4cc4737c264bb5bdbd.png](https://i-blog.csdnimg.cn/blog_migrate/8dd74ae9192a6bc13f68f338c5c9d6de.jpeg)
看看这种方式的执行时间:
![28b7bd6e4c9e00db97c7db0447678cb0.png](https://i-blog.csdnimg.cn/blog_migrate/dd24d6c8d15b47c6d49fc17ef2450dcd.jpeg)
我们发现这种方式,插入2000行数据,仅需要0.099秒,比之前的方式,速度快了313倍。
对update多行数据的性能优化
同样地,使用如上的2000行文本数据,现在需要根据 id 将mysql中的某个字段更新成这个2000行文本。
1、常规的方式是
![a525673e4f0fdd9a5619476943d1d035.png](https://i-blog.csdnimg.cn/blog_migrate/25b8b3633be3fc785f7df73cd4f96e58.jpeg)
执行耗时:
![a5591d83bed0a51aace86dff712702e2.png](https://i-blog.csdnimg.cn/blog_migrate/28b5e05f51405284c2e1136ae7d3b352.jpeg)
这种一个事务更新一行记录的方式,处理2000行数据,需要耗时31.19秒。
2、能不能写成一个事务更新多行的方式
虽然MySQL原生语法,没有一次update更新多行记录的语法。但是,我们可以结合case when 语法,很优雅的实现这个需求。
![a9533b584d38daadd5c65a315a36f1e3.png](https://i-blog.csdnimg.cn/blog_migrate/4bf15ae4227498a7f9e3983b3c09b914.jpeg)
执行时间:
![33dca9bacaf7fd4a01997c946a1ac477.png](https://i-blog.csdnimg.cn/blog_migrate/b57a7fd5fe3d02e9b8884e5d44302b17.jpeg)
发现,采用这种方式,更新2000行文本的时间,仅需要0.11秒,比常规方式加速了283倍。
小结
对于MySQL的insert和update的操作,尽可能的使用一个事务处理多行记录值的方式,这样可以大大提升对MySQL的操作性能。另外需要注意的时,当使用一个事务处理多行记录值时,可能会出现如下的报错信息:"Got a packet bigger than 'max_allowed_packet' bytes"。
一个事务处理多行虽好,但是事务处理多行也是有最大上限的。当出现这个报错信息时,需要将数据分成几个batch,然后每个batch来使用一个事务处理多行的方式即可。