mysqldump导出的SQL事务大小可以控制么？

MySQL的mysqldump是我们常用的一个数据导出工具，他可以跟着很多类型的参数，实现不同的需求，技术社群的这篇文章《技术分享 | 控制mysqldump导出的SQL文件的事务大小》可以了解下事务相关的配置。

背景

有人问mysqldump出来的insert语句，是否可以按每 10 row 一条insert语句的形式组织。

表示使用长INSERT，多row在合并一起批量INSERT，提高导入效率。

均不满足群友需求，无法控制按每10 row一条insert语句的形式组织。

2. 那么mysqldump出来的insert语句可能是大事务吗？

• 定义：运行时间比较长，操作的数据比较多的事务我们称之为大事务。

• 大事务风险，

  ∘ 锁定太多的数据，造成大量的阻塞和锁超时，回滚所需要的时间比较长。

  ∘ 执行时间长，容易造成主从延迟。

  ∘ undo log膨胀

• 避免大事务：按公司实际场景，规定了，每次操作/获取数据量应该少于5000条，结果集应该小于2M。

前提，MySQL默认是自提交的，所以如果没有明确地开启事务，一条SQL语句就是一条事务。在mysqldump里，就是一条SQL语句为一条事务。

[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也得增大这个值，测试结论是不需要的。怀疑官方文档有误。

setglobal max_allowed_packet=16*1024*1024*1024;

[root@192-168-199-198 ~]# mysql -uroot -proot -P3306 -h192.168.199.198 test <16M.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2006 (HY000) at line 46: MySQL server has gone away

相关测试

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.00 sec)

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)

[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

[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=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

[root@192-168-199-198 ~]# time mysql -uroot -proot -S /tmp/mysql3306.sock test <16K.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

real    0m10.911s  #11秒
user    0m1.273s
sys    0m0.677s
[root@192-168-199-198 ~]# mysql -uroot -proot -S /tmp/mysql3306.sock -e "reset master";
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@192-168-199-198 ~]# time mysql -uroot -proot -S /tmp/mysql3306.sock test <16M.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

real    0m8.083s  #8秒
user    0m1.669s
sys    0m0.066s

如果您认为这篇文章有些帮助，还请不吝点下文章末尾的"点赞"和"在看"，或者直接转发pyq，