背景

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

碎片治理思路

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

?

?

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

上述分區(qū)表，在某一分區(qū)內(nèi)數(shù)據(jù)結(jié)轉(zhuǎn)完成后，

ALTER TABLE warehouse_stock_flow drop PARTITION p24;

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

?

表名切換

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

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

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

總結(jié)

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

擴(kuò)展

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

附件

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

審核編輯 黃宇