一個InnoDB表包含兩部分：表結(jié)構定義和數(shù)據(jù)。在MySQL 8.0版本前，表結(jié)構存在以.frm為后綴的文件里。之后的版本允許把表結(jié)構定義放在系統(tǒng)數(shù)據(jù)表中。由于表結(jié)構定義占用空間很小，所以主要討論表數(shù)據(jù)。

接下來，先說明為什么簡單刪除表數(shù)據(jù)達不到表空間回收的效果，再介紹正確回收空間的方法。

參數(shù)innodb_file_per_table

表數(shù)據(jù)既可以存在共享表空間里，也可以是單獨的文件，這由參數(shù)innodb_file_per_table控制：

• 設為OFF，表示表數(shù)據(jù)放在系統(tǒng)共享表空間，也就是跟數(shù)據(jù)字典放在一起；

• 設為ON，表示每個InnoDB表數(shù)據(jù)存儲在一個以.ibd為后綴的文件中。

從MySQL 5.6.6版本開始，默認值為ON。建議也是使用ON，因為一個表單獨存儲為一個文件更容易管理，而且在不需要該表時通過drop table命令，系統(tǒng)就會直接刪除文件；如果是放在共享表空間中，即使表刪除，空間也是不會回收的。

接下來的討論也是基于innodb_file_per_table=ON的設置。

在刪除整張表的時候，可以使用drop table命令回收表空間。但是，平時更多的場景是刪除某些行。

數(shù)據(jù)刪除流程

為了搞懂刪除部分行的場景，需要先從數(shù)據(jù)刪除流程開始說。

看一下InnoDB中一個索引的示意圖：

假設要刪除R4這個記錄，InnoDB只會把R4這個記錄標記為刪除。如果之后插入一個ID在300-600間的記錄，可能會復用這個位置，但磁盤文件的大小不會縮小。

那么如果將一個數(shù)據(jù)頁上的所有記錄都刪除，會怎么樣呢？答案是整個數(shù)據(jù)頁可以復用。

但是數(shù)據(jù)頁的復用和記錄的復用還是不一樣的。記錄的復用只限于符合范圍條件的數(shù)據(jù)，而一旦一個數(shù)據(jù)頁可以復用，所有范圍的數(shù)據(jù)都可以使用。比如在上面的索引中，若page A是可復用的，ID=50這樣的記錄也能使用該頁。

如果相鄰兩個數(shù)據(jù)頁利用率都很小，系統(tǒng)會把這兩個頁上的數(shù)據(jù)合到其中一個頁上，另一個頁就會被標記為可以復用。

進一步地，如果用delete命令刪除整個表的數(shù)據(jù)，那么所有數(shù)據(jù)頁都會被標記為可復用，而磁盤上的文件并不會變小。也就是說，delete命令不能回收表空間，這些可以復用卻沒被使用的空間，看起來就像“空洞”。

實際上不止刪除數(shù)據(jù)會造成空洞，插入數(shù)據(jù)也會。如果數(shù)據(jù)的插入是隨機的，可能造成索引的數(shù)據(jù)頁分裂。比如在上面的索引中，假設page A已滿，這時若要再插入一行數(shù)據(jù)ID=550：

當page A已滿的情況下進行插入，就必須再申請一個新的頁面page B來保存數(shù)據(jù)。由于頁分裂導致部分數(shù)據(jù)移動，page A就出現(xiàn)了空洞。

除了插入，由于更新可以看為刪除+插入，也可能造成空洞。即，增刪改都可能出現(xiàn)空洞。所以，如果能把這些空洞去掉，就能達到收縮表空間的目的。

重建表就可以達到這樣的目的。

重建表

假設現(xiàn)在有一個表A，需要去除其中的空洞，有什么辦法呢？

可以新建一個與表A結(jié)構相同的表B，然后按照主鍵ID遞增的順序，把數(shù)據(jù)逐行從表A讀取出來再插入到表B中。由于表B是新建的表，所以沒有表A上的空洞。把表B作為臨時表，數(shù)據(jù)從表A導入表B后，再用表B替換表A，從效果上就是表A沒有空洞了。

