MySQL 空间碎片详解

前言

MySQL 数据库在运行过程中，随着时间的推移，可能会出现空间碎片的问题。空间碎片是指数据库表中不再使用的空间，但由于各种原因，这些空间并没有被有效地回收和再利用，从而导致数据库文件占用的磁盘空间比实际存储的数据要大。

1. 空间碎片如何产生

MySQL InnoDB 引擎中，删除一条记录分为两种情况，一种称为删除标记（delete mark）仅在记录头部中设置 DELETED_FLAG 标记，记录链中依然保留该记录。另一种是真正删除，将记录从记录链中移除，记录占用的空间可被重用。

如下图，Record 2 被 delete mark 后，还在记录链表中。这行记录占用的空间可以理解为是空间空洞，空间空洞多起来就成为空间碎片。

上图来源于：YunChe MySQL 运维实战 系列文章。

标记删除导致的空间空洞，会被重新利用，但是依然可能会造成空间浪费。

如果页面内的未使用空间不足，无法容纳新插入的数据，但是碎片空间中有足够的空间，则可以对页面进行碎片回收后，再插入新的数据。碎片回收时，会先在内存中申请一个空闲页面，将存在碎片空间的旧页面中的记录依次插入到新页面，然后释放旧页面。

被动触发空间碎片回收条件，是页面空间碎片中有足够的空间，可以容纳新插入的记录，那如果无法容纳，就需要新申请页面。在大规模连续删除过的数据的表上，写入数据时，表空间可能不会明显增长或者不会增长。

除了 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;
1
2
3
4
5
6
7
8
9
10

下方为查看指定库和指定表，空间使用情况的 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;
1
2
3
4
5
6
7
8
9
10
11

3. 空间碎片如何回收

MySQL 中可以使用下方命令回收空间碎片，支持 online DDL。

ALTER TABLE tbl_name ENGINE=INNODB;
1

表中碎片多大需要回收呢？这里提供一个参考标准：单表大于 6G 且碎片率大于 30% 需要清理空间碎片。

Tips: 空间碎片回收，是一个代价比较高的操作，虽然支持 Online DDL，但是依然会带来额外的负载，建议业务低峰执行。如果是一套 MySQL 集群，需要注意主从延迟问题。

对于一些需要周期删除的日志表，可以使用 MySQL 中的分区表来管理，需要清理一批数据的时候，可以用 partition truncate 的方式进行清理，磁盘空间也能直接释放掉。

后记

总结一下，InnoDB 引擎中 Delete 和 Update 都会产生空间空洞，积累起来就会出现空间碎片问题，MySQL 有对应的回收算法让空间空洞会重新利用起来，但是很难保证充分利用。空间碎片可以使用重建表的方式进行回收。