MySQL刪除數據表文件大小不變的原因是什么

今天就跟大家聊聊有關MySQL刪除數據表文件大小不變的原因是什么，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。

對于運行很長時間的數據庫來說，往往會出現表占用存儲空間過大的問題，可是將許多沒用的表刪除之后，表文件的大小并沒有改變，想解決這個問題，就需要了解 InnoDB 如何回收表空間的。

對于一張表來說，占用空間重要分為兩部分，表結構和表數據。通常來說，表結構定義占用的空間很小。所以空間的問題主要和表數據有關。

在 MySQL 8.0 前，表結構存儲在以 .frm 為后綴的文件里。在 8.0，允許將表結構定義在系統數據表中。

關于表數據的存放

可以將表數據存在共享表空間，或者單獨的文件中，通過 innodb_file_per_table 來控制。

• 如果為 OFF ，表示存在系統共享表空間中，和數據字典一起
• 如果為 ON，每個 InnoDB 表結構存儲在 .idb 為后綴的文件中

在 5.6.6 以后，默認值為 ON.

建議將該參數設置為 ON，這樣在不需要時，通過 drop table 命令，系統就會直接刪除該文件。

但在共享表空間中，即使表刪掉，空間也不會回收。

truncate = drop + create 

數據刪除流程

但有時使用 delete刪除數據時，僅僅刪除的是某些行，但這可能就會出現表空間沒有被回收的情況。

我們知道，MySQL InnoDB 中采用了 B+ 樹作為存儲數據的結構，也就是常說的索引組織表，并且數據時按照頁來存儲的。

在刪除數據時，會有兩種情況：

• 刪除數據頁中的某些記錄
• 刪除整個數據頁的內容
   

比如想要刪除 R4 這條記錄：

InnoDB 直接將 R4 這條記錄標記為刪除，稱為可復用的位置。如果之后要插入 ID 在 300 到 700 間的記錄時，就會復用該位置。由此可見，磁盤文件的大小并不會減少。

而且記錄的復用，只限于符合范圍條件的數據。之后要插入 ID 為 800 的記錄，R4 的位置就不能被復用了。

再比如要是刪除了整個數據頁的內容，假設刪除 R3 R4 R5，為 Page A 數據頁。

這時 InnoDB 就會將整個 Page A 標記為刪除狀態，之后整個數據都可以被復用，沒有范圍的限制。比如要插入 ID=50 的內容就可以直接復用。

并且如果兩個相鄰的數據頁利用率都很小，就會把兩個頁中的數據合到其中一個頁上，另一個頁標記為可復用。

綜上，無論是數據行的刪除還是數據頁的刪除，都是將其標記為刪除的狀態，用于復用，所以文件并不會減小。對應到具體的操作就是使用 delete 命令.

而且，我們還可以發現，對于第一種刪除記錄的情況，由于復用時會有范圍的限制，所以就會出現很多空隙的情況，比如刪除 R4，插入的卻是 ID=800.

插入操作也會造成空隙

在插入數據時，如果數據按照索引遞增順序插入，索引的結構會是緊湊的。但如果是隨機插入的，很可能造成索引數據頁分裂。

比如給已滿的 Page A 插入數據。

由于 Page A 滿了，所以要申請 Page B，調整 Page A 的過程到 Page B，這也稱為頁分裂。

結束后 Page A 就有了空隙。

另外對于更新操作也是，先刪除再插入，也會造成空隙。

進而對于大量進行增刪改的表，都有可能存在空洞。如果把空洞去掉，自然空間就被釋放了。

使用重建表

為了把表中的空隙去掉，這時就可以采用重新建一個與表 A 結構相同的表 B，然后按照主鍵 ID 遞增的順序，把數據依次插入到 B 表中。

由于是順序插入，自然 B 表的空隙不存在，數據頁的利用率也更高。之后用表 B 代替表 A，好像起到了收縮表 A 空間的作用。

具體通過:

alter table A engine=InnoDB

