MySQL---資料刪除之後表檔案不變

在 MySQL 8.0 版本以前，表結構是存在以.frm 為字尾的檔案裡。而 MySQL 8.0 版本，則已經允許把表結構定義放在系統資料表中了。

引數 innodb_file_per_table

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

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

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

建議不論使用 MySQL 的哪個版本，都將這個值設定為 ON。因為，一個表單獨儲存為一個檔案更容易管理，而且在你不需要這個表的時候，通過 drop table 命令，系統就會直接刪除這個檔案。

假設，在一個資料頁上要在300和600之間刪除一個值，如果之後要再插入一個 ID 在 300 和 600 之間的記錄時，可能會複用這個位置。記錄的複用，只限於符合範圍條件的資料。

假設，某頁上的記錄全部刪除，整個資料頁就可以被複用了。當整個頁從 B+ 樹裡面摘掉以後，可以複用到任何位置。

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

進一步地，如果我們用 delete 命令把整個表的資料刪除呢？結果就是，所有的資料頁都會被標記為可複用。但是磁碟上，檔案不會變小。

delete 命令其實只是把記錄的位置，或者資料頁標記為了“可複用”，但磁碟檔案的大小是不會變的。

實際上，不止是刪除資料會造成空洞，插入資料也會。在隨機插入資料的時候，可能會引起頁分裂，從而導致某個頁不能填充滿，也就是所謂的空洞。

解決空洞，收縮空間的方法就是重建表。

你可以使用 alter table A engine=InnoDB 命令來重建表。在整個 DDL 過程中，表 A 中不能有更新。也就是說，這個 DDL 不是 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 讀鎖不會阻塞增刪改操作。

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

推薦你使用 GitHub 開源的 gh-ost 來做。

根據表 A 重建出來的資料是放在“tmp_file”裡的，這個臨時檔案是 InnoDB 在內部建立出來的。整個 DDL 過程都在 InnoDB 內部完成。對於 server 層來說，沒有把資料挪動到臨時表，是一個“原地”操作，這就是“inplace”名稱的來源~~~~~這個理解我認為其實是比較扯的。

因為畢竟資料或資料頁的位置要發生變化，怎麼能以參考“對於 server 層來說沒有把資料挪動到臨時表”就稱為原地操作呢？

 

alter table t engine=InnoDB，其實隱含的意思是：alter table t engine=innodb,ALGORITHM=inplace;

拷貝表的方式了，用法是：alter table t engine=innodb,ALGORITHM=copy;

 

給表增加全文索引：alter table t add FULLTEXT(field_name);

 

從 MySQL 5.6 版本開始，alter table t engine = InnoDB（也就是 recreate）預設的就是上面圖 4 的流程了；

analyze table t 其實不是重建表，只是對錶的索引資訊做重新統計，沒有修改資料，這個過程中加了 MDL 讀鎖；

optimize table t 等於 recreate+analyze。