MySQL中如何重建表

目錄

• 1.刪除表數據，為什么表文件大小不變
• 2.刪除操作
• 3.新增操作
• 4.重建表
• 總結

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層來說就相當于原地操作的

總結

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