前言

MySQL 數(shù)據(jù)庫在運行過程中，隨著時間的推移，可能會出現(xiàn)空間碎片的問題?？臻g碎片是指數(shù)據(jù)庫表中不再使用的空間，但由于各種原因，這些空間并沒有被有效地回收和再利用，從而導致數(shù)據(jù)庫文件占用的磁盤空間比實際存儲的數(shù)據(jù)要大。

1. 空間碎片如何產(chǎn)生

MySQL InnoDB 引擎中，刪除一條記錄分為兩種情況，一種稱為刪除標記（delete mark）僅在記錄頭部中設置 DELETED_FLAG 標記，記錄鏈中依然保留該記錄。另一種是真正刪除，將記錄從記錄鏈中移除，記錄占用的空間可被重用。

如下圖，Record 2 被 delete mark 后，還在記錄鏈表中。這行記錄占用的空間可以理解為是空間空洞，空間空洞多起來就成為空間碎片。

標記刪除導致的空間空洞，會被重新利用，但是依然可能會造成空間浪費。

如果頁面內的未使用空間不足，無法容納新插入的數(shù)據(jù)，但是碎片空間中有足夠的空間，則可以對頁面進行碎片回收后，再插入新的數(shù)據(jù)。碎片回收時，會先在內存中申請一個空閑頁面，將存在碎片空間的舊頁面中的記錄依次插入到新頁面，然后釋放舊頁面。

被動觸發(fā)空間碎片回收條件，是頁面空間碎片中有足夠的空間，可以容納新插入的記錄，那如果無法容納，就需要新申請頁面。在大規(guī)模連續(xù)刪除過的數(shù)據(jù)的表上，寫入數(shù)據(jù)時，表空間可能不會明顯增長或者不會增長。

除了 Delete 會產(chǎn)生空間空洞外，Update 語句也會引起空間空洞問題，比如修改 varchar 變長字符串類型字段，改短一些的時候就會出現(xiàn)非常小的空洞，改長的話就有可能因為頁面空間不足，導致把 Record 遷移到其他頁面中去。

2. 空間碎片如何查看

MySQL 系統(tǒng)表中，可以查看空間碎片情況。下方 SQL 是統(tǒng)計庫粒度空間統(tǒng)計信息，其中 FREE_MB 為空間碎片大小。

SELECT TABLE_SCHEMA,
       round(SUM(data_length + index_length + DATA_FREE) / 1024 / 1024, 2) AS TOTAL_MB,
       round(SUM(data_length) / 1024 / 1024, 2)                            AS DATA_MB,
       round(SUM(index_length) / 1024 / 1024, 2)                           AS INDEX_MB,
       round(SUM(DATA_FREE) / 1024 / 1024, 2)                              AS FREE_MB,
       COUNT(*)                                                            AS TABLES
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'INFORMATION_SCHEMA', 'performance_schema')
GROUP BY TABLE_SCHEMA
ORDER BY 2 DESC;

下方為查看指定庫和指定表，空間使用情況的 SQL 語句。其中 FREE_MB 表示碎片大小 FREE_PCT 表示碎片率。

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       round((data_length + index_length + DATA_FREE) / 1024 / 1024, 2) AS TOTAL_MB,
       round(data_length / 1024 / 1024, 2)                              AS DATA_MB,
       round(index_length / 1024 / 1024, 2)                             AS INDEX_MB,
       round(DATA_FREE / 1024 / 1024, 2)                                AS FREE_MB,
       CONCAT(ROUND(DATA_FREE / data_length, 2), ' %')                  AS FREE_PCT
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA = '數(shù)據(jù)庫名'
  and TABLE_NAME = '表名'
ORDER BY TOTAL_MB DESC;

3. 空間碎片如何回收

MySQL 中可以使用下方命令回收空間碎片，支持 online DDL。

-- 以下 SQL 效果相同
ALTER TABLE tbl_name ENGINE=INNODB;

-- 有審核平臺有規(guī)范不允許修改存儲引擎，可以使用下方 SQL
alter table tbl_name force, ALGORITHM=INPLACE, LOCK=NONE

表中碎片多大需要回收呢？這里提供一個參考標準：單表大于 6G 且碎片率大于 30% 需要清理空間碎片。

Tips: 空間碎片回收，是一個代價比較高的操作，雖然支持 Online DDL，但是依然會帶來額外的負載，建議業(yè)務低峰執(zhí)行。如果是一套 MySQL 集群，需要注意主從延遲問題。

對于一些需要周期刪除的日志表，可以使用 MySQL 中的分區(qū)表來管理，需要清理一批數(shù)據(jù)的時候，可以用 partition truncate 的方式進行清理，磁盤空間也能直接釋放掉。

后記

總結一下，InnoDB 引擎中 Delete 和 Update 都會產(chǎn)生空間空洞，積累起來就會出現(xiàn)空間碎片問題，MySQL 有對應的回收算法讓空間空洞會重新利用起來，但是很難保證充分利用?？臻g碎片可以使用重建表的方式進行回收。

到此這篇關于MySQL 空間碎片的查看與回收 的文章就介紹到這了,更多相關MySQL 空間碎片內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論