可以使用alter table A engine=InnoDB的命令重建表。在MySQL 5.5版本前，這個命令的執(zhí)行流程和上面描述的差不多，區(qū)別只是不需要自己創(chuàng)建臨時表，MySQL會自動完成轉(zhuǎn)存數(shù)據(jù)、交換表名、刪除舊表的操作。

在往臨時表插入數(shù)據(jù)的過程中，如果有新的數(shù)據(jù)要寫入表A，會造成數(shù)據(jù)損失，因此整個DDL的過程中，表A不能有更新，即DDL不是Online的。

而MySQL 5.6開始的版本引入了Online DDL，對這個操作流程做了優(yōu)化。新的流程為：

• 建立一個臨時文件；

• 掃描表A主鍵的所有數(shù)據(jù)頁，用里面的記錄生成B+樹，存儲到臨時文件中；

• 生成臨時文件的過程中，將所有對A的操作記錄在一個日志文件（row log）中，對應下圖中state 2的狀態(tài)；

• 臨時文件生成以后，將日志文件中的操作應用到臨時文件，得到一個邏輯數(shù)據(jù)上與表A相同的臨時文件；

• 用臨時文件替換表A。

該操作流程由于日志文件和重放操作的功能，在重建表的過程中允許對表A做增刪改操作。

當然，由于對表做改動，會有MDL鎖的存在。alter語句在啟動時會獲取MDL寫鎖，但這個鎖在真正拷貝數(shù)據(jù)之前就會退化成讀鎖，目的是禁止其他線程對這個表同時做DDL，又不會阻塞增刪改操作。

對于一個大表來說，Online DDL最耗時的過程就是拷貝數(shù)據(jù)到臨時表的過程，所以相對整個DDL過程來說，寫鎖鎖住的時間非常短，可以認為是Online的。

需要說明的是，上述這些重建方法都會掃描原表數(shù)據(jù)和構建臨時文件，對于很大的表來說，該操作很消耗IO和CPU資源。因此，如果是線上服務需要控制操作時間，推薦使用開源的gh-ost來做。

Online和inplace

說到Online，再講一個容易混淆的概念inplace。

在早版本的重建表過程中，表A數(shù)據(jù)導出來的存放位置叫做tmp_table，這個臨時表是在Server層創(chuàng)建的。

而在后面的版本，表A重建出來的數(shù)據(jù)是放在tmp_file里的（見前面的圖），這個臨時文件是InnoDB在內(nèi)部創(chuàng)建出來的。由于整個DDL過程在InnoDB內(nèi)部完成，對于Server層來說，沒有把數(shù)據(jù)挪動到臨時表，是一個“原地”操作，因此叫inplace。

那么假如表大小為1TB，磁盤空間為1.2TB，是否能做inplace的DDL呢？答案是不行的，因為tmp_file會占用臨時空間。

重建表的完整語句其實是下面這樣：

alter table t engine=innodb,ALGORITHM=inplace;
alter table t engine=innodb,ALGORITHM=copy;

其中，copy表示強制拷貝表，即使用臨時表；inplace表示使用臨時文件。

那是否表示，inplace就是Online？也不是，只是在重建表這個邏輯中剛好是這樣。

如果說這兩個邏輯之間的關系是什么，可以概括為：

• DDL過程如果是Online的，就一定是inplace的；

• 反之不正確，inplace的DDL，不一定是Online的。截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空間索引 (SPATIAL index) 就屬于這種情況。比如要給InnoDB表的一個字段加全文索引，過程是inplace的，但會阻塞增刪改。

到此這篇關于MySQL 13 為什么表數(shù)據(jù)刪掉一半，表文件大小不變？的文章就介紹到這了,更多相關mysql表數(shù)據(jù)刪掉一半表文件大小不變內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論