1.前言我们在操作大型数据表或者日志文件的时候，经常需要往数据库中写入数据，那么最适合的方案就是批量插入数据库。只是我们在进行批量操作的时候，一次插入多少数据才合适呢？如果要插入几百万条数据，一次批量插入多少条，效率会更高吗？在这里博主和大家一起讨论一下这个问题。应用环境是批量向临时表插入数据。2.批量插入前准备。博主的本地数据本来是循环签出的，然后每1000条插入一次，直到插入操作完成。但是为什么要设置1000个条目呢？说实话，这是因为项目中的其他批量插入一次插入了1000条。.汗，博主不服气，所以想测试一下。首先是检查当前数据库的版本。毕竟，版本之间存在差异。谈数据库没有版本就跟耍流氓一样（以前花样很多）：mysql>selectversion();+------------+|版本()|+------------+|5.6.34-log|+------------+1rowinset(0.00sec)1,插入数据表的字段对于手动创建的临时表，当然是字段越少越好,并且字段占用的空间尽量小，这样临时表才不会太大而影响表操作的性能。这里需要插入的字段有：Field1int(10)Field2int(10)Field3int(10)Field4varchar(10)我们一共插入4个字段，分别是3个int类型和1个varchar类型，一般来说这些字段比较小，占用的内存空间也会小一些。2.计算一行字段占用的空间对于innodb引擎来说，int类型可以存储4个字节，里面的Int(M)不会影响存储字节的大小。这个M只是数据显示位数，和mysql的ZEROFILL属性有关，即在长度不够达到设定长度的数据前面补0。这里就不多说了，想了解的朋友可以百度一下，还是很有意思的。varchar(10)表示可以存储10个字符，不管是英文还是中文，最大都是10个，这部分假设存储的是中文，在utf-8mb4下，10个中文占10*4=40字节那么一行data最多占用：4+4+4+40=52bytes3.插入数据时，整体时间分配链路花费时间（30%）将查询发送给服务器（20%）解析查询（20%)%)插入操作(10%*条目数)插入索引(10%*索引数)关闭链接(10%)从这里可以看出真正耗时的不是操作，而是链接和解析的过程。对于单个sql，在链接和解析部分会耗费大量时间，所以速度会很慢，所以我们一般采用批量插入操作，尽量在一个链接中写入尽可能多的数据，以提高插入速度.但是，尽可能多的数据是多少？一次插入多少合适？3.批量插入数据测试开始测试，但是一开始插入多少合适，有上限吗？查看mysql手册，我们知道sql语句的大小是有限制的。1.SQL语句的大小限制my.ini中有一个max_allowed_pa??cket参数来控制通信的数据包大小。mysqlsql语句默认最大限制为1M（mysql5.7客户端默认16M，服务器默认4M），可以根据设置勾选。官方的解释是适当增加max_allowed_pa??cket参数可以让系统在客户端向服务端传输大数据时，分配更多的扩展内存用于处理。官方手册：https://dev.mysql.com/doc/ref...2.查看服务器上的参数：mysql>showvariableslike'%max_allowed_pa??cket%';+---------------------------+------------+|变量名|值|+--------------------------+------------+|最大允许数据包|33554432||slave_max_allowed_pa??cket|1073741824|+----------------------+------------+2行集合（0.00秒）33554432字节=32M，即指定大小不能超过32M。3.计算一次可以插入的最大行记录。如果计算1M，则为(1024*1024)/52≈20165。为了防止溢出，一次最多可以插入20000条记录（根据自己的配置和SQL语句的大小计算）。那么32M就是：20000*32=640000，也就是64W。4、测试插入数据的对比（1）插入11W条数据，每次按照10,600,1000,20000,80000测试：+----------------+|计数（c1.uin）|+----------------+|110000|+----------------+有博客说一次插入10条最快，，我觉得一次插入有点小。我们来试试这位博主的测试，认为一次插入10条记录是最快的性能。他的每条记录都是3kb，相当于我的59行数据。取一个整数60，然后为这个博主插入10个条目，为我插入：600，尝试所有这些值。耗时：11W数据，每次插入10条记录。耗时：2.361s11W数据，每次插入600条。耗时：0.523s11W数据，每次插入1000条。耗时：0.429s11W数据，每次插入20000条记录。耗时：0.426s11W数据，每次插入8万条记录。耗时：0.352s从这个角度来看，随着批量插入的增加，速度略有提升，至少一次插入10个应该不是最优的。插入的数据量大，减少了循环次数，也就是减少了数据库链接耗时的部分，但是这个8W并不是极限数据，具体插入多少数据在一个时间还有待参考。（2）增加数据量到24w+----------------+|计数（c1.uin）|+----------------+|241397|+----------------+耗时：24W数据，每次插入10条记录。耗时：4.445s24W数据，每次插入600条。耗时：1.187s24W数据，每次插入1000条。耗时：1.13s24W数据，每次插入20000条记录。耗时：0.933s24W数据，每次插入8万条记录。耗时：0.753s一次插上24W，但是性能是最好的，也就是说我们的测试数据量还是不够用。(3)将测试音量增加到42W+----------------+|计数（c1.uin）|+----------------+|418859|耗时：42W数据，每次插入1000条记录。耗时：2.216s42W数据，每次插入8万条记录。耗时：1.777s42W数据，每次插入16W。耗时：1.523s42W数据，每次插入20W。耗时：1.432s42W数据，每次插入30W。耗时：1.362s42W数据，每次插入40W。耗时：1.764s随着插入量的增加，批量插入次数增加后性能有所提升。但达到30W以上后，效率有所下降。这部分我的理解是mysql需要为传输的数据包分配一定的内存。当批量插入的数据量达到一定程度时，一次插入操作的开销会消耗大量的内存。个人觉得最合适的大小是max_allowed_pa??cket的一半，也就是限制可以插入到64W。选择32W可能会有更好的性能，对mysql的其他操作不会有太大影响。5、如果插入的值是SQL语句限制的最大值，性能真的好吗？博主疯狂谷歌百度，找不到人详细说说这个问题，但是在高性能的mysql中找到了一句话：clientsendsthequeryrequesttotheserverwithaseparatedatapacket，所以当查询语句很长时，需要设置max_allowed_pa??cket参数。但需要注意的是，如果查询过大，服务器会拒绝接收更多的数据并抛出异常。相反，服务器响应给用户的数据通常很多，由多个数据包组成。但是当服务器响应客户端的请求时，客户端必须接收到整个返回的结果，而不是简单的取前几个结果，然后让服务器停止发送。因此，在实际开发中，尽量保持查询简单，只返回必要的数据，减少通信数据包的大小和数量，是一个很好的习惯。这也是查询中要尽量避免SELECT*和LIMIT的原因。一。后来通过各种百度，博主觉得maximum只代表传输的数据包的最大长度，但是性能是否最好还要从多方面去分析。例如下面列出的插入缓冲区，插入索引时对缓冲区的剩余空间要求，事务占用的内存，都会影响批量插入的性能。四、其他影响插入性能的因素1、首先，插入时要注意缓冲区的大小和使用情况。在分析源码的过程中，有一句话说：如果bufferpoolbalance小于25%，插入失败，返回DB_LOCK_TABLE_FULL。此错误不是直接错误：max_allowed_pa??cket不够大。这个错误是因为对于innodb引擎来说，一次插入涉及到事务和锁。插入索引时，需要判断buffer的剩余情况，所以insert不仅仅与max_allowed_pa??cket有关，还与buffer的大小有关。2、插入缓存另外，对于innodb引擎来说，因为有插入缓冲区（InsertBuffer）的概念，所以在插入的时候也会消耗一定的缓冲池内存。在密集写入的情况下，插入缓冲区会占用过多的缓冲池内存。默认情况下，它最多可以占用缓冲池内存的1/2。当插入缓冲区占用缓冲池内存过多时，会影响其他操作。.也就是说，insertbuffering受bufferpool大小的影响，即：mysql>showvariableslike'innodb_buffer_pool_size';+------------------------+------------+|变量名|值|+------------------------+----------+|innodb_buffer_pool_size|134217728|+------------------------+-----------+转换后的结果为：128M，即比如说，插入缓存最多可以占用64M的缓冲区大小。这个大小超过了我们设置的sql语句的大小，所以可以忽略。详解：我们都知道在InnoDB引擎上插入时，一般需要按照主键的顺序插入，这样才能获得较高的插入性能。当表中存在非聚集非唯一索引时，插入时，数据页的存储仍然是按照主键顺序存储，但非聚集索引叶子节点的插入不再是有序的.此时，需要离散访问非聚集索引页，由于随机读的存在导致插入操作性能下降。InnoDB为插入优化设计了InsertBuffer。对于非聚集索引的插入或更新操作，并不是每次都直接插入到索引页中，而是先判断插入的非聚集索引是否在缓冲池中，如果在，则直接插入；如果不是，则先将其放入InsertBuffer。好像是数据库的非聚集索引找到了叶子节点，其实并没有，存放在另外一个位置。然后以一定的频率和情况对InsertBuffer和非聚集索引页的子节点进行merge操作。这时，通常可以将多个插入合并为一个操作，这大大提高了非聚集索引的插入性能。3、使用事务提高效率还有一种说法是使用事务可以提高数据插入的效率。这是因为当执行INSERT操作时，MySQL会在内部创建一个事务，而真正的插入处理操作是在事务内部进行的。使用事务可以减少创建事务的消耗，所有insert都在commit前执行。大致如下：STARTTRANSACTION；INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('0','userid_0','content_0',0);INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('1','userid_1','content_1',1);...COMMIT;需要控制交易的大小，交易的大小可能会影响执行效率。MySQL有innodb_log_buffer_size配置项。如果超过这个值，innodb的数据会被刷到磁盘。这时候效率就会下降。因此，最好在数据达到这个值之前提交事务。View:showvariableslike'%innodb_log_buffer_size%';+------------------------+----------+|变量名|值|+------------------------+---------+|innodb_log_buffer_size|67108864|+----------------------+------------+大概：64M类似于批量写入，除了forsql语句还是单句，然后统一提交。一个瓶颈是SQL语句的大小，一个瓶颈是事务的大小。我们在提交sql的时候，首先受限于sql的大小，其次受限于事务的大小。在启用事务时使用批量插入将节省大量事务开销。如果想追求极致的速度，建议在事务打开的情况下插入。但是需要注意的是，内存是有限的，是共享的。如果批量插入占用过多的事务内存，势必会对其他业务操作造成一定的影响。4、通过配置提高读写性能也可以通过增加innodb_buffer_pool_sizebuffer来提高读写性能，但是buffer占用内存空间，非常宝贵，所以当内存充裕但性能瓶颈时可以使用该方案考虑。5、索引影响插入性能如果表中有多个字段索引，在对表进行增删改查数据时，也必须动态维护索引。这会减慢数据插入速度。对于普通的数据表，主键索引是必须要有的。如果要加快性能，需要按顺序插入。每个插入记录都在索引的末尾。指数的定位效率很高，指数的调整幅度很小。.如果插入的记录在索引的中间，B+tree需要进行拆分和合并，会消耗更多的计算资源，插入记录的索引定位效率会下降，并且在插入记录的时候会频繁的磁盘操作数据量大。5.总结博主测试+谷歌，最终选择了批量插入数据量为max_allowed_pa??cket一半大小。只是在不断的查找中，发现影响插入性能的地方有很多。如果仅仅把参数max_allowed_pa??cket作为分析的话，其实是没有意义的。这个参数只设置了最大值，并不是最好的性能。但是需要注意的是，由于sql语句比较大，执行完insert操作后一定要释放变量，以免造成不必要的内存丢失，影响程序性能。我们的mysql也是如此。mysql最好的性能是建立在各种参数的合理设置的基础上的，这样协同工作的效果才是最好的。如果其他设置不到位，就像木桶原理一样。即使将内存缓冲区设置为较大的大小，性能也取决于设置最差的配置。关于mysql的配置调优，我们都在路上，加油！版权声明：本文为CSDN博主“铁柱同学”原创文章，遵循CC4.0BY-SA版权协议。转载请附上原文出处链接及本声明。
