如果你熟悉 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、手動觸發清理大部分的資料空洞（5.6 的 Online DDL 可能會存一些寫操作，可能會產生一些資料空洞），具體做法就是執行 "Alter table 表名 engine = InnoDB"，因為 Alter 語句是修改表結構，而執行一個空修改操作就可以在不修改結構的情況下將資料空洞清除。具體原理是會先建立一個臨時表，將當前表中的所有記錄依次新增到臨時表中，最後再將臨時表替換原表的表。但是重建表並一定就是最緊湊的，因為在重建時每個資料頁會留 1/16 用於更新，同時 5.6 後可能還會在向臨時表遷移資料時積累一些寫操作造成頁分裂。而在這過程中不能有其他操作干擾，比如修改資料、讀資料，所以在執行此操作時會新增 MDL 寫鎖，而在執行讀寫操作時會新增 MDL 讀鎖，兩者互斥。
關於 MDL 鎖的解析可檢視部落格 Mysql 中的MDL 。
參考部落格：