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

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

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

一個(gè) 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ù)表中了。因?yàn)楸斫Y(jié)構(gòu)定義占用的空間很小，所以我們今天主要討論的是表數(shù)據(jù)。

參數(shù)innodb_file_per_table的作用：

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

兩者的區(qū)別就是

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

show global variables where Variable_name = 'innodb_file_per_table'

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

• 因?yàn)椋粋€(gè)表單獨(dú)存儲為一個(gè)文件更容易管理，而且在你不需要這個(gè)表的時(shí)候，通過 drop table 命令，系統(tǒng)就會(huì)直接刪除這個(gè)文件。
• 而如果是放在共享表空間中，即使表刪掉了，空間也是不會(huì)回收的。

2.刪除操作

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

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

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

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

3.新增操作

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

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

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

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

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

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

4.重建表

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

• 1.可以新建一個(gè)與表A結(jié)構(gòu)相同的表B，
• 2.然后按照主鍵ID遞增的順序，把數(shù)據(jù)一行一行地從表A里讀出來再插入到表B中。由于表B是新建的表，所以表A主鍵索引上的空洞，在表B中就都不存在了。
• 3.顯然地，表B的主鍵 索引更緊湊，數(shù)據(jù)頁的利用率也更高。如果我們把表B作為臨時(shí)表，數(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版本之前，這個(gè)命 令的執(zhí)行流程跟我們前面描述的差不多，區(qū)別只是這個(gè)臨時(shí)表B不需要你自己創(chuàng)建，MySQL會(huì)自 動(dòng)完成轉(zhuǎn)存數(shù)據(jù)、交換表名、刪除舊表的操作。

顯然，花時(shí)間最多的步驟是往臨時(shí)表插入數(shù)據(jù)的過程，如果在這個(gè)過程中，有新的數(shù)據(jù)要寫入到 表A的話，就會(huì)造成數(shù)據(jù)丟失。因此，在整個(gè)DDL過程中，表A中不能有更新。也就是說，這個(gè) 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.建立一個(gè)臨時(shí)文件，掃描表A主鍵的所有數(shù)據(jù)頁；
• 2.用數(shù)據(jù)頁中表A的記錄生成B+樹，存儲到臨時(shí)文件中；
• 3.生成臨時(shí)文件的過程中，將所有對A的操作記錄在一個(gè)日志文件（rowlog）中，對應(yīng)的是圖 中state2的狀態(tài)；
• 4.臨時(shí)文件生成后，將日志文件中的操作應(yīng)用到臨時(shí)文件，得到一個(gè)邏輯數(shù)據(jù)上與表A相同的數(shù)據(jù)文件.
• 5.用臨時(shí)文件替換表A的數(shù)據(jù)文件。

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

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

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

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

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

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

區(qū)別：

兩者的區(qū)別就是

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

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評論