mysql处理大数据量 MySQL批量操作，每次插入多少行数据效率最高？

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

谁关心第一波或第二波？

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

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

源代码精品专栏

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

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

基于Spring Boot + MyBatis Plus + Vue＆Element的后端管理系统+用户小程序，支持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类型，一个是varchar类型，一般来说这几个字段比较小，占用的内存空间也比较少。

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

varchar(10) 表示可以存储10个字符，无论英文还是中文，最大都是10个。这部分假设存储的是中文。在utf-8mb4下，10个中文字符占用10*4 = 40个字节。所以一行数据最大占用：4+4+4+40 = 52个字节

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

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

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

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

my.ini 中有个参数叫 max_allowed_pa​​cket，控制通讯的数据包大小。MySQL 默认的 sql 语句最大限制是 1M（MySQL 5.7 客户端默认是 16M，服务端默认是 4M），可以在设置里查看。官方的解释是，适当调大 max_allowed_pa​​cket 参数可以让系统在从客户端向服务端传输大数据时，分配更多的扩展内存来处理。

官方手册：

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。

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

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

有个博客说一次插入10个是最快的，我觉得一次插入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需要为传输的数据包分配一定的内存，当批量插入量达到一定程度时，一次插入操作的开销就会消耗大量的内存。

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

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

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

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

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

参考淘宝的数据库日报：

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

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

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

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

详细解释：

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

InnoDB为了优化插入设计了Insert Buffer，对于非聚集索引的插入或者更新操作，并不是每次都直接插入到索引页中，而是先检查插入的非聚集索引是否在缓冲池中，如果在，则直接插入；如果没有，则先放入一个Insert Buffer中。

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

参考：

参考：MySQL技术内幕Innodb

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

参考：

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

查看：显示类似'％innodb_log_buffer_size％'的变量；

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

大约：64M

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

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

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

参考：

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

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

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

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

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

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

近期更新的《云道SpringBoot 2.X入门》系列共有101余篇文章，涵盖了MyBatis、Redis、MongoDB、ES、分片、读写分离、SpringMVC、Webflux、权限、WebSocket、Dubbo、RabbitMQ、RocketMQ、Kafka、性能测试等等。

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