MySQL 13 为什么表数据删掉一半,表文件大小不变?

[复制链接]
发表于 昨天 22:10 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有账号?立即注册

×
核心结论与决策建议(BLUF)
在执行了大量的
  1. DELETE FROM t WHERE ...
复制代码
操作删除半数以上历史数据后,查看磁盘上的数据文件
  1. t.ibd
复制代码
,其体积往往分毫未减甚至进一步变大。该现象的本质根源在于:InnoDB 的数据组织机制是基于 B+ 树数据页(Page,默认16KB)存储的,DELETE 操作仅在逻辑上将行记录标记为“已删除”(可复用),并不会释放物理 Page 归还操作系统;随机插入或修改更会导致物理页频繁发生 50% 空间分裂,产生大量充斥着“空洞”的表空间碎片。彻底回收磁盘空间与消除空洞的唯一正道是执行表重建(Rebuild Table);生产高可用环境严禁裸写单机阻塞式的 OPTIMIZE TABLE,强推使用无锁的 Online DDL 或开源工具
  1. gh-ost
复制代码
平滑收缩。
空间治理方案底层执行机制业务读写锁表现磁盘额外空间需求生产推荐度
裸写 DELETE FROM仅将数据行标记为已删除,保留空洞产生行锁与间隙锁,高并发易死锁0 额外空间(原空间不释放)数据清理初步,无法缩小文件
OPTIMIZE TABLE (原生)全表数据拷贝重建紧凑临时表MySQL 5.6+ 走 Online DDL,短暂加元数据锁需原表 1.1 倍的额外磁盘可用空间★★★☆☆ (业务低峰期使用)
ALTER TABLE t ENGINE=InnoDB等价于重建表重整B+树聚簇空间支持 In-Place Online DDL,允许并发写需原表 1.1 倍额外空间★★★★☆ (常规碎片治理主力标准)
gh-ost 异步平滑重建Binlog增量追溯 + 影子表分块拷贝替换完全无锁、可随时限流暂停需原表 1.1 倍额外空间★★★★★ (百G以上超大表首选)


一、InnoDB 索引中的“空洞”成因解密
1. 行级删除空洞(Record Hole):
在 InnoDB 索引树中:



如果将记录 R4 物理删除,InnoDB 并不会将该记录从数据页中抹去并截断磁盘,而是仅仅在 R4 的行记录头中将
  1. deleted_flag
复制代码
打上标记,放入可复用链表中。如果后续插入一条主键范围相近的新数据,可以复用此位置;但若后续不再有匹配该范围的插入,该位置就永久沦为占用磁盘的“空洞”。
2. 页级删除与分裂空洞(Page Hole):
即使一个物理 Page 内的所有记录全部被删除,整个 Page 也仅被标记为可复用,并不会归还给操作系统。



当在满页 Page A 中间随机插入数据时,InnoDB 必须申请一个新 Page B,并将 Page A 中 50% 的数据迁移到新页,形成页分裂。分裂后的两个页各自只充填了一半数据,导致表空间物理体积凭空暴增一倍!

二、表重建(Rebuild Table)的物理收缩机理
要消除空洞,必须新建一棵紧凑无碎片的全新 B+ 树,将有效数据顺序紧凑地写入新树中:



自 MySQL 5.6 起,引入了强大的Online DDL机制:
1. 建立一个临时文件,扫描原表 A 主键的所有有效记录并紧凑生成 B+ 树。
2. 在此期间,业务对原表 A 的所有增量 INSERT / UPDATE / DELETE 依然正常运行,修改事件被暂存在
  1. row_log
复制代码
内存日志日志中。
3. 基础拷贝完成后,将
  1. row_log
复制代码
重放到临时文件中追平增量。
4. 获取短暂的独占元数据锁(MDL),执行原子替换,删除旧表空间文件。
SQL 执行语句:

  1. -- 重建表并回收物理磁盘碎片
  2. ALTER TABLE t_order ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;
复制代码


三、生产级碎片率精准检测与排查
在盲目执行重建前,先评估该表是否存在严重的碎片空洞:

  1. -- 查询 shop_db 数据库下所有大表的碎片大小与空洞率
  2. SELECT
  3.     table_name,
  4.     ENGINE,
  5.     ROUND(data_length / 1024 / 1024, 2) AS data_mb,
  6.     ROUND(index_length / 1024 / 1024, 2) AS index_mb,
  7.     ROUND(data_free / 1024 / 1024, 2) AS data_free_mb,
  8.     ROUND(data_free / (data_length + index_length + data_free) * 100, 2) AS fragmentation_pct
  9. FROM information_schema.TABLES
  10. WHERE table_schema = 'shop_db' AND data_free > 100 * 1024 * 1024
  11. ORDER BY data_free DESC;
复制代码

治理标准:当
  1. data_free
复制代码
(空闲碎片量)超过 5GB,且碎片率
  1. fragmentation_pct
复制代码
超过 30% 时,安排在维护窗口执行表重建。

常见问题(FAQ)
Q1: 为什么执行
  1. ALTER TABLE t ENGINE=InnoDB
复制代码
时,操作系统磁盘空间必须剩余原表 1.1 倍以上?

A1: Online DDL 在重建过程中采用的是文件级拷贝替换法。新生成的临时紧凑表空间文件必须与原有的旧表空间文件在磁盘上同时共存,直到最后一秒完成替换并删除旧文件。如果磁盘可用空间低于原表体积,重建过程会直接将剩余磁盘全部写满引发数据库宕机!

Q2: 为什么删除了表中的大量数据后,即使不执行重建,后续再次插入新数据表空间文件也不会继续变大?
A2: 因为之前被 DELETE 标记为已删除的物理数据页和记录空间,已经被纳入了 InnoDB 内部的空闲链表(Free List)中。后续发生相同主键范围的 INSERT 写入时,引擎会优先复用这些现成的空洞空间,只有当所有历史空洞被填满之后,表空间文件才会继续向操作系统申请扩张。



qidao123.com企服评测-应用市场 编辑提示:本文由企服大模型基于真实系统运维实践重构整理,聚焦技术原理、性能对比与选型避坑。若在实际部署中遇到异常,欢迎在下方跟帖交流;如需企业级技术支持或深度运维解决方案,请访问 qidao123.com 获取专属支持。
回复

使用道具 举报

登录后关闭弹窗

登录参与点评抽奖  加入IT实名职场社区
去登录
快速回复 返回顶部 返回列表