MySQL数据插入insert性能优化详解

对于一些数据量较大的系统，面临的问题除了是查询效率低下，还有一个很重要的问题就是插入时间长。我们就有一个业务系统，每天的数据导入需要4-5个钟。这种费时的操作其实是很有风险的，假设程序出了问题，想重跑操作那是一件痛苦的事情。因此，提高大数据量系统的MySQL insert效率是很有必要的。

经过对MySQL的测试，发现一些可以提高insert效率的方法，供大家参考参考。
1. 一条SQL语句插入多条数据。

常用的插入语句如：

修改后的插入操作能够提高程序的插入效率。这里第二种SQL执行效率高的主要原因有两个，一是减少SQL语句解析的操作， 只需要解析一次就能进行数据的插入操作，二是SQL语句较短，可以减少网络传输的IO。

这里提供一些测试对比数据，分别是进行单条数据的导入与转化成一条SQL语句进行导入，分别测试1百、1千、1万条数据记录。
 

记录数 单条数据插入 多条数据插入

1百 0.149s 0.011s
1千 1.231s 0.047s
1万 11.678s 0.218s

2. 在事物中进行插入处理。
把插入修改成：

使用事物可以提高数据的插入效率，这是因为进行一个INSERT操作时，MySQL内部会建立一个事物，在事物内进行真正插入处理。通过使用事物可以减少创建事物的消耗，所有插入都在执行后才进行提交操作。

这里也提供了测试对比，分别是不使用事物与使用事物在记录数为1百、1千、1万的情况。
 

记录数 不使用事物 使用事物

1百 0.149s 0.033s
1千 1.231s 0.115s
1万 11.678s 1.050s

性能测试：

这里提供了同时使用上面两种方法进行INSERT效率优化的测试。即多条数据合并为同一个SQL，并且在事物中进行插入。

记录数 单条数据插入 合并数据+事物插入
1万 0m15.977s 0m0.309s
10万 1m52.204s 0m2.271s
100万 18m31.317s 0m23.332s
 

从测试结果可以看到，insert的效率大概有50倍的提高，这个一个很客观的数字。

如果要在同一个客户端在同一时间内插入很多记录，可以使用INSERT语句附带有多个values值。这种做法比使用单一值的INSERT语句快多了（在一些情况下比较快）。如果是往一个非空数据表增加记录，可以调整变量bulk_insert_buffer_size的值使其更快。
 
如果要从不用的客户端插入大量记录，使用INSERT DELAYED语句也可以提高速度。
 
对应MyISAM，可以在SELECT语句正在运行时插入记录，只要这时候没有正在删除记录。
 
想要将一个文本文件加载到数据表中，可以使用LOAD DATA INFILE。这通常是使用大量INSERT语句的20倍。
 
通过一些额外工作，就可以让LOAD DATA INFILE在数据表有大量索引的情况下运行更快。步骤如下：
 
用create table随表建一个表
 
执行FLUSH TABLES语句或admin flush-tables命令
 
执行myisamchk –keys-used=0 -rq /path/to/db/tbl_name命令，删除数据表所有索引。
 
执行LOAD DATA INFILE，数据插入到表中，由于无需更新表索引，因此这将非常快。
 
如果将来只是读取该表，运行myisampack让数据表更小。
 
运行myisamchk -r -q /path/to/db/tbl_name重建索引。创建的索引树在写入磁盘前先保存在内存中，这省去了磁盘磁盘搜索，因此速度快很多。重建后的索引树分布非常均衡。
 
执行FLUSH TABLES语句或mysqladmin flush-tables命令
 
注意，在Mysql 4.0起，可以运行ALTER TABLE tbl_name DISABLE KEYS来代替myisamchk –keys-used=0 -rq /path/to/db/tbl_name.运行ALTER TABLE tbl_name ENABLE KEYS代替myisamchk -r -q /path/to/db/tbl_name.这么做就可以省去FLUSH TABLES步骤。
 
 
 
可以在锁表后，一起执行几个语句来加速INSERT操作：
 
LOCK TABLES a WRITE;
 
INSERT INTO a VALUES(1,23),(2,23);
 
INSERT INTO a VALUES(8,7);
 
UNLOCK TABLES;
 
这对性能提高的好处在于：直到所有的INSERT语句都完成之后，索引缓存一次性刷新到磁盘中。通常情况下，有多少次INSERT语句就会有多少次索引缓存刷新到磁盘中的开销。如果能在一个语句中一次性插入多个值的话，显然锁表操作也没有必要了。对于事务表而言，用BEGIN/COMMIT代替LOCK TABLES来提高速度。锁表也会降低多次连接测试的总时间，尽管每个独立连接为了等待锁的最大等待时间也会增加。
 
Connection 1 does 1000 inserts
 
Connection 2,3 and 4 do 1 insert
 
Connection 5 does 1000 inserts
 
如果没有锁表，则连接2，3，4会在1，5之前完成。如果锁表了，则连接2，3，4可能在1，5之后才能完成，但总时间可能只需要40%。Mysql的INSERT、UPDATE、DELETE操作都非常快，不过在一个语句中如果超过5个插入或者更新时最好加锁以得到更好的性能。如果要一次性做很多次插入，最好在每个循环的前后加上LOCK TABLES和UNLOCK TABLES，从而让其他进程也能访问数据表；这么做性能依然不错。INSERT总比LOAD DATA INFILE插入数据慢，因为二者实现策略有分明的不同。
 
想要MyISAM表更快，在LOAD DATA INFILE和INSERT时都可以增加系统变量key_buffer_size的值。

注意事项：

1. SQL语句是有长度限制，在进行数据合并在同一SQL中务必不能超过SQL长度限制，通过max_allowed_packe配置可以修改，默认是1M。

2. 事物需要控制大小，事物太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项，超过这个值会日志会使用磁盘数据，这时，效率会有所下降。所以比较好的做法是，在事物大小达到配置项数据级前进行事物提交。