MySQL 回收表碎片实践教程

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS SQL Server,基础系列 2核4GB
简介: 在 MySQL 数据库中,随着数据的增删改操作,表空间可能会出现碎片化,这不仅会占用额外的存储空间,还可能降低表的扫描效率,特别是一些大表,在进行数据清理后会产生大量的碎片。本篇文章我们一起来学习下如何进行碎片回收以及相关注意点。

前言:

在 MySQL 数据库中,随着数据的增删改操作,表空间可能会出现碎片化,这不仅会占用额外的存储空间,还可能降低表的扫描效率,特别是一些大表,在进行数据清理后会产生大量的碎片。本篇文章我们一起来学习下如何进行碎片回收以及相关注意点。

查看表碎片大小

一般 MySQL 数据库都是开启 innodb_file_per_table 参数的,这代表每个表使用独立的表空间,即每个表的数据及索引存储在一个独立的 表名.ibd 文件里,如果某个表有大量碎片,ibd 文件占用磁盘空间会非常大,碎片回收掉后 ibd 文件也会显著减小。

首先我们要确定哪些表需要进行回收碎片操作,MySQL 系统表 information.TABLES 中的 DATA_FREE 字段显示的是可用的空闲空间量(单位:字节),它可以帮助你估计碎片的程度,如果 DATA_FREE 很大,那么这个表的碎片量一般也比较大。

如果某个表比较大或者变动特别频繁,你可以看下这个表的 DATA_FREE 大小,看是否需要回收碎片,也可以从系统表中筛选出碎片量大于 100M 的表或者碎片率达到多少的表,这类表一般是需要进行碎片回收的。下面几条查询 SQL 可能对你有所帮助:

# 查看某个表的详细信息(包含碎片大小)
select 
table_schema as '数据库',
TABLE_NAME as '表名',
sys.FORMAT_BYTES(data_length) as '数据容量',
sys.FORMAT_BYTES(index_length) as '索引容量',
sys.FORMAT_BYTES(data_length+index_length) as '总容量' ,
sys.FORMAT_BYTES(DATA_FREE) as '碎片大小'
from information_schema.tables where TABLE_SCHEMA = 'db_name' and TABLE_NAME = 'tb_name';

# 按碎片大小排序
SELECT t.TABLE_SCHEMA,
       t.TABLE_NAME,
       t.DATA_FREE,
       sys.FORMAT_BYTES(DATA_LENGTH+INDEX_LENGTH) as '总容量' ,
       sys.FORMAT_BYTES(DATA_FREE) as '碎片大小'
FROM information_schema.tables t
WHERE
    t.table_schema NOT IN (
        'mysql',
        'information_schema',
        'performance_schema',
        'sys'
    )
AND t.table_type = 'BASE TABLE' ORDER BY `DATA_FREE` DESC LIMIT 20

# 查看碎片率大于0.3的表
select 
table_schema as '数据库',
TABLE_NAME as '表名',
sys.FORMAT_BYTES(DATA_LENGTH+INDEX_LENGTH) as '总容量' ,
sys.FORMAT_BYTES(DATA_FREE) as '碎片大小',
(DATA_FREE / (data_length + index_length)) AS '碎片率'
FROM information_schema.tables t
WHERE
    t.table_schema NOT IN (
        'mysql',
        'information_schema',
        'performance_schema',
        'sys'
    ) AND t.table_type = 'BASE TABLE' and (DATA_FREE / (data_length + index_length)) > 0.3

以上三条 SQL 基本能覆盖日常所需场景,想要回收表碎片的话,可以按照不同场景执行相关 SQL 来查找,比如是想回收碎片比较多的表还是碎片率比较大的表。找到需要回收碎片的表后,下一步就是评估进行正式回收碎片操作了。

回收表碎片

对于 InnoDB 存储引擎的表,可以用 optimize table table_name; 或者 alter table table_name engine = innodb; 两种方式进行回收。

OPTIMIZE TABLE 对于 InnoDB 表来说,实际上会执行一个重建表的操作,这与 ALTER TABLE ... FORCE 类似。这个过程会重新组织表的数据和索引,更新索引统计信息,并释放聚簇索引中未使用的空间。它可以在一定程度上减少表占用的空间,并提高访问表时的 IO 效率。OPTIMIZE TABLE 对于常规的和分区的 InnoDB 表使用 online DDL ,这减少了并发的 DML 操作的停机时间。OPTIMIZE TABLE 仅在操作的准备阶段和提交阶段短暂地获取独占的表锁,在准备阶段,元数据会被更新并且创建一个中间表,在提交阶段,将提交表元数据更改。

