关于java:MySQL-批量操作一次插入多少行数据效率最高

咱们在操作大型数据表或者日志文件的时候常常会须要写入数据到数据库，那么最合适的计划就是数据库的批量插入。只是咱们在执行批量操作的时候，一次插入多少数据才适合呢？

如果须要插入的数据有百万条，那么一次批量插入多少条的时候，效率会高一些呢？这里博主和大家一起探讨下这个问题，应用环境为批量插入数据到长期表。

博主本地本来是循环查出来的数据，而后每 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)

咱们一共插入四个字段，别离是 3 个 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 的话，会在链接，解析局部消耗大量的工夫，因而速度会很慢，所以咱们个别都是采纳批量插入的操作，争取在一次链接外面写入尽可能多的数据，以此来晋升插入的速度。然而这个尽可能多的数据是多少呢？一次到底插入多少才适合呢？

开始测试，然而一开始插入多少是适合的呢，是否有下限？查问 mysql 手册，咱们晓得 sql 语句是有大小限度的。

my.ini 里有 max_allowed_packet 这个参数管制通信的 packet 大小。mysql 默认的 sql 语句的最大限度是 1M（mysql5.7 的客户端默认是 16M，服务端默认是 4M），能够依据设置查看。官网解释是适当增大 max_allowed_packet 参数能够使 client 端到 server 端传递大数据时，零碎可能调配更多的扩大内存来解决。

官网手册：https://dev.mysql.com/doc/ref…

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，为了避免溢出，最大可一次性插入 20000 条（依据本人的配置和 sql 语句大小计算）。那么 32M 的话就是：20000 *32 = 640000 也就是 64W 条。

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

+---------------+
| 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 并不是极限数据，具体一次插入多少条，还有待参考。

（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 的其余操作产生太大的影响。

博主疯狂谷歌百度，都没有找到有人来具体的说一下这个问题，不过在高性能 mysql 外面发现一句话：

客户端用一个独自的数据包将查问申请发送给服务器，所以当查问语句很长的时候，须要设置 max_allowed_packet 参数。然而须要留神的是，如果查问切实是太大，服务端会回绝接管更多数据并抛出异样。与之相同的是，服务器响应给用户的数据通常会很多，由多个数据包组成。然而当服务器响应客户端申请时，客户端必须残缺的接管整个返回后果，而不能简略的只取后面几条后果，而后让服务器进行发送。因此在理论开发中，尽量放弃查问简略且只返回必须的数据，减小通信间数据包的大小和数量是一个十分好的习惯，这也是查问中尽量避免应用 SELECT * 以及加上 LIMIT 限度的起因之一。

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

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

另外对于 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 和非聚簇索引页子节点的合并操作。这时通常可能将多个插入合并到一个操作中，这样就大大提高了对于非聚簇索引的插入性能。

还有一种说法，应用事务能够进步数据的插入效率，这是因为进行一个 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 的数据刷到磁盘中，这时，效率会有所降落。所以比拟好的做法是，在数据达到这个这个值前进行事务提交。

查看：show variables like '%innodb_log_buffer_size%';

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

大略是：64M

这种写法和批量写入的成果差不多，只不过 sql 语句还是单句的，而后对立提交。一个瓶颈是 SQL 语句的大小，一个瓶颈是事务的大小。当咱们在提交 sql 的时候，首先是受到 sql 大小的限度，其次是受到事务大小的限度。在开启事务的状况下应用批量插入，会节俭不少事务的开销，如果要谋求极致的速度的话，倡议是开着事务插入的。

不过须要留神一下，内存是无限且共享的，如果批量插入占用太多的事务内存，那么势必会对其余的业务操作等有肯定的影响。

也能够通过增大innodb_buffer_pool_size 缓冲区来晋升读写性能，只是缓冲区是要占用内存空间的，内存很宝贵，所以这个计划在内存富裕，而性能瓶颈的时候，能够思考下。

如果表中存在多个字段索引，当对表中的数据进行减少、删除和批改的时候，索引也要动静的保护。这样就升高了数据的插入速度。对于一般的数据表，主键索引是必定要有的，想要放慢性能的话，就是要有序插入，每次插入记录都在索引的最初面，索引的定位效率很高，并且对索引调整较小。如果插入的记录在索引两头，须要 B +tree 进行决裂合并等解决，会耗费比拟多计算资源，并且插入记录的索引定位效率会降落，数据量较大时会有频繁的磁盘操作。

博主通过测试 + 谷歌，最终是选用的一次批量插入数据量为 max_allowed_packet 大小的一半。只是在一直的搜寻中，发现影响插入性能的中央挺多的，如果仅仅是拿 max_allowed_packet 这个参数作为剖析，其实是没有意义的，这个参数只是设置最大值，但并不是最佳性能。

不过须要留神，因为 sql 语句比拟大，所以才执行完插入操作之后，肯定要开释变量，不要造成无谓的内存损耗，影响程序性能。

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

版权申明：本文为 CSDN 博主「铁柱同学」的原创文章，遵循 CC 4.0 BY-SA 版权协定，转载请附上原文出处链接及本申明。原文链接：https://blog.csdn.net/LJFPHP/…

参考：

参考：mysql 技术底细 Innodb 篇
https://www.cnblogs.com/aicro…
https://my.oschina.net/songho…
https://my.oschina.net/anuodo…
http://mysql.taobao.org/month…
https://cloud.tencent.com/dev…

近期热文举荐：

1.1,000+ 道 Java 面试题及答案整顿(2022 最新版)

2. 劲爆！Java 协程要来了。。。

3.Spring Boot 2.x 教程，太全了！

4. 别再写满屏的爆爆爆炸类了，试试装璜器模式，这才是优雅的形式！！

5.《Java 开发手册（嵩山版）》最新公布，速速下载！

感觉不错，别忘了顺手点赞 + 转发哦！

关于java:MySQL-批量操作一次插入多少行数据效率最高

一、前言

二、批量插入前筹备

1、插入到数据表的字段

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

3、在数据里做插入操作的时候，整体工夫的调配

三、批量插入数据测试

1、SQL 语句的大小限度

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

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

4、测试插入数据比对

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

（2）加大数据量到 24w

（3）加大测试量到 42W

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

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

1、首先是插入的时候，要留神缓冲区的大小应用状况

2、插入缓存

3、应用事务晋升效率

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

5、索引影响插入性能

五、总结

Just My Socks（注册教程内含优惠码）

关于java:MySQL-批量操作一次插入多少行数据效率最高

一、前言

二、批量插入前筹备

1、插入到数据表的字段

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

3、在数据里做插入操作的时候，整体工夫的调配

三、批量插入数据测试

1、SQL 语句的大小限度

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

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

4、测试插入数据比对

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

（2）加大数据量到 24w

（3）加大测试量到 42W

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

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

1、首先是插入的时候，要留神缓冲区的大小应用状况

2、插入缓存

3、应用事务晋升效率

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

5、索引影响插入性能

五、总结

Just My Socks（注册教程 内含优惠码）

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

Just My Socks（注册教程内含优惠码）