mysql如何清理碎片文件_mysql表碎片整理与优化-mysql教程-PHP中文网

OPTIMIZE TABLE 对 InnoDB 常无效，因其本质是重建表且依赖 innodb_file_per_table=ON 才能缩文件；多数碎片由 purge 线程自动回收，仅当 DATA_FREE 持续超 20% 或 free_pct＞25% 时才需干预。

mysql如何清理碎片文件_mysql表碎片整理与优化

为什么 `OPTIMIZE TABLE` 有时没用？

MySQL 表碎片是否真需要清理，取决于存储引擎和实际写入模式。InnoDB 的聚簇索引本身会复用空闲页，频繁 DELETE 或 UPDATE 后留下的“空洞”多数时候由后台 purge 线程自动回收，OPTIMIZE TABLE 并不总能压缩数据文件大小。

常见错误现象：OPTIMIZE TABLE 执行完，data_length 几乎不变，磁盘空间也没释放——大概率是因为表没真正产生可回收的外部碎片，或者 innodb_file_per_table=OFF 导致数据写在共享表空间里，无法收缩。

仅当 innodb_file_per_table=ON（默认）且表是独立 .ibd 文件时，OPTIMIZE TABLE 才可能减小物理文件尺寸
OPTIMIZE TABLE 对 InnoDB 实际执行的是 ALTER TABLE ... FORCE，会重建整张表，锁表时间长、IO 压力大，线上慎用
如果只是想回收已删除行的空间供后续 INSERT 复用，通常不需要人工干预；真正要关注的是 DATA_FREE 值持续偏高（比如超过表大小 20%）且 innodb_page_size 下碎片页比例异常

替代 `OPTIMIZE TABLE` 的轻量方案

想避免锁表和全量重建？优先考虑 ALTER TABLE ... ENGINE=InnoDB 或 ALTER TABLE ... FORCE 的等效写法，它们语义更明确，也更容易被 DBA 工具识别为“在线重组织”意图。

使用场景：主从架构下需最小化主库阻塞，或表太大无法接受小时级锁表。

ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE —— 在支持的 MySQL 版本（5.6+）中可做到几乎无锁，但要求表无全文索引、无虚拟列等限制
确认是否支持在线 DDL：SHOW CREATE TABLE t1 查看建表语句，再查 information_schema.INNODB_TABLES 中的 FILE_FORMAT 是否为 Barracuda（旧格式 Antelope 可能限制 ALGORITHM）
执行前务必检查 innodb_online_alter_log_max_size，太小会导致 DDL 中途失败并报错 Operating system error number 28 in a file operation

`information_schema` 里怎么看真实碎片？

别只盯着 DATA_FREE。它只反映聚集索引未使用的空间，对二级索引无效，且在 innodb_file_per_table=OFF 下恒为 0。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

更靠谱的方式是结合 INFORMATION_SCHEMA.INNODB_SYS_TABLES 和 INNODB_SYS_INDEXES 查底层页使用率：

SELECT 
  t.NAME AS table_name,
  t.FILE_SIZE / 1024 / 1024 AS file_mb,
  t.ALLOCATED_SIZE / 1024 / 1024 AS allocated_mb,
  ROUND((t.FILE_SIZE - t.ALLOCATED_SIZE) / t.FILE_SIZE * 100, 2) AS free_pct
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t
WHERE t.NAME LIKE 'your_db/your_table';

FILE_SIZE 是 .ibd 文件当前大小，ALLOCATED_SIZE 是 InnoDB 实际分配并标记为“已用”的空间（含页头、页尾等开销）
如果 free_pct > 25%，且该表长期有大量随机 DELETE，才值得介入
注意：该查询依赖 innodb_stats_on_metadata=OFF（默认），否则可能因统计刷新导致短暂卡顿

分区表怎么安全清理碎片？

分区表不能对整个表直接 OPTIMIZE TABLE，否则会锁所有分区；但逐个 OPTIMIZE PARTITION 又容易遗漏或误操作。

正确做法是按分区粒度做在线重组，并控制并发影响：

先查分区定义：SHOW CREATE TABLE t_partitioned，确认分区字段和类型（RANGE/LIST/HASH）
对单个分区优化：ALTER TABLE t_partitioned OPTIMIZE PARTITION p202401，它只锁该分区，不影响其他分区读写
如果分区数量多，建议用脚本生成语句，并加 SLEEP(1) 避免连续 DDL 触发 innodb_dedicated_server 自动调优机制误判负载
特别注意：OPTIMIZE PARTITION 对 MyISAM 分区无效，仅适用于 InnoDB 分区表

碎片不是越少越好，关键是空间复用效率和查询局部性。很多所谓“高碎片”表，实际 buffer_pool_hit_rate 接近 99%，根本没必要碰。真正该警惕的是 innodb_data_pending_fsyncs 持续升高 + SELECT 延迟毛刺——那往往指向 IO 调度或 SSD 寿命问题，而不是表碎片。