mysql批量插入数据，一次插入多少行数据效率最高？

一、前言

      我们在操作大型数据表或者日志文件的时候经常会需要写入数据到数据库，那么最合适的方案就是数据库的批量插入。只是我们在执行批量操作的时候，一次插入多少数据才合适呢？假如需要插入的数据有百万条，那么一次批量插入多少条的时候，效率会高一些呢？这里博主和大家一起探讨下这个问题，应用环境为批量插入数据到临时表。

二、批量插入前准备

      博主本地原本是循环查出来的数据，然后每1000条插入一次，直至完成插入操作。但是为什么要设置1000条呢，实不相瞒，这是因为项目里的其他批量插入都是一次插1000条。。汗，博主不服，所以想要测试下。

      首先是查看当前数据库的版本，毕竟各个版本之间存在差异，脱离版本讲数据库就是耍流氓（以前没少耍啊）：

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.34-log |
+------------+
1 row in set (0.00 sec)

1、插入到数据表的字段

      对于手动创建的临时表来说，字段当然是越少越好，而且字段占用的空间要尽量小一些，这样临时表不至于太大，影响表操作的性能。这里需要插入的字段是：

字段1 int(10)
字段2 int(10)
字段3 int(10)
字段4 varchar(10)

      我们一共插入四个字段，分别是3个int类型的，一个varchar类型的，整体来说这些字段都比较小，占用的内存空间会小一些。

2、计算一行字段占用的空间

      对于innodb引擎来说，int类型可以存储4个字节，里面的Int(M)并不会影响存储字节的大小，这个M只是数据的展示位数，和mysql的ZEROFILL属性有关，即在数字长度不够的数据前面填充0，以达到设定的长度。此处不多说，想要了解的朋友可以百度一下，还是很有意思的。
varchar（10）代表可以存储10个字符，不管是英文还是中文，最多都是10个，这部分假设存储的是中文，在utf-8mb4下，10个中文占用10*4 = 40个字节那么一行数据最多占用：4+4+4+40 = 52字节

3、在数据里做插入操作的时候，整体时间的分配

链接耗时 （30%）
发送query到服务器 （20%）
解析query （20%）
插入操作 （10% * 词条数目）
插入index （10% * Index的数目）
关闭链接 （10%）

      从这里可以看出来，真正耗时的不是操作，而是链接，解析的过程。单条sql的话，会在链接，解析部分耗费大量的时间，因此速度会很慢，所以我们一般都是采用批量插入的操作，争取在一次链接里面写入尽可能多的数据，以此来提升插入的速度。但是这个尽可能多的数据是多少呢？一次到底插入多少才合适呢？

三、批量插入数据测试

      开始测试，但是一开始插入多少是合适的呢，是否有上限？查询mysql手册，我们知道sql语句是有大小限制的。

1、SQL语句的大小限制

my.ini 里有max_allowed_packet 这个参数控制通信的packet 大小。mysql默认的sql语句的最大限制是1M（mysql5.7的客户端默认是16M，服务端默认是4M），可以根据设置查看。官方解释是适当增大max_allowed_packet 参数可以使client端到server端传递大数据时，系统能够分配更多的扩展内存来处理。

官方手册：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

2、查看服务器上的参数：

mysql> show variables like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 33554432   |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

33554432字节 =32M ，也就是规定大小不能超过32M。

3、计算一次能插入的最大行记录

1M计算的话，(1024*1024)/52 ≈ 20165 ，为了防止溢出，最大可一次性插入20000条（根据自己的配置和sql语句大小计算）。那么32M的话就是：20000 *32 = 640000 也就是64W条。

4、测试插入数据比对

（1）插入11W条数据，按照每次10,600,1000，20000,80000来测试：

+---------------+
| count(c1.uin) |
+---------------+
|         110000 |
+---------------+

有个博客说一次插入10条最快，，我觉得一次插的有点少，咱们试试

参考：https://www.cnblogs.com/aicro/p/3851434.html

      这个博主测试后，认为一次插10条是性能最快的，他的每条记录是3kb，相当于我的59行数据，取个整数60，那么对于这个博主是插入10条，对我来说插入：600，这几个值都试试。

耗时：

11W的数据，每次插入10条。耗时：2.361s
11W的数据，每次插入600条。耗时：0.523s
11W的数据，每次插入1000条。耗时：0.429s
11W的数据，每次插入20000条。耗时：0.426s
11W的数据，每次插入80000条。耗时：0.352s

      从这部分看，随着批量插入的增加，速度略有提升，最起码一次插10条应该不是最佳的。插入数据量多，减少了循环的次数，也就是在数据库链接部分的耗时有所减少，只是这个8W并不是极限数据，具体一次插入多少条，还有待参考。

（2）加大数据量到24w

+---------------+
| count(c1.uin) |
+---------------+
|        241397 |
+---------------+

耗时：

24W的数据，每次插入10条。耗时：4.445s
24W的数据，每次插入600条。耗时：1.187s
24W的数据，每次插入1000条。耗时：1.13s
24W的数据，每次插入20000条。耗时：0.933s
24W的数据，每次插入80000条。耗时：0.753s

      一次插入24W反而性能最佳，这么代表我们的测试数据量依然不够。

（3）加大测试量到42W

+---------------+
| count(c1.uin) |
+---------------+
|        418859 |

耗时：

