MySQL 如何批量插入数据较佳

一、探讨
我们在操作大型数据表或者日志文件的时候经常会需要写入数据到数据库，那么合适的方案就是数据库的批量插入。只是我们在执行批量操作的时候，一次插入多少数据才合适呢？假如需要插入的数据有百万条，那么一次批量插入多少条的时候，效率会高一些呢？这里博主和大家一起探讨下这个问题，应用环境为批量插入数据到临时表。
二、批量插入前准备
本地原本是循环查出来的数据，然后每1000条插入一次，直至完成插入操作，为什么要设置1000条呢，实不相瞒，这是因为项目里的其他批量插入都是一次插1000条。
1：插入到数据表的字段
对于手动创建的临时表来说，字段当然是越少越好，而且字段占用的空间要尽量小一些，这样临时表不至于太大，影响表操作的性能。
2：计算一行字段占用的空间
对于innodb引擎来说，int类型可以存储4个字节，里面的Int
M
并不会影响存储字节的大小，这个M只是数据的展示位数，和mysql的ZEROFILL属性有关，即在数字长度不够的数据前面填充0，以达到设定的长度。此处不多说，想要了解的朋友可以百度一下，还是很有意思的。
3：在数据里做插入操作的时候，整体时间的分配
真正耗时的不是操作，而是链接，解析的过程。单条sql的话，会在链接，解析部分耗费大量的时间，因此速度会很慢，所以我们一般都是采用批量插入的操作，争取在一次链接里面写入尽可能多的数据，以此来提升插入的速度。但是这个尽可能多的数据是多少呢？一次到底插入多少才合适呢？
三、批量插入数据测试
查询mysqL手册，我们知道sql语句是有大小限制的。
1：SQL语句的大小限制
m***i里有max_allowed_packet这个参数控制通信的packet大小。mysql默认的sql语句的大限制是1M（mysql5.7的客户端默认是16M，服务端默认是4M），可以根据设置查看。解释是适当增大max_allowed_packet参数可以使client端到server端传递大数据时，系统能够分配更多的扩展内存来处理。
2：查看服务器上的参数
33554432字节 = 32M ，也就是规定大小不能超过32M。
3：计算一次能插入的大行记录
1M计算的话，
1024*1024
/52 ≈ 20165，为了防止溢出，大可一次性插入20000条（根据自己的配置和sql语句大小计算）。那么32M的话就是：20000 *32 = 640000也就是64W条。
4：测试插入数据比对
随着插入量的增加，批量插入条数多了之后，性能是有所提升的。但是在达到30W以上之后，效率反而有所下降。这部分我的理解是mysql是要分配一定的内存给传过来的数据包使用，当批量插入的数据量到达一定程度之后，一次插入操作的开销就很耗费内存了。
5：如果插入的值就是sql语句限制的大值，那么性能真的好吗？
客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。
四、其他影响插入性能的因素
1：首先是插入的时候，要注意缓冲区的大小使用情况
在分析源码的过程中，有一句话：如果buffer pool余量不足25%，插入失败，返回DB_LOCK_TABLE_FULL。这个错误并不是直接报错：max_allowed_packet不够大之类的，这个错误是因为对于innodb引擎来说，一次插入是涉及到事务和锁的，在插入索引的时候，要判断缓冲区的剩余情况，所以插入并不能仅仅只考虑max_allowed_packet的问题，也要考虑到缓冲区的大小。
2：插入缓存
另外对于innodb引擎来说，因为存在插入缓存（Insert Buffer）这个概念，所以在插入的时候也是要耗费一定的缓冲池内存的。
3：使用事务提升效率
还有一种说法，使用事务可以提高数据的插入效率，这是因为进行一个INSERT操作时，MySQL内部会建立一个事务，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交操作。
4：通过配置提升读写性能
也可以通过增大innodb_buffer_pool_size缓冲区来提升读写性能，只是缓冲区是要占用内存空间的，内存很珍贵，所以这个方案在内存富裕，而性能瓶颈的时候，可以考虑下。
5：索引影响插入性能
如果表中存在多个字段索引，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护。这样就降低了数据的插入速度。