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