MySQL的表空间释放

概述

最近为了对 MySQL 数据库磁盘占用瘦身，对一张近100GB表的历史数据进行了 delete 删除，删除了约2/3的数据，删除后发现该表占用的空间并未减少。通过下面语句查看该表的磁盘占用情况：

1. SELECT
2.     TABLE_NAME AS`表名`,
3.     ROUND(DATA_LENGTH/1024/1024/1024,2)AS`数据大小(GB)`,
4.     ROUND(INDEX_LENGTH/1024/1024/1024,2)AS`索引大小(GB)`,
5.     ROUND(DATA_FREE/1024/1024/1024,2)AS`碎片空间(GB)`,
6.     ROUND((DATA_LENGTH+INDEX_LENGTH+DATA_FREE)/1024/1024/1024,2)AS`预估总占用(GB)`
7. FROM
8.     information_schema.TABLES
9. WHERE
10.     TABLE_SCHEMA='your_table_schema'
11. ORDER BY
12.     (DATA_LENGTH+INDEX_LENGTH)
13. DESC;

复制代码

发现总的空间并没有发生变化，只是表数据空间减小，而碎片空间大幅上升：

所以，为什么数据删除了，表占用的磁盘空间却没有释放？
这里先交代一下全文 MySQL 相关的论述的基础：MySQL 版本为5.7.36，存储引擎为 InnoDB，参数innodb_file_per_table=ON。
为什么删除数据表空间未释放？

为了解释这个问题，先探究一下 MySQL 数据删除的过程。
如上图，现在要删掉 R7 这个记录，InnoDB 引擎只会把 R7 这个记录标记为删除，以待后续复用，但磁盘文件的大小并不会缩小。由于 InnoDB 的数据是按页存储的，那么如果删掉了一个数据页上的所有记录，整个数据页就也是可以被复用的。
然而，数据页的复用跟记录的复用是不同的。
记录的复用，只限于符合范围条件的数据：比如上面的假设，R7 这条记录被删除后，如果插入一个 ID 是 008 的行，那么可以直接复用这个空间；但如果插入的是一个 ID 是 012 的行，就不可以复用这个位置了。
数据页的复用，是当整个页从 B+ 树里面删掉以后，可以复用任何位置：以上图为例，如果将数据页 page A 上的所有记录被删除以后，page A 就会被标记为可复用，但是磁盘上的文件不会变小。
所以，delete 命令其实只是把记录的位置或数据页标记为了「可复用」，但不会改变磁盘文件的大小，即只通过 delete 是不能释放表空间的。这些可以复用，而又没有归还给操作系统的空间，看起来就像是“干净的地板上散落的碎片”。
如果能把这些碎片清理干净，那么就达到释放表空间的目的。
如何释放碎片占用的空间？

查阅 MySQL 官方文档-Defragmenting a Table，发现可以使用如下语句整理碎片：

1. ALTER TABLE tbl_name ENGINE=INNODB;

复制代码

当然也可以使用下面的的语句整理碎片：

1. ALTER TABLE tbl_name FORCE;

复制代码

这两种操作都使用到了在线 DDL 来重建表。那在线 DDL 又是什么？
在线 DDL 重建表的流程是什么？

使用ALTER 语句重新表的在线 DDL 机制大致如下：

• ALTER语句在初始化启动的时候需要获取 MDL 写锁；
  
• MDL写锁在真正拷贝数据之前就退化成读锁，MDL 读锁不会阻塞增删改等 DML 操作，InnoDB把这些操作记入 ROW_LOG；
  
• 最后完成存量数据相关拷贝，MDL 升级为独占锁，重放 ROW_LOG以写入增量数据。
  

而整个 DDL 最耗时的过程就是拷贝目标表数据到中间表文件的过程，这个过程中又可以增删改数据，而相对于整个 DDL 过程来说，锁的时间非常短，所以对业务来说，就可以认为是没有中断且在线的，这就是称为在线 DDL 的缘由，官方称之为 Online DDL。

在 Online DDL 过程中哪些操作可以触发重建表，哪些操作是“原地”或称“就地”操作，官方给出了详细说明，这里罗列一些开发中常见的一些操作：

Online DDL 过程中会产生临时文件，而这些临时文件也需要占用磁盘空间，对应的磁盘空间要求，官方也出了明确描述：

上面的 ALTER 语句在 MySQL 5.7下隐含的意思其实是：

1. ALTER TABLE tbl_name engine=innodb,ALGORITHM=INPLACE;

复制代码

同时整个过程不需要经过服务层搬运数据，整个过程是在 InnoDB 内部完成的，是一个“原地”、“就地”操作，这就是称为“inplace”的来源。
跟 INPLACE 相对应的就是拷贝表：

1. ALTER TABLE tbl_name engine=innodb,ALGORITHM=COPY;

复制代码

当使用 ALGORITHM=COPY 的时候，表示的是强制拷贝表，这是 MySQL5.6版本以前的重建表流程：

• MySQL服务层触发创建临时表，对源表加MDL锁，阻塞DML写而不阻塞DML读；
  
• MySQL服务层从目标表中逐行读取数据，写入到临时表；
  
• 数据拷贝完成后，禁止读写，删除目标表，把临时表重命名为目标表。
  

上面的流程大致如下图：

如何理解 INPLACE 和 COPY

想象一个堆着货物又杂乱无章的仓库，现在要进行整理，以充分利用仓库内的空间：
INPLACE 就是在仓库内部整理，货物不用搬出仓库，货物按照编号摆放到货架。在这个过程中，可能要从货架临时取出一些货物，把它们放到仓库内指定的某处，以腾出空间排放正确编号的货物，所以仓库要有足够的空间，来临时堆放那些从货架临时取出的货物；
COPY 就是把仓库里的货物从货架取出，然后全部搬到仓库外面指定的某个场地（这个场地的空间需要足够大，以容纳所有货物），然后再按照编号顺序一件一件的搬回仓库，放到货架。
操作注意事项

• 由于通过 Online DDL 方式整理碎片的过程中会产生和原数据表空间大小几乎规模相当的临时文件，所以执行前一定要确认 MySQL 磁盘剩余可用空间是否足够；
  
• Online DDL在ALTER表过程中，会通过过 ROW_LOG 临时文件记录当时的并发 DML 操作，这个临时文件的大小上限由参数innodb_online_alter_log_max_size决定（默认大小128MB），所以要在业务低峰期操作碎片整理，保证没有大量 DML 操作，或者调整innodb_online_alter_log_max_size大小；
  
• 在碎片整理过程中，可以通过以下方式来监控整理过程：
  

1. -- 监控磁盘临时文件
2. SHOW STATUS LIKE 'Created_tmp_disk_tables';
3. SHOW STATUS LIKE 'Created_tmp_files';
5. -- 查看正在进行的 DDL 操作
6. SELECT * FROM information_schema.INNODB_TRX;

复制代码

附录：
Online DDL 参考文档

• 如果你觉得我的工作对你有帮助，可以通过分享这篇文字或者关注同名公众号来支持我，你的支持是我持续创作的动力：
  
  
  
  
• 转载以及引用请注明原文链接：https://www.cnblogs.com/wrinkle-of-silicon/p/18893608
  
• 本博客所有文章除特别声明外，均采用CC 署名-非商业使用-相同方式共享 许可协议。
  

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！