如何优化MySQL insert语句与性能?

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

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

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

　　常用的插入语句如：

　　INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('0','userid_0','content_0',0);

　　INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('1','userid_1','content_1',1);

　　修改成：

　　INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('0','userid_0','content_0',0),

　　('1','userid_1','content_1',1);

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

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

　　2.在事务中进行插入处理。

　　把插入修改成：

　　STARTTRANSACTION;

　　INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('0','userid_0','content_0',0);

　　INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('1','userid_1','content_1',1);

　　...

　　COMMIT;

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

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

　　性能测试：

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

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

　　注意事项：

　　1)如果你同时从同一客户插入很多行，使用多个值表的INSERT语句。这比使用分开INSERT语句快(在一些情况中几倍)。

　　Insertintotestvalues(1,2),(1,3),(1,4)…

　　2)如果你从不同客户插入很多行，能通过使用INSERTDELAYED语句得到更高的速度。Delayed的含义是让insert语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘；这比每条语句分别插入要快的多；LOW_PRIORITY刚好相反，在所有其他用户对表的读写完后才进行插入。

　　3)将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）。

　　4)如果进行批量插入，可以增加bulk_insert_buffer_size变量值的方法来提高速度，但是，这只能对myisam表使用。

　　5)当从一个文本文件装载一个表时，使用LOADDATAINFILE。这通常比使用很多INSERT语句快20倍。

　　6)根据应用情况使用replace语句代替insert。

　　7)根据应用情况使用ignore关键字忽略重复记录。

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

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

　　小编结语：

　　更多内容尽在课课家教育！