MySQL 批量操作,一次插入多少行数据效率最高?
共 8165字,需浏览 17分钟
·
2023-02-04 18:40
阅读本文大概需要 12 分钟。
来自:blog.csdn.net/LJFPHP/article/details/99708888
一、前言
二、批量插入前准备
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)
2、计算一行字段占用的空间
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%)
三、批量插入数据测试
1、SQL语句的大小限制
my.ini
里有 max_allowed_packet
这个参数控制通信的 packet 大小。mysql默认的sql语句的最大限制是1M(mysql5.7的客户端默认是16M,服务端默认是4M),可以根据设置查看。官方解释是适当增大 max_allowed_packet
参数可以使client端到server端传递大数据时,系统能够分配更多的扩展内存来处理。2、查看服务器上的参数:
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、计算一次能插入的最大行记录
(1024*1024)/52 ≈ 20165
,为了防止溢出,最大可一次性插入20000条(根据自己的配置和sql语句大小计算)。那么32M的话就是:20000 *32 = 640000
也就是64W条。4、测试插入数据比对
(1)插入11W条数据,按照每次10,600,1000,20000,80000
来测试:
+---------------+
| count(c1.uin) |
+---------------+
| 110000 |
+---------------+
11W的数据,每次插入10条。耗时:2.361s
11W的数据,每次插入600条。耗时:0.523s
11W的数据,每次插入1000条。耗时:0.429s
11W的数据,每次插入20000条。耗时:0.426s
11W的数据,每次插入80000条。耗时:0.352s
(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
(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
max_allowed_packet
的一半,也就是极限能插入64W,选用32W也许性能会更好一些,同时也不会对mysql的其他操作产生太大的影响。5、如果插入的值就是sql语句限制的最大值,那么性能真的好吗?
max_allowed_packet
参数。但是需要注意的是,如果查询实在是太大,服务端会拒绝接收更多数据并抛出异常。与之相反的是,服务器响应给用户的数据通常会很多,由多个数据包组成。但是当服务器响应客户端请求时,客户端必须完整的接收整个返回结果,而不能简单的只取前面几条结果,然后让服务器停止发送。因而在实际开发中,尽量保持查询简单且只返回必需的数据,减小通信间数据包的大小和数量是一个非常好的习惯,这也是查询中尽量避免使用SELECT *
以及加上LIMIT
限制的原因之一。四、其他影响插入性能的因素
1、首先是插入的时候,要注意缓冲区的大小使用情况
buffer pool
余量不足25%,插入失败,返回DB_LOCK_TABLE_FULL
。这个错误并不是直接报错:max_allowed_packet
不够大之类的,这个错误是因为对于innodb引擎来说,一次插入是涉及到事务和锁的,在插入索引的时候,要判断缓冲区的剩余情况,所以插入并不能仅仅只考虑max_allowed_packet
的问题,也要考虑到缓冲区的大小。2、插入缓存
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
Insert Buffer
中。Insert Buffer
和非聚簇索引页子节点的合并操作。这时通常能够将多个插入合并到一个操作中,这样就大大提高了对于非聚簇索引的插入性能。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;
innodb_log_buffer_size
配置项,超过这个值会把innodb的数据刷到磁盘中,这时,效率会有所下降。所以比较好的做法是,在数据达到这个这个值前进行事务提交。show variables like '%innodb_log_buffer_size%';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| innodb_log_buffer_size | 67108864 |
+------------------------+----------+
4、通过配置提升读写性能
innodb_buffer_pool_size
缓冲区来提升读写性能,只是缓冲区是要占用内存空间的,内存很珍贵,所以这个方案在内存富裕,而性能瓶颈的时候,可以考虑下。5、索引影响插入性能
五、总结
max_allowed_packet
大小的一半。只是在不断的搜索中,发现影响插入性能的地方挺多的,如果仅仅是拿max_allowed_packet
这个参数作为分析,其实是没有意义的,这个参数只是设置最大值,但并不是最佳性能。推荐阅读:
首次力压 MacOS,Linux 桌面版在2022年杀疯了!
互联网初中高级大厂面试题(9个G) 内容包含Java基础、JavaWeb、MySQL性能优化、JVM、锁、百万并发、消息队列、高性能缓存、反射、Spring全家桶原理、微服务、Zookeeper......等技术栈!
⬇戳阅读原文领取! 朕已阅