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

可能用很多朋友会发现如果使用insert插入较小数据量我们会很慢,但如果上万条记录同时插入时会发现很慢,下面我来给大家介绍为什么会这样及数据插入insert性能优化方法,有需要的朋友可进入参考.

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

经过对MySQL的测试，发现一些可以提高insert效率的方法，供大家参考参考。

1.一条SQL语句插入多条数据.

常用的插入语句,代码如下:

1. INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('0','userid_0','content_0',0); 
2. INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('1','userid_1','content_1',1); 
3.  
4. //修改成： 
5.  
6. INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('0','userid_0','content_0',0), 
7. ('1','userid_1','content_1',1); 

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

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

1. 记录数 单条数据插入 多条数据插入 
2. 1百 0.149s 0.011s  
3. 1千 1.231s 0.047s  
4. 1万 11.678s 0.218s 

2.在事物中进行插入处理,把插入修改成如下代码:

1. STARTTRANSACTION; 
2. INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('0','userid_0','content_0',0); 
3. INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('1','userid_1','content_1',1); 
4. ... 
5. COMMIT;  

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

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

1. 记录数 不使用事物 使用事物 
2. 1百 0.149s 0.033s  
3. 1千 1.231s 0.115s  
4. 1万 11.678s 1.050s 

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

1. 记录数 单条数据插入 合并数据+事物插入  
2. 1万 0m15.977s 0m0.309s  
3. 10万 1m52.204s 0m2.271s  
4. 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操作:

1. LOCK TABLES a WRITE; 
2.  
3. INSERT INTO a VALUES(1,23),(2,23); 
4.  //phpfensi.com
5. INSERT INTO a VALUES(8,7); 
6.  
7. UNLOCK TABLES; 

这对性能提高的好处在于:直到所有的INSERT语句都完成之后,索引缓存一次性刷新到磁盘中,通常情况下,有多少次INSERT语句就会有多少次索引缓存刷新到磁盘中的开销,如果能在一个语句中一次性插入多个值的话,显然锁表操作也没有必要了,对于事务表而言,用BEGIN/COMMIT代替LOCK TABLES来提高速度,锁表也会降低多次连接测试的总时间,尽管每个独立连接为了等待锁的最大等待时间也会增加.

1. Connection 1 does 1000 inserts 
2.  
3. Connection 2,3 and 4 do 1 insert 
4.  
5. 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配置项，超过这个值会日志会使用磁盘数据，这时，效率会有所下降。所以比较好的做法是，在事物大小达到配置项数据级前进行事物提交。