关于mysql碎片和数据页分裂问题

倒悬于世

于 2024-07-19 11:52:29 发布

阅读量751

点赞数 19

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_44146398/article/details/140541691

版权

一、mysql碎片问题产生的原因

1. delete时会产生碎片：

1. 在delete删除数据的时候，MySQL内部不会真正删除数据来释放空间，而且做标记删除，即将delflag:N修改为delflag:Y（即为"可复用"），commit之后会会被purge进入删除链表。

2. 对于删除后的数据，依然保存在数据页内，只有插入的记录小于此空间大小时，才会被重用，如果下一次insert更大的记录，这块空间不会被重用。 这种删除操作会减少数据页的填充率，但不会立即触发数据页的分裂。

3. 删除操作可能会影响索引的碎片。因为删除行会减少索引树的节点，但索引的物理存储空间并不会立即减少。索引的碎片可以通过 OPTIMIZE TABLE 命令来减少，这会重建索引并减少索引的碎片。

2. insert随机值作为主键id，也会产生很多数据页分裂操作：

1. 当使用随机值作为主键时，数据在索引树中的分布会更加不均匀。这会导致索引树的某些部分变得非常密集，而其他部分则相对稀疏。这种不均匀的分布会增加数据页分裂和合并的频率。

2. 在插入数据时，MySQL会尽量将数据存储在同一数据页中，以减少I/O操作。然而，当使用随机值作为主键时，数据的插入位置变得不确定，这可能导致数据页的填充率较低，从而增加碎片，导致数据页分裂的频率增加。

3. 频繁的数据页分裂和合并会消耗更多的系统资源，如CPU和I/O，从而影响数据库的整体性能。

更新操作：更新行记录时，如果新数据比旧数据大，可能需要移动行记录到新的数据页，这也会产生碎片。

3. 总结：

1：insert，delete、uodate都可能会产生碎片，浪费空间

2：只有insert随机主键时，产生碎片会导致数据页分离。

3：碎片多会导致索引数据不集中，影响索引性能

二、mysql碎片问题怎么解决？

optimize table sys_user

什么情况下使用?

使用delete语句删除数据时，delete语句只会将记录的位置或者数据页标记为"可复用"，但是数据库磁盘文件的大小不会改变，即表空间不会被回收，此时使用该命令可以释放空间，压缩数据文件。

optimize table语句在 MySQL 5.7、 MySQL 8.0上采用Online DDL方式执行，允许并发执行DML。对大表进行optimize table操作会带来突发的IO和Buffer使用量，可能导致锁表和抢占资源，业务高峰期可能会导致实例不可用以及监控断点。建议在业务低峰期操作。

原理:

执行 OPTIMIZE TABLE 命令的过程包括以下几个关键步骤：

创建临时表：MySQL 首先会创建一个与原表结构相同的临时表。
复制数据到临时表：将原表中的数据复制到临时表中。
优化表：在数据复制的过程中，MySQL 会对数据进行整理和重组，去除碎片，提高数据的连续性。
重命名表：当数据复制完成并且表被优化后，MySQL 会删除原表，然后将临时表重命名为原表的名称。

通过这个过程，OPTIMIZE TABLE 可以帮助数据库重新组织表的存储结构，提高查询性能和减少存储空间占用。

注意事项

使用OPTIMIZE TABLE 作用于InnoDB和MYISM引擎时（只有这两个引擎可用），在MySQL5.7和MySQL8.0及以上版本的时候，因为数据库支持online DDL，允许并发执行DML。对大表进行optimize table操作会带来突发的IO和Buffer使用量，可能导致锁表和抢占资源，业务高峰期可能会导致实例不可用以及监控断点。建议在业务低峰期操作。
使用OPTIMIZE TABLE 需要创建一个临时表，所以需要在此之前保证磁盘容量足够。
如果表没有大量使用delete来删除大量数据，使用optimize table语句也无法降低表空间的使用率。

通过 ALTER TABLE 命令更改存储引擎，也可以达到重构表的效果，减少数据碎片。