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

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

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

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

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

• 指標(biāo)一：表的狀態(tài)：SHOW TABLE STATUS LIKE 'courier_consume_fail_message';
• 指標(biāo)二：表的實(shí)際行數(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)四：表查詢(xún)的執(zhí)行計(jì)劃：EXPLAIN SELECT * FROM courier_consume_fail_message WHERE service='courier-transfer-mq';

?-- 清理磁盤(pán)碎片
?OPTIMIZE TABLE courier_consume_fail_message;

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

一、清理前

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

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

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

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

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

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

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

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

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

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

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

三、清理碎片

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

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

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

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

小結(jié)

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

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

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

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

最新評(píng)論