MySQL中如何重建表

1.刪除表資料，為什麼表檔案大小不變

在日常開發中，你會發現當你刪除表的資料後，整個資料庫檔案大小還是沒有變化。這就是資料庫表的空間回收問題。

首先我們還是針對 MySQL 中應用最廣泛的 InnoDB 引擎展開討論。

一個 InnoDB 表包含兩部分，即：表結構定義和資料。

• 在 MySQL 8.0 版本以前，表結構是存在以.frm 為字尾的檔案裡。
• 而 MySQL 8.0 版本，則已經允許把表結構定義放在系統資料表中了。因為表結構定義佔用的空間很小，所以我們今天主要討論的是表資料。

引數innodb_file_per_table的作用：

• 設定成on，表示每個InnoDB表資料儲存在一個.ibd字尾的檔案中。
• 設定成off，則表示表的資料存放在系統共用空間，也就是根據資料字典放在一塊。

兩者的區別就是

• 1.如果表資料是儲存在系統共用空間中的，即使刪除了表，空間也不會被回收的；
• 2.如果表資料是儲存在單個檔案中的，通過drop table命令刪除的時候就會將資料檔案刪除掉。

show global variables where Variable_name = 'innodb_file_per_table'

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

• 因為，一個表單獨儲存為一個檔案更容易管理，而且在你不需要這個表的時候，通過 drop table 命令，系統就會直接刪除這個檔案。
• 而如果是放在共用表空間中，即使表刪掉了，空間也是不會回收的。

2.刪除操作

總所周知MySQL資料結構是B+樹，現在假設刪除掉r4的記錄，InnoDB只會把r4這個記錄標記為刪除，如果之後插入一條10-20的記錄，就會複用這個r4的位置，但是磁碟檔案的大小並不會因為標記為刪除而減小，類似於假刪除。

當整個頁從B+樹裡面摘掉以後，可以複用到任何位置，可以儲存任何新增的資料。如果相鄰的兩個資料頁利用率都很小，系統就會把這兩個頁上的資料合到其中一個頁上，另外一個資料頁就被標記為可複用。

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

實際上，delete命令其實只是把記錄的位置，或者資料頁標記為了“可複用”，但磁碟檔案的大小是不會變的。也就是說，通過delete命令是不能回收表空間的。這些可以複用，而沒有被使用的空間，看起來就像是“空洞”。

3.新增操作

假設上圖PageA滿了，我們在新增一條資料8會怎樣.

可以看到，由於page A滿了，再插入一個ID是8的資料時，就不得不再申請一個新的頁面 page C來儲存資料了。

頁分裂完成後，page A的末尾就留下了空洞（注意：實際上，可能不止1 個記錄的位置是空洞）。

另外，更新索引上的值，可以理解為刪除一箇舊的值，再插入一個新值。不難理解，這也是會造 成空洞的。

也就是說，經過大量增刪改的表，都是可能是存在空洞的。

所以，如果能夠把這些空洞去掉，就 能達到收縮表空間的目的。 而重建表，就可以達到這樣的目的。

4.重建表

方式一：新建一張表結構一樣的表

• 1.可以新建一個與表A結構相同的表B，
• 2.然後按照主鍵ID遞增的順序，把資料一行一行地從表A裡讀出來再插入到表B中。由於表B是新建的表，所以表A主鍵索引上的空洞，在表B中就都不存在了。
• 3.顯然地，表B的主鍵 索引更緊湊，資料頁的利用率也更高。如果我們把表B作為臨時表，資料從表A匯入表B的操作完 成後，用表B替換A，從效果上看，就起到了收縮表A空間的作用。

方式二：alter table t engine=innodb,ALGORITHM=copy;（DDL）

可以使用**alter table t engine=innodb,ALGORITHM=copy;**命令來重建表。

在MySQL 5.5版本之前，這個命 令的執行流程跟我們前面描述的差不多，區別只是這個臨時表B不需要你自己建立，MySQL會自 動完成轉存資料、交換表名、刪除舊錶的操作。

顯然，花時間最多的步驟是往臨時表插入資料的過程，如果在這個過程中，有新的資料要寫入到 表A的話，就會造成資料丟失。因此，在整個DDL過程中，表A中不能有更新。也就是說，這個 DDL不是Online的。

方式三：alter table t engine=innodb,ALGORITHM=inplace;（Online DDL）

而在MySQL 5.6 M 版本開始引入的 版 Online DDL，之前的sql語句就變為了alter table t engine=innodb,ALGORITHM=inplace;

• 1.建立一個臨時檔案，掃描表A主鍵的所有資料頁；
• 2.用資料頁中表A的記錄生成B+樹，儲存到臨時檔案中；
• 3.生成臨時檔案的過程中，將所有對A的操作記錄在一個紀錄檔檔案（rowlog）中，對應的是圖 中state2的狀態；
• 4.臨時檔案生成後，將紀錄檔檔案中的操作應用到臨時檔案，得到一個邏輯資料上與表A相同的資料檔案.
• 5.用臨時檔案替換表A的資料檔案。

引入Online DDL的區別就是由於紀錄檔檔案記錄和重放操作這個功能的存在，這個方 案在重建表的過程中，允許對錶A做增刪改操作。這也就是Online DDL名字的來源。

在執行 alter table t engine=innodb,ALGORITHM=inplace; 語句的時候，需要獲取到MDL鎖，但是這個寫鎖在真正拷貝資料 之前就退化成讀鎖了。

Online DDL 其實是會先獲取MDL寫鎖, 再退化成MDL讀鎖；但MDL寫鎖持有時間比較短，所以可以稱為Online； 而MDL讀鎖，不阻止資料增刪查改，但會阻止其它執行緒修改表結構；

• 1.拿MDL寫鎖
• 2.降級成MDL讀鎖
• 3.真正做DDL
• 4.升級成MDL寫鎖
• 5.釋放MDL鎖 1、2、4、5如果沒有鎖衝突，執行時間非常短。第3步佔用了DDL絕大部分時間，這期間這個表可以正常讀寫資料，是因此稱為“online

為什麼要退化呢？為了實現Online，MDL讀鎖不會阻塞增刪改操作。

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

區別：

兩者的區別就是

• 方式二是根據源表重建出來的資料是存在臨時表中的（tmp:“tmp_table”）,表示的是強拷貝表，是將源表中重建的資料存放在一個臨時表中，這個臨時表是在server層中建立的。
• 方式三是根據源表重建出來的資料是存在臨時檔案中的（tmp_file），這個臨時檔案是InnoDB建立的，這個過程是在引擎層中發生的，對於server層來說就相當於原地操作的

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。