目錄

• 1. MySQL表空間回收
• 2. MySQL表空間設(shè)置
• 3. MySQL刪除數(shù)據(jù)流程
• 4. MySQL數(shù)據(jù)頁(yè)空洞問(wèn)題

1. MySQL表空間回收

我們經(jīng)常會(huì)發(fā)現(xiàn)一個(gè)問(wèn)題，就是把表數(shù)據(jù)刪除以后發(fā)現(xiàn)，數(shù)據(jù)文件大小并沒(méi)有變化，這就是標(biāo)題中所說(shuō)的MySQL數(shù)據(jù)庫(kù)表空間回收問(wèn)題。

這里，我們還是針對(duì)MySQL中應(yīng)用最廣泛的InnoDB引擎展開(kāi)討論。一個(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ù)。

2. MySQL表空間設(shè)置

MySQL表空間設(shè)置是通過(guò)參數(shù)innodb_file_per_table，表數(shù)據(jù)既可以存在共享表空間里，也可以是單獨(dú)的文件。這個(gè)行為是由參數(shù)innodb_file_per_table控制的：

• 這個(gè)參數(shù)設(shè)置為OFF表示的是，表的數(shù)據(jù)放在系統(tǒng)共享表空間，也就是跟數(shù)據(jù)字典放在一起；
• 這個(gè)參數(shù)設(shè)置為ON表示的是，每個(gè)InnoDB表數(shù)據(jù)存儲(chǔ)在一個(gè)以 .ibd為后綴的文件中。

從MySQL 5.6.6版本開(kāi)始，它的默認(rèn)值就是ON了。這里建議你不論使用MySQL的哪個(gè)版本，都將這個(gè)值設(shè)置為ON。因?yàn)椋粋€(gè)表單獨(dú)存儲(chǔ)為一個(gè)文件更容易管理，而且在你不需要這個(gè)表的時(shí)候，通過(guò)drop table命令，系統(tǒng)就會(huì)直接刪除這個(gè)文件。而如果是放在共享表空間中，即使表刪掉了，空間也是不會(huì)回收的。
所以將innodb_file_per_table i 設(shè)置為 設(shè)ON，是推薦做法，我們接下來(lái)的討論都是基于這個(gè) ，設(shè)置展開(kāi)的。

我們?cè)趧h除整個(gè)表的時(shí)候，可以使用drop table命令回收表空間。但是，我們遇到的更多的刪除數(shù)據(jù)的場(chǎng)景是刪除某些行，這時(shí)就遇到了我們文章開(kāi)頭的問(wèn)題：表中的數(shù)據(jù)被刪除了，但是表空間卻沒(méi)有被回收。

檢查是否開(kāi)啟獨(dú)立表空間

show variables like "%per_table%";

設(shè)置獨(dú)立表空間
innodb_file_per_table 可通過(guò)SET GLOBAL動(dòng)態(tài)的修改為ON或OFF，也可以在my.cnf中做永久性修改，在my.cnf中修改后生效的話需要重啟mysqld服務(wù)。
1.innodb_file_per_table在my.cnf中[mysqld]下設(shè)置，開(kāi)啟方法

innodb_file_per_table=1

關(guān)閉獨(dú)享表空間
innodb_file_per_table=0關(guān)閉獨(dú)立的表空間

show variables like "%per_table%";

如果啟用了innodb_file_per_talbe參數(shù)，需要注意的是每張表的表空間內(nèi)存放的只是數(shù)據(jù)、索引和插入緩沖Bitmap頁(yè)，其他數(shù)據(jù)如：回滾信息、插入緩沖索引頁(yè)、系統(tǒng)事物信息、二次寫緩沖（Double write buffer）等還是放在原來(lái)的共享表空間內(nèi)。同時(shí)說(shuō)明了一個(gè)問(wèn)題：即使啟用了innodb_file_per_table參數(shù)共享表空間還是會(huì)不斷的增加其大小的。
注意：動(dòng)態(tài)修改后僅對(duì)后續(xù)操作生效，如原來(lái)為共享表空間，動(dòng)態(tài)修改為獨(dú)立表空間后僅新建的表為獨(dú)立表空間。

