作者:夹uh山下 | 来源:互联网 | 2023-05-17 05:21
概述
我们经常会使用VARCHAR、TEXT、BLOB等可变长度的文本数据类型。不过,当我们使用这些数据类型之后,就不得不做一些额外的工作——MySQL数据表碎片整理。
每当MySQL从你的列表中删除了一行内容,该段空间就会被留空。而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空间更大。
当MySQL对数据进行扫描时,它扫描的对象实际是列表的容量需求上限,也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作,MySQL将尝试利用这些留空的区域,但仍然无法将其彻底占用。
1、计算碎片大小
要整理碎片,首先要了解碎片的计算方法。
可以通过show table [from|in db_name] status like '%table_name%'命令查看:
打开搜狗搜索APP,查看更多精彩资讯
碎片大小 = 数据总大小 - 实际表空间文件大小数据总大小 = Data_length + Data_length = 66650112
实际表空间文件大小 = rows * Avg_row_length = 3867 * 17235 = 66647745
碎片大小 = (66650112-66647745) / 1024 = 2.3KB
通过information_schema.tables的DATA_FREE列查看表有没有碎片:
2、整理碎片
2.1 使用alter table table_name engine = innodb命令进行整理。
2.2 使用pt-online-schema-change工具也能进行在线整理表结构,收集碎片等操作。
后面会单独介绍一下pt这个工具,所以这里就不多做介绍了。
2.3 使用optimize table命令,整理碎片。
运行OPTIMIZE TABLE, InnoDB创建一个新的.ibd具有临时名称的文件,只使用存储的实际数据所需的空间。优化完成后,InnoDB删除旧.ibd文件并将其替换为新文件。如果先前的.ibd文件显着增长但实际数据仅占其大小的一部分,则运行OPTIMIZE TABLE可以回收未使用的空间。
优化后可以看到DATA_FREE明显减少很多。
上面是针对单表去做碎片整理,那么我如果很多张表应该怎么处理呢?后面会整理一个shell脚本来对多个表做碎片整理,感兴趣的朋友可以关注一下~