MySQL批量导入数据时,为何表空间膨胀了N倍

数据库 MySQL
同事在客户现场利用DTS工具,从A实例将数据迁移到B实例过程中,发现几乎稍大点的表在迁移完成后,目标端表空间大小差不多都是源端的3倍,也就是说表空间膨胀了2倍。

问题缘起

同事在客户现场利用DTS工具,从A实例将数据迁移到B实例过程中,发现几乎稍大点的表在迁移完成后,目标端表空间大小差不多都是源端的3倍,也就是说表空间膨胀了2倍。

排查思路

对这篇文章 《叶问》第16期 有印象的话,应该还能记得,数据迁移(导入导出)过程中,也包括主从复制场景,导致表空间膨胀的原因有几种:

  • MySQL表默认是InnoDB引擎且目前索引只支持B+树索引,在数据的增删改过程中,会因为page分裂而导致表产生碎片,主从服务器上同张表的碎片率不同也会导致表空间相差很大。
  • 主库整理过碎片(相当于重建整表),从库则是从原先的未整理的物理备份中恢复出来的。
  • 两端表结构不一致,如从库可能比主库多索引。
  • 两端表的行格式不一致,如主库为dynamic,从库为compressed。
  • 两端字符集不同,例如源端是latin1,目标端是utf8mb4。
  • 个别云数据库在从库上可能采用特殊的并行复制技术,导致在从库上有更高的碎片率(有个极端的案例,同一个表在主库只有6G,从库上则有将近150G)。
  • 数据表上没有自增ID作为主键,数据写入随机离散,page频繁分裂造成碎片率很高。

问题发现

顺着上面的思路,逐一排查,看能否定位问题原因。

  • 因素1,不存在,这是全量迁移场景,不是在日常随机增删改的过程中导致膨胀的。
  • 因素2,不存在,这是利用DTS工具迁移数据的场景。
  • 因素3、4、5,不存在,两边表结构一致。
  • 因素6,不存在,原因同2。
  • 因素7,不存在,每个表都有自增ID作为主键。

排查到这里,就显得有点诡异了,似乎遇到了玄学问题。不过没关系,我们还需要先了解DTS工具的工作方式,大致如下:

  • 计算数据表总行数。
  • 根据batch size,分成多段并行读取数据;例如总共10000行数据,batch size是1000,则总共分为10次读取数据。
  • 将读取出来的数据拼接成INSERT...VALUES...ON DUPLICATE KEY UPDATE​,因为DTS工具要支持增量迁移数据,所以才加上 ON DUPLICATE KEY UPDATE 子句。
  • 将拼接后的SQL并行写入到目标端。

初看上述工作过程,似乎也没什么特别之处会导致数据写入后产生大量碎片,从而表空间文件急剧膨胀。

首先,读取数据阶段只涉及到源端,可以先排除了。所以,疑点集中在第3、4两步。

了解InnoDB引擎特点的话应该知道,当InnoDB表有自增ID作为主键时,如果写入的数据总是顺序递增的话,那么产生碎片的概率就会很低。但是,如果写入的数据是离散化的(比如插入的顺序是随机离散的,或者比如插入顺序为1、10000、2、3000、3、5000...这种完全离散无序的),则有极大可能会造成碎片率很高。

按照上述疑点,我们需要确认DTS工具构造的SQL是什么样的,这就需要修改选项 binlog_format = statement,这是为了获取其原生的SQL,row模式下可能就相对不好排查了。然后再次运行DTS工具,查看生成的SQL。

经过排查,终于发现问题所在,原来是DTS工具在拼接SQL时,虽然是分段读取数据,但没有将读取出来的结果集先行排序,造成了拼接后的SQL大概像下面这样的:

INSERT INTO t VALUES (100, ...), (99, ...), (98, ...)...(1, ...);

这种方式写入的话,而且还是并发写入,就会极大概率造成InnoDB data page频繁分裂,所以表空间文件才膨胀到原来的3倍之巨。原因不难理解,就好比排队机制,本来我们是按照身高顺序排,但现在有几位高个子的先排在前面了,那么后来的每次都要让这几个人频繁往后移动才行,这就造成了data page分裂,产生大量碎片。

我用几万条sysbench标准表做测试,采用这种方式写入的话,大概会造成约20%的表空间膨胀率。

问题已然明确,只需要在读取数据拼接插入SQL这个阶段,先行对结果集进行排序,就可以完美解决这个问题了。

并顺手给负责SQL优化器的同学提了个feature request(MySQL bug#109087),希望能在遇到上述倒序INSERT的情况下,自动完成SQL改写,改倒序为正序(或者说,INSERT的顺序和表主键定义的顺序一致,通常都是正序的INT),也就可以完美避开这类风险了。

责任编辑:武晓燕 来源: GreatSQL社区
相关推荐

2010-11-24 11:13:07

MySQL批量导入

2009-11-06 14:25:20

Oracle创建用户表

2019-09-22 18:31:46

Oracleundo空间

2017-05-25 10:23:13

数据a表b表

2021-07-28 14:20:13

正则PythonFlashText

2020-10-06 18:57:14

PostgreSQL数据库数据导入

2021-09-08 19:35:02

MySQL Keyring加密

2011-07-18 15:59:17

MySQL数据库

2021-07-14 10:38:29

MySQL共享表独立表

2010-05-18 17:17:02

MySQL数据表

2010-11-23 13:57:50

MySQL独立表空间

2021-01-08 08:10:34

MySQL表空间回收

2020-03-22 21:46:06

MySQLInnoDB表空间

2011-08-05 14:31:04

mysql_conve批量转换MYSQL数据MYISAM

2010-05-19 15:01:14

MySQL数据导入

2010-05-27 14:35:25

MySQL批量导入

2011-08-15 16:58:34

SQL Server远程查询批量导入数据

2023-11-01 21:45:59

数据库MySQL单表

2020-09-13 13:30:01

命令开发代码

2018-03-16 08:14:42

互联网共享单车佛系
点赞
收藏

51CTO技术栈公众号