想要將共享表空間轉(zhuǎn)化為獨(dú)立表空間有兩種方法：
1.先邏輯備份，然后修改配置文件my.cnf中的參數(shù)innodb_file_per_table參數(shù)為1，重啟服務(wù)后將邏輯備份導(dǎo)入即可。
2.修改配置文件my.cnf中的參數(shù)innodb_file_per_table參數(shù)為1，重啟服務(wù)后將需要修改的所有innodb表都執(zhí)行一遍：alter table table_name engine=innodb;
使用第二種方式修改后，原來(lái)庫(kù)中的表中的數(shù)據(jù)會(huì)繼續(xù)存放于ibdata1中，新建的表才會(huì)使用獨(dú)立表空間

3. MySQL刪除數(shù)據(jù)流程

我們先再來(lái)看一下InnoDB中一個(gè)索引的示意圖。

如果刪除某條記錄，則該記錄空間可以復(fù)用，比如我們要?jiǎng)h掉R4這個(gè)記錄，InnoDB引擎只會(huì)把R4這個(gè)記錄標(biāo)記為刪除。如果之后要再插入一個(gè)ID在300和600之間的記錄時(shí)，可能會(huì)復(fù)用這個(gè)位置。但是，磁盤文件的大小并不會(huì)縮小。

如果刪除整個(gè)數(shù)據(jù)頁(yè)，則整個(gè)數(shù)據(jù)頁(yè)就可以被復(fù)用。但是，數(shù)據(jù)頁(yè)的復(fù)用跟記錄的復(fù)用是不同的。 數(shù)記錄的復(fù)用，只限于符合范圍條件的數(shù)據(jù)。比如上面的這個(gè)例子，R4這條記錄被刪除后，如果插入一個(gè)ID是400的行，可以直接復(fù)用這個(gè)空間。但如果插入的是一個(gè)ID是800的行，就不能復(fù)用這個(gè)位置了。

而當(dāng)整個(gè)頁(yè)從B+樹(shù)里面摘掉以后，可以復(fù)用到任何位置。以圖1為例，如果將數(shù)據(jù)頁(yè)page A上的所有記錄刪除以后，page A會(huì)被標(biāo)記為可復(fù)用。這時(shí)候如果要插入一條ID=50的記錄需要使用新頁(yè)的時(shí)候，page A是可以被復(fù)用的。如果相鄰的兩個(gè)數(shù)據(jù)頁(yè)利用率都很小，系統(tǒng)就會(huì)把這兩個(gè)頁(yè)上的數(shù)據(jù)合到其中一個(gè)頁(yè)上，另外一個(gè)數(shù)據(jù)頁(yè)就被標(biāo)記為可復(fù)用。

進(jìn)一步地，如果我們用delete命令把整個(gè)表的數(shù)據(jù)刪除呢？結(jié)果就是，所有的數(shù)據(jù)頁(yè)都會(huì)被標(biāo)記為可復(fù)用。但是磁盤上，文件不會(huì)變小。所以delete命令其實(shí)只是把記錄的位置，或者數(shù)據(jù)頁(yè)標(biāo)記為了“可復(fù)用”，但磁盤文件的大小是不會(huì)變的。也就是說(shuō)，通過(guò)delete命令是不能回收表空間的。這些可以復(fù)用，而沒(méi)有被使用的空間，看起來(lái)就像是“空洞”。

4. MySQL數(shù)據(jù)頁(yè)空洞問(wèn)題

實(shí)際上，不止是刪除數(shù)據(jù)會(huì)造成空洞，插入數(shù)據(jù)也會(huì)。如果數(shù)據(jù)是按照索引遞增順序插入的，那么索引是緊湊的。但如果數(shù)據(jù)是隨機(jī)插入的，就可能造成索引的數(shù)據(jù)頁(yè)分裂。
假設(shè)圖1中page A已經(jīng)滿了，這時(shí)我要再插入一行數(shù)據(jù)，會(huì)怎樣呢？

可以看到，由于page A滿了，再插入一個(gè)ID是550的數(shù)據(jù)時(shí)，就不得不再申請(qǐng)一個(gè)新的頁(yè)面page B來(lái)保存數(shù)據(jù)了。頁(yè)分裂完成后，page A的末尾就留下了空洞（注意：實(shí)際上，可能不止1個(gè)記錄的位置是空洞）。

