MySQL清理數據并釋放磁盤空間的實現示例

目錄一、清理前二、清理數據三、清理碎片小結

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

但目前發現，該表的數據量已經達到百萬級別，原因產生了大量的重試消費，這導致了該表的慢查詢。

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

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

在表碎片清理前，我們關注以下四個指標。

指標一：表的狀態：SHOW TABLE STATUS LIKE 'courier_consume_fail_message';指標二：表的實際行數：SELECT count(*) FROM courier_consume_fail_message;指標三：要清理的行數：SELECT count(*) FROM courier_consume_fail_message where created_at < '2023-04-19 00:00:00';指標四：表查詢的執行計劃：EXPLAIN SELECT * FROM courier_consume_fail_message WHERE service='courier-transfer-mq';?-- 清理磁盤碎片?OPTIMIZE TABLE courier_consume_fail_message;

以下是清理前后的指標對比。

一、清理前

指標一，表的狀態：

指標二，表的實際行數：76986

指標三，要清理的行數：76813

指標四，表查詢的執行計劃：

二、清理數據

下面是執行 DELETE FROM courier_consume_fail_message WHERE created_at < '2023-04-19 00:00:00'; 后的統計。

指標一，表的狀態：

指標二，表的實際行數：173

指標三，要清理的行數：0

指標四，表查詢的執行計劃：

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

三、清理碎片

下面是執行 OPTIMIZE TABLE courier_consume_fail_message; 后的統計。

指標一，表的狀態：

指標四，表查詢的執行計劃：

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

小結

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

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

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

到此這篇關于MySQL清理數據并釋放磁盤空間的實現示例的文章就介紹到這了,更多相關MySQL 清理數據并釋放磁盤空間內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！