业务背景：因为业务表中的数据量达到了1.5亿条，导致数据查询很慢，现在需要将部分数据迁移到HBase中，迁移的数据使用delete命令进行删除，但是删除之后发现表所占用的磁盘空间并没有少，后来经过查询才知道是因为数据的删除导致了表数据空洞。

为什么使用delete进行数据的删除就会导致表数据空洞呢？

做个实验

首先整一张表结构：订单表 order，主键是 id，另外还有一个索引 index_city 用 city 字段建索引。

CREATE TABLE `order` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `user_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户编号', `goods_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品名称', `order_date` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间', `city` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '下单城市', `order_num` int(10) NOT NULL COMMENT '订单号数量', PRIMARY KEY (`id`) USING BTREE, INDEX `city_index`(`city`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2000002 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '商品订单表' ROW_FORMAT = Compact;

下面先来看看删除数据的流程

InnoDB 里的数据都是用 B+ 树的结构组织的，假设现在我们表里的数据长这样：

我删除 id = 10 的这行数据，MySQL 实际上只是把这行数据标记为已删除，并不会回收表空间，而是给后来的数据复用。

那怎么复用呢？总得有规则吧？如果这时客户端申请插入的是 id 在 (8，18) 范围内的数据，此时 id = 10 的位置就会被复用。比如我插入 id=11 的记录就会复用 id=10 的空间。但如果插入的是 id = 20 的数据就没法复用这个空间了。

2.1 整页删除

InnoDB 的数据是按页存储的，如果删掉了一个数据页上的所有记录，会怎么样？那就是这个页的所有数据都能被复用。

但是数据页的复用跟记录的复用是不同，记录的复用有限定范围，而数据页的复用并没有限制。举例：如果我现在把 P2 整页数据删除，那么限制我要插入 id = 50 的数据也是可以被复用，当然这时候 P2 页的范围就不再是 id (8，19) 了。

2.2 什么是数据 "空洞"？

如果相邻的两个数据页利用率都很小，MySQL 会把这两个页的数据合到其中一个页，另外一个被标记为可复用。

当然，如果用 delete 删除整个表数据的结果就是：所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。

所以，delete 命令其实只是把记录的位置，或者数据页标记为了可复用，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。

这些可以复用，而没有被使用的空间，被称为空洞。

新增数据

不止是删除数据会造成空洞，插入数据也会

如果数据是随机插入，非主键自增的，就可能造成索引的数据页分裂。

下图中，假设数据页 P2 已满，这时再插入 id=16 的记录，就需要申请一个新的 P3 页来存储数据。等到页分裂完成后，P2 的末尾就留下了空洞（PS：实际上，可能不止 1 个记录的位置是空洞）。

但是如果数据是按照索引递增顺序插入的，索引就是紧凑的，就不会有页分裂这回事。这也是为什么数据库要设置自增 ID 的主要原因

修改数据

不仅是插入数据，更新数据也会造成空洞。很多人可能不理解这个过程，更新数据主键都没变怎么会造成数据空洞呢？实际上更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。

比如，我把 id = 10 的城市从北京改成东京，就会造成空洞。

你可能会说不对啊，上图中 id 都没变怎么会数据空洞呢？实际上文章开头就说了，city 这个字段是二级索引，索引 index_city 的值从北京变成南京，北京的索引数据会标记为删除，然后重新建立南京的索引数据，一删一增的过程就产生了空洞。

总结一句：更新过程中如果有索引更新了，就会造成数据空洞。也就是二级索引树更新造成的数据空洞

解决表数据空间漏洞

从上面的结论你也知道了，大量的增删改确实会造成空洞的。如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表就能做到。具体怎么做呢？

拿 order 表举例，可以新建一个临时表 order_tmp，它的表数据结构与 order 完全相同。然后按 id 从小到大的顺序把数据从 order 表读出来插入到 order_tmp 表。

此时，由于 order_tmp 并没有数据空洞，所以它的主键索引更紧凑，数据页利用率更高。等到迁移完成，可以用 order_tmp 表替代 order 表，从而收缩 order 表的空间。

以上描述的一系列操作，是不是觉得超级麻烦？贴心的 MySQL 在 5.5 版本之前，提供了以下命令来重建表，回收空间。

alter table order engine=InnoDB

执行它，临时表 order_tmp 不需要你自己创建，MySQL 会自动完成转存数据、交换表名、删除旧表的操作。

我画个流程图，帮助大家理解下：

看到这里你可能觉得完美解决了空洞问题，其实不然，这个方案最大的缺点就是：表重构过程中，往临时表插入数据是很耗时的；如果有新的数据写入 order 时，不会被迁移，会造成数据丢失。

Online DDL

那咋办呢？MySQL 5.6 版本开始引入的 Online DDL，解决了这个问题。引入了 Online DDL 之后，重建表的流程只这样的：

建立一个临时文件，扫描表 order 主键的所有数据页；
用数据页中表 order 的记录生成 B+ 树，存储到临时文件中；
生成临时文件的过程中，将所有对 order 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 order 相同的数据文件，对应的就是图中 state3 的状态；
用临时文件替换表 order 的数据文件。

上图，方便你们理解：

由于日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表 A 做增删改操作。

总结：

这篇文章我们聊了 MySQL 中大量的增删改都有可能造成数据空洞、数据库中收缩表空间的方法。其中 delete 命令是不会回收表空间的，还要通过 alter table 命令重建表，才能达到表文件变小的目的。

这个命令在 5.6 版本以及之后可以考虑在业务低峰期使用的，但在 5.5 及之前的版本，这个命令是会阻塞 DML 的，建议你慎重。

另外，重建表都会扫描原表数据和构建临时文件。对于大表来说，这个操作是很消耗 IO 和 CPU 的。因此，如果是线上服务你要很小心地控制操作时间。如果想要比较安全的操作的话，推荐使用 GitHub 开源的 gh-ost 来做。

那么在日常工作当中如何从源头解决这个问题呢？

对一些日志表，或者是有区域性特征的表，建议使用 MySQL 的分区表来管理，需要清理一批数据的时候，可以用 partition truncate 的方式进行清理，磁盘空间也能直接释放掉。

如何实现表分区呢？

MySQL InnoDB 存储引擎支持表分区的功能是在 MySQL 5.1 版本中引入的，因此您需要使用 MySQL 5.1 或更新的版本才能使用表分区功能。请注意，随着不同版本的 MySQL 的发布，表分区功能也有所改进和增强。

要在 MySQL InnoDB 中实现表分区，您可以按照以下步骤操作：

创建一个支持表分区的表：首先，您需要创建一个支持表分区的表。在创建表时，使用 PARTITION BY 子句来指定分区策略，例如按范围、按列表、按哈希等。以下是一个示例创建表并按范围分区的 SQL 语句：

CREATE TABLE my_partitioned_table (
    id INT,
    name VARCHAR(50)
)
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (200),
    PARTITION p2 VALUES LESS THAN (MAXVALUE)
);

