怎么做制作网站的教程,全网关键词云查询,api网站制作,网站充值这么做文章目录一、前言二、批量插入前准备1、插入到数据表的字段2、计算一行字段占用的空间3、在数据里做插入操作的时候#xff0c;整体时间的分配三、批量插入数据测试1、SQL语句的大小限制2、查看服务器上的参数#xff1a;3、计算一次能插入的最大行记录4、测试插入数据比对整体时间的分配三、批量插入数据测试1、SQL语句的大小限制2、查看服务器上的参数3、计算一次能插入的最大行记录4、测试插入数据比对1插入11W条数据按照每次10,600,100020000,80000来测试2加大数据量到24w3加大测试量到42W5、如果插入的值就是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 int(10)我们一共插入四个字段分别是3个int类型的一个varchar类型的整体来说这些字段都比较小占用的内存空间会小一些。
2、计算一行字段占用的空间
对于innodb引擎来说int类型可以存储4个字节里面的Int(M)并不会影响存储字节的大小这个M只是数据的展示位数和mysql的ZEROFILL属性有关即在数字长度不够的数据前面填充0以达到设定的长度。此处不多说想要了解的朋友可以百度一下还是很有意思的。 varchar10代表可以存储10个字符不管是英文还是中文最多都是10个这部分假设存储的是中文在utf-8mb4下10个中文占用10*4 40个字节那么一行数据最多占用44440 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语句的最大限制是1Mmysql5.7的客户端默认是16M服务端默认是4M可以根据设置查看。官方解释是适当增大 max_allowed_packet 参数可以使client端到server端传递大数据时系统能够分配更多的扩展内存来处理。
官方手册https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html2、查看服务器上的参数
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。
3、计算一次能插入的最大行记录
1M计算的话(1024*1024)/52 ≈ 20165 为了防止溢出最大可一次性插入20000条根据自己的配置和sql语句大小计算。那么32M的话就是20000 *32 640000 也就是64W条。
4、测试插入数据比对
1插入11W条数据按照每次10,600,100020000,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的数据刷到磁盘中这时效率会有所下降。所以比较好的做法是在数据达到这个这个值前进行事务提交。
查看 show variables like %innodb_log_buffer_size%;
----------------------------------
| Variable_name | Value |
----------------------------------
| innodb_log_buffer_size | 67108864 |
----------------------------------大概是64M这种写法和批量写入的效果差不多只不过sql语句还是单句的然后统一提交。一个瓶颈是SQL语句的大小一个瓶颈是事务的大小。当我们在提交sql的时候首先是受到sql大小的限制其次是受到事务大小的限制。在开启事务的情况下使用批量插入会节省不少事务的开销如果要追求极致的速度的话建议是开着事务插入的。不过需要注意一下内存是有限且共享的如果批量插入占用太多的事务内存那么势必会对其他的业务操作等有一定的影响。
4、通过配置提升读写性能
也可以通过增大innodb_buffer_pool_size 缓冲区来提升读写性能只是缓冲区是要占用内存空间的内存很珍贵所以这个方案在内存富裕而性能瓶颈的时候可以考虑下。
参考https://my.oschina.net/anuodog/blog/3002941
5、索引影响插入性能
如果表中存在多个字段索引当对表中的数据进行增加、删除和修改的时候索引也要动态的维护。这样就降低了数据的插入速度。对于普通的数据表主键索引是肯定要有的想要加快性能的话就是要有序插入每次插入记录都在索引的最后面索引的定位效率很高并且对索引调整较小。如果插入的记录在索引中间需要Btree进行分裂合并等处理会消耗比较多计算资源并且插入记录的索引定位效率会下降数据量较大时会有频繁的磁盘操作。
五、总结
博主经过测试谷歌最终是选用的一次批量插入数据量为max_allowed_packet大小的一半。只是在不断的搜索中发现影响插入性能的地方挺多的如果仅仅是拿max_allowed_packet这个参数作为分析其实是没有意义的这个参数只是设置最大值但并不是最佳性能。不过需要注意由于sql语句比较大所以才执行完插入操作之后一定要释放变量不要造成无谓的内存损耗影响程序性能。
对于我们的mysql来说也是一样的mysql的最佳性能是建立在各个参数的合理设置上这样协同干活儿的效果最佳。如果其他设置不到位的话就像是木桶原理一样哪怕内存缓冲区设置的很大但是性能取决的反而是设置最差的那个配置。关于mysql的配置调优我们都在路上加油
