前言：
在 MySQL 數據庫中咧七，隨著數據的增刪改操作递宅，表空間可能會出現碎片化，這不僅會占用額外的存儲空間锉屈，還可能降低表的掃描效率荤傲，特別是一些大表，在進行數據清理后會產生大量的碎片部念。本篇文章我們一起來學習下如何進行碎片回收以及相關注意點弃酌。
查看表碎片大小
一般 MySQL 數據庫都是開啟 innodb_file_per_table 參數的氨菇，這代表每個表使用獨立的表空間，即每個表的數據及索引存儲在一個獨立的 表名.ibd 文件里妓湘，如果某個表有大量碎片查蓉，ibd 文件占用磁盤空間會非常大，碎片回收掉后 ibd 文件也會顯著減小榜贴。
首先我們要確定哪些表需要進行回收碎片操作豌研，MySQL 系統(tǒng)表 information.TABLES 中的 DATA_FREE 字段顯示的是可用的空閑空間量（單位：字節(jié)），它可以幫助你估計碎片的程度唬党，如果 DATA_FREE 很大鹃共，那么這個表的碎片量一般也比較大。
如果某個表比較大或者變動特別頻繁驶拱，你可以看下這個表的 DATA_FREE 大小霜浴，看是否需要回收碎片，也可以從系統(tǒng)表中篩選出碎片量大于 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 基本能覆蓋日常所需場景税迷，想要回收表碎片的話永丝，可以按照不同場景執(zhí)行相關 SQL 來查找，比如是想回收碎片比較多的表還是碎片率比較大的表箭养。找到需要回收碎片的表后慕嚷，下一步就是評估進行正式回收碎片操作了。
回收表碎片
對于 InnoDB 存儲引擎的表毕泌，可以用 optimize table table_name; 或者 alter table table_name engine = innodb; 兩種方式進行回收喝检。
OPTIMIZE TABLE 對于 InnoDB 表來說，實際上會執(zhí)行一個重建表的操作懈词，這與 ALTER TABLE ... FORCE 類似蛇耀。這個過程會重新組織表的數據和索引，更新索引統(tǒng)計信息坎弯，并釋放聚簇索引中未使用的空間纺涤。它可以在一定程度上減少表占用的空間，并提高訪問表時的 IO 效率抠忘。OPTIMIZE TABLE 對于常規(guī)的和分區(qū)的 InnoDB 表使用 online DDL 撩炊，這減少了并發(fā)的 DML 操作的停機時間。OPTIMIZE TABLE 僅在操作的準備階段和提交階段短暫地獲取獨占的表鎖崎脉，在準備階段拧咳，元數據會被更新并且創(chuàng)建一個中間表，在提交階段囚灼，將提交表元數據更改骆膝。
ALTER TABLE ... ENGINE = InnoDB 命令實際上是將表的存儲引擎重新設置為 InnoDB 祭衩。在這個過程中，MySQL 會對表進行重建阅签，會回收掉未使用的空間掐暮。在 5.6 及以后的版本中，這個操作會使用 Online DDL 政钟，減少對并發(fā) DML 操作的影響路克。它通過創(chuàng)建一個臨時文件，掃描表的數據頁养交，并將操作記錄在日志文件中精算，最后將臨時文件替換原表的數據文件。此方法只適用于 InnoDB 引擎表碎连。
總的來說灰羽，兩者都可以用于整理 InnoDB 表的碎片，但是 OPTIMIZE TABLE 更側重于專門的碎片整理和空間回收鱼辙，還可以用于其他存儲引擎谦趣。而 ALTER TABLE ... ENGINE=InnoDB 主要是更改存儲引擎屬性時附帶的一些空間優(yōu)化。在實際使用中座每，可以根據具體情況選擇合適的方式來回收 InnoDB 表的空間。
需要注意的一點是摘悴，盡管二者操作都是 Online DDL 峭梳，但回收操作還是盡量在業(yè)務低峰期執(zhí)行，特別是大表蹂喻，回收操作還是需要一段時間的葱椭。除此之外，要確保有足夠的磁盤空間進行回收操作口四，因為執(zhí)行期間會生成臨時文件孵运，進一步占用磁盤空間，執(zhí)行完成后才會刪除臨時文件蔓彩。例如你要對一個 200G 的表進行回收操作治笨，預估能回收掉 50G 碎片，則要確保磁盤空間至少剩余 150G赤嚼，一般建議剩余空間在表大小以上。如果你的磁盤剩余空間不足則無法完成回收操作。
總結：
本篇文章介紹了如何查看 InnoDB 表的碎片以及如何進行回收昔头。生產環(huán)境中洛口，建議定期巡檢 MySQL 系統(tǒng)中的表碎片，并在業(yè)務低峰期執(zhí)行回收操作蹂空「┟龋回收表碎片是一種良好的數據庫維護實踐果录，可以提高數據庫查詢性能，同時也可以提高存儲效率和管理簡便性咐熙。
