MySQL數據庫表空間回收的解決

目錄

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

1. MySQL表空間回收

我們經常會發現一個問題，就是把表數據刪除以后發現，數據文件大小并沒有變化，這就是標題中所說的MySQL數據庫表空間回收問題。

這里，我們還是針對MySQL中應用最廣泛的InnoDB引擎展開討論。一個InnoDB表包含兩部分，即：表結構定義和數據。在MySQL 8.0版本以前，表結構是存在以.frm為后綴的文件里。而MySQL 8.0版本，則已經允許把表結構定義放在系統數據表中了。因為表結構定義占用的空間很小，所以我們今天主要討論的是表數據。

2. MySQL表空間設置

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

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

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

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

檢查是否開啟獨立表空間

show variables like "%per_table%";

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

innodb_file_per_table=1

關閉獨享表空間
innodb_file_per_table=0關閉獨立的表空間

show variables like "%per_table%";

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

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

3. MySQL刪除數據流程

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

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

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

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

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

4. MySQL數據頁空洞問題

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

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

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

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

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

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

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

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

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

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

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

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

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

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