当前位置：首页 > news >正文

MySQL碎片清理

news 2025/8/7 10:14:17

为什么产生？

经过大量增删改的表，都可能存在碎片

MySQL数据结构是B+树，
删除某一记录，只会标记为删除，后续插入一条该区间的记录，就会复用这个位置。
删除整个数据页的记录，则整个页标记为“可复用”，但磁盘文件的大小是不会变的。

产生碎片就要定期清理

官方建议不要频繁(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可。
建议：碎片空间大或者占比>50% 再做整理。

# 查询现状碎片情况
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, AUTO_INCREMENT, TABLE_COMMENT, DATA_FREE, ROUND(data_free / 1024 / 1024 / 1024, 2) AS free_G
FROM `information_schema`.tables
WHERE 1 = 1AND `TABLE_SCHEMA` NOT IN ('information_schema', 'mysql')
HAVING free_G > 1
ORDER BY `DATA_FREE` DESC
LIMIT 10;

如何清理？

官方做法

ALTER TABLE tbl_name ENGINE=INNODB

You can also use ALTER TABLE tbl_name FORCE to perform a “null” alter operation that rebuilds the table.

会重建表，这个过程会不会锁表？影响数据读写？

搜集相关知识，有人提供方案

ALTER TABLE tbl_name ENGINE=INNODB, ALGORITHM=INPLACE

锁表时间很短，几乎不影响业务读写，属于Online DDL
详情参考资料3

至于 inplace 和 copy 的差异简单来讲：

copy 基于临时表，锁表时间长，容易丢数据
inplace 基于临时文件,锁表时间短，不易丢数据。

那么需要指定 ALGORITHM 吗？
搜索MySQL官方文档，发现

The ALGORITHM clause is optional. If the ALGORITHM clause is omitted, MySQL uses ALGORITHM=INPLACE for storage engines and ALTER TABLE clauses that support it. Otherwise, ALGORITHM=COPY is used.