為什麼刪除記錄表文件不會減小？(記錄的插入與刪除在磁碟上的變化)
如果你熟悉 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 。
參考部落格：
