我要评论前言
mysql 数据库在运行过程中，随着时间的推移，可能会出现空间碎片的问题。空间碎片是指数据库表中不再使用的空间，但由于各种原因，这些空间并没有被有效地回收和再利用，从而导致数据库文件占用的磁盘空间比实际存储的数据要大。
1. 空间碎片如何产生
mysql innodb 引擎中，删除一条记录分为两种情况，一种称为删除标记（delete mark）仅在记录头部中设置 deleted_flag 标记，记录链中依然保留该记录。另一种是真正删除，将记录从记录链中移除，记录占用的空间可被重用。
如下图，record 2 被 delete mark 后，还在记录链表中。这行记录占用的空间可以理解为是空间空洞，空间空洞多起来就成为空间碎片。
标记删除导致的空间空洞，会被重新利用，但是依然可能会造成空间浪费。
如果页面内的未使用空间不足，无法容纳新插入的数据，但是碎片空间中有足够的空间，则可以对页面进行碎片回收后，再插入新的数据。碎片回收时，会先在内存中申请一个空闲页面，将存在碎片空间的旧页面中的记录依次插入到新页面，然后释放旧页面。
被动触发空间碎片回收条件，是页面空间碎片中有足够的空间，可以容纳新插入的记录，那如果无法容纳，就需要新申请页面。在大规模连续删除过的数据的表上，写入数据时，表空间可能不会明显增长或者不会增长。
除了 delete 会产生空间空洞外，update 语句也会引起空间空洞问题，比如修改 varchar 变长字符串类型字段，改短一些的时候就会出现非常小的空洞，改长的话就有可能因为页面空间不足，导致把 record 迁移到其他页面中去。
2. 空间碎片如何查看
mysql 系统表中，可以查看空间碎片情况。下方 sql 是统计库粒度空间统计信息，其中 free_mb 为空间碎片大小。
select table_schema,
round(sum(data_length + index_length + data_free) / 1024 / 1024, 2) as total_mb,
round(sum(data_length) / 1024 / 1024, 2) as data_mb,
round(sum(index_length) / 1024 / 1024, 2) as index_mb,
round(sum(data_free) / 1024 / 1024, 2) as free_mb,
count(*) as tables
from information_schema.tables
where table_schema not in ('sys', 'mysql', 'information_schema', 'performance_schema')
group by table_schema
order by 2 desc;
下方为查看指定库和指定表，空间使用情况的 sql 语句。其中 free_mb 表示碎片大小 free_pct 表示碎片率。
select table_schema,
table_name,
round((data_length + index_length + data_free) / 1024 / 1024, 2) as total_mb,
round(data_length / 1024 / 1024, 2) as data_mb,
round(index_length / 1024 / 1024, 2) as index_mb,
round(data_free / 1024 / 1024, 2) as free_mb,
concat(round(data_free / data_length, 2), ' %') as free_pct
from information_schema.tables
where table_schema = '数据库名'
and table_name = '表名'
order by total_mb desc;
3. 空间碎片如何回收
mysql 中可以使用下方命令回收空间碎片，支持 online ddl。
-- 以下 sql 效果相同 alter table tbl_name engine=innodb; -- 有审核平台有规范不允许修改存储引擎，可以使用下方 sql alter table tbl_name force, algorithm=inplace, lock=none
表中碎片多大需要回收呢？这里提供一个参考标准：单表大于 6g 且碎片率大于 30% 需要清理空间碎片。
tips: 空间碎片回收，是一个代价比较高的操作，虽然支持 online ddl，但是依然会带来额外的负载，建议业务低峰执行。如果是一套 mysql 集群，需要注意主从延迟问题。
对于一些需要周期删除的日志表，可以使用 mysql 中的分区表来管理，需要清理一批数据的时候，可以用 partition truncate 的方式进行清理，磁盘空间也能直接释放掉。
后记
总结一下，innodb 引擎中 delete 和 update 都会产生空间空洞，积累起来就会出现空间碎片问题，mysql 有对应的回收算法让空间空洞会重新利用起来，但是很难保证充分利用。空间碎片可以使用重建表的方式进行回收。
到此这篇关于mysql 空间碎片的查看与回收 的文章就介绍到这了,更多相关mysql 空间碎片内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论