在 5.5 版本后，該命令和上面提到的流程差不多，而且 MySQL 會自己完成數據，交換表名，刪除舊表的操作。

但這就有一個問題，在 DDL 中，表 A 不能有更新，此時有數據寫入表 A 的話，就會造成數據丟失。

在 5.6 版本后引入了 Online DDL。

Online DDL

Online DDL 在其基礎上做了如下的更新：

重建表的過程如下：

1. 建立一個臨時文件，掃描表 A 主鍵的所有數據頁。
2. 用生成的數據頁生成 B+ 樹，存儲到臨時文件中。
3. 生成臨時文件時，如果有對 A 的操作，將其記錄在日志文件中，對應圖中 state 2 的狀態。
4. 臨時文件生成后，將日志文件應用到臨時文件中，得到與 A 表相同的數據文件，對應 state 3 狀態。
5. 用臨時文件替換 A 表的數據文件。
    

由于 row log 日志文件存在，可以在重建表示，對表 A 進行 DML 操作。

需要注意的是，在 alter 語句執行前，會先申請 MDL 寫鎖，但在拷貝數據前會退化成 MDL 讀鎖，從而支持 DML 操作。

至于為什么不大 MDL 去掉，是防止其他線程對這個表同時做 DDL 操作。

對于大表來說，該操作很耗 IO 和 CPU 資源，所以在線上操作時，要控制操作時間。如果為了保證安全，推薦使用 gh-ost 來遷移。

Online 和 inplace

首先說一下 inplace 和 copy 的區別：

在 Online DDL 中，表 A 重建后的數據放在 tmp_file 中，這個臨時文件是在 InnoDB 內部創建出來的。整個 DDL 在 InnoDB 內部完成。進而對于 Server 層來說，并沒有數據移動到臨時表中，是一個 "原地" 操作，所以叫 "inplace" .

而在之前普通的 DDL 中，創建后的表 A 是在 tmp_table 是 Server 創建的，所以叫 "copy"

對應到語句其實就是：

-- alter table t engine=InnoDB 默認為下面
alter table t engine=innodb,ALGORITHM=inplace;

-- 走的就是 server 拷貝的過程
alter table t engine=innodb,ALGORITHM=copy;

需要注意的是 inplace 和 Online 并不是對應關系：

1. DDL 過程是 Online，則一定是 inplace
2. 如果是 inplace 的 DDL 不應當是 Online，如在 <= 8.0, 添加全文索引和空間索引就屬于這種情況。
    

拓展

說一下 optimize，analyze，alter table 三種重建表之間的區別：

1. alter table t engine = InnoDB（也就是 recreate）默認的是 Oline DDL 過程。
2. analyze table t 不是重建表，僅僅是對表的索引信息做重新統計，沒有修改數據，期間加 MDL 讀鎖。
3. optimize table t 等于上兩步的操作。
    

在事務里面使用 alter table 默認會自動提交事務，保持事務一致性

如果有時，在重建某張表后，空間不僅沒有變小，甚至還變大了一點點。這時因為，重建的這張表本身沒有空隙，在 DDL 期間，剛好有一些 DML 執行，引入了一些新的空隙。

而且 InnoDB 不會把整張表填滿，每個頁留下 1/16 給后續的更新用，所以可能遠離是緊湊的，但重建后變成的稍有空隙。

總結

現在我們知道，在使用 delete 刪除數據時，其實對應的數據行并不是真正的刪除，InnoDB 僅僅是將其標記成可復用的狀態，所以表空間不會變小。

通常來說，在標記復用空間時分為兩種，一種是僅將某些數據頁中的位置標記為刪除狀態，但這樣的位置只會在一定范圍內使用，會出現空隙的情況。

另一種是將整個數據頁標記成可復用的狀態，這樣的數據頁沒有限制，可直接復用。

為了解決這個問題，我們可以采用重建表的方式，其中在 5.6 版本后，創建表已經支持 Online 的操作，但最后是在業務低峰時使用

看完上述內容，你們對MySQL刪除數據表文件大小不變的原因是什么有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。