MySQL数据库碎片清理，优化大表性能
MySQL 数据库碎片清理是优化大表性能的常见手段，尤其当表频繁增删改后，数据页出现空洞、索引统计信息不准，查询会明显变慢。
本文面向零基础用户，讲清碎片产生原因，并给出可执行的清理步骤、避坑要点和验证方法。
先判断你的表是否真的需要清理
碎片不是凭空出现的。
InnoDB 存储引擎在删除或更新变长字段时，不会立即回收空间，而是留下空洞。
这些空洞会让表占用的磁盘空间比实际数据大，扫描时读取更多页，拖慢查询。
不是所有表都需要清理。只有数据频繁删除、更新，且表体积较大时，碎片影响才明显。
小表清理收益很低，反而可能锁表。
检查碎片情况，可以查询 information_schema.TABLES：
SELECT
TABLE_NAME,
ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
ROUND(DATA_FREE / 1024 / 1024, 2) AS free_mb,
ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE) * 100, 2) AS frag_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '你的数据库名'
AND DATA_FREE > 0
ORDER BY DATA_FREE DESC;DATA_FREE 表示碎片占用的字节数。
如果 frag_pct 超过 20%，且表大小在几百 MB 以上，清理通常有明显收益。
清理前的准备：备份、选时间、确认引擎
动手前必须做三件事：
- 备份数据。至少用
mysqldump导出目标表：
mysqldump -u root -p 数据库名 表名 > /backup/表名_$(date +%F).sql大表建议用 --single-transaction 减少锁：
mysqldump -u root -p --single-transaction 数据库名 表名 > /backup/表名.sql- 选择业务低峰期。清理操作可能锁表，尤其是 MyISAM 引擎，InnoDB 在 MySQL 5.6 之后支持 Online DDL，但仍会消耗 IO。
- 确认存储引擎。执行
SHOW TABLE STATUS LIKE '表名';，看Engine列。MyISAM 碎片清理简单但锁全表；InnoDB 推荐用ALTER TABLE ... ENGINE=InnoDB重建。
宝塔面板用户可以在“数据库”页面点击“管理”，进入 phpMyAdmin 后选择表，点击“操作”标签，找到“优化表”按钮。但大表不建议在 phpMyAdmin 中操作，容易超时。
执行碎片清理：三种方法按场景选
方法一：OPTIMIZE TABLE（适合小表或 MyISAM）
OPTIMIZE TABLE 表名;这个命令会重建表并释放空间。
对 InnoDB 表，MySQL 会将其映射为 ALTER TABLE ... FORCE，触发重建。
注意：执行期间会占用额外磁盘空间，需要至少和原表一样大的剩余空间。
如果表很大，可能报错 ERROR 1114 (HY000): The table '表名' is full，说明临时目录空间不足。
可以修改 tmpdir 到更大分区，或改用方法二。
方法二：ALTER TABLE 重建（InnoDB 大表推荐）
ALTER TABLE 表名 ENGINE=InnoDB;这会重建表并整理碎片。
MySQL 5.6 及以上支持 Online DDL，允许并发读写，但需要关注 innodb_online_alter_log_max_size 参数，默认 128M，大表操作可能超出导致失败。
可以临时调大：
SET GLOBAL innodb_online_alter_log_max_size = 512 * 1024 * 1024;执行后观察是否完成，不要中途 kill，否则可能留下临时表。
方法三：pt-online-schema-change（超大型表、不能停业务）
Percona Toolkit 的工具，通过创建影子表、复制数据、原子重命名完成，几乎不锁原表。
安装后执行：
pt-online-schema-change --alter "ENGINE=InnoDB" \
--host=127.0.0.1 --user=root --ask-pass \
D=数据库名,t=表名 --execute执行前会检查外键、触发器，建议先加 --dry-run 试运行。注意：磁盘空间要足够容纳新旧两张表。
避坑指南：这些情况别乱清理
- 主从复制环境：清理操作会写入 binlog，从库可能延迟。建议在从库先测试，或设置
sql_log_bin=0临时跳过（需谨慎，可能导致主从不一致）。 - 磁盘空间不足：重建表需要额外空间，至少预留原表大小 1.2 倍。
- 外键约束：
ALTER TABLE可能因外键失败，先用SET FOREIGN_KEY_CHECKS=0;临时关闭，完成后恢复。 - 业务高峰期：即使 Online DDL 也会消耗 CPU 和 IO，可能拖慢其他查询。
- MyISAM 表：
OPTIMIZE TABLE会锁全表，期间无法读写，务必在停写窗口操作。
如果清理后碎片比例仍然很高，可能是表本身写入模式导致，比如频繁更新变长字段。
这种情况可以调整表结构，把频繁更新的字段拆分到单独表。
清理后怎么验证效果
重新执行第一步的碎片查询，对比 DATA_FREE 和 frag_pct。
理想情况下 DATA_FREE 应接近 0，表文件大小也会下降。
再用 EXPLAIN 观察典型查询的执行计划：
EXPLAIN SELECT * FROM 表名 WHERE 索引列 = '值';关注 rows 列是否减少，type 是否从 ALL 变为 ref 或 range。
碎片清理不会改变索引结构，但重建后统计信息更新，优化器可能选择更优路径。
也可以对比清理前后的查询耗时：
SELECT BENCHMARK(1000000, (SELECT COUNT(*) FROM 表名 WHERE 条件));BENCHMARK 只适合粗略对比，生产环境建议用慢查询日志观察实际业务 SQL。
常见疑问
清理碎片会丢失数据吗？
正常操作不会。但任何重建操作都有风险，务必先备份。如果中途断电或磁盘满，可能损坏表，需要从备份恢复。
多久清理一次合适？
没有固定周期。建议每月检查一次 DATA_FREE，当碎片比例超过 20% 且影响查询时再清理。频繁清理反而增加 IO 负担。
OPTIMIZE TABLE 和 ALTER TABLE 有什么区别？
对 InnoDB，两者最终都触发重建，但 OPTIMIZE TABLE 会返回信息行，ALTER TABLE 更直接。大表推荐 ALTER TABLE 或 pt-online-schema-change。
清理后表大小没变？
可能因为 innodb_file_per_table 关闭，表空间共享，文件不会缩小。可以检查该参数，如果为 OFF，需要先开启并重建表才能释放空间。
MySQL 数据库碎片清理不是万能药，但针对频繁增删改的大表，定期维护能明显改善查询性能。
操作前备份、选低峰、留足空间，清理后验证碎片比例和执行计划，才算完整闭环。
