前言

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

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

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

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

標(biāo)記刪除導(dǎo)致的空間空洞，會(huì)被重新利用，但是依然可能會(huì)造成空間浪費(fèi)。

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

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

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

2. 空間碎片如何查看

MySQL 系統(tǒng)表中，可以查看空間碎片情況。下方 SQL 是統(tǒng)計(jì)庫粒度空間統(tǒng)計(jì)信息，其中 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;

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

表中碎片多大需要回收呢？這里提供一個(gè)參考標(biāo)準(zhǔn)：?jiǎn)伪泶笥?6G 且碎片率大于 30% 需要清理空間碎片。

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

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

后記

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

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

最新評(píng)論