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 表空間回收內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！