剩余空间不足无法转存_MySQL 批量操作，一次插入多少行数据效率最高？

点击上方的“宇道源代码”，选择“”

谁关心第一波或第二波？

能掀起的浪花才是好浪花！

每天10点33分更新文章，每天都会掉一点头发……

源代码精品专栏

当我们操作大型数据表或者日志文件的时候，经常需要将数据写入数据库，那么最适合的解决方案就是数据库的批量插入。但是当我们进行批量操作的时候，每次应该插入多少数据呢？

如果有百万条数据需要插入，那么每次应该插入多少条记录才能达到较高的效率呢？这里博主就和大家探讨一下这个问题。应用环境是将数据批量插入到临时表中。

基于Boot+Plus+Vue&的后端管理系统+用户小程序，支持RBAC动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能

博主本来是循环遍历数据，每1000条记录插入一次，直到插入完成。但是为什么要设置1000条记录呢？说实话，这是因为项目中其他批量插入都是每次插入1000条记录。汗，博主很不服气，所以想测试一下。

第一步是查看当前数据库的版本，毕竟版本之间是有差异的，不考虑版本就谈论数据库就是耍流氓（我之前就干过很多次这种事）：

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

对于手动创建的临时表，字段越少越好，字段占用的空间尽量小，这样不至于临时表过大而影响表操作的性能。这里需要插入的字段有：

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

我们一共插入了四个字段，三个是int类型，一个是type类型，总体来说这些字段比较小，占用的内存空间也比较少。

对于引擎来说，int类型可以存储4个字节，里面的Int(M)并不影响存储的字节数大小，这个M只是显示数据的位数，跟MySQL的属性有关，就是长度不够的数据前面补0，达到设定的长度。这里就不细说了，想了解的话可以百度搜一下，还是很有意思的。

（10）表示可以存储10个字符，不管是英文还是中文。这部分假设存储的是中文。在utf-8mb4下，10个汉字占用10*4=40个字节。所以一行数据最多占用：4+4+4+40=52个字节

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

从这里我们可以看出，真正耗时的部分并不是操作，而是链接和解析过程。对于单个 SQL 语句来说，大量的时间会花在链接和解析部分，因此速度会非常慢。所以我们通常使用批量插入操作，试图在一次链接中写入尽可能多的数据，以提高插入速度。但是这个尽可能多的数据是多少呢？一次应该插入多少数据呢？

基于Cloud++Nacos++Vue的后端管理系统+用户小程序&支持RBAC动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能

我们开始测试吧，但是一开始应该插入多少数据呢？有上限吗？查了 MySQL 手册，知道 SQL 语句是有大小限制的。

my.ini中的这个参数控制着通讯的大小，mysql默认的sql语句最大限制是1M（.7客户端默认是16M，服务端默认是4M），可以在设置里查看。官方的解释是，适当增大该参数可以让系统在端到端传输时分配更多的扩展内存来处理大数据。

官方手册：

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)

Bytes = 32M，表示指定的大小不能超过32M。

如果使用1M，则（1024*1024）/52≈20165，为了防止溢出，一次最多可以插入2万条记录（根据自己的配置和SQL语句大小计算），如果使用32M，则2万*32=64万条记录。

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

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

参考：

这位博主经过测试，认为每次插入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并不是极限数据，具体一次插入多少条记录还有待参考。

+---------------+
| 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其实就已经提供最好的性能了，也就是说测试数据量还是不足的。

+---------------+
| 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

随着插入量的增加，性能会提升，但是当批量插入超过30万条时，效率就会下降，我的理解是MySQL需要为传输的数据包分配一定的内存，当批量插入量达到一定程度时，一次插入操作的开销就会消耗大量的内存。

个人感觉最好的大小是它的一半，也就是说最多可以插入64W的数据量，选择32W可能性能会更好一些，而且对mysql的其他操作也不会有太大的影响。

博主疯狂的在、百度搜索，也没找到人详细讲这个问题，不过在《High MySQL》里找到了这样一句话：

客户端以单个数据包的形式向服务端发送查询请求，因此当查询语句很长时，需要设置参数。但需要注意的是，如果查询语句过大，服务端会拒绝接收更多数据并抛出异常。相比之下，服务端响应给用户的数据通常很多，由多个数据包组成。但服务端在响应客户端请求时，客户端必须完整接收整个返回结果，不能简单地取前几个结果然后要求服务端停止发送。因此，在实际开发中，保持查询简单、只返回必要的数据，减少通信之间的数据包大小和数量是一个好习惯。这也是为什么在查询中应尽量避免使用 * 和添加 LIMIT 限制的原因之一。

