MySQL批量導(dǎo)入數(shù)據(jù)時(shí)，為何表空間膨脹了N倍
問(wèn)題緣起
同事在客戶(hù)現(xiàn)場(chǎng)利用DTS工具，從A實(shí)例將數(shù)據(jù)遷移到B實(shí)例過(guò)程中，發(fā)現(xiàn)幾乎稍大點(diǎn)的表在遷移完成后，目標(biāo)端表空間大小差不多都是源端的3倍，也就是說(shuō)表空間膨脹了2倍。
排查思路
對(duì)這篇文章 《葉問(wèn)》第16期 有印象的話(huà)，應(yīng)該還能記得，數(shù)據(jù)遷移（導(dǎo)入導(dǎo)出）過(guò)程中，也包括主從復(fù)制場(chǎng)景，導(dǎo)致表空間膨脹的原因有幾種：
- MySQL表默認(rèn)是InnoDB引擎且目前索引只支持B+樹(shù)索引，在數(shù)據(jù)的增刪改過(guò)程中，會(huì)因?yàn)閜age分裂而導(dǎo)致表產(chǎn)生碎片，主從服務(wù)器上同張表的碎片率不同也會(huì)導(dǎo)致表空間相差很大。
- 主庫(kù)整理過(guò)碎片（相當(dāng)于重建整表），從庫(kù)則是從原先的未整理的物理備份中恢復(fù)出來(lái)的。
- 兩端表結(jié)構(gòu)不一致，如從庫(kù)可能比主庫(kù)多索引。
- 兩端表的行格式不一致，如主庫(kù)為dynamic，從庫(kù)為compressed。
- 兩端字符集不同，例如源端是latin1，目標(biāo)端是utf8mb4。
- 個(gè)別云數(shù)據(jù)庫(kù)在從庫(kù)上可能采用特殊的并行復(fù)制技術(shù)，導(dǎo)致在從庫(kù)上有更高的碎片率（有個(gè)極端的案例，同一個(gè)表在主庫(kù)只有6G，從庫(kù)上則有將近150G）。
- 數(shù)據(jù)表上沒(méi)有自增ID作為主鍵，數(shù)據(jù)寫(xiě)入隨機(jī)離散，page頻繁分裂造成碎片率很高。
問(wèn)題發(fā)現(xiàn)
順著上面的思路，逐一排查，看能否定位問(wèn)題原因。
- 因素1，不存在，這是全量遷移場(chǎng)景，不是在日常隨機(jī)增刪改的過(guò)程中導(dǎo)致膨脹的。
- 因素2，不存在，這是利用DTS工具遷移數(shù)據(jù)的場(chǎng)景。
- 因素3、4、5，不存在，兩邊表結(jié)構(gòu)一致。
- 因素6，不存在，原因同2。
- 因素7，不存在，每個(gè)表都有自增ID作為主鍵。
排查到這里，就顯得有點(diǎn)詭異了，似乎遇到了玄學(xué)問(wèn)題。不過(guò)沒(méi)關(guān)系，我們還需要先了解DTS工具的工作方式，大致如下：
- 計(jì)算數(shù)據(jù)表總行數(shù)。
- 根據(jù)batch size，分成多段并行讀取數(shù)據(jù)；例如總共10000行數(shù)據(jù)，batch size是1000，則總共分為10次讀取數(shù)據(jù)。
- 將讀取出來(lái)的數(shù)據(jù)拼接成INSERT...VALUES...ON DUPLICATE KEY UPDATE?，因?yàn)镈TS工具要支持增量遷移數(shù)據(jù)，所以才加上 ON DUPLICATE KEY UPDATE 子句。
- 將拼接后的SQL并行寫(xiě)入到目標(biāo)端。
初看上述工作過(guò)程，似乎也沒(méi)什么特別之處會(huì)導(dǎo)致數(shù)據(jù)寫(xiě)入后產(chǎn)生大量碎片，從而表空間文件急劇膨脹。
首先，讀取數(shù)據(jù)階段只涉及到源端，可以先排除了。所以，疑點(diǎn)集中在第3、4兩步。
了解InnoDB引擎特點(diǎn)的話(huà)應(yīng)該知道，當(dāng)InnoDB表有自增ID作為主鍵時(shí)，如果寫(xiě)入的數(shù)據(jù)總是順序遞增的話(huà)，那么產(chǎn)生碎片的概率就會(huì)很低。但是，如果寫(xiě)入的數(shù)據(jù)是離散化的（比如插入的順序是隨機(jī)離散的，或者比如插入順序?yàn)?、10000、2、3000、3、5000...這種完全離散無(wú)序的），則有極大可能會(huì)造成碎片率很高。
按照上述疑點(diǎn)，我們需要確認(rèn)DTS工具構(gòu)造的SQL是什么樣的，這就需要修改選項(xiàng) binlog_format = statement，這是為了獲取其原生的SQL，row模式下可能就相對(duì)不好排查了。然后再次運(yùn)行DTS工具，查看生成的SQL。
經(jīng)過(guò)排查，終于發(fā)現(xiàn)問(wèn)題所在，原來(lái)是DTS工具在拼接SQL時(shí)，雖然是分段讀取數(shù)據(jù)，但沒(méi)有將讀取出來(lái)的結(jié)果集先行排序，造成了拼接后的SQL大概像下面這樣的：
這種方式寫(xiě)入的話(huà)，而且還是并發(fā)寫(xiě)入，就會(huì)極大概率造成InnoDB data page頻繁分裂，所以表空間文件才膨脹到原來(lái)的3倍之巨。原因不難理解，就好比排隊(duì)機(jī)制，本來(lái)我們是按照身高順序排，但現(xiàn)在有幾位高個(gè)子的先排在前面了，那么后來(lái)的每次都要讓這幾個(gè)人頻繁往后移動(dòng)才行，這就造成了data page分裂，產(chǎn)生大量碎片。
我用幾萬(wàn)條sysbench標(biāo)準(zhǔn)表做測(cè)試，采用這種方式寫(xiě)入的話(huà)，大概會(huì)造成約20%的表空間膨脹率。
問(wèn)題已然明確，只需要在讀取數(shù)據(jù)拼接插入SQL這個(gè)階段，先行對(duì)結(jié)果集進(jìn)行排序，就可以完美解決這個(gè)問(wèn)題了。
并順手給負(fù)責(zé)SQL優(yōu)化器的同學(xué)提了個(gè)feature request（MySQL bug#109087），希望能在遇到上述倒序INSERT的情況下，自動(dòng)完成SQL改寫(xiě)，改倒序?yàn)檎颍ɑ蛘哒f(shuō)，INSERT的順序和表主鍵定義的順序一致，通常都是正序的INT），也就可以完美避開(kāi)這類(lèi)風(fēng)險(xiǎn)了。