上述示例中，my_partitioned_table 表被按 id 列的范围分成了三个分区。

插入数据：接下来，您可以向分区表中插入数据。数据将根据分区策略自动放入适当的分区。
查询分区表：您可以像查询普通表一样查询分区表。MySQL InnoDB 会根据查询条件自动定位到适当的分区，这使得查询分区表非常方便。
管理分区：您可以使用 ALTER TABLE 语句来添加、删除、合并或拆分分区，以满足不同的需求。例如，您可以使用以下命令来添加一个新的分区：

ALTER TABLE my_partitioned_table ADD PARTITION (
    PARTITION p3 VALUES LESS THAN (300)
);

当然，表分区也并不能就可以解决掉空间空洞的问题，还需要以下几点：

数据删除和维护：使用表分区时，您可以更容易地删除整个分区中的数据，而不必对整个表进行操作。这可以帮助释放分区中的未使用存储空间，从而减少空间洞。
数据迁移：您可以将数据从一个分区复制到另一个分区，然后删除原分区中的数据，以整理数据并减少空间洞。这种数据迁移操作更容易在表分区中执行，而不必对整个表进行操作。
管理和维护：表分区可以使您更容易执行管理和维护操作，例如合并分区、拆分分区或添加新分区。这些操作可以有助于优化表的数据布局，减少空间洞的发生。

请注意，表分区并不能完全消除空间洞问题，因为它们只是一种数据组织和管理的手段。空间洞问题可能仍然存在，特别是在大量数据删除或更新的情况下。要彻底解决空间洞问题，可能需要定期执行表的优化、整理和维护操作，或使用专门的工具来处理未使用的存储空间。表分区只是数据库管理工具的一部分，可以在某些情况下有助于减轻空间洞问题的影响。

Mysql生产问题-磁盘漏洞