一、背景

最近微信群里的一位网友就“引火上身”了，而其他群友则“因祸得福”了。

事情的起因是这样的，一位网友发现测试服务器上的一个 log 表达到了 100G。他想这个表太大了，而且测试环境的 log 数据，没啥用，就想着给 delete 掉。

一开始，他就直接执行 delete，发现，执行了半天，没反应。

image.png

于是，他就从 Navicat 上，强制结束了执行，选择使用 drop 进行执行。

image.png

执行 drop 命令后，发现花了更久的时间，命令还是没返回执行成功。

他就慌了，更要命的是，这个时候，各个业务线的人，都在反馈，测试环境很卡。

最后，他只好坦白从宽，去和领导和DBA 反馈问题了。

二、 原理

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

别看 drop 命令很简单，但是当执行时机不对，执行姿势不对，可能会引发“大祸”。

那么，drop 一张表的时候，MySQL 的底层都干了些什么？

它为什么会慢，如何快速的进行一张表的 drop?

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

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

针对第一点，当我们的 Buffer Pool 缓冲设置的越大，drop 时就越耗时，因此合理的 Buffer Pool 缓冲区设置就显得很重要。

第二点，尤其重要，因为它是真正存数据的文件。drop 数据慢，最大的原因，就是和它有关。

在DROP TABLE的时候，所有进程不管是DDL还是DML都被HANG起，直到DROP结束才继续执行。
这是因为INNODB会维护一个全局独占锁（在table cache上面），直到DROP TABLE完成才释放。
在我们常用的ext3,ext4，ntfs文件系统，要删除一个大文件（几十G，甚至几百G）还是需要点时间的。

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

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

image.png image.png

当多个文件名同时指向同一个 INODE 时，这个 INODE 的引用数 N > 1，删除其中任何一个文件名都会很快。因为其直接的物理文件块没有被删除，只是删除了一个指针而已；当 INODE 的引用数 N = 1 时，删除文件需要去把这个文件相关的所有数据块清除，所以会比较耗时。

三、操作

因此，我们可以合理的利用这个机制，给数据库表的 .ibd 文件创建一个新的硬链接。
如果是主从架构，请在所有机器上创建硬链接。
当删除表时，删除物理文件时，其实删除的就是物理文件的一个指针，所以删除操作响应速度会非常快，不到 1 秒就能完成这个操作。

image.png

最后就是要真正删除掉物理文件，释放文件所占用的磁盘空间。这一步我就不展开说了，网上有很多工具，都可以做到。

直接从硬盘文件中删除：

$  rm  -f  table_test.ibd.bak

分步删除，最后从硬盘中删除文件

100 G 文件，每删除1G内容歇1秒，直到最后文件只剩下1G，删除文件

$  for i in `seq 100 -1 1 ` ;   do sleep 1;  truncate  -s  ${i}G  table_test.ibd.bak; done 
$  rm  -f  table_test.ibd.bak

我们这里讲了大表的 drop，同样的，这个过程这适合大库 drop。如果一个数据库比较大，那我们可以先删除其中较大的表，最后在执行 DROP DATABASE 删除整个库，对大表的删除可参见上面的方法。

如果要重建表，那么可以在删除表之前，先备份建表语句。

show create table table_test;

四、参考

如何在Linux中使用 Truncate 命令
http://blog.itpub.net/31559985/viewspace-2738955

MySQL删除大表更快的drop table办法
https://www.cnblogs.com/bjx2020/p/9105044.html

面试官：如何快速的 drop 掉一个 100G 的大表?
https://mp.weixin.qq.com/s/cY3vxkXn86cyTszR4URE4w