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

2023年 9月 25日 84.1k 0

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,可以提升导出导入性能。如果为了避免大事务,那就不建议调整这个参数,使用默认值即可。

  1. [root@192-168-199-198 ~]# mysqldump --net-buffer-length=104652800 -uroot -proot -P3306 -h192.168.199.198 test t >16M.sql

  2. mysqldump: [Warning] option 'net_buffer_length': unsigned value 104652800 adjusted to 16777216

  3. #设置大于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的值。

  1. setglobal max_allowed_packet=16*1024*1024*1024;

不调整的话,会出现以下报错,

  1. [root@192-168-199-198 ~]# mysql -uroot -proot -P3306 -h192.168.199.198 test select version();

  2. +------------+

  3. | version() |

  4. +------------+

  5. | 5.7.26-log |

  6. +------------+

  7. 1 row in set (0.00 sec)

造100万行数据,

  1. create database test;

  2. use test;

  3. CREATE TABLE `t` (

  4. `a` int(11) DEFAULT NULL,

  5. `b` int(11) DEFAULT NULL,

  6. `c` varchar(255) DEFAULT NULL

  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  8. insert into t values (1,1,'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyztuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz');

  9. insert into t select * from t; #重复执行20次

  10. # 直到出现Records: 524288 Duplicates: 0 Warnings: 0

  11. # 说明数据量达到100多万条了。

  12. mysql> select count(*) from t;

  13. +----------+

  14. | count(*) |

  15. +----------+

  16. | 1048576 |

  17. +----------+

  18. 1 row in set (1.04 sec)

数据大小如下,有287MB,

  1. [root@192-168-199-198 test]# pwd

  2. /data/mysql/mysql3306/data/test

  3. [root@192-168-199-198 test]# du -sh t.ibd

  4. 287M t.ibd

--net-buffer-length=1M

  1. [root@192-168-199-198 ~]# mysqldump -uroot -proot -S tmp/mysql3306.sock test t >1M.sql

  2. [root@192-168-199-198 ~]# du -sh 1M.sql

  3. 225M 1M.sql

  4. [root@192-168-199-198 ~]# cat 1M.sql |grep -i insert |wc -l

  5. 226

默认 --net-buffer-length=1M的情况下,225M的SQL文件里有226条insert,平均下来确实就是每条insert的SQL大小为1M。

--net-buffer-length=16M

  1. [root@192-168-199-198 ~]# mysqldump --net-buffer-length=16M -uroot -proot -S tmp/mysql3306.sock test t >16M.sql

  2. [root@192-168-199-198 ~]# du -sh 16M.sql

  3. 225M 16M.sql

  4. [root@192-168-199-198 ~]# cat 16M.sql |grep -i insert |wc -l

  5. 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倍。

  1. [root@192-168-199-198 ~]# time mysql -uroot -proot -S /tmp/mysql3306.sock test

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论