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

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

问题缘起

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

排查思路

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

问题发现

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

因素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），也就可以完美避开这类风险了。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。