MySQL 清除表空间碎片的实例详解

男娘i 2023-09-24 10:31 100阅读 0赞

MySQL 清除表空间碎片的实例详解

碎片产生的原因

(1)表的存储会出现碎片化，每当删除了一行内容，该段空间就会变为空白、被留空，而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大；

(2)当执行插入操作时，MySQL会尝试使用空白空间，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；

(3)当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分；

例如：

一个表有1万行，每行10字节，会占用10万字节存储空间，执行删除操作，只留一行，实际内容只剩下10字节，但MySQL在读取时，仍看做是10万字节的表进行处理，所以，碎片越多，就会越来越影响查询性能。

查看表碎片大小

(1)查看某个表的碎片大小

mysql> SHOW TABLE STATUS LIKE '表名';

结果中'Data\_free'列的值就是碎片大小

![MySQL 清除表空间碎片的实例详解\_MySQL 清除表空间碎片的实例详解][MySQL _MySQL]

(2)列出所有已经产生碎片的表

mysql> select table\_schema db, table\_name, data\_free, engine

from information\_schema.tables

where table\_schema not in ('information\_schema', 'mysql') and data\_free > 0;

清除表碎片

(1)MyISAM表

mysql> optimize table 表名

(2)InnoDB表

mysql> alter table 表名 engine=InnoDB

Engine不同,OPTIMIZE 的操作也不一样的,MyISAM 因为索引和数据是分开的,所以 OPTIMIZE 可以整理数据文件,并重排索引.

OPTIMIZE 操作会暂时锁住表,而且数据量越大,耗费的时间也越长,它毕竟不是简单查询操作.所以把 Optimize 命令放在程序中是不妥当的,不管设置的命中率多低,当访问量增大的时候,整体命中率也会上升,这样肯定会对程序的运行效率造成很大影响.比较好的方式就是做个shell,定期检查mysql中 information\_schema.TABLES字段,查看 DATA\_FREE 字段,大于0话,就表示有碎片

建议

清除碎片操作会暂时锁表，数据量越大，耗费的时间越长，可以做个脚本，定期在访问低谷时间执行，例如每周三凌晨，检查DATA\_FREE字段，大于自己认为的警戒值的话，就清理一次。

如有疑问请留言或者到本站社区交流讨论，感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！

您可能感兴趣的文章:解析mysql 表中的碎片产生原因以及清理MySQL的表空间是什么Mysql脏页flush及收缩表空间原理解析MySQL InnoDB表空间加密示例详解Mysql在线回收undo表空间实战记录深度解析MySQL 5.7之临时表空间mysql Innodb表空间卸载、迁移、装载的使用方法MySQL中查询所有数据库占用磁盘空间大小和单个库中所有表的大小的sql语句MySQL 表空间碎片的概念及相关问题解决

[MySQL _MySQL]: https://img-blog.csdnimg.cn/img_convert/2b52bbcc0324316a6e99cb7c7ed1f8db.png

发表评论取消回复

表情：

评论列表（有 0 条评论，100人围观）

还没有评论，来说两句吧...

相关阅读

相关 Oracle 整理表碎片、释放表的空间

表中的数据被频繁 UPDATE 或 DELETE 时，需要整理表中的碎片空间。 **--使用 SHRINK 命令** ALTER TABLE tabl...

缺乏、安全感/ 2024年04月17日 23:50/ 0 赞/ 91 阅读

相关 MySQL - 表空间碎片整理方法

MySQL数据库中的表在进行了多次delete、update和insert后，表空间会出现碎片。定期进行表空间整理，消除碎片可以提高访问表空间的性能。检查表空间碎片下

逃离我推掉我的手/ 2024年03月24日 22:44/ 0 赞/ 72 阅读

相关 mysql如何消除表空间碎片

MySQL 清除表空间碎片碎片产生的原因: （1）表的存储会出现碎片化，每当删除了一行内容，该段空间就会变为空白、被留空，而在一段时间内的大量删除操作，会使这种留空的空间

冷不防/ 2024年02月19日 20:05/ 0 赞/ 15 阅读

相关 MySQL表空间碎片

碎片产生的原因（1）表的存储会出现碎片化，每当删除了一行内容，该段空间就会变为空白、被留空，而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空

深碍√TFBOYSˉ_/ 2024年02月18日 15:19/ 0 赞/ 80 阅读

相关 MYSQL表碎片整理

1. 清除表碎片 MyISAM表：optimize table 表名 InnoDB表：alter table 表名 engine=Inno

朴灿烈づ我的快乐病毒、/ 2024年02月18日 14:16/ 0 赞/ 80 阅读

相关 MySQL 清除表空间碎片的实例详解

　　MySQL 清除表空间碎片的实例详解　　碎片产生的原因　　(1)表的存储会出现碎片化，每当删除了一行内容，该段空间就会变为空白、被留空，而在一段时间内的大量删除操作

男娘i/ 2023年09月24日 10:31/ 0 赞/ 101 阅读

相关 MySQL表空间碎片产生原因和优化

MySQL表空间碎片产生原因和优化 ![format_png][] 1、场景描述在使用MySQL数据库的过程当中，以下两种场景是大家经常遇到的：

冷不防/ 2023年07月19日 08:22/ 0 赞/ 11 阅读

相关 mysql表碎片整理

1.首先查看下表碎片信息 select table\_schema,table\_name,engine,table\_rows,data\_length+index\_l

墨蓝/ 2023年06月20日 06:52/ 0 赞/ 24 阅读

相关 MySQL表的碎片整理和空间回收小结

MySQL表碎片化（Table Fragmentation）的原因关于MySQL中表碎片化（Table Fragmentation）产生的原因，简单总结一下，MySQ

╰+哭是因爲堅強的太久メ/ 2022年12月11日 10:26/ 0 赞/ 175 阅读

相关 MySQL 清除表空间碎片的实例详解

表的存储会出现碎片化，每当删除了一行内容，该段空间就会变为空白、被留空，而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大；当执行插入操...

朱雀/ 2020年04月27日 06:36/ 0 赞/ 790 阅读