关于mysql批量插入多少条数据最佳探索 - 图1参考博客

批量删除前的准备

首先查看mysql的版本

  1. mysql> select version();
  3. +------------+
  4. | version()  |
  5. +------------+
  6. | 5.7.0      |
  7. +------------+
  8. 1 row in set (0.00 sec)

计算插入至数据库的字段空间

假设插入的字段信息是：

  1. 字段1 int(10) -- 4个字节， 10只是代表数据展示的位数，和mysql的zerofill属性有关
  2. 字段2 int(10) -- 4个字节
  3. 字段3 int(10) -- 4个字节
  4. 字段4 varchar(10) -- 10个字符，在utf-8mb4中，一个中文字符占4个字节，所以共占用40个字节
  6. 计算可得，一行数据里面，字节占用的空间有： 4+4+4+10*4 = 52 字节

在数据里做插入操作的是哦湖，整体的时间分配

  1. 链接耗时-- 30%
  2. 发送query到服务器 -- 20%
  3. 解析query -- 20%
  4. 插入操作 -- 10% * 词条数目
  5. 插入index -- 10% * index的数目
  6. 关闭链接 -- 10%

由此可见，真正耗时的不是操作，而是链接和解析的过程。 单条的sql的话，会在链接，解析部分耗费大量的时间，因此速度很慢。所以在进行大量的数据插入操作的时候，一般采用批量插入的操作，争取在一次链接里写入尽可能多的数据，以此来提升插入的速度。

批量插入数据测试

1、SQL语句的大小限制

my.ini 里的 max_allowed_packet 这个参数控制通信的 packet 大小。
mysql5.7 的客户端默认是 16M , 服务端默认是 4M

官方解释适当增大 max_allowed_packet 参数可以使 client 端到 server 端传递大数据的时候，系统能够分配更多的扩展内存来处理。 官方手册

2、查看服务器上的参数

  1. mysql> show variables like '%max_allowed_packet%';
  2. +--------------------------+------------+
  3. | Variable_name            | Value      |
  4. +--------------------------+------------+
  5. | max_allowed_packet       | 33554432   |
  6. | slave_max_allowed_packet | 1073741824 |
  7. +--------------------------+------------+
  8. 2 rows in set (0.00 sec)
  10. 33554432字节 = 32M

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

每条数据占 52( 4+4+4+40) 字节， 1M 的话可以大概容纳 1024*1024/52≈20165 ，避免溢出，最大可一次性插入 20000 条数据。（上面的统计要根据自己插入的sql语句大小进行计算）。
所以， 32M 的上限的话，最大的行记录就有 32 * 20000 = 64W 条。

4、测试插入数据比对

（此部分暂时省略）

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

《高性能mysql》 客户端用一个单独的数据包将查询请求发送给服务端，所以当查询语句很长的时候，需要设置 max_allowed_packet 参数。但是需要注意的是，如果查询实在是太大了，服务端会拒绝接收更多数据并抛出异常。 与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务端响应客户端请求时，客户端必须完整的接收整个返回结果，而不是简单的只取前面几条结果，然后才能让服务端停止发送。

因此在实际开发过程中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用 select * 以及加上 limit 限制的原因之一。

其他影响插入性能的因素

在插入的时候，注意缓冲区的大小使用情况

buffer_pool 的大小
对innodb 引擎来说，一次插入是涉及到事务和锁的，在插入索引的时候，要判断缓冲区的剩余情况，所以插入的时候并不能仅仅只考虑 max_allowed_packet 的问题，也要考虑到缓冲区的大小。

插入缓存

innodb 引擎存在插入缓存 Insert Buffer 的概念，所以在插入的时候也是要耗费一定的缓冲池内存的。当写密集的情况下，插入缓存会占用过多的缓冲池内存，默认最大可以占用1/2的缓冲池内存，当插入缓冲占用太多缓冲池内存的情况下，会影响到其他的操作。
查看缓冲池大小命令:

  1. mysql> show variables like 'innodb_buffer_pool_size';
  2. mysql> show variables like 'innodb_buffer_pool_size';
  3. +-------------------------+-----------+
  4. | Variable_name           | Value     |
  5. +-------------------------+-----------+
  6. | innodb_buffer_pool_size | 134217728 |
  7. +-------------------------+-----------+
  9. 1342117728字节 = 128M

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

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

使用事务提升效率

写法

  1. START TRANSACTION;
  2. INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  3.     VALUES ('0', 'userid_0', 'content_0', 0);
  4. INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  5.     VALUES ('1', 'userid_1', 'content_1', 1);
  6. ...
  7. COMMIT;

进行一个 INSERT 操作时， MySQL 内部会建立一个事务，在事务内才进行真正插入处理操作。通过使用事务可以减少插入事务的小猴，所有插入都在执行后才进行提交操作。
事务需要控制大小，事务太大可能会影响执行效率。 MySQL 有 innodb_log_buffer_size 的配置项，超过这个值会把 innodb 的数据刷到磁盘中，这时，效率就会有所下降。所以比较好的做法是，在数据达到这个值前进行事务的提交。

  1. mysql> show variables '%innodb_log_buffer_size%';
  2. +------------------------+----------+
  3. | Variable_name          | Value    |
  4. +------------------------+----------+
  5. | innodb_log_buffer_size | 67108864 |
  6. +------------------------+----------+
  8. 大概是：64M

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

通过配置提升读写性能

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

索引影响插入性能

如果表中存在多个字段索引，当对表中的数据进行增加、删除和修改的时候，索引也要动态维护。这样就降低了数据的插入速度。插入的数据可能会有索引间的位置调整和页分裂的情况，此时会消耗较多的计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘操作。