MySQL插入效率改进

MySQL表在批量插入大量数据时，会出现插入速度越来越慢的现象：实际使用时，10列的表，前100W条速度比较快，到了200W条时速度已经慢了十几倍不止，已经到了无法忍受的程度，遇到这种现象时可以使用下面方法改进速度：

1、单条insert into语句中包含更多条数据

insert into table_xxx values(1,2,3,4)

改为使用多条

insert into table_xxx values(1,2,3,4),(5,6,7,8),(9,10,11,12)

这样做的确能加快不少速度，但是实际测试结果发现，这样只是前期速度较快，延后了速度变到极慢的时间。需要注意max_allowed_packet变量的设置，该变量表示单条SQL的最大长度。

2、控制commit频率

在mysql中运行 ： “show variables like '%commit%';”，autocommit=ON表示每次insert请求MySQL都会commit一次，设为OFF后，由程序在插入批量数据之后再统一进行COMMIT会比较快。PS：实际使用时发现插入特别多数据都不进行COMMIT也可能导致速度过慢- -（有待观察）

另外改变innodb_flush_log_at_trx_commit在使用innodb的时候也可以提高效率，该值影响到MySQL向硬盘Flush日志的频率，设为0会降低安全性但提高效率。

3、插入有序数据，降低更新索引时间

据说有序（从小到大）地插入批量数据会让索引类型为B-TREE的表索引更新速度更快，但未试验过- -

另外，增大innodb_buffer_pool_size可以让使用innodb的表索引更新更快。

4、别用innodb，换用MyISAM

前者的安全性较高，支持事务、外键，速度较慢，后者恰恰相反。测试效果很好，基本上不存在插入速度递减的现象！