背景

數(shù)據(jù)結轉過程中經(jīng)常進行 delete 操作，產(chǎn)生空白空間，如果進行新的插入操作，MySQL將嘗試利用這些留空的區(qū)域，但仍然無法將其徹底占用，于是造成了數(shù)據(jù)的存儲位置不連續(xù),以及物理存儲順序與理論上的排序順序不同，久而久之就產(chǎn)生了碎片。

碎片治理思路

根據(jù)線上處理經(jīng)驗總結比對4種處理磁盤碎片優(yōu)缺點

?

?

創(chuàng)建分區(qū)表

上述分區(qū)表，在某一分區(qū)內數(shù)據(jù)結轉完成后，

ALTER TABLE warehouse_stock_flow drop PARTITION p24;

當然不是所有的表都是可以創(chuàng)建分區(qū)表的。如果某一張數(shù)據(jù)表在很長一段時間內沒有進行數(shù)據(jù)結轉，且無法創(chuàng)建分區(qū)表的話，可以利用以下方法。

?

表名切換

如果某一張數(shù)據(jù)表在很長一段時間內沒有進行數(shù)據(jù)結轉，可以創(chuàng)建臨時表，通過大數(shù)據(jù)將某一結轉周期內數(shù)據(jù)推送至臨時表，在代碼層面進行數(shù)據(jù)的雙寫，最后再通過表名更換的方式進行表名轉換。其實，治理磁盤碎片最好的方法就是刪除表，不同業(yè)務對數(shù)據(jù)的要求不同。如果有可能的話新建一個臨時表。

利用rename語句對數(shù)據(jù)庫表信息進行修改，不會鎖表，可以達到零延遲，無抖動，對線上無任何影響。

rename table xx_record to xx_record_temp1,xx_temp to xx_record,xx_record_temp1 to xx_record_temp;

總結

不管是使用云還是商城數(shù)據(jù)庫，只要使用mysql，必然會遇到Mysql碎片問題痛點，數(shù)據(jù)量大的業(yè)務表應該設計成分區(qū)表方便磁盤碎片整理，降低維護成本和業(yè)務影響。碎片清理前后，IO性能會上升，SQL執(zhí)行效率更快。所以，在日常運維工作中，應對碎片進行定期清理，保證數(shù)據(jù)庫有穩(wěn)定的性能和充足的空間。

擴展

提到提高IO性能，在緊急情況下還可以考慮開啟刷盤（設置 sync_binlog=0;innodb_flush_log_at_trx_commit=0），但開啟刷盤會有數(shù)據(jù)丟失風險（集團數(shù)據(jù)庫模板配置參數(shù)默認sync_binlog=1;innodb_flush_log_at_trx_commit=1）。

附件

mysql數(shù)據(jù)庫核心參數(shù)介紹：https://www.cnblogs.com/klvchen/p/10861850.html?

審核編輯 黃宇