mysql删除大表更快的drop table办法

水深无声 2022-05-24 08:51 304阅读 0赞

**[利用硬链接和truncate降低drop table对线上环境的影响][truncate_drop table]**

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

**尝试5：**又找了另一位大牛，这下得解救了，翻身农奴了，拜啊，三柱香，牛！想知道是怎么做的吗？我知道你想，嘻嘻，别着急，是这样的，**建硬链接**

在DB server上，找到mytable表对应的文件，我的是/data/mysql3306/data/mydatabase/。

在这个目录下，我们可以看到以下记录，真的是390G！吓死银呀！

-rw------- **1** oracle oinstall  **46672** Aug 30 15:42 **mytable.frm**

-rw------- **1** oracle oinstall **391466975232** Aug 30 15:42 **mytable.ibd **

以上记录中，1表示该文件只有一个链接（没有另外的人链接到它，要删就是真的删文件本身了哦），怪不得我执行truncate/drop这么慢，原来背后就是在删这个东东呀！

那怎么办呢？能不能绕过，不删文件本身，先快速把表drop掉该多好呀。那么建硬链接ln可以完成。

**         ln mytable.ibd  mytable.ibd.h**

**         ln mytable.frm  mytable.frm.h**

**         **相当于一个文件被两个索引链接着，要删就是只删链接，而不是删文件本身了，直到只有一个人链接它，才会是真的删呢。

这时再执行drop表的动作，别提多快了，oh my god，快到惊人，mytable.ibd瞬间它不见了，不见了！

最后别忘了把那个大大的“真文件”手工删删掉

**         rm -f mytable.ibd.h**

**         rm -f mytable.frm.h**

所以，同学们，乡亲们，最最亲爱的屌丝们，我痛完了，也絮叨完了，希望你疼的时候能看到这篇博客，帮你节约哪怕一杯茶的时间，也值了。共勉！

众所周知drop table会严重的消耗服务器IO性能，如果被drop的表容量较大，甚至会影响到线上的正常。

首先，我们看一下为什么降容量大的表会影响线上服务

--------------------

直接执行drop table，[MySQL][]会将表定义和表数据全都删除，包括磁盘上的物理文件，也包括缓冲池中的内存数据。

这么分两步，第一步从缓冲池中删除，这会涉及到table\_cache的锁，如果持有table\_cache的锁，这将导致其他查询都无法执行。这种情况在没有innodb\_per\_table之前尤为严重。另外，mysql5.5.23之后添加lazy drop table功能，这个功能用来解决互斥的LRU列表。其中心思想就是加锁，找到需要被删除的页面，删除1024个页之后释放锁让其他线程工作，之后循环而percona的lazy drop处理起来更优雅一些，其会先加锁，然后找到需要被删除的页面，标记，释放锁，后台慢慢删除。

![151414271973357.png][]

之后就是第二步，这步在大容量表的时候更为消耗时间，那就是在OS上删除物理文件。大家都知道在EXT3上RM一个200G的文件会非常耗时，这是由于EXT3存储数据的结构导致，如果一个很大的文件，EXT3的i\_block无法直接存放，需要多层嵌套才能完全存储下，在这种情况下由于映射的层次多，并且由于多层映射也不会是顺序存储的，就导致了很大的随机IO，这就导致了删除物理文件非常慢的现象。在这种情况下，建议升级到EXT4，这是由于EXT4比ext3的使用程度分配存储空间，其最大的优势就是顺序存储。

EXT3：

![151428432285734.jpg][]

EXT4：

![151428588223037.jpg][]

知道了原因，我们来说说如何解决具体步骤如下：

--------------------

1，建立硬链接。

ln table.ibd table.idb.hdlk

2，[mysql][MySQL]执行drop table操作。

drop  table  if  exists tablename;

3，使用截断删除物理文件。

truncate -s 1024 * 1024 * 4 filename

