如何快速的 drop 掉一个超过 100G 的大表？

这篇文章主要介绍怎么实现快速删除一个超过 100G 的大表，正常的我们都会执行命令 delete from test 来删除表。

但是会有一个问题，就是执行 delete 命令之后，删除没有反应，一直卡顿在哪里。于是强制结束了执行并且选择 drop（drop table test） 进行删除，但执行 drop 命令后，发现花了更久的时间，命令还是没返回执行成功，这到底是怎么回事呢？

出现这个情况的原因就是因为，在 drop 的时候，InnoDB 维护了一个全局独占锁（在 table cache 上面），直到 drop 完成才释放。drop 会严重的消耗服务器 IO 性能，如果被 drop 的表容量较大，甚至会影响到线上的正常。造成长时间的业务无法访问数据库，更严重，导致数据库崩溃，宕机都是有可能的。

这意味着，如果在白天，访问量非常大的时候，如果我们在不做任何处理措施的情况下，执行了删大表的命令，整个 MySQL 就挂在那一动不动的，可能无法访问数据库、数据库崩溃、宕机等。

所以，今天我们就来分析分析 MySQL 的底层都干了些什么，为什么它会这么慢，并且有什么办法快速的进行一张表的 drop 操作而不卡顿和响应结果。

从高性能 MySQL 一书中，我们得出 MySQL 的 drop 命令，主要干了两件事，如下所示：

• 清除 Buffer Pool 缓冲
• 删除对应的磁盘数据文件 ibd

第一步：在 drop table 时，InnoDB 引擎会清理该表在每个 Buffer Pool 实例中中对应的数据块页面，为了避免对系统的影响，这里的清除操作并不是真正的 flush，而是将涉及到的页面从 flush 队列中摘除。但在摘除过程中，删除进程会持有每个 Buffer Pool 的全局锁，然后搜索这个 Buffer Pool 里对应的页面以便从 flush list 中删除。如果在 Buffer Pool 中需要被搜索并删除的页面过多，那么遍历时间就会增大，这就导致了其他事务操作被阻塞，严重时可导致数据库锁住。

在这里还需要注意一件事情，当我们的 Buffer Pool 缓冲设置的越大，导致遍历时间变长 ，drop 时就越耗时，因此合理的 Buffer Pool 缓冲区设置就显得很重要。

清理 Buffer Pool 时，还包含清理 AHI 包含此表的数据，AHI 在这里就不细说，主要是当 B+tree 的层级变高时，为避免 B+tree 逐层搜索，AHI 能根据某个检索条件，直接查询到对应的数据页，跳过逐层定位的步骤。其次 AHI 会占用 1/16 的 Buffer Pool 的大小，如果线上表数据不是特别大，不是超高并发，不建议将开启 AHI，可以考虑关闭 AHI 功能，关于drop table 时关闭 AHI，这篇文章后面也有了结论：https://www.likecs.com/show-72366.html

设置 AHI 命令：

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name                   | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+
1 row in set (0.01 sec)


mysql> SET GLOBAL innodb_adaptive_hash_index=OFF;
Query OK, 0 rows affected (0.00 sec)


mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name                   | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | OFF   |
+----------------------------+-------+
1 row in set (0.01 sec)

第二步：假设我们现在有 datadir = /data/mysql/，另外，我们有有一个 database，名为test 。

在数据库 test 中，有一个表 user。

执行下列命令：

mysql> system ls -l /data/mysql/mytest/

得到 user 表结构和索引:

-rw-r----- 1 mysql mysql          9023  6 14 11:21 user.frm
-rw-r----- 1 mysql mysql 2356792000512  6 14 11:21 user.ibd

• .frm 文件：保存了每个表的元数据，包括表结构的定义等，该文件与数据库引擎无关。
• .ibd 文件：保存了每个表的数据和索引的文件。

user.ibd 这个文件，它是真正存数据的文件，drop 数据慢，最大的原因，就是和它有关。

