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

太过爱你忘了你带给我的痛 2021-10-15 17:55 623阅读 0赞

### 文章目录 ###

*   *  一、前言
     *  二、批量插入前准备
     *   *  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、索引影响插入性能
     *  五、总结

## 一、前言 ##

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

## 二、批量插入前准备 ##

博主本地原本是循环查出来的数据，然后每`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、查看服务器上的参数： ###

`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`的数据刷到磁盘中，这时，效率会有所下降。所以比较好的做法是，在数据达到这个这个值前进行事务提交。

这种写法和批量写入的效果差不多，只不过`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**