博主在百度各种查找之后，觉得最大值只是代表传输数据包的最大长度，但性能是否最优需要从各个方面来分析，比如下面列出的插入缓冲区，插入索引时缓冲区需要的剩余空间，以及事务占用的内存等等，都会影响批量插入的性能。

在分析源码的时候，有这样一句话：如果池余额小于25%，则插入失败并返回。这个错误并不是直接报错：不够大或者类似这样的错误。这个错误是因为对于引擎来说，插入涉及到事务和锁，在插入索引的时候需要判断的剩余状态，所以插入不能只考虑这个问题，还要考虑的大小。

参考淘宝的数据库日报：

另外对于引擎来说，因为有 cache()的概念，在插入时会消耗一定的 pool ，当写密集的时候， 会占用过多的 pool ，默认情况下最多可以占用 pool 的1/2，当 占用过多 pool 时，就会影响其他操作。

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

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

换算之后的结果是：128M，也就是 cache最多能占用64M的 size，这个大小比我们设置的SQL语句大小要大，所以可以忽略。

详细解释：

我们都知道，在引擎上插入数据时，一般需要按照主键的顺序插入数据，才能获得较高的插入性能。当表有非聚集非唯一索引时，插入时数据页依然按照主键的顺序存储，但是非聚集索引叶子节点的插入不再是顺序的。此时需要离散地访问非聚集索引页，由于随机读的存在，插入性能会降低。

为此，设计了插入优化，对于非聚集索引的插入或者更新操作，不是每次都直接插入到索引页中，而是先判断插入的非聚集索引是否在缓冲池中，如果在，则直接插入；如果不在，则先放入缓冲区。

看上去数据库已经找到了这个非聚集索引的叶子节点，但实际上并没有，它存储在另外一个位置，然后以一定的频率和情况进行与非聚集索引页子节点的合并操作，此时通常可以将多次插入合并为一次操作，大大提高非聚集索引的插入性能。

参考：

参考：MySQL技术内幕

还有一种说法是使用事务可以提高数据插入的效率。这是因为在执行操作时，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;

参考：

事务大小需要控制，事务过大可能会影响执行效率。MySQL有个ze配置项，超过这个值数据就会被刷到磁盘，效率会降低，所以最好在数据达到这个值之前就提交事务。

查看：显示类似‘%ze%’；

+------------------------+----------+
        | Variable_name          | Value    |
        +------------------------+----------+
        | innodb_log_buffer_size | 67108864 |
        +------------------------+----------+

大约：64M

这种写法跟批量写的效果差不多，只不过SQL语句还是单句的，然后统一提交。一个瓶颈是SQL语句的大小，另一个瓶颈是事务的大小。我们在提交SQL的时候，首先受限于SQL的大小，其次受限于事务的大小。使用开启事务的批量插入会省去很多事务开销。如果要追求极速，建议开启事务插入。

但需要注意的是，内存是有限且共享的，如果批量插入占用了过多的事务内存，势必会对其他业务操作造成一定的影响。

也可以通过增加缓冲区的大小来提高读写性能，但是缓冲区会占用内存空间，而内存是宝贵的，所以在内存充足，但性能存在瓶颈的情况下可以考虑这种方案。

参考：

如果一个表中有多个字段索引，那么在表中增加、删除、修改数据时，必须动态维护索引。这样会降低数据插入的速度。对于普通的数据表，主键索引是必须的，如果想加快性能，必须按顺序插入记录，每条插入的记录都在索引的末尾，索引定位效率很高，索引调整量小。如果插入的记录在索引的中间，则需要进行B+树的分裂和合并，会消耗更多的计算资源，插入记录的索引定位效率也会下降，当数据量很大的时候，会存在频繁的磁盘操作。

博主经过测试和查找，最终选择一次批量插入一半的数据量，但是在不断的查找中发现影响插入性能的地方还有很多，如果只用这个参数来分析其实是没有意义的，这个参数只是设置了最大值，但并不是性能最优。

不过需要注意的是，由于SQL语句比较大，所以执行完插入操作后一定要释放变量，避免造成不必要的内存损失，影响程序性能。

我们的MySQL也是一样，MySQL的最佳性能是建立在各项参数合理设置，让协同工作效果达到最佳的基础上的，如果其他设置不到位，就像木桶原理一样，即使内存缓冲区设置得很大，性能还是取决于最差的设置。关于MySQL的配置调优，我们都在路上，加油！

欢迎加入我的知识星球，一起讨论架构、交流源码，加入请长按下方二维码：

源代码分析已在知识星球更新如下：

近期更新的《云道2.X入门》系列已有101余篇文章，涵盖了Redis、ES、分片、读写分离、SQL、权限、Dubbo、SQL、Kafka、性能测试等内容。

提供近3万行代码的示例以及超4万行代码的电商微服务项目。