42W的数据，每次插入1000条。耗时：2.216s
42W的数据，每次插入80000条。耗时：1.777s
42W的数据，每次插入16W条。耗时：1.523s
42W的数据，每次插入20W条。耗时：1.432s
42W的数据，每次插入30W条。耗时：1.362s
42W的数据，每次插入40W条。耗时：1.764s

      随着插入量的增加，批量插入条数多了之后，性能是有所提升的。但是在达到30W以上之后，效率反而有所下降。这部分我的理解是mysql是要分配一定的内存给传过来的数据包使用，当批量插入的数据量到达一定程度之后，一次插入操作的开销就很耗费内存了。个人感觉，最佳大小是max_allowed_packet的一半，也就是极限能插入64W，选用32W也许性能会更好一些，同时也不会对mysql的其他操作产生太大的影响。

5、如果插入的值就是sql语句限制的最大值，那么性能真的好吗？

      博主疯狂谷歌百度，都没有找到有人来具体的说一下这个问题，不过在高性能mysql里面发现一句话：
      客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。

      后面通过各种百度，博主觉得最大只是代表传输数据包的最大长度，但性能是不是最佳就要从各个方面来分析了。比如下面列出的插入缓冲，以及插入索引时对于缓冲区的剩余空间需求，以及事务占有的内存等，都会影响批量插入的性能。

四、其他影响插入性能的因素

1、首先是插入的时候，要注意缓冲区的大小使用情况

      在分析源码的过程中，有一句话：如果buffer pool余量不足25%，插入失败，返回DB_LOCK_TABLE_FULL。这个错误并不是直接报错：max_allowed_packet 不够大之类的，这个错误是因为对于innodb引擎来说，一次插入是涉及到事务和锁的，在插入索引的时候，要判断缓冲区的剩余情况，所以插入并不能仅仅只考虑max_allowed_packet的问题，也要考虑到缓冲区的大小。

参考淘宝的数据库日报：http://mysql.taobao.org/monthly/2017/09/10/

2、插入缓存

      另外对于innodb引擎来说，因为存在插入缓存（Insert Buffer）这个概念，所以在插入的时候也是要耗费一定的缓冲池内存的。当写密集的情况下，插入缓冲会占用过多的缓冲池内存，默认最大可以占用到1/2的缓冲池内存，当插入缓冲占用太多缓冲池内存的情况下，会影响到其他的操作。

      也就是说，插入缓冲受到缓冲池大小的影响，缓冲池大小为：

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

      换算后的结果为：128M，也就是说，插入缓存最多可以占用64M的缓冲区大小。这个大小要超过咱们设置的sql语句大小，所以可以忽略不计。

详细解释：

      我们都知道，在InnoDB引擎上进行插入操作时，一般需要按照主键顺序进行插入，这样才能获得较高的插入性能。当一张表中存在非聚簇的且不唯一的索引时，在插入时，数据页的存放还是按照主键进行顺序存放，
但是对于非聚簇索引叶节点的插入不再是顺序的了，这时就需要离散的访问非聚簇索引页，由于随机读取的存在导致插入操作性能下降。

InnoDB为此设计了Insert Buffer来进行插入优化。对于非聚簇索引的插入或者更新操作，不是每一次都直接插入到索引页中，而是先判断插入的非聚集索引是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buffer中。看似数据库这个非聚集的索引已经查到叶节点，而实际没有，这时存放在另外一个位置。然后再以一定的频率和情况进行Insert Buffer和非聚簇索引页子节点的合并操作。这时通常能够将多个插入合并到一个操作中，这样就大大提高了对于非聚簇索引的插入性能。

参考：https://cloud.tencent.com/developer/article/1200824
参考：mysql技术内幕 Innodb篇

3、使用事务提升效率

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

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;

参考：https://my.oschina.net/songhongxu/blog/163063

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

查看： show variables like '%innodb_log_buffer_size%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 67108864 |
+------------------------+----------+

大概是：64M

      这种写法和批量写入的效果差不多，只不过sql语句还是单句的，然后统一提交。一个瓶颈是SQL语句的大小，一个瓶颈是事务的大小。当我们在提交sql的时候，首先是受到sql大小的限制，其次是受到事务大小的限制。在开启事务的情况下使用批量插入，会节省不少事务的开销，如果要追求极致的速度的话，建议是开着事务插入的。不过需要注意一下，内存是有限且共享的，如果批量插入占用太多的事务内存，那么势必会对其他的业务操作等有一定的影响。

4、通过配置提升读写性能

      也可以通过增大innodb_buffer_pool_size 缓冲区来提升读写性能，只是缓冲区是要占用内存空间的，内存很珍贵，所以这个方案在内存富裕，而性能瓶颈的时候，可以考虑下。

参考：https://my.oschina.net/anuodog/blog/3002941

5、索引影响插入性能

      如果表中存在多个字段索引，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护。这样就降低了数据的插入速度。对于普通的数据表，主键索引是肯定要有的，想要加快性能的话，就是要有序插入，每次插入记录都在索引的最后面，索引的定位效率很高，并且对索引调整较小。如果插入的记录在索引中间，需要B+tree进行分裂合并等处理，会消耗比较多计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘操作。

五、总结

      博主经过测试+谷歌，最终是选用的一次批量插入数据量为max_allowed_packet大小的一半。只是在不断的搜索中，发现影响插入性能的地方挺多的，如果仅仅是拿max_allowed_packet这个参数作为分析，其实是没有意义的，这个参数只是设置最大值，但并不是最佳性能。不过需要注意，由于sql语句比较大，所以才执行完插入操作之后，一定要释放变量，不要造成无谓的内存损耗，影响程序性能。

      对于我们的mysql来说也是一样的，mysql的最佳性能是建立在各个参数的合理设置上，这样协同干活儿的效果最佳。如果其他设置不到位的话，就像是木桶原理一样，哪怕内存缓冲区设置的很大，但是性能取决的反而是设置最差的那个配置。关于mysql的配置调优，我们都在路上，加油！

end