清理表的数据碎片
在使用GaussDB(for MySQL)过程中,经常会遇到表碎片率过高的问题。表碎片是指在数据库中,表的数据和索引分散在不同的物理块中,这些物理块可能不连续,或者有一些空闲的空间,从而导致表的数据和索引在磁盘上的存储不是最优的。
这种现象主要是由于表的数据操作(比如删除、更新、插入等操作)引起的,会使得表中的数据行不断地被修改和移动,从而导致表中的数据片段变得不连续。
影响及风险
查看表的碎片情况
- 执行如下SQL,查看所有已经产生碎片的表。
select table_schema, table_name, data_free, data_length from information_schema.tables where table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys') and data_free > 0;
- 执行如下SQL,查看指定表的碎片情况。
SELECT table_name, data_length, data_free, data_length FROM information_schema.tables WHERE table_schema = 'database_name' AND table_name = 'table_name';
表1 参数说明 参数名称
描述
table_schema
库名称
table_name
表名称
data_length
表存储的数据大小,单位:字节。
data_free
表剩余的空闲空间大小(即代表碎片的大小),单位:字节。
如果经常删改数据表,会造成大量的data_free。一般来说,可以通过data_free占data_length的比例来初步判断碎片率,估计碎片情况。
清理碎片(回收空间)
针对表碎片率过高的问题,建议定期对频繁访问的表做碎片分析并清理碎片,进行表空间优化整理,从而缩小空间,优化性能。建议不要经常(每小时或每天)进行碎片整理,一般根据实际情况,只需要每周或者每月整理一次即可。
GaussDB(for MySQL)可以通过optimize table语句释放表空间,重组表数据和索引的物理页,减少表所占空间和优化读写性能。
OPTIMIZE TABLE <table_name>;
![](https://support.huaweicloud.com/trouble-gaussdbformysql/public_sys-resources/caution_3.0-zh-cn.png)
- OPTIMIZE TABLE对常规和分区InnoDB表使用online DDL方式,减少了并发DML操作的阻塞时间。
- 对于包含FULLTEXT索引的InnoDB表,不支持使用online DDL优化表。而是使用表复制算法。
- OPTIMIZE TABLE命令会有短暂锁表操作,整体执行时间与表大小有关。一般执行时间较长,且较为占用资源(必须预留被optimize表1.5倍大小的磁盘空间),所以进行表空间优化时建议避开业务高峰期,避免影响正常业务的进行。
- online DDL优化改表,在执行阶段,可能会短暂地请求一个排他的元数据锁。因此,如果一个事务持有元数据锁,将会导致online DDL被阻塞,事务可能在online DDL之前或者执行过程中持有元数据锁,一个长事务,不管其是正在运行,还是休眠事务,都有可能导致online DDL操作超时。