MySQL中如何重建表

更新時間：2023年02月18日 14:43:51 作者：又?欠

這篇文章主要介紹了MySQL中如何重建表問題。具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

1.刪除表數(shù)據(jù)，為什么表文件大小不變

在日常開發(fā)中，你會發(fā)現(xiàn)當(dāng)你刪除表的數(shù)據(jù)后，整個數(shù)據(jù)庫文件大小還是沒有變化。這就是數(shù)據(jù)庫表的空間回收問題。

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

一個 InnoDB 表包含兩部分，即：表結(jié)構(gòu)定義和數(shù)據(jù)。

在 MySQL 8.0 版本以前，表結(jié)構(gòu)是存在以.frm 為后綴的文件里。
而 MySQL 8.0 版本，則已經(jīng)允許把表結(jié)構(gòu)定義放在系統(tǒng)數(shù)據(jù)表中了。因為表結(jié)構(gòu)定義占用的空間很小，所以我們今天主要討論的是表數(shù)據(jù)。

參數(shù)innodb_file_per_table的作用：

配置成on，表示每個InnoDB表數(shù)據(jù)存儲在一個.ibd后綴的文件中。
配置成off，則表示表的數(shù)據(jù)存放在系統(tǒng)共享空間，也就是根據(jù)數(shù)據(jù)字典放在一塊。

兩者的區(qū)別就是

1.如果表數(shù)據(jù)是存儲在系統(tǒng)共享空間中的，即使刪除了表，空間也不會被回收的；
2.如果表數(shù)據(jù)是存儲在單個文件中的，通過drop table命令刪除的時候就會將數(shù)據(jù)文件刪除掉。

show global variables where Variable_name = 'innodb_file_per_table'

從 MySQL 5.6.6 版本開始，它的默認(rèn)值就是 ON 了。

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

2.刪除操作

總所周知MySQL數(shù)據(jù)結(jié)構(gòu)是B+樹，現(xiàn)在假設(shè)刪除掉r4的記錄，InnoDB只會把r4這個記錄標(biāo)記為刪除，如果之后插入一條10-20的記錄，就會復(fù)用這個r4的位置，但是磁盤文件的大小并不會因為標(biāo)記為刪除而減小，類似于假刪除。

當(dāng)整個頁從B+樹里面摘掉以后，可以復(fù)用到任何位置，可以存儲任何新增的數(shù)據(jù)。如果相鄰的兩個數(shù)據(jù)頁利用率都很小，系統(tǒng)就會把這兩個頁上的數(shù)據(jù)合到其中一個頁上，另外一個數(shù)據(jù)頁就被標(biāo)記為可復(fù)用。

如果我們用delete命令把整個表的數(shù)據(jù)刪除呢？結(jié)果就是，所有的數(shù)據(jù)頁都會被標(biāo)記為可復(fù)用。但是磁盤上，文件不會變小。

實際上，delete命令其實只是把記錄的位置，或者數(shù)據(jù)頁標(biāo)記為了“可復(fù)用”，但磁盤文件的大小是不會變的。也就是說，通過delete命令是不能回收表空間的。這些可以復(fù)用，而沒有被使用的空間，看起來就像是“空洞”。

3.新增操作

假設(shè)上圖PageA滿了，我們在新增一條數(shù)據(jù)8會怎樣.

可以看到，由于page A滿了，再插入一個ID是8的數(shù)據(jù)時，就不得不再申請一個新的頁面 page C來保存數(shù)據(jù)了。

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

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

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

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

4.重建表

方式一：新建一張表結(jié)構(gòu)一樣的表

1.可以新建一個與表A結(jié)構(gòu)相同的表B，
2.然后按照主鍵ID遞增的順序，把數(shù)據(jù)一行一行地從表A里讀出來再插入到表B中。由于表B是新建的表，所以表A主鍵索引上的空洞，在表B中就都不存在了。
3.顯然地，表B的主鍵索引更緊湊，數(shù)據(jù)頁的利用率也更高。如果我們把表B作為臨時表，數(shù)據(jù)從表A導(dǎo)入表B的操作完成后，用表B替換A，從效果上看，就起到了收縮表A空間的作用。

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

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

在MySQL 5.5版本之前，這個命令的執(zhí)行流程跟我們前面描述的差不多，區(qū)別只是這個臨時表B不需要你自己創(chuàng)建，MySQL會自動完成轉(zhuǎn)存數(shù)據(jù)、交換表名、刪除舊表的操作。

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

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

而在MySQL 5.6 M 版本開始引入的版 Online DDL，之前的sql語句就變?yōu)榱薬lter table t engine=innodb,ALGORITHM=inplace;

1.建立一個臨時文件，掃描表A主鍵的所有數(shù)據(jù)頁；
2.用數(shù)據(jù)頁中表A的記錄生成B+樹，存儲到臨時文件中；
3.生成臨時文件的過程中，將所有對A的操作記錄在一個日志文件（rowlog）中，對應(yīng)的是圖中state2的狀態(tài)；
4.臨時文件生成后，將日志文件中的操作應(yīng)用到臨時文件，得到一個邏輯數(shù)據(jù)上與表A相同的數(shù)據(jù)文件.
5.用臨時文件替換表A的數(shù)據(jù)文件。

引入Online DDL的區(qū)別就是由于日志文件記錄和重放操作這個功能的存在，這個方案在重建表的過程中，允許對表A做增刪改操作。這也就是Online DDL名字的來源。

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

Online DDL 其實是會先獲取MDL寫鎖, 再退化成MDL讀鎖；但MDL寫鎖持有時間比較短，所以可以稱為Online；而MDL讀鎖，不阻止數(shù)據(jù)增刪查改，但會阻止其它線程修改表結(jié)構(gòu)；

1.拿MDL寫鎖
2.降級成MDL讀鎖
3.真正做DDL
4.升級成MDL寫鎖
5.釋放MDL鎖 1、2、4、5如果沒有鎖沖突，執(zhí)行時間非常短。第3步占用了DDL絕大部分時間，這期間這個表可以正常讀寫數(shù)據(jù)，是因此稱為“online

為什么要退化呢？為了實現(xiàn)Online，MDL讀鎖不會阻塞增刪改操作。

那為什么不干脆直接解鎖呢？為了保護自己，禁止其他線程對這個表同時做DDL。

區(qū)別：

兩者的區(qū)別就是

方式二是根據(jù)源表重建出來的數(shù)據(jù)是存在臨時表中的（tmp:“tmp_table”）,表示的是強拷貝表，是將源表中重建的數(shù)據(jù)存放在一個臨時表中，這個臨時表是在server層中創(chuàng)建的。
方式三是根據(jù)源表重建出來的數(shù)據(jù)是存在臨時文件中的（tmp_file），這個臨時文件是InnoDB創(chuàng)建的，這個過程是在引擎層中發(fā)生的，對于server層來說就相當(dāng)于原地操作的