如果你熟悉 MySQL 緩衝池（不熟悉可以檢視 一條 sql 的執行過程詳解），可能會覺得是因為刪除操作只更新到緩衝池和 redo log，並沒有進行 flush 落盤，但如果關閉資料庫，觸發 flush ，會發現表檔案大小還是不會改變，這是為什麼？
原因
首先要了解資料的儲存方式，儲存方式共有兩種，是由引數 innodb_file_per_table 來控制的。
off：共享表結構，表示所有的檔案資料儲存在同一個檔案中，這樣在刪除整張表後空間也不會被回收，只是被位置被標記為可重用，下次建立表可能就在該位置建立。
on：表示每張表的資料各用一個檔案來儲存，在刪除整張表後該檔案也會被回收，減小總佔用空間。這也是預設的使用方式。如果儲存引擎是 InnoDB ，那麼資料檔案就是.ibd 格式的，如果是 MyISAM，那麼檔案就是 .MYD 格式的。
雖然執行 drop 刪除表時會減小表檔案大小，但在刪除記錄時還是不能減小結構，這個原因與上面的 off 共享表結構很像，因為 資料頁是 InnoDB 管理資料的最小的磁碟單位，資料頁就相當於上面的 "一張表的資料"，因為一張表的資料頁都是存在同一個檔案中的，所以在執行 delete 刪除資料後只會將將改位置標記可重用，並不會回收，而如果刪除整個頁，那麼也只能將該頁標記為可重用而不會回收。這種刪除了但是沒有被回收的位置就稱為 "資料空洞"。
頁合併與頁分裂
頁合併：既然產生了資料空洞，那麼資料檔案將會變得越來越大，這樣是很不利的，所以 MySQL 會在資料空洞達到一定比例後出觸發 "頁合併"，觸發的頁會找最靠近的可以合併的頁進行合併來優化空間（只會將資料頁使用權騰出來，並不會減小表檔案大小），防止後續的資料插入使用更多的資料頁造成檔案更大。
頁分裂：頁分裂是在插入操作時操作的記錄主鍵 ID 在原本的記錄之間時產生的，因為記錄儲存在資料頁中，如果該資料頁沒有合適的位置來儲存這條記錄，那麼就會將該條記錄以及後面的記錄另開要一個資料頁來儲存。
優化：因為頁合併和頁分裂都需要消耗額外的效能。所以我們在插入資料時應當按主鍵遞增順序插入（主鍵可以使用自增ID 或 雪花演算法，但如果業務欄位有唯一欄位且沒有其他索引，那麼可以使用其作為主鍵來避免每次查詢都需要回表），刪除資料時按主鍵順序刪除。
如何減小表檔案
1、自動觸發的頁合併。
2、手動觸發清理所有的資料空洞，具體做法就是執行 "Alter table 表名 engine = InnoDB"，因為 Alter 語句是修改表結構，而執行一個空操作就可以在不修改結構的情況下將資料空洞清除。具體實現是先建立一個臨時表，將當前表中的所有記錄依次新增到臨時表中，最後再將臨時表替換原表的表。而在這過程中不能有其他操作干擾，比如修改資料、讀資料，所以在執行此操作時會新增 MDL 寫鎖，而在執行讀寫操作時會新增 MDL 讀鎖，兩者互斥。
關於 MDL 鎖的解析可檢視部落格 Mysql 中的MDL 。
參考部落格：