另外，更新索引上的值，可以理解為刪除一個(gè)舊的值，再插入一個(gè)新值。不難理解，這也是會(huì)造成空洞的。也就是說(shuō)，經(jīng)過(guò)大量增刪改的表，都是可能是存在空洞的。所以，如果能夠把這些空洞去掉，就能達(dá)到收縮表空間的目的。而重建表，就可以達(dá)到這樣的目的。

MySQL空洞問(wèn)題解決方案
重建表，試想一下，如果你現(xiàn)在有一個(gè)表A，需要做空間收縮，為了把表中存在的空洞去掉，你可以怎么做呢？

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

這里，你可以使用alter table A engine=InnoDB命令來(lái)重建表。在MySQL 5.5版本之前，這個(gè)命令的執(zhí)行流程跟我們前面描述的差不多，區(qū)別只是這個(gè)臨時(shí)表B不需要你自己創(chuàng)建，MySQL會(huì)自動(dòng)完成轉(zhuǎn)存數(shù)據(jù)、交換表名、刪除舊表的操作。

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

而在MySQL 5.6 M 版本開(kāi)始引入的 版 Online DDL O ，對(duì)這個(gè)操作流程做了優(yōu)化。
我給你簡(jiǎn)單描述一下引入了Online DDL之后，重建表的流程：

• 建立一個(gè)臨時(shí)文件，掃描表A主鍵的所有數(shù)據(jù)頁(yè)；
• 用數(shù)據(jù)頁(yè)中表A的記錄生成B+樹(shù)，存儲(chǔ)到臨時(shí)文件中；
• 生成臨時(shí)文件的過(guò)程中，將所有對(duì)A的操作記錄在一個(gè)日志文件rowlog中，對(duì)應(yīng)的是圖中state2的狀態(tài)；
• 臨時(shí)文件生成后，將日志文件中的操作應(yīng)用到臨時(shí)文件，得到一個(gè)邏輯數(shù)據(jù)上與表A相同的數(shù)據(jù)文件，對(duì)應(yīng)的就是圖中state3的狀態(tài)；
• 用臨時(shí)文件替換表A的數(shù)據(jù)文件。

可以看到，與圖3過(guò)程的不同之處在于，由于日志文件記錄和重放操作這個(gè)功能的存在，這個(gè)方案在重建表的過(guò)程中，允許對(duì)表A做增刪改操作。這也就是Online DDL名字的來(lái)源。

確實(shí)，圖4的流程中，alter語(yǔ)句在啟動(dòng)的時(shí)候需要獲取MDL寫鎖，但是這個(gè)寫鎖在真正拷貝數(shù)據(jù)之前就退化成讀鎖了。

為什么要退化呢？為了實(shí)現(xiàn)Online，MDL讀鎖不會(huì)阻塞增刪改操作。那為什么不干脆直接解鎖呢？為了保護(hù)自己，禁止其他線程對(duì)這個(gè)表同時(shí)做DDL。而對(duì)于一個(gè)大表來(lái)說(shuō)，Online DDL最耗時(shí)的過(guò)程就是拷貝數(shù)據(jù)到臨時(shí)表的過(guò)程，這個(gè)步驟的執(zhí)行期間可以接受增刪改操作。所以，相對(duì)于整個(gè)DDL過(guò)程來(lái)說(shuō)，鎖的時(shí)間非常短。對(duì)業(yè)務(wù)來(lái)說(shuō)，就可以認(rèn)為是Online的。

需要補(bǔ)充說(shuō)明的是，上述的這些重建方法都會(huì)掃描原表數(shù)據(jù)和構(gòu)建臨時(shí)文件。對(duì)于很大的表來(lái)說(shuō)，這個(gè)操作是很消耗IO和CPU資源的。因此，如果是線上服務(wù)，你要很小心地控制操作時(shí)間。如果想要比較安全的操作的話，我推薦你使用GitHub開(kāi)源的gh-ost來(lái)做。

到此這篇關(guān)于MySQL數(shù)據(jù)庫(kù)表空間回收的解決的文章就介紹到這了,更多相關(guān)MySQL 表空間回收內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！