MySQL 空间碎片详解

文章目录

- 前言
- 1. 空间碎片如何产生
- 2. 空间碎片如何查看
- 3. 空间碎片如何回收
- 后记

前言

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;

下方为查看指定库和指定表，空间使用情况的 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;