ALTER TABLE ... ENGINE = InnoDB 命令实际上是将表的存储引擎重新设置为 InnoDB 。在这个过程中,MySQL 会对表进行重建,会回收掉未使用的空间。在 5.6 及以后的版本中,这个操作会使用 Online DDL ,减少对并发 DML 操作的影响。它通过创建一个临时文件,扫描表的数据页,并将操作记录在日志文件中,最后将临时文件替换原表的数据文件。此方法只适用于 InnoDB 引擎表。

总的来说,两者都可以用于整理 InnoDB 表的碎片,但是 OPTIMIZE TABLE 更侧重于专门的碎片整理和空间回收,还可以用于其他存储引擎。而 ALTER TABLE ... ENGINE=InnoDB 主要是更改存储引擎属性时附带的一些空间优化。在实际使用中,可以根据具体情况选择合适的方式来回收 InnoDB 表的空间。

需要注意的一点是,尽管二者操作都是 Online DDL ,但回收操作还是尽量在业务低峰期执行,特别是大表,回收操作还是需要一段时间的。除此之外,要确保有足够的磁盘空间进行回收操作,因为执行期间会生成临时文件,进一步占用磁盘空间,执行完成后才会删除临时文件。例如你要对一个 200G 的表进行回收操作,预估能回收掉 50G 碎片,则要确保磁盘空间至少剩余 150G,一般建议剩余空间在表大小以上。如果你的磁盘剩余空间不足则无法完成回收操作。

总结:

本篇文章介绍了如何查看 InnoDB 表的碎片以及如何进行回收。生产环境中,建议定期巡检 MySQL 系统中的表碎片,并在业务低峰期执行回收操作。回收表碎片是一种良好的数据库维护实践,可以提高数据库查询性能,同时也可以提高存储效率和管理简便性。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5月前
|
存储 关系型数据库 MySQL
MySQL数据库碎片化:隐患与解决策略
UUID作为主键可能导致MySQL存储碎片,影响性能。频繁的DML操作、字段长度变化和非顺序插入(如UUID)都会造成碎片。碎片增加磁盘I/O,降低查询效率,浪费空间,影响备份速度。建议使用自增ID,固定长度字段,并适时运行OPTIMIZE TABLE来减少碎片。
|
SQL 存储 关系型数据库
MySQL学习笔记-如何有效的回收表空间
MySQL学习笔记-如何有效的回收表空间
197 0
|
关系型数据库 MySQL 数据库
MySQL部分权限回收功能(Partial Revokes)的使用---发表在爱可生开源社区
MySQL数据库对于对象的操作级别分为:全局、数据库、表、字段等。粒度从粗到细。如果粗的粒度的权限满足了,将不再检验细粒度的级别,这种验证方式有的时候不方便,例如需要把100个数据库中除了某一个数据库外的访问权限赋予某个用户
156 0
|
存储 关系型数据库 MySQL
关于MySQL账号授权和回收的那点“破事”!
本篇带领对用户的授权和回收似懂非懂的朋友进行简单剖析,希望能起到抛砖引玉的效果。放弃前戏,直奔主题!!!
214 0
|
SQL 关系型数据库 MySQL
Mysql碎片整理:一些注意事项整理
整理Mysql的一些注意事项整理,不断汇总,可以通过二级标题去筛选,每个二级标题都是独立的。可以在评论区评论留下注意事项,我汇总,积少成多。
179 0
|
存储 SQL 关系型数据库
Mysql碎片整理:存储程序
定义变量;语句结束分隔符;存储函数;存储过程;游标在存储过程和存储函数中的使用;触发器的定义;事件的创建。
104 0
|
SQL 关系型数据库 MySQL
Mysql碎片整理:视图的学习
视图可以帮助我们做复杂查询时省去细节,也可以对大量重复的sql语句做简化,因为它是一个别名的存在,视图并不真的存数据内容,它还是调用底层的sql代码。尽量不要对视图做修改。
101 0
|
搜索推荐 关系型数据库 MySQL
|
SQL 数据可视化 关系型数据库
Mysql碎片整理:约束
约束:即一些关联规则
104 0
|
关系型数据库 MySQL

相关产品

  • 云数据库 RDS MySQL 版