python输入数据至mysql效率提升_优化Python对MySQL批量插入的效率

之前测试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。

15个分库每个60十多万条。

过程中有个问题，开始我的sql变量是连接所有插入条目的，但是16节点的cobar在输100万条的时候就连接断开了，而单库直接10条也插不进去，显示mysql连接断开。

这个是sql语句长度限制的问题，在mysql的配置文件中有一个max_allowed_packet = 1M，10万条插入语句已经超过这个限额了，100万条分给16个cobar节点也超过了，所以可以把这个参数调大，或者代码里分段执行sql。

还有一个问题是事务大小的问题，innodb_log_buffer_size参数决定这个，超限的话数据会写入磁盘，从而导致效率下滑，所以事务提交也不能攒得太大。