判断MySQL索引碎片主要看information_schema.TABLES中DATA_FREE字段，结合data_length+index_length计算碎片率；碎片率＞20%或DATA_FREE＞100MB建议整理，常用OPTIMIZE TABLE或ALTER TABLE ... ENGINE=InnoDB重建表，大表宜用pt-online-schema-change在线处理。
MySQL索引碎片主要源于频繁的增删改操作，尤其在InnoDB中，DELETE只是逻辑标记、UPDATE可能引发页分裂、INSERT随机主键导致页填充率低——这些都会让数据页变得稀疏，形成“空洞”，最终影响查询性能和磁盘空间利用率。处理的关键不是盲目优化，而是先识别、再按需整理，并兼顾后续预防。
怎么判断表有没有索引碎片
核心看 DATA_FREE 字段，它代表表中未被使用的字节数（即物理空洞大小）。结合数据+索引总大小，可算出碎片率：
- 执行查询：
SELECT table_name, data_length, index_length, data_free,
ROUND((data_free / NULLIF(data_length + index_length, 0)) * 100, 2) AS frag_pct
FROM information_schema.tables
WHERE table_schema = 'your_db' AND data_free > 0; - 一般认为碎片率 > 20% 就值得整理；超过 50% 或 DATA_FREE > 100MB 建议优先处理
- InnoDB 表注意：即使
data_free = 0，也不绝对代表无碎片（比如页内碎片无法通过该字段体现），但这是最实用的初筛指标
常用整理方法及适用场景
真正生效的整理本质都是重建表结构，区别在于语法封装和锁行为：
-
OPTIMIZE TABLE table_name
对 MyISAM 是原生命令；对 InnoDB 实际等价于ALTER TABLE ... ENGINE=InnoDB，会触发重建+ANALYZE。期间加读锁（可查不可写），适合低峰期单表操作 -
ALTER TABLE table_name ENGINE=InnoDB
更显式，兼容性好，效果同上。如果表原本就是 InnoDB，执行它仍会重建并整理碎片 -
ALTER TABLE table_name FORCE
轻量级重建语法，不改变引擎，仅强制重写数据文件，同样能清理碎片，开销略小 - 大批量操作时，用 mysqlcheck -o database_name 可批量优化整个库的表
哪些情况要特别注意
不是所有碎片都必须立刻处理，得结合业务权衡：
- 高写入低查询的表（如日志表）：碎片影响小，但频繁 OPTIMIZE 反而加重I/O压力，建议按月或按分区归档后清理
-
大表（>10GB）：重建耗时长、锁表久，生产环境慎用。可考虑 Percona Toolkit 的
pt-online-schema-change在线重建，避免服务中断 - 使用 UUID 或随机字符串作主键：这是碎片“加速器”，长期来看应优先重构为自增ID或时间有序ID，比定期整理更治本
- innodb_file_per_table = OFF 时，碎片无法通过单表操作释放到磁盘——必须迁移至独立表空间后才有效
日常怎么减少碎片产生
预防比修复更重要：
- 主键尽量用 自增整型，避免随机写入引发页分裂
- 大字段（TEXT/BLOB）谨慎使用，必要时考虑外置存储或压缩
- 删除大量旧数据后，及时执行
OPTIMIZE TABLE或ALTER TABLE ... ENGINE=InnoDB释放空间 - 合理设置 innodb_fill_factor（如 80~90），为更新预留页内空间，减少分裂频率（需 MySQL 5.7.20+）
- 定期检查
information_schema.TABLES中DATA_FREE增长趋势，建立自动化巡检脚本