在我们删除物理数据文件时，如果数据文件过大，删除过程会产生大量的 IO 并耗费更多的时间，造成磁盘 IO 开销飙升，CPU 负载过高，影响其他程序运行。

那么如何解决这个问题呢？

好在 Linux 提供的有硬连接特性，我们可以合理的利用这个特性，加快删除速度。

软链接其实大家可以类比理解为 windows 中的快捷方式，就不多介绍了，主要介绍一下硬链接。

硬链接是指通过索引节点来进行链接。在 Linux 的文件系统中，保存在磁盘分区中的文件不管是什么类型都给它分配一个编号，称为索引节点号(Inode Index)。在 Linux 中，多个文件名指向同一索引节点是存在的。一般这种链接就是硬链接。

硬链接的作用是允许一个文件拥有多个有效路径名，这样用户就可以建立硬链接到重要文件，以防止误删的功能。其原因如上所述，因为对应该目录的索引节点有一个以上的链接。只删除一个链接并不影响索引节点本身和其它的链接，只有当最后一个链接被删除后，文件的数据块及目录的链接才会被释放。也就是说，文件真正删除的条件是与之相关的所有硬链接文件均被删除。

硬链接特点如下：

• 文件有相同的 inode 及 data block。
• 只能对已存在的文件进行创建。
• 不能交叉文件系统进行硬链接的创建。
• 不能对目录进行创建，只可对文件创建。
• 删除一个硬链接文件并不影响其他有相同 inode 号的文件。

首先看看 Linux 系统的硬链接示意图：

那么硬链接的流程怎么样的呢，如下：

1、对于真正存储的文件来说，只有一个。

2、然后有一个文件名指向上面的 Node Index。

3、那么，现在又有一个文件名指向 Node Index，硬链接就是不止一个文件指向 Node Index。

4、这个时候，使用 drop 删除文件名 1 的操作，Linux 系统检测到，还有一个文件名 2 指向 Node Index，因此并不会真正的把文件删了，而是把步骤 2 的引用给删了，这步操作非常快，这是因为只是删除引用。

接下来，我们再删除文件名 2 ，Linux 系统检测到，没有其他文件名指向该 Node Index，就会删除真正的存储文件，这步操作，是删真正的文件，所以比较慢。

下面就来演示一下具体的操作

先给 user.ibd 建立一个硬链接，利用 ln 命令

mysql> ln user.ibd user.ibd.bak

此时，文件目录如下所示

-rw-r----- 1 mysql mysql          9023  6 14 11:21 user.frm
-rw-r----- 2 mysql mysql 2356792000512  6 14 11:21 user.ibd
-rw-r----- 2 mysql mysql 2356792000512  6 14 14:17 user.ibd.bak

此时，执行 drop 操作

mysql> drop table user;
Query OK, 0 rows affected (0.99 sec)

当我们指向 drop 以后不到 1 秒就删除了。这是为什么？此时有两个文件名称 user.ibd 和 user.ibd.bak，同时指向一个 inode，如果给数据库表的 .ibd 文件创建一个硬链接，当删除表时，删除物理文件时，其实删除的就是物理文件的一个指针，所以删除操作响应速度会非常快，大约不到 1 秒左右。

但是磁盘空间，还没释放，因为还剩一个文件 user.ibd.bak，最后就是要真正删除掉物理文件，释放文件所占用的磁盘空间，那么如何正确的删除 user.ibd.bak 呢？

这里推荐大家使用 coreutils 工具集中的 truncate 命令来删除，安装使用网上很多例子，这里不再细说

删除脚本如下：

#!/bin/bash


TRUNCATE=/usr/local/bin/truncate
for i in `seq 100 -10 10 `; 
do 
  sleep 2
  $TRUNCATE -s ${i}G /data/mysql/user.ibd.bak 
done
rm -rf /data/mysql/user.ibd.bak;

从 100G 开始，每次缩减 10G，停 2 秒，继续，直到文件只剩 10G，最后使用 rm 命令删除剩余的部分。

最后，建议对生产环境数据进行批量操作删除，不要在访问服务高峰期做 drop 操作，一定要在低峰期做。