如何优化MySQL insert性能

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

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

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

常用的插入语句如：

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES (0, userid_0, content_0, 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES (1, userid_1, content_1, 1);

修改成：

INSERT INTO `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、在事物中进行插入处理。

把插入修改成：

START TRANSACTION;
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES (0, userid_0, content_0, 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES (1, userid_1, content_1, 1);
...
COMMIT;

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

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

性能测试：

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

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

注意事项：

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

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

====================================分割线================================

最新内容请见作者的GitHub页：http://qaseven.github.io/

索引对MYSQL性能的影响 索引到底对性能有多少影响？ 这个问题估计是很多MySQL小白好奇的问题。当然我也是一样。因为之前的时候，并没有对索引有太多的注意，而且之前的工作经历，因为数据量很小，索引所起到的作用并不是很大，所以也没有太大注意。
手把手教你Prometheus + Granafa实现mysql 性能监测部署 数据库性能监控可以说是十分重要，能否自行搭建环境实现像阿里云或是腾讯云那样直观的展示不同维度数据的功能？答案是肯定的。下面详细说明一下安装部署过程以及过程中出现的问题，希望对你有所帮助！
Intel PAUSE指令变化如何影响MySQL的性能 x86、arm指令都很多，无论是应用程序员还是数据库内核研发大多时候都不需要对这些指令深入理解，但是 Pause 指令和数据库操作太紧密了，本文通过一次非常有趣的性能优化来引入对 Pause 指令的理解，期望可以事半功倍地搞清楚 CPU指令集是如何影响你的程序的。
ELK搭建（六）：搭建mysql性能、执行效率监控平台 mysql作为市场的主流数据库，承载了大部分公司的核心业务数据，同时也是大多数业务的底层存储。 针对mysql运行情况的监控必不可少，之前我们讲解了如何搭建mysql慢日志、错误日志的监控平台。 那么本期，我们针对mysql集群、性能、各类sql语句执行情况、服务状态等指标来搭建一个可视化的监控平台，方便我们实时了解mysql资源利用率、sql执行效率、访问压力等等。