优化Python对MySQL批量插入的效率

最新推荐文章于 2024-12-29 16:11:06 发布

weixin_30497527

最新推荐文章于 2024-12-29 16:11:06 发布

阅读量509

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 python

原文链接：http://www.cnblogs.com/hyace/p/4173831.html

本文分享了使用Python的MySQLdb模块优化MySQL批量插入性能的经验，包括多条插入、事务管理和有序插入等策略。同时，解决了由于SQL语句过长导致的连接断开问题，并讨论了事务大小的限制及其对性能的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

之前测试cobar的效率，因为cobar不支持存储过程，所以需要应用程序插入数据，jdbc不灵活，用Python的MySQLdb模块可以实现。

　　开始测试的时候用单条insert语句循环n次，最后commit，结果慢的要死，插一万条用了两分钟，十万条我去吃了个饭回来还在插。十万条用存储过程插单库也用了50多秒。

　　从UC的这篇文章学习了一些SQL优化的知识。

　　主要有三条：
1、insert的时候尽量多条一起插，不要单条插。这样可以减少日志量，降低日志刷盘数据量和频率，效率提高很多。

这是文章提供的测试对比：

2、在事务中进行插入。也就是每次部分commit，否则每条insert commit 创建事务的消耗也是不小的。

3、数据插入的时候保持有序。比如Innodb用的是B+树索引，对B+树的插入如果是在索引中间就会需要树节点分裂合并，这也会有一定的消耗。

几种方法综合起来的测试数据如下：

　　可以看到1000万以下数据的优化效果是明显的，但是单合并数据+事务的方式在1000万以上性能会有急剧下降，因为此时已经达到了innodb_buffer的上限，随机插入每次需要大量的磁盘操作，性能下降明显。而有序插入在1000万以上时也表现稳定，因为索引定位方便，不需要频繁对磁盘读写，维持较高性能。

据此修改了Python程序：

 import MySQLdb

 db=MySQLdb.connect(host='127.0.0.1', user='test', passwd='test', port=8066)

 cur=db.cursor()

 cur.execute('use dbtest')

 cur.execute('truncate table tb5')

 for t in range(0,100):

     sql = 'insert into tb5 (id, val) values '

     for i in range(1,100000):
            sql += ' ('+`t*100000+i`+', "tb5EXTRA"),'
     sql += ' ('+`t`+'00000, "tb5EXTRA")'

     cur.execute(sql)

     db.commit()

 cur.close()

 db.close()

共插入了1000万条数据，sublime显示用了333.3s，比之前插入10万条都减少了很多，测试插10万条只需要3s。