第十二講: 為什麼表資料刪掉一半，表檔案大小不變？
簡概：
問題：表刪掉了一半的資料，表檔案的大小還是沒變？
經常會有同學來問我，我的資料庫佔用空間太大，我把一個最大的表刪掉了一半的資料，怎麼表檔案的大小還是沒變？
InnoDB 的表回收
那麼今天，我就和你聊聊資料庫表的空間回收，看看如何解決這個問題。這裡，我們還是針對 MySQL 中應用最廣泛的 InnoDB 引擎展開討論。一個 InnoDB 表包含兩部分，即：表結構定義和資料。在 MySQL 8.0 版本以前，表結構是存在以.frm 為字尾的檔案裡。而 MySQL 8.0 版本，則已經允許把表結構定義放在系統資料表中了。因為表結構定義佔用的空間很小，所以我們今天主要討論的是表資料。接下來，我會先和你說明為什麼簡單地刪除表資料達不到表空間回收的效果，然後再和你介紹正確回收空間的方法。
關鍵引數 innodb_file_per_table
表資料既可以存在共享表空間裡，也可以是單獨的檔案。這個行為是由引數 innodb_file_per_table 控制的：這個引數設定為 OFF 表示的是，表的資料放在系統共享表空間，也就是跟資料字典放在一起；這個引數設定為 ON 表示的是，每個 InnoDB 表資料儲存在一個以 .ibd 為字尾的檔案中。我建議你不論使用 MySQL 的哪個版本，都將這個值設定為 ON。因為，一個表單獨儲存為一個檔案更容易管理，而且在你不需要這個表的時候，透過 drop table 命令，系統就會直接刪除這個檔案。而如果是放在共享表空間中，即使表刪掉了，空間也是不會回收的。
所以，將 innodb_file_per_table 設定為 ON，是推薦做法，我們接下來的討論都是基於這個設定展開的。
[!NOTE]
一個表放一個檔案，不要放在共享表裡面，這樣刪除的時候也好刪。
資料刪除流程是什麼，你知道嗎
我們要徹底搞明白這個問題的話，就要從資料刪除流程說起了。
我們先再來看一下 InnoDB 中一個索引的示意圖。在前面第三講：深入淺出的索引上 - guixiang - 部落格園 (cnblogs.com)和第 5篇文章第四講：深入淺出索引（下） - guixiang - 部落格園 (cnblogs.com)中，我和你介紹索引時曾經提到過，InnoDB 裡的資料都是用 B+ 樹的結構組織的。
假設，我們要刪掉 R4 這個記錄，InnoDB 引擎只會把 R4 這個記錄標記為刪除。如果之後要再插入一個 ID 在 300 和 600 之間的記錄時，可能會複用這個位置。但是，磁碟檔案的大小並不會縮小。
[!IMPORTANT]
最直觀的區別，b+樹只有葉子節點才會儲存資料，而b-樹都會存
innodb的資料都是使用B+樹的結構組織的
資料頁空隙沒法回收只能複用,只做邏輯刪除，不做物理刪除；再次插入記錄時可複用該空間
刪掉了一個資料頁上的大量甚至所有記錄，如何複用
現在，你已經知道了 InnoDB 的資料是按頁儲存的，那麼如果我們刪掉了一個資料頁上的所有記錄，會怎麼樣？
答案是，整個資料頁就可以被複用了。但是，資料頁的複用跟記錄的複用是不同的。
記錄的複用，只限於符合範圍條件的資料。比如上面的這個例子，R4 這條記錄被刪除後，如果插入一個 ID 是 400 的行，可以直接複用這個空間。但如果插入的是一個 ID 是 800 的行，就不能複用這個位置了。
而當整個頁從 B+ 樹裡面摘掉以後，可以複用到任何位置。以圖 1 為例，如果將資料頁 page A 上的所有記錄刪除以後，page A 會被標記為可複用。這時候如果要插入一條 ID=50 的記錄需要使用新頁的時候，page A 是可以被複用的。如果相鄰的兩個資料頁利用率都很小，系統就會把這兩個頁上的資料合到其中一個頁上，另外一個資料頁就被標記為可複用。
[!TIP]
這裡的相鄰應該是指B+樹上的邏輯相鄰，而不是表空間檔案的物理相鄰，合併資料頁的過程應該正好對應B+樹刪除葉子結點中某條記錄導致其利用率很低，然後和鄰居節點合併的過程。
進一步地，如果我們用 delete 命令把整個表的資料刪除呢？結果就是，所有的資料頁都會被標記為可複用。但是磁碟上，檔案不會變小。
你現在知道了，delete 命令其實只是把記錄的位置，或者資料頁標記為了“可複用”，但磁碟檔案的大小是不會變的。也就是說，透過 delete 命令是不能回收表空間的。這些可以複用，而沒有被使用的空間，看起來就像是“空洞”。
不止是刪除資料會造成空洞，插入資料也會。
如果資料是按照索引遞增順序插入的，那麼索引是緊湊的。但如果資料是隨機插入的，就可能造成索引的資料頁分裂。假設圖 1 中 page A 已經滿了，這時我要再插入一行資料，會怎樣呢？
可以看到，由於 page A 滿了，再插入一個 ID 是 550 的資料時，就不得不再申請一個新的頁面 page B 來儲存資料了。頁分裂完成後，page A 的末尾就留下了空洞（注意：實際上，可能不止 1 個記錄的位置是空洞）。
[!CAUTION]
傳統B+樹頁面分裂是按照原頁面中50%的資料量進行分裂,而mysql如果是按主鍵順序插入的話那麼頁面分裂是不移動原有頁面的任何記錄，只是將新插入的記錄寫到新頁面之中
非遞增情況innodb的頁分裂策略就是50%分裂，在整個頁中前50%的不動，後百分之50%的移動到新的頁。所以老師這麼畫是對的。 50%分裂策略的優勢： 分裂之後，兩個頁面的空間利用率是一樣的；如果新的插入是隨機在兩個頁面中挑選進行，那麼下一次分裂的操作就會更晚觸發； 50%分裂策略的劣勢： 空間利用率不高：按照傳統50%的頁面分裂策略，索引頁面的空間利用率在50%左右； 分裂頻率較大：針對如上所示的遞增插入(遞減插入)，每新插入兩條記錄，就會導致最右的葉頁面再次發生分裂；
另外，更新索引上的值，可以理解為刪除一箇舊的值，再插入一個新值。不難理解，這也是會造成空洞的。也就是說，經過大量增刪改的表，都是可能是存在空洞的。
所以，如果能夠把這些空洞去掉，就能達到收縮表空間的目的。
而重建表，就可以達到這樣的目的。
重建表有利於收縮表空間
試想一下，如果你現在有一個表 A，需要做空間收縮，為了把表中存在的空洞去掉，你可以怎麼做呢？
你可以新建一個與表 A 結構相同的表 B，然後按照主鍵 ID 遞增的順序，把資料一行一行地從表 A 裡讀出來再插入到表 B 中。由於表 B 是新建的表，所以表 A 主鍵索引上的空洞，在表 B 中就都不存在了。顯然地，表 B 的主鍵索引更緊湊，資料頁的利用率也更高。如果我們把表 B 作為臨時表，資料從表 A 匯入表 B 的操作完成後，用表 B 替換 A，從效果上看，就起到了收縮表 A 空間的作用。
這裡，你可以使用此命令來重建表。
alter table A engine=InnoDB
在 MySQL 5.5 版本之前，這個命令的執行流程跟我們前面描述的差不多，區別只是這個臨時表 B 不需要你自己建立，MySQL 會自動完成轉存資料、交換表名、刪除舊錶的操作。
缺點很容易看出來
顯然，花時間最多的步驟是往臨時表插入資料的過程，如果在這個過程中，有新的資料要寫入到表 A 的話，就會造成資料丟失。因此，在整個 DDL 過程中，表 A 中不能有更新。也就是說，這個 DDL 不是 Online 的。
而在 MySQL 5.6 版本開始引入的 Online DDL，對這個操作流程做了最佳化。
我給你簡單描述一下引入了 Online DDL 之後，重建表的流程：
- 建立一個臨時檔案，掃描表 A 主鍵的所有資料頁；
- 用資料頁中表 A 的記錄生成 B+ 樹，儲存到臨時檔案中；
- 生成臨時檔案的過程中，將所有對 A 的操作記錄在一個日誌檔案（row log）中，對應的是圖中 state2 的狀態；
- 臨時檔案生成後，將日誌檔案中的操作應用到臨時檔案，得到一個邏輯資料上與表 A 相同的資料檔案，對應的就是圖中 state3 的狀態；
- 用臨時檔案替換表 A 的資料檔案。
可以看到，與上圖過程的不同之處在於，由於日誌檔案記錄和重放操作這個功能的存在，這個方案在重建表的過程中，允許對錶 A 做增刪改操作。這也就是 Online DDL 名字的來源。
我記得有同學在第五講：全域性鎖和表鎖 ：給表加個欄位怎麼有這麼多阻礙？ - guixiang - 部落格園 (cnblogs.com)的評論區留言說，DDL 之前是要拿 MDL 寫鎖的，這樣還能叫 Online DDL 嗎？
確實，圖 4 的流程中，alter 語句在啟動的時候需要獲取 MDL 寫鎖，但是這個寫鎖在真正複製資料之前就退化成讀鎖了。
[!CAUTION]
一開始獲取寫鎖，目的是保證在一些準備動作（如row log的建立）還未完成之前，主表不允許做任何修改或讀取，之後降級是允許其他執行緒 DML，因為這時 log 檔案已經就緒，他們的 DML 都會進入 log 檔案中。開始複製資料的時候退化成讀鎖，不解鎖的原因是防止有其他執行緒獲取DML寫鎖。最後把row log的資料複製到臨時表的時候還會獲取一次MDL寫鎖，防止其他DML操作。
為什麼要退化呢？為了實現 Online，MDL 讀鎖不會阻塞增刪改操作。
那為什麼不乾脆直接解鎖呢？為了保護自己，禁止其他執行緒對這個表同時做 DDL。
而對於一個大表來說，Online DDL 最耗時的過程就是複製資料到臨時表的過程，這個步驟的執行期間可以接受增刪改操作。所以，相對於整個 DDL 過程來說，鎖的時間非常短。對業務來說，就可以認為是 Online 的。需要補充說明的是，上述的這些重建方法都會掃描原表資料和構建臨時檔案。
對於很大的表來說，這個操作是很消耗 IO 和 CPU 資源的。因此，如果是線上服務，你要很小心地控制操作時間。如果想要比較安全的操作的話，我推薦你使用 GitHub 開源的 gh-ost 來做。
Online 和 inplace概念區別
說到 Online，我還要再和你澄清一下它和另一個跟 DDL 有關的、容易混淆的概念 inplace 的區別。
你可能注意到了，在圖 3 （上上個圖）中，我們把表 A 中的資料匯出來的存放位置叫作 tmp_table。這是一個臨時表，是在 server 層建立的。
在圖 4 中（上個圖），根據表 A 重建出來的資料是放在“tmp_file”裡的，這個臨時檔案是 InnoDB 在內部建立出來的。整個 DDL 過程都在 InnoDB 內部完成。對於 server 層來說，沒有把資料挪動到臨時表，是一個“原地”操作，這就是“inplace”名稱的來源。
收縮表也要預留空間
所以，我現在問你，如果你有一個 1TB 的表，現在磁碟間是 1.2TB，能不能做一個 inplace 的 DDL 呢？
答案是不能。因為，tmp_file 也是要佔用臨時空間的。我們重建表的這個語句 alter table t engine=InnoDB，其實隱含的意思是：
alter table t engine=innodb,ALGORITHM=inplace;
跟 inplace 相對立的就是複製表copy的方式了，用法是
alter table t engine=innodb,ALGORITHM=copy;
[!CAUTION]
inplace 是innodb層
Online的含義就是在操作時不會阻塞對原表的增刪改功能
online肯定是innodb幫忙建立臨時表檔案了 那server就不用建立臨時表了, 因此就是inplace的
對於Alter table 語句，inplace模式表示在InnoDB引擎中複製表，而copy模式表示強制複製表，是在server層複製的表。前者是Online DDL ，而後者就是DDL。
當你使用 ALGORITHM=copy 的時候，表示的是強制複製表，對應的流程就是圖 3 的操作過程。
反轉：
但我這樣說你可能會覺得，inplace 跟 Online 是不是就是一個意思？其實不是的，只是在重建表這個邏輯中剛好是這樣而已。
比如，如果我要給 InnoDB 表的一個欄位加全文索引，寫法是：
alter table t add FULLTEXT(field_name);
這個過程是 inplace 的，但會阻塞增刪改操作，是非 Online 的。
如果說這兩個邏輯之間的關係是什麼的話，可以概括為：DDL 過程如果是 Online 的，就一定是 inplace 的；反過來未必，也就是說 inplace 的 DDL，有可能不是 Online 的。截止到 MySQL 8.0，新增全文索引（FULLTEXT index）和空間索引 (SPATIAL index) 就屬於這種情況。
最後，我們再延伸一下
在第 10 篇文章MySQL為什麼有時候會選錯索引？ 的評論區中，有同學問到使用 optimize table、analyze table 和 alter table 這三種方式重建表的區別。這裡，我順便再簡單和你解釋一下。
- 從 MySQL 5.6 版本開始，alter table t engine = InnoDB（也就是 recreate）預設的就是上面圖 4 的流程了；
- analyze table t 其實不是重建表，只是對錶的索引資訊做重新統計，沒有修改資料，這個過程中加了 MDL 讀鎖；
- optimize table t 等於 recreate+analyze。
小結：
今天這篇文章，我和你討論了資料庫中收縮表空間的方法。
現在你已經知道了，如果要收縮一個表，只是 delete 掉表裡面不用的資料的話，表檔案的大小是不會變的，你還要透過 alter table 命令重建表，才能達到表檔案變小的目的。我跟你介紹了重建表的兩種實現方式，Online DDL 的方式是可以考慮在業務低峰期使用的，而 MySQL 5.5 及之前的版本，這個命令是會阻塞 DML 的，這個你需要特別小心。
提問：
假設現在有人碰到了一個“想要收縮表空間，結果適得其反”的情況，看上去是這樣的：
- 一個表 t 檔案大小為 1TB；
- 對這個表執行 alter table t engine=InnoDB；
- 發現執行完成後，空間不僅沒變小，還稍微大了一點兒，比如變成了 1.01TB。
你覺得可能是什麼原因呢 ？
答案：
1、這個表已經重建過了，沒啥空洞，現在在重建，innodb會給資料頁一些預留空間，導致檔案變大 2、表重建過程中，有大量更新，又產生了空洞 3、在 DDL 期間，如果剛好有外部的 DML 在執行，這期間可能會引入一些新的空洞。
更加難想到的回答：
在重建表的時候，InnoDB 不會把整張表佔滿，每個頁留了 1/16 給後續的更新用。也就是說，其實重建表之後不是“最”緊湊的。假如是這麼一個過程：將表 t 重建一次，插入一部分資料，但是插入的這些資料，用掉了一部分的預留空間。這種情況下，再重建一次表 t，就可能會出現問題中的現象。