为什么MySQL执行Delete语句时索引页会出现空洞？-mysql教程-PHP中文网

innodb的delete仅逻辑标记删除行，不释放物理空间，形成空洞；需optimize table或alter table engine=innodb重建表才能真正回收空间。

为什么mysql执行delete语句时索引页会出现空洞？

DELETE 不释放物理空间，只做逻辑标记

InnoDB 的 DELETE 本质是把记录打上“已删除”标记（deleted_flag），并不立即擦除数据、也不挪动页内其他记录位置。索引页里对应键值的条目被标记为可复用，但页本身仍保留在 B+ 树结构中——这就形成了“空洞页”：页里有大量 deleted_flag=1 的槽位，n_leaf_pages 没变，但有效数据密度下降。

常见错误现象：EXPLAIN 显示 rows 估算严重偏高、key_len 突然变小、Cardinality 接近 0 或恒为 1；information_schema.INNODB_INDEX_STATS 中 n_diff_pfx01 远低于实际唯一值数量。

不是所有 DELETE 都立刻产生明显空洞：顺序主键 + 尾部删除（如 WHERE id > 1000000）可能触发页合并，空洞较少
高比例随机条件删除（如 WHERE status = 'archived'）最易积累空洞，尤其当该字段无索引时需全表扫描再逐条标记
data_free 只反映聚簇索引空洞总量，二级索引空洞需单独查 INNODB_INDEX_STATS

UPDATE 触发“删+插”，比 DELETE 更易产空洞

UPDATE 在 InnoDB 里不是原地修改，而是先按旧值定位并标记删除，再以新值插入——如果新值长度 > 旧值，或插入位置无法复用原槽位，就会引发页分裂。分裂后旧页残留碎片，新页未必填满，两页都出现空洞。

典型场景：UPDATE users SET bio = CONCAT(bio, '...') WHERE id = 123，bio 字段从 50 字节涨到 200 字节，原页放不下，必须分裂；即使主键有序，索引顺序也被破坏，B+ 树节点不再紧凑。

更新主键字段（UPDATE ... SET id = ...）等价于 DELETE + INSERT，必然跨页移动，空洞风险最高
更新非索引列（如 UPDATE t SET remark = 'ok' WHERE id = 1）只影响聚簇索引页，二级索引不受影响
使用 ROW_FORMAT=DYNAMIC 可缓解大字段更新导致的页分裂，但不能消除空洞根源

OPTIMIZE TABLE 并不总是能清掉索引空洞

OPTIMIZE TABLE 对 InnoDB 表实际执行的是 ALTER TABLE ... FORCE，它重建聚簇索引和所有二级索引，理论上应消除空洞。但效果取决于表配置和空间管理方式。

MySQL

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

下载

关键限制条件：

若 innodb_file_per_table = OFF，表在系统表空间（ibdata1）中，OPTIMIZE 无法 shrink 系统表空间，du -h 看不到文件变小
若表使用 ROW_FORMAT=COMPRESSED 且未启用 innodb_file_per_table，OPTIMIZE 会失败并报错 ERROR 1031 (HY000): Table storage engine for 't' doesn't support optimize
8.0+ 虽支持 ALGORITHM=INPLACE，但重建期间仍需 ≈ 原表大小的额外磁盘空间；若空间不足，操作会中断并回滚

查空洞比猜空洞更可靠

别凭 SELECT COUNT(*) 或文件大小判断是否需要整理。真实空洞量级藏在 information_schema 里：

SELECT 
  table_name,
  ROUND(data_length / 1024 / 1024, 2) AS data_mb,
  ROUND(data_free / 1024 / 1024, 2) AS free_mb,
  ROUND(100 * data_free / (data_length + data_free), 2) AS frag_pct
FROM information_schema.tables 
WHERE table_schema = 'your_db' AND table_name = 'your_table';

同时查索引统计：

SELECT index_name, n_leaf_pages, n_diff_pfx01 
FROM information_schema.INNODB_INDEX_STATS 
WHERE table_name = 'your_table' AND database_name = 'your_db';