底層知識

INNODB表的儲存

一個 InnoDB 表包含兩部分，即：表結構定義和資料。在 MySQL 8.0 版本以前，表結構是存在以 .frm 為字尾的檔案裡。而

MySQL 8.0 版本，則已經允許把表結構定義放在系統資料表中了。

表資料既可以存在共享表空間裡，也可以是單獨的檔案。這個行為是由引數innodb_file_per_table 控制的：

1. 這個引數設定為 OFF 表示的是，表的資料放在系統共享表空間，也就是跟資料字典放在一起；

2. 這個引數設定為 ON 表示的是，每個 InnoDB 表資料儲存在一個以 .ibd 為字尾的檔案中。

不需要這個表的時候，透過 drop table 命令，系統就會直接刪除這個檔案。

從 MySQL 5.6.6 版本開始，它的預設值就是 ON 。

資料處理流程

InnoDB的資料都是用B+樹的結構組織的。

刪除一條記錄，InnoDB引擎只會把記錄標記為刪除，之後如果插入資料是按照索引排序剛好落在這個區間內，可能會複用這個位置，但是磁碟檔案的大小不會縮小。

InnoDB的資料是按頁儲存的，如果刪掉一個資料頁上的所有記錄，整個資料頁會被複用。而記錄的複用是需要滿足範圍條件的，只有在範圍條件內的資料才會被複用。

如果相鄰的兩個資料頁利用率很小，系統會把兩個頁上的資料合併到其中一個頁上，另外的一個資料頁就被標記為可複用。

其實不止刪除資料會造成空洞，插入資料也會。

如果資料是按照索引遞增順序插入的，那麼索引是緊湊的。但如果資料是隨機插入的，就可能造成索引的資料頁分裂。

更新索引上的值，可以理解為刪除一箇舊的值，再插入一個新值。也會造出空洞！

經過大量增刪改的表，都是可能是存在空洞的。所以，如果能夠把這些空洞去掉，就能達到收縮表空間的目的。

（1）表的儲存會出現碎片化，每當刪除了一行內容，該段空間就會變為空白、被留空，而在一段時間內的大量刪除操作，會使這種留空的空間變得比儲存列表內容所使用的空間更大；

（2）當執行插入操作時，MySQL會嘗試使用空白空間，但如果某個空白空間一直沒有被大小合適的資料佔用，仍然無法將其徹底佔用，就形成了碎片；

（3）當MySQL對資料進行掃描時，它掃描的物件實際是列表的容量需求上限，也就是資料被寫入的區域中處於峰值位置的部分；

透過alter table A engine=InnoDB 命令來重建表。在 MySQL 5.5 版本之前，這個命令會自動完成轉存資料、交換表名、刪除舊錶的操作。

在往臨時表插入資料的過程中，有新資料寫入到A表，會造成資料丟失。這個操作不是Online的。

MySQL 5.6 版本開始引入的 Online DDL，重建表流程。

引入了 Online DDL 之後，重建表的流程：

1. 建立一個臨時檔案，掃描表 A 主鍵的所有資料頁；

2. 用資料頁中表 A 的記錄生成 B+ 樹，儲存到臨時檔案中；

3. 生成臨時檔案的過程中，將所有對 A 的操作記錄在一個日誌檔案（ row log ）中，對應的是圖中 state2 的狀態；

4. 臨時檔案生成後，將日誌檔案中的操作應用到臨時檔案，得到一個邏輯資料上與表 A 相同的資料檔案，對應的就是圖中 state3 的狀態；

5. 用臨時檔案替換表 A 的資料檔案。

在流程中，alter 語句在啟動的時候需要獲取 MDL 寫鎖，但是這個寫鎖為了實現 Online，MDL 讀鎖不會阻塞增刪改操作。在真正複製資料之前就退化成讀鎖了。

那為什麼不乾脆直接解鎖呢？為了保護自己，禁止其他執行緒對這個表同時做 DDL 。

而對於一個大表來說， Online DDL 最耗時的過程就是複製資料到臨時表的過程，這個步驟的執行期間可以接受增刪改操作。所以，相對於整個 DDL 過程來說，鎖的時間非常短。對業務來說，就可以認為是 Online 的。

需要補充說明的是，上述的這些重建方法都會掃描原表資料和構建臨時檔案。對於很大的表來說，這個操作是很消耗 IO 和 CPU 資源的。因此，如果是線上服務，你要很小心地控制操作時間。如果想要比較安全的操作的話，我推薦你使用 GitHub 開源的 gh-ost 來做。

參考文章：丁奇，MySQL45講，13.為什麼表資料刪掉一半，表檔案大小不變？