其实硬链接和drop table就不用多说了，在建立硬链接之后，mysql会认为rm了硬链接文件之后就算操作完毕，不会真正去删除物理文件从而提高了速度。但是对于服务器来说，实际的物理文件还在，如果手动RM，还是会产生很多的IO影响，这时候就用到了截断这个工具。这个工具会根据指定的尺寸大小进行逐步删除，会将对IO造成的影响降到最低。

![复制代码][copycode.gif]

用法：truncate OPTION ... FILE ...
    缩小或扩展每个FILE的大小到指定的大小
    
    创建不存在的FILE参数。
    
    如果文件大于指定的大小，则额外的数据将丢失。
    如果文件较短，则延长部分（孔）
    读为零字节。
    长期期权的
    强制性论据也适用于 短期期权。
      -c，--no创建         做不创建任何文件
       -o，--io- 块对待SIZE作为IO块而不是字节数
       -r，--reference = 上的RFile的RFile基底大小
       -s，--size = SIZE通过SIZE设置或调整文件大小
           - 帮助显示此帮助和退出
           - 版本输出版本信息并退出
    
    SIZE可以是（或可以是可选的后面的整数）以下之一：对于G，T，P，E，Z，Y，
    KB 1000，K 1024，MB 1000 * 1000，M 1024 * 1024等。
    
    SIZE也可以前缀为以下修改字符之一：
    ` + ' 延伸，` - '减少，`< ' 至多，`> ' 至少，
    ` / ' 循环到多个，'％' 轮到多个。
    
    报告截断bug - coreutils@gnu.org
    GNU coreutils主页： http：// www.gnu.org/software/coreutils/> 
    一般帮助使用GNU软件：<http：// www.gnu.org/gethelp/> 
    有关完整的文档，请运行：info coreutils ' 截断调用“

[root@127.0.0.1][root_127.0.0.1] : test 21:39:34> drop table tt ;  
Query OK, 0 rows affected (25.01 sec)

删除一个11G的表用时25秒左右（硬件不同，时间不同）；

下面我们来对另一个更大的表进行删除；  
但之前，我们需要对这个表的数据文件做一个硬连接：

root@ \# ln stock.ibd stock.id.hdlk  
root@ \# ls stock.\* -l  
\-rw-rw—- 1 [MySQL][] [mysql][MySQL]        9196 Apr 14 23:03 stock.frm  
\-rw-r–r– 2 mysql mysql 19096666112 Apr 15 09:55 stock.ibd  
\-rw-r–r– 2 mysql mysql 19096666112 Apr 15 09:55 stock.id.hdlk

你会发现stock.ibd的INODES属性变成了2；

[root@127.0.0.1][root_127.0.0.1] : test 21:39:34> drop table stock ;  
Query OK, 0 rows affected (0.99 sec)

1秒不到就删除完成； 也就是DROP TABLE不用再HANG这么久了。  
但table是删除了，数据文件还在，所以你还需要最后数据文件给删除。

root \# ll  
total 19096666112  
\-rw-r–r– 2 mysql mysql 19096666112 Apr 15 09:55 stock.id.hdlk  
root \# rm stock.id.hdlk  
虽然DROP TABLE 多绕了几步。(如果你有一个比较可靠的自运行程序（自动为大表建立硬链接，并会自动删除过期的硬链接文件），就会显得不那么繁琐。)  
这样做能大大减少MYSQL HANG住的时间； 相信还是值得的。

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

[truncate_drop table]: http://blog.csdn.net/jiao_fuyou/article/details/25873677
[MySQL]: http://lib.csdn.net/base/mysql
[151414271973357.png]: /images/20220524/9af1fb0da0f1487fa7538ab5ed51ad10.png
[151428432285734.jpg]: /images/20220524/ba3b401f636d4cd48a50b54dae49477c.png
[151428588223037.jpg]: /images/20220524/a0bda205ac1d440c8d390f12c62c2a7e.png
[copycode.gif]: /images/20220524/931e438721104c79ae5a0a67a666567b.png
[root_127.0.0.1]: mailto:root@127.0.0.1