目錄

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

1. MySQL表空間回收

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

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

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

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

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

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

檢查是否開啟獨立表空間

show variables like "%per_table%";

設(shè)置獨立表空間
innodb_file_per_table 可通過SET GLOBAL動態(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)閉獨享表空間
innodb_file_per_table=0關(guān)閉獨立的表空間

show variables like "%per_table%";

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

想要將共享表空間轉(zhuǎn)化為獨立表空間有兩種方法：
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ù)會繼續(xù)存放于ibdata1中，新建的表才會使用獨立表空間

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

我們先再來看一下InnoDB中一個索引的示意圖。

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

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

而當(dāng)整個頁從B+樹里面摘掉以后，可以復(fù)用到任何位置。以圖1為例，如果將數(shù)據(jù)頁page A上的所有記錄刪除以后，page A會被標(biāo)記為可復(fù)用。這時候如果要插入一條ID=50的記錄需要使用新頁的時候，page A是可以被復(fù)用的。如果相鄰的兩個數(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ù)用，而沒有被使用的空間，看起來就像是“空洞”。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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