在我們的生產(chǎn)環(huán)境中有一張表：courier_consume_fail_message，是存放消息消費失敗的數(shù)據(jù)的，設(shè)計之初，這張表的數(shù)據(jù)量評估在萬級別以下，因此沒有建立索引。

但目前發(fā)現(xiàn)，該表的數(shù)據(jù)量已經(jīng)達到百萬級別，原因產(chǎn)生了大量的重試消費，這導(dǎo)致了該表的慢查詢。

因此需要清理該表數(shù)據(jù)。而實際上，使用 DELETE 命令刪除數(shù)據(jù)后，我們發(fā)現(xiàn)查詢速度并沒有顯著提高，甚至可能會降低。為什么？

因為 DELETE 命令只是標(biāo)記該行數(shù)據(jù)為“已刪除”狀態(tài)，并不會立即釋放該行數(shù)據(jù)在磁盤中所占用的存儲空間，這樣就會導(dǎo)致數(shù)據(jù)文件中存在大量的碎片，從而影響查詢性能。所以，除了刪除表記錄外，還需要清理磁盤碎片。

在表碎片清理前，我們關(guān)注以下四個指標(biāo)。

• 指標(biāo)一：表的狀態(tài)：SHOW TABLE STATUS LIKE 'courier_consume_fail_message';
• 指標(biāo)二：表的實際行數(shù)：SELECT count(*) FROM courier_consume_fail_message;
• 指標(biāo)三：要清理的行數(shù)：SELECT count(*) FROM courier_consume_fail_message where created_at < '2023-04-19 00:00:00';
• 指標(biāo)四：表查詢的執(zhí)行計劃：EXPLAIN SELECT * FROM courier_consume_fail_message WHERE service='courier-transfer-mq';

?-- 清理磁盤碎片
?OPTIMIZE TABLE courier_consume_fail_message;

以下是清理前后的指標(biāo)對比。

一、清理前

指標(biāo)一，表的狀態(tài)：

指標(biāo)二，表的實際行數(shù)：76986

指標(biāo)三，要清理的行數(shù)：76813

指標(biāo)四，表查詢的執(zhí)行計劃：

二、清理數(shù)據(jù)

下面是執(zhí)行 DELETE FROM courier_consume_fail_message WHERE created_at < '2023-04-19 00:00:00'; 后的統(tǒng)計。

指標(biāo)一，表的狀態(tài)：

指標(biāo)二，表的實際行數(shù)：173

指標(biāo)三，要清理的行數(shù)：0

指標(biāo)四，表查詢的執(zhí)行計劃：

通過指標(biāo)四可以看到，清理表記錄后，查詢掃描的行數(shù)依然沒變：8651048。

三、清理碎片

下面是執(zhí)行 OPTIMIZE TABLE courier_consume_fail_message; 后的統(tǒng)計。

指標(biāo)一，表的狀態(tài)：

指標(biāo)四，表查詢的執(zhí)行計劃：

通過指標(biāo)四可以看到，清理表記錄后，查詢掃描的行數(shù)變成了 100。

小結(jié)

可以看到，該表的數(shù)據(jù)行數(shù)和數(shù)據(jù)長度都被清理了，查詢語句掃描的行數(shù)也減少了。

為了提升 SELECT * FROM courier_consume_fail_message WHERE service='courier-transfer-mq'; 語句的查詢效率，還是應(yīng)當(dāng)建立索引。

?alter` `table` `ec_courier.courier_consume_fail_message ``add` `index` `idx_service(service);

到此這篇關(guān)于MySQL清理數(shù)據(jù)并釋放磁盤空間的實現(xiàn)示例的文章就介紹到這了,更多相關(guān)MySQL 清理數(shù)據(jù)并釋放磁盤空間內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論