资讯

历史

科技

环境与自然

成长

游戏

财经

文学与艺术

美食

健康

家居

文化

情感

汽车

三农

军事

旅行

运动

教育

生活

星座命理

MySQL数据碎片如何影响你的数据库性能？

创作时间:

作者:

@小白创作中心

MySQL数据碎片如何影响你的数据库性能？

引用

CSDN

等

来源

https://blog.csdn.net/weixin_41273024/article/details/140760663

https://blog.csdn.net/jimn2000/article/details/142348457

https://blog.csdn.net/wangkun_j/article/details/143182597

https://cloud.baidu.com/article/2862898

https://blog.csdn.net/UserFrank/article/details/124711275

https://www.cnblogs.com/abclife/p/17542253.html

https://cloud.tencent.com/developer/article/1798640

https://www.cnblogs.com/kerrycode/p/10943122.html

在MySQL数据库的日常运维中，数据碎片是一个不容忽视的问题。它不仅会占用额外的存储空间，更会影响数据库的查询性能。本文将深入探讨数据碎片的形成原因、对性能的具体影响，并提供实用的解决方案和最佳实践。

数据碎片的形成原因

数据碎片主要由以下几种操作引起：

删除操作：InnoDB引擎中删除数据仅标记为"已删除"而非物理删除，异步Purge线程清理这些空间。若后续插入数据无法完全填充这些空白区域，则形成碎片。
更新操作：当更新导致数据行长度变化（如VARCHAR字段缩短），原数据页可能留下空闲空间。若页内空闲空间无法被新数据完全利用，会产生碎片。此外，频繁更新可能引发页分裂，导致页稀疏填充。
插入操作：非顺序插入（如使用UUID或随机主键）会导致页分裂。当新数据无法连续填充页时，物理存储变得不连续。
数据存储设计因素：可变长度字段（如VARCHAR或TEXT类型），更新时若字段长度变化较大，易产生行碎片。非自增主键，随机主键导致数据分散存储，增加页分裂概率。

数据碎片对性能的影响

数据碎片会导致以下问题：

空间浪费：表占用的空间比实际数据需要的空间多。
性能下降：数据物理存储不连续，导致查询效率降低。
备份时间增长：碎片化的数据会增加备份文件的大小，延长备份和恢复的时间。

解决方案和最佳实践

为了解决数据碎片问题，可以采取以下措施：

OPTIMIZE TABLE：这个命令可以重新组织表和索引的物理存储，减少碎片并优化表的存储和访问速度。对于InnoDB表，OPTIMIZE TABLE命令会执行一个空的ALTER TABLE操作，重建整个表，删除未使用的空间。需要注意的是，OPTIMIZE TABLE在执行期间会锁定表，对于大表可能需要较长时间。
ALTER TABLE：通过ALTER TABLE table_name ENGINE=InnoDB;命令，可以重建表，这在MySQL 5.6及以上版本中支持在线DDL，对DML操作的影响较小。
使用自增ID：作为主键，可以减少页分裂的可能性，因为新行通常会被添加到B+树的末尾。
使用固定长度字段：如CHAR而不是VARCHAR，以减少因字段长度变化导致的碎片。
定期维护：定期运行OPTIMIZE TABLE或ALTER TABLE命令来减少碎片。
第三方工具：如pt-osc或gh-ost，这些工具可以在不锁定表的情况下进行在线碎片整理。

在实际操作中，应该根据表的大小和业务需求来选择合适的碎片整理策略。对于大型生产环境，可能需要在低峰时段进行操作，以减少对业务的影响。同时，建议在进行任何优化操作前备份数据，以防万一。

如何查看表碎片大小

一般 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，一般建议剩余空间在表大小以上。如果你的磁盘剩余空间不足则无法完成回收操作。