經(jīng)常會有同學(xué)來問我，我的數(shù)據(jù)庫占用空間太大，我把一個最大的表刪掉了一半的數(shù)據(jù)，怎么表文件的大小還是沒變？
InnoDB 表的組成：表結(jié)構(gòu)定義和數(shù)據(jù)。
數(shù)據(jù)刪除流程
InnoDB 中，數(shù)據(jù)是以 B+ 樹結(jié)構(gòu)來存儲數(shù)據(jù)的：
假設(shè)刪掉表記錄 R4，此時(shí) InnoDB 只是把 R4 標(biāo)記為已刪除狀態(tài)，后續(xù)這個位置可以插入新的數(shù)據(jù)，但是磁盤文件大小并不會變化。
何時(shí)復(fù)用？
比如，插入一個 ID 是 400 的記錄，就可以直接復(fù)用原來 R4 的空間，若插入的是 800，為了保持 B+ 樹的結(jié)構(gòu)，就不能復(fù)用該空間了。
刪除整頁數(shù)據(jù)
當(dāng)刪除了整頁數(shù)據(jù)后，InnoDB 會將該頁標(biāo)記為已刪除，整頁都可復(fù)用。
同時(shí)，若兩個相鄰的數(shù)據(jù)頁利用率都比較低，系統(tǒng)會把兩頁上的數(shù)據(jù)整合到一個頁中，另一個頁就會標(biāo)記為可復(fù)用。
刪除整個表
此時(shí)，所有的數(shù)據(jù)頁都會標(biāo)記為可復(fù)用，但是磁盤空間仍然不會變小。
總結(jié)
delete 操作，只是把記錄的位置標(biāo)記為「可復(fù)用」，但是磁盤大小不會變化，這些可以復(fù)用，而未被使用的空間，看起來就像空洞。
增刪改-造成空洞
當(dāng)數(shù)據(jù)是隨機(jī)插入時(shí)，就可能造成索引的數(shù)據(jù)頁分裂。
如圖所示，由于 page A 寫滿，此時(shí)插入 ID 為 550 的數(shù)據(jù)，就不得不申請新的數(shù)據(jù)頁，頁分裂完成后，A 上就會留下空洞。
另外，更新索引上的值，其實(shí)是刪除舊值，插入新值，這個過程同樣會造成空洞。
綜上，經(jīng)過大量增刪改操作的表，都是可能存在大量空洞的，若要收縮表空間，就要清除這些空洞。
重建表-清除空洞
如何清除空洞？
直接的想法就是，新建一個表結(jié)構(gòu)相同的表，然后按主鍵 ID 遞增的順序，將原表中的數(shù)據(jù)，插入到新表。
這樣，新表中就不會存在空洞了。新表的主鍵索引也會更加緊湊，數(shù)據(jù)頁的利用率也更高。
上面的操作可以通過下面的語句自動完成：
alter table A engine=InnoDB;
MySQL 會自動完成轉(zhuǎn)存數(shù)據(jù)、交換表名、刪除舊表的操作。
詳細(xì)流程：
1、新建臨時(shí)文件，掃描原表 A 的所有數(shù)據(jù)頁；
2、根據(jù)表 A 的記錄生成 B+ 樹，存儲到臨時(shí)文件中；
3、生成臨時(shí)文件過程中，對 A 的所有操作都會記錄在一個日志文件中，對應(yīng)圖中的 state2 狀態(tài)；
4、臨時(shí)文件生成后，將日志文件中的操作應(yīng)用于臨時(shí)文件，得到一個完整的數(shù)據(jù)文件，對于 state3；
5、用臨時(shí)文件替換表 A 的數(shù)據(jù)文件；
表重建的過程是允許對表 A 做增刪改操作的，因此是一個 Online DDL（MySQL5.6+）
另外，根據(jù)表 A 重建出來的數(shù)據(jù)是放在“tmp_file”里的，這個臨時(shí)文件是 InnoDB 在內(nèi)部創(chuàng)建出來的。整個 DDL 過程都在 InnoDB 內(nèi)部完成。對于 server 層來說，沒有把數(shù)據(jù)挪動到臨時(shí)表，是一個“原地”操作，這就是“inplace”名稱的來源。
上述的這些重建方法都會掃描原表數(shù)據(jù)和構(gòu)建臨時(shí)文件。對于很大的表來說，這個操作是很消耗 IO 和 CPU 資源的。
因此，如果是線上服務(wù)，你要很小心地控制操作時(shí)間。如果想要比較安全的操作的話，我推薦你使用 GitHub 開源的 gh-ost 來做。
關(guān)注本公眾號，后臺回復(fù)「2018」即可獲取傳智播客 2018 最新 Python 和 Java 教程。
公眾號提供CSDN資源免費(fèi)下載服務(wù)！
