MySQL刪除數據表空間不變原因頭條網

2020-12-15 08:58:05 佚名

作為一名資深的CV 戰士，我們可能很少去關注 MySQL的數據文件存儲在哪裡。當我們執行一批刪除操作的時候，也不會去觀察數據庫文件的大小到底有沒有變化。下面我們一起來看一下 MySQL 數據存儲相關的原理。

在MySQL 8.0 之前，表結構定義是存儲在 .frm 為後綴的文件裡，如下圖所示。因為表結構定義佔用空間比較小，下面主要介紹一下表數據的存儲相關的東西。

innodb_file_per_table

表數據可以存儲在共享表空間裡（ibdata1、ibdata2......），也可以單獨存儲，這個是有innodb_file_per_table 參數決定的。innodb_file_per_table 設置為 OFF 時，表示表的數據存儲在共享表空間，也就是和數據字典存放在一起；當設置為ON 時，每個 InnoDB 的表數據都會存儲在一個 .ibd 的文件中。（從 MySQL 5.6.6 開始默認為 ON）

innodb_file_per_table 建議設置為 ON，也就是每個表數據單獨存儲。當我們使用 drop 刪除表時，就可以回收磁盤空間了。否則磁盤空間還不一定會被回收。

數據刪除流程

上面是主鍵索引樹的結構，比如當我們要刪除 id = 500 的記錄。此時500 位置上的空間就會空出來，磁盤的空間佔用並不會減小。MySQL是按頁存儲的，當我們把整個 PageA 的數據都刪除掉時。此時磁盤的空間也不會減小，而僅僅是PageA 是可以複用。當然，我們使用 delete 命令把所有數據都刪除掉之後，其磁盤空間也不會減小的。也是隻之前所有的數據頁都是可複用而已。

不僅僅數據的刪除會造成數據空洞，數據的插入也會造成數據的空洞。

如果所插入的數據是按照索引遞增的插入的，那麼索引就是緊湊的。如果是隨機寫入的，就有可能會造成索引的數據頁分裂。

從上面的結果可以看出，當新寫入一個 550 時，由於 PageA 滿了，頁分裂出來了 PageB ，也造成了數據頁的空洞。而數據頁的空洞可以通過重建表的方式來達到去除空洞的目的。

重建表

我們可以使用 alter table A engine=InnoDB; 來重建表，其過程如下圖所示：

從上圖可以看出，首先MySQL 會創建出一個 tmp 臨時表，然後將數據按照主鍵自增的方式插入，最後將 tmp 表覆蓋 A 表。在MySQL 5.5 及以前，上面的重建表是 Offline的，也就是重建的過程中不對外提供服務。

在MySQL 5.6 版本開始引入 Online DDL ，也就是重建表的過程中也可以對外提供服務，如下圖所示：

從上圖可以看出，Online 的重建表與 Offline 的不同在於：如果在重建的過程中有表的更改，其會存儲在 row log 當中，當重建完成後，再執行 row log 中的更改。

Online 和 inplace

上面重建表的第一張圖，把表 A 導出來的數據是存放在 tmp 表中的，這個 tmp 表是在 Server 層創建的。而第二張圖中 Online 操作，是 tmp_file 這個臨時文件是在 InnoDB 內部創建的。對於 Server 來說，它是一個原地的操作，這就是 inplace 的由來。比如磁盤只有1.2TB，此時你需要對一張 1TB 大小的 table 做 inplace 操作，此時磁盤是不夠用的。

默認的情況下，我們使用 alter table t engine=InnoDB; 其實就等於 alter table t engine=InnoDB, ALGORITHM=inplace; 。那麼於此對應的就是 alter table t engine=InnoDB, ALGORITHM=copy; ，此時是強拷貝，就對應的是第一張圖中的過程。