目錄

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

1. MySQL表空間回收

我們經(jīng)常會(huì)發(fā)現(xiàn)一個(gè)問題，就是把表數(shù)據(jù)刪除以后發(fā)現(xiàn)，數(shù)據(jù)文件大小并沒有變化，這就是標(biāo)題中所說的MySQL數(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ù)。

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

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

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

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

show variables like "%per_table%";

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

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頁，其他數(shù)據(jù)如：回滾信息、插入緩沖索引頁、系統(tǒng)事物信息、二次寫緩沖（Double write buffer）等還是放在原來的共享表空間內(nèi)。同時(shí)說明了一個(gè)問題：即使啟用了innodb_file_per_table參數(shù)共享表空間還是會(huì)不斷的增加其大小的。
注意：動(dòng)態(tài)修改后僅對后續(xù)操作生效，如原來為共享表空間，動(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;
使用第二種方式修改后，原來庫中的表中的數(shù)據(jù)會(huì)繼續(xù)存放于ibdata1中，新建的表才會(huì)使用獨(dú)立表空間

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

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

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

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

4. MySQL數(shù)據(jù)頁空洞問題

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

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

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

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

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

這里，你可以使用alter table A engine=InnoDB命令來重建表。在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的。

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

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

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

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

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

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

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