MySQL 回收表碎片實踐教程

前言：

在 MySQL 資料庫中，隨著資料的增刪改操作，表空間可能會出現碎片化，這不僅會佔用額外的儲存空間，還可能降低表的掃描效率，特別是一些大表，在進行資料清理後會產生大量的碎片。本篇文章我們一起來學習下如何進行碎片回收以及相關注意點。

檢視表碎片大小

一般 MySQL 資料庫都是開啟 innodb_file_per_table 引數的，這代表每個表使用獨立的表空間，即每個表的資料及索引儲存在一個獨立的 表名.ibd 檔案裡，如果某個表有大量碎片，ibd 檔案佔用磁碟空間會非常大，碎片回收掉後 ibd 檔案也會顯著減小。

首先我們要確定哪些表需要進行回收碎片操作，MySQL 系統表 information.TABLES 中的 DATA_FREE 欄位顯示的是可用的空閒空間量（單位：位元組），它可以幫助你估計碎片的程度，如果 DATA_FREE 很大，那麼這個表的碎片量一般也比較大。

如果某個表比較大或者變動特別頻繁，你可以看下這個表的 DATA_FREE 大小，看是否需要回收碎片，也可以從系統表中篩選出碎片量大於 100M 的表或者碎片率達到多少的表，這類表一般是需要進行碎片回收的。下面幾條查詢 SQL 可能對你有所幫助：

# 檢視某個表的詳細資訊（包含碎片大小）
select 
table_schema as '資料庫',
TABLE_NAME as '表名',
sys.FORMAT_BYTES(data_length) as '資料容量',
sys.FORMAT_BYTES(index_length) as '索引容量',
sys.FORMAT_BYTES(data_length+index_length) as '總容量' ,
sys.FORMAT_BYTES(DATA_FREE) as '碎片大小'
from information_schema.tables where TABLE_SCHEMA = 'db_name' and TABLE_NAME = 'tb_name';

# 按碎片大小排序
SELECT t.TABLE_SCHEMA,
       t.TABLE_NAME,
       t.DATA_FREE,
       sys.FORMAT_BYTES(DATA_LENGTH+INDEX_LENGTH) as '總容量' ,
       sys.FORMAT_BYTES(DATA_FREE) as '碎片大小'
FROM information_schema.tables t
WHERE
    t.table_schema NOT IN (
        'mysql',
        'information_schema',
        'performance_schema',
        'sys'
    )
AND t.table_type = 'BASE TABLE' ORDER BY `DATA_FREE` DESC LIMIT 20

# 檢視碎片率大於0.3的表
select 
table_schema as '資料庫',
TABLE_NAME as '表名',
sys.FORMAT_BYTES(DATA_LENGTH+INDEX_LENGTH) as '總容量' ,
sys.FORMAT_BYTES(DATA_FREE) as '碎片大小',
(DATA_FREE / (data_length + index_length)) AS '碎片率'
FROM information_schema.tables t
WHERE
	t.table_schema NOT IN (
		'mysql',
		'information_schema',
		'performance_schema',
		'sys'
	) AND t.table_type = 'BASE TABLE' and (DATA_FREE / (data_length + index_length)) > 0.3

以上三條 SQL 基本能覆蓋日常所需場景，想要回收表碎片的話，可以按照不同場景執行相關 SQL 來查詢，比如是想回收碎片比較多的表還是碎片率比較大的表。找到需要回收碎片的表後，下一步就是評估進行正式回收碎片操作了。

回收表碎片

對於 InnoDB 儲存引擎的表，可以用 optimize table table_name; 或者 alter table table_name engine = innodb; 兩種方式進行回收。

OPTIMIZE TABLE 對於 InnoDB 表來說，實際上會執行一個重建表的操作，這與 ALTER TABLE ... FORCE 類似。這個過程會重新組織表的資料和索引，更新索引統計資訊，並釋放聚簇索引中未使用的空間。它可以在一定程度上減少表佔用的空間，並提高訪問表時的 IO 效率。OPTIMIZE TABLE 對於常規的和分割槽的 InnoDB 表使用 online DDL ，這減少了併發的 DML 操作的停機時間。OPTIMIZE TABLE 僅在操作的準備階段和提交階段短暫地獲取獨佔的表鎖，在準備階段，後設資料會被更新並且建立一箇中間表，在提交階段，將提交表後設資料更改。

ALTER TABLE ... ENGINE = InnoDB 命令實際上是將表的儲存引擎重新設定為 InnoDB 。在這個過程中，MySQL 會對錶進行重建，會回收掉未使用的空間。在 5.6 及以後的版本中，這個操作會使用 Online DDL ，減少對併發 DML 操作的影響。它透過建立一個臨時檔案，掃描表的資料頁，並將操作記錄在日誌檔案中，最後將臨時檔案替換原表的資料檔案。此方法只適用於 InnoDB 引擎表。

總的來說，兩者都可以用於整理 InnoDB 表的碎片，但是 OPTIMIZE TABLE 更側重於專門的碎片整理和空間回收，還可以用於其他儲存引擎。而 ALTER TABLE ... ENGINE=InnoDB 主要是更改儲存引擎屬性時附帶的一些空間最佳化。在實際使用中，可以根據具體情況選擇合適的方式來回收 InnoDB 表的空間。

需要注意的一點是，儘管二者操作都是 Online DDL ，但回收操作還是儘量在業務低峰期執行，特別是大表，回收操作還是需要一段時間的。除此之外，要確保有足夠的磁碟空間進行回收操作，因為執行期間會生成臨時檔案，進一步佔用磁碟空間，執行完成後纔會刪除臨時檔案。例如你要對一個 200G 的表進行回收操作，預估能回收掉 50G 碎片，則要確保磁碟空間至少剩餘 150G，一般建議剩餘空間在表大小以上。如果你的磁碟剩餘空間不足則無法完成回收操作。

總結：

本篇文章介紹瞭如何檢視 InnoDB 表的碎片以及如何進行回收。生產環境中，建議定期巡檢 MySQL 系統中的表碎片，並在業務低峰期執行回收操作。回收表碎片是一種良好的資料庫維護實踐，可以提高資料庫查詢效能，同時也可以提高儲存效率和管理簡便性。