MySQL占用空間后越刪越大的示例分析

今天就跟大家聊聊有關(guān)MySQL占用空間后越刪越大的示例分析，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

你MySQL中查詢回表

話說(shuō)有一天，我心血來(lái)潮。登錄阿里云，發(fā)現(xiàn) RDS 占用的空間挺大的。想起有一張表，當(dāng)初設(shè)計(jì)的不好，沒(méi)用的數(shù)據(jù)挺多的。就想著刪除清理一部分?jǐn)?shù)據(jù)，節(jié)約空間。

說(shuō)做就做，一條 delete 語(yǔ)句，如下：

沒(méi)想到意外發(fā)生了，刪除幾十萬(wàn)條數(shù)據(jù)后，我再去查看 RDS 占用的數(shù)據(jù)空間，不僅沒(méi)降，還上升了。這是怎么回事？

這個(gè)問(wèn)題可能很多人都沒(méi)有注意到。平時(shí)大家的關(guān)注點(diǎn)可能都不在這方面。

我把這個(gè)數(shù)據(jù)刪除后，阿里云立馬發(fā)郵件報(bào)警，說(shuō)我們的 RDS 存儲(chǔ)空間過(guò)大，沒(méi)辦法，當(dāng)初知識(shí)水平有限。就又花公司的錢(qián)，升級(jí)了存儲(chǔ)空間。

這個(gè)問(wèn)題一直困惑著我，直到兩年前看了《高性能MySQL》。而今天剛好又有一個(gè)網(wǎng)友私信我，我想起了這件事。所以，今天就給大家解惑一下，為什么會(huì)這樣。

第一個(gè)就是網(wǎng)上很多人所說(shuō)的那樣，這是一個(gè)MySQL的BUG，直到MySQL 5.7 版本才解決。由于我們當(dāng)初選的是 MySQL 5.6 的版本，出現(xiàn)這個(gè)問(wèn)題后，也咨詢了阿里云。但是阿里云給出的答復(fù)是只能進(jìn)行數(shù)據(jù)遷移，雖然MySQL5.7版本，可以解決這個(gè)問(wèn)題，但是阿里云的MySQL5.6的版本并不能直接升級(jí)到5.7。而遷移數(shù)據(jù)庫(kù)也太浪費(fèi)時(shí)間和精力了，搞不好，再把主要數(shù)據(jù)給丟失了。BOSS 非剁了我不可。

第二個(gè)我要說(shuō)的就是 MySQL 5.6中默認(rèn)是獨(dú)立表空間，如果采用獨(dú)立表存儲(chǔ)模式，data 中還會(huì)產(chǎn)生 report_site_day.ibd 這類文件（存儲(chǔ)數(shù)據(jù)信息和索引信息）。而刪除的時(shí)候，它不會(huì)被刪除。

第三個(gè)就是 MySQL 對(duì)于刪除的功能的底層實(shí)現(xiàn)，并不是大家想象的那樣，執(zhí)行 delete 就真的給刪除了。而是當(dāng)你使用 delete 刪除的時(shí)候，MySQL 并沒(méi)有把數(shù)據(jù)文件刪除，而是將數(shù)據(jù)文件的標(biāo)識(shí)位刪除，沒(méi)有整理文件，因此不會(huì)徹底釋放空間。被刪除的數(shù)據(jù)將會(huì)被保存在一個(gè)鏈接清單中，當(dāng)有新數(shù)據(jù)寫(xiě)入的時(shí)候，MySQL 會(huì)利用這些已刪除的空間再寫(xiě)入。即，刪除操作會(huì)帶來(lái)一些數(shù)據(jù)碎片，正是這些碎片在占用硬盤(pán)空間。

關(guān)于數(shù)據(jù)遷移也會(huì)帶來(lái)一些問(wèn)題，這個(gè)我們下一章再說(shuō)。今天我先說(shuō)一下，MySQL 官方推薦的 OPTIMIZE TABLE 命令來(lái)清理優(yōu)化表 InnoDB 空間的做法。

OPTIMIZE TABLE 命令的格式如下：

具體的用法如下：

或者，你也可以這樣批量生成優(yōu)化所有表的 SQL，再?gòu)?fù)制出來(lái)執(zhí)行：

除此之外，我們還還可以把表設(shè)置為單表存儲(chǔ)的形式，這樣每個(gè)表中的數(shù)據(jù)單獨(dú)存儲(chǔ)，在刪除數(shù)據(jù)的時(shí)候，會(huì)隨著數(shù)據(jù)的刪除而釋放存儲(chǔ)空間。

至于數(shù)據(jù)遷移，我們可以先備份后刪除然后進(jìn)行導(dǎo)入。

看完上述內(nèi)容，你們對(duì)MySQL占用空間后越刪越大的示例分析有進(jìn)一步的了解嗎？如果還想了解更多知識(shí)或者相關(guān)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。