mysql怎么把表中的信息全部删完（面试官mysql表删除一半数据）

这期面试官提的问题是：

MySQL 表删除一半数据，表空间是否会变小？为什么？

我：

你这么问，肯定是不会？但是我不知道为什么（理直气壮.jpg）

国际惯例先上思维导图：

遇到这种问题先做一波实验，我的思路验证下是否会删除。声明：此次实验采用的 MySQL 版本是 5.7，引擎是 InnDB

往期精彩

MySQL 查询语句是怎么执行的？

MySQL 索引

MySQL 日志

MySQL 事务与 MVCC

MySQL 的锁机制

MySQL 字符串怎么设计索引？

面试官：数据库自增 ID 用完了会咋样？

面试官：order by 怎么优化？

面试官：count (*) 怎么优化？

面试官：explain 应该关注哪些指标？

01 做个实验

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

CREATETABLE`order`( `id`bigint(20)NOTNULLAUTO_INCREMENTCOMMENT'主键', `user_code`varchar(64)CHARACTERSETutf8COLLATEutf8_general_ciNOTNULLCOMMENT'用户编号', `goods_name`varchar(64)CHARACTERSETutf8COLLATEutf8_general_ciNOTNULLCOMMENT'商品名称', `order_date`timestamp(0)NULLDEFAULTCURRENT_TIMESTAMPCOMMENT'下单时间', `city`varchar(64)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT'下单城市', `order_num`int(10)NOTNULLCOMMENT'订单号数量', PRIMARYKEY(`id`)USINGBTREE, INDEX`city_index`(`city`)USINGBTREE )ENGINE=innodbAUTO_INCREMENT=2000002CHARACTERSET=utf8COLLATE=utf8_general_ciCOMMENT='商品订单表'ROW_FORMAT=Compact;

1.1 插入数据

造点数据，为了效果。我直接造 200W 条数据，然后直接 delete 删掉一半。

//第一步：创建函数 delimiter// DROPPROCEDURE IF EXISTSproc_buildata; CREATEPROCEDUREproc_buildata(INloop_timesINT)BEGIN DECLAREvarINTDEFAULT0; WHILE var<loop_timesDO SETvar=var 1; INSERTINTO`order`(`id`,`user_code`,`goods_name`,`order_date`,`city`,`order_num`) VALUES (var,var 1,'有线耳机','2021-06-2016:46:00','杭州',1); ENDWHILE; END//delimiter; //第二步：调用上面生成的函数，即可插入数据 CALLproc_buildata(2000000);

插入完成，耗时贼久。建议批量插入：

插入完成，到 MySQL 查看文件大小对应文件大小（下图中的 .idb 文件）

200W 数据大概是 184M 左右的大小：

1.1.1 一些小知识

1、一个 InnoDB 表包含表结构定义和数据两部分，在 MySQL 8.0 版本以前，表结构是存在于 .frm 为后缀的文件里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了

2、表数据既可以存在共享表空间里，也可以是单独的文件。由参数 InnoDB_file_per_table 控制。MySQL 5.6.6 版本之后，默认是 ON，也即每个 InnoDB 表数据以及索引存储在一个以 .ibd 为后缀的文件中。

3、为方便管理建议你设置为 ON，因为当你不需要这个表时，通过 drop table 命令，系统直接删除这个文件。而如果放在共享表空间中，即使表删掉了，空间也是不会回收的。

4、由于表结构文件一半很小，本文讨论的表空间是指表数据文件 .ibd 的变化。

1.2 删除数据

批量删除其中的 100W 的数据，此时的总数据量：

再次查看 order.ibd 文件的大小，还是 184M。也就是说 MySQL 表删除一半数据之后，表空间并没有随之减小，好特么奇怪呀。

这是为啥呢？这就得说说 MySQL 删除数据的流程了

02 删除数据流程

还记得我之前讲的索引原理么？不清楚的朋友们，请看以下这篇文章，看看 InnDB 索引是怎么组织数据的。不然你是看不懂下面的过程的。

MySQL 索引原理

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 命令是不能回收表空间的。

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

03 新增数据

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

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

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

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

04 修改数据

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

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

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

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

05 重建表，回收空间

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

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

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

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

altertableorderengine=InnoDB

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

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

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

5.2 Online DDL

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

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

上图，方便你们理解：

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

06 总结

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

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

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

6.1 参考

• https://time.geekbang.org/column/article/73479
• https://mp.weixin.qq.com/s/B0frdGgUciYckRNfRkcRvw

原文链接：https://mp.weixin.qq.com/s/7LIyobRjrIpGJ3J6BMEcxA

,