wxya🗒️Mysql OPTIMIZE TABLE
type
status
slug
date
tags
summary
category
password
icon
warning 先看风险
- 晚上用户少的时候执行（如果晚上定时任务多，cpu 占用比白天还高，也不要再白天执行，出现问题说不清楚）
- 低版本 mysql （CPU pegged at 100% after optimizing table belonging to merge set （）可能会出现这个 bug——具体的 rds 日志上没有看到，表现出来的就是如标题所说，这个 bug 还关联了另一个 bug，很早以前已经被解决（问题表象看起来和这个描述的一样）
- 按原理讲，应该不会有这样问题
- 条件允许情况下，一定要先备份后执行
- 从小表到大表
- 分批分天执行，不要一次性操作完
- 收缩的时候会占用该表2~3倍空间，空间要充足
- 阿里云 RDS 介绍：回收大容量表的碎片空间时，请确保实例剩余的存储空间大小至少为目标表大小的2~3倍，并在变更过程中密切关注实例剩余空间情况。
- 说明回收大容量表的碎片空间时，可能需要临时存储数据的副本或其他，这可能会导致额外的空间需求。如果空间不足，可能会导致回收碎片空间的操作失败或者实例被锁定。
- 对大表进行optimize table操作会带来突发的IO和Buffer使用量，可能导致锁表和抢占资源，业务高峰期可能会导致实例不可用以及监控断点。建议在业务低峰期操作。
执行SQL
为什么这么做
阿里 RDS 介绍
使用
delete语句删除数据时，delete语句只是将记录的位置或数据页标记为了“可复用”，但是磁盘文件的大小不会改变，即表空间不会直接回收。此时您可以通过optimize table语句释放表空间。对大表进行optimize table操作会带来突发的IO和Buffer使用量，可能导致锁表和抢占资源，业务高峰期可能会导致实例不可用以及监控断点。建议在业务低峰期操作。
IO 和 Buffer 操作，那个表也没有人用，导致 CPU 100% 确实不在意料之中
晚上有定时任务要跑，还有一堆表，负载比白天还高
找了一个没人用的表操作了一下
原理
当我们使用mysql进行delete数据，delete完以后，发现空间文件ibd并没有减少，这是因为碎片空间的存在，举个例子，一共公司有10号员工，10个座位，被开除了7个员工，但这些座位还是保留的，碎片整理就像，让剩下的3个员工都靠边坐，然后把剩下的7个作为给砸掉，这样就能释放出空间了
好处除了减少表数据与表索引的物理空间，还能降低访问表时的IO，这个比较理解，整理之前，取数据需要跨越很多碎片空间，这时需要时间的，整理后，想要的数据都放在一起了，直接拿就拿到了，效率提高
1.创建一张新的临时表 tmp
2.把旧表锁住，禁止插入删除，只允许读写 （这就是为什么上面的insert语句都停留在waiting for table metadata lock）
3.把数据不断的从旧表，拷贝到新的临时表，(这就是上面报copy to tmp table)
4.等表拷贝完后，进行瞬间的rename操作
5.旧表删除掉
本质就是创建新表复制过程
空间需要充足
要排序，先清理小的，然后再清理大的
执行时间参考
8c16 机器
MB 秒执行完成（100M 十多秒）
个位数 GB 分钟执行完成 （7g 2 分半）
如果二位数 GB 甚至 3 位数，一定要晚上执行（有可能是因为性能问题 cpu100%，也有可能因为 mysql bug cpu 100% 白天出了问题说不清）
optimzing table期间 cpu100% 临时解决方法
推荐使用方法
无锁变更原理
DMS为解决结构变更时的锁表问题而推出的无锁结构变更功能，能较好地规避因数据库变更导致锁表以至于阻塞业务的现象、以及数据库原生Online DDL带来的主备延迟现象，主要原理为：创建临时表（变更后的表结构），往临时表中拷贝全量数据，同步增量Binlog数据，切换临时表为正式表。
如果使用云厂商数据库，推荐使用无锁变更来进行优化操作
参考
上一篇
MySQL 批量修改表名
下一篇
RDS清理历史数据释放空间
Loading...