MySQL的mysqldump是我们常用的一个数据导出工具,他可以跟着很多类型的参数,实现不同的需求,技术社群的这篇文章《技术分享 | 控制mysqldump导出的SQL文件的事务大小》可以了解下事务相关的配置。
背景
有人问mysqldump出来的insert语句,是否可以按每 10 row 一条insert语句的形式组织。
思考1:参数--extended-insert回忆过去所学,我只知道有一对参数,--extended-insert(默认值)
表示使用长INSERT,多row在合并一起批量INSERT,提高导入效率。
--skip-extended-insert一行一个的短INSERT
均不满足群友需求,无法控制按每10 row一条insert语句的形式组织。
思考2:“避免大事务”之前一直没有考虑过这个问题。这个问题的提出,相信主要是为了“避免大事务”。所以满足insert均为小事务即可。下面,我们来探讨一下以下问题,1. 什么是大事务?
2. 那么mysqldump出来的insert语句可能是大事务吗?
什么是大事务?
-
定义:运行时间比较长,操作的数据比较多的事务我们称之为大事务。
-
大事务风险,
∘ 锁定太多的数据,造成大量的阻塞和锁超时,回滚所需要的时间比较长。
∘ 执行时间长,容易造成主从延迟。
∘ undo log膨胀
- 避免大事务:按公司实际场景,规定了,每次操作/获取数据量应该少于5000条,结果集应该小于2M。
mysqldump出来的SQL文件有大事务吗?
前提,MySQL默认是自提交的,所以如果没有明确地开启事务,一条SQL语句就是一条事务。在mysqldump里,就是一条SQL语句为一条事务。
按照“避免大事务”自定义规定,答案是没有的。原来,mysqldump会按照参数--net-buffer-length,来自动切分SQL语句。默认值是1M。按照我们前面定义的标准,没有达到我们的 2M 的大事务标准。--net-buffer-length最大可设置为16777216,人手设置大于这个值,会自动调整为16777216,即16M。设置16M,可以提升导出导入性能。如果为了避免大事务,那就不建议调整这个参数,使用默认值即可。
-
[root@192-168-199-198 ~]# mysqldump --net-buffer-length=104652800 -uroot -proot -P3306 -h192.168.199.198 test t >16M.sql
-
mysqldump: [Warning] option 'net_buffer_length': unsigned value 104652800 adjusted to 16777216
-
#设置大于16M,参数被自动调整为16M
注意,指的是mysqldump的参数,而不是mysqld的参数。官方文档提到: If you increase this variable, ensure that the MySQL server net_buffer_length system variable has a value at least this large.
意思是mysqldump增大这个值,mysqld也得增大这个值,测试结论是不需要的。怀疑官方文档有误。
不过,在导入的时候,受到服务器参数max_allowed_packet影响,它控制了服务器能接受的数据包的最大大小,默认值是4194304,即4M。所以导入数据库时需要调整参数max_allowed_packet的值。
-
setglobal max_allowed_packet=16*1024*1024*1024;
不调整的话,会出现以下报错,
-
[root@192-168-199-198 ~]# mysql -uroot -proot -P3306 -h192.168.199.198 test select version();
-
+------------+
-
| version() |
-
+------------+
-
| 5.7.26-log |
-
+------------+
-
1 row in set (0.00 sec)
造100万行数据,
-
create database test;
-
use test;
-
CREATE TABLE `t` (
-
`a` int(11) DEFAULT NULL,
-
`b` int(11) DEFAULT NULL,
-
`c` varchar(255) DEFAULT NULL
-
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-
insert into t values (1,1,'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyztuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz');
-
insert into t select * from t; #重复执行20次
-
# 直到出现Records: 524288 Duplicates: 0 Warnings: 0
-
# 说明数据量达到100多万条了。
-
mysql> select count(*) from t;
-
+----------+
-
| count(*) |
-
+----------+
-
| 1048576 |
-
+----------+
-
1 row in set (1.04 sec)
数据大小如下,有287MB,
-
[root@192-168-199-198 test]# pwd
-
/data/mysql/mysql3306/data/test
-
[root@192-168-199-198 test]# du -sh t.ibd
-
287M t.ibd
--net-buffer-length=1M
-
[root@192-168-199-198 ~]# mysqldump -uroot -proot -S tmp/mysql3306.sock test t >1M.sql
-
[root@192-168-199-198 ~]# du -sh 1M.sql
-
225M 1M.sql
-
[root@192-168-199-198 ~]# cat 1M.sql |grep -i insert |wc -l
-
226
默认 --net-buffer-length=1M的情况下,225M的SQL文件里有226条insert,平均下来确实就是每条insert的SQL大小为1M。
--net-buffer-length=16M
-
[root@192-168-199-198 ~]# mysqldump --net-buffer-length=16M -uroot -proot -S tmp/mysql3306.sock test t >16M.sql
-
[root@192-168-199-198 ~]# du -sh 16M.sql
-
225M 16M.sql
-
[root@192-168-199-198 ~]# cat 16M.sql |grep -i insert |wc -l
-
15
默认--net-buffer-length=16M 的情况下,225M的SQL文件里有15条insert,平均下来确实就是每条insert的SQL大小为16M。所以,这里证明了--net-buffer-length确实可用于拆分mysqldump备份文件的SQL大小的。性能测试insert次数越多,交互次数就越多,性能越低。但鉴于上面例子的insert数量差距不大,只有16倍,性能差距不会很大(实际测试也是如此)。我们直接对比--net-buffer-length=16K和--net-buffer-length=16M的情况,他们insert次数相差了1024倍。
-
[root@192-168-199-198 ~]# time mysql -uroot -proot -S /tmp/mysql3306.sock test