【MySQL】如何快速删除大表

前言：MySQL运维内参中有提到如何快速删除大表。看到书中案列说drop大表的时候导致MySQL实例夯住了，但是本人暂时还没有碰到过这种情况，也可能是生产中没有这么大的表，或者机器性能还不错的原因吧，但是该项技能还是需要掌握一下，以备不时之需。

一、理论基础

MySQL在删除表的时候大致做了以下工作：

• Buffer Pool页面清除过程
• 删除ibd磁盘文件的过程
  之所以删除大表会导致实例夯住的原因是如果Buffer Pool很大，或者是在Buffer Pool有很多需要清除的页面，那么遍历扫描需要占用的时间就会比较久，导致其他事物在用到相应Buffer Pool实例时就会被阻塞。
  这里摘抄一下运维内参对于MySQL drop table的过程。
• 通过buf_pool_mutex_enter(buf_pool)函数持有buffer pool mutex
• 通过buf_flush_list_mutex_enter(buf_pool)函数持有buffer pool中的flush list mutex。
• 开始扫描flush list
  • 如果脏页属于DROP TABLE，则直接从flush list列表中移除。
  • 如果占用CPU和mutex时间过长，则调用buf_flush_try_yield函数释放CPU资源、flush list mutex和buffer pool mutex，并调用ps_thread_yield()函数强制进行context switch。
  • 重新持有buffer pool mutex
  • 重新持有flush list mutex
• 释放持有flush list mutex
• 释放持有buffer pool mutex

二、实战操作

1、假设我现在有一张大表，物理文件大小达到200G，那么在drop table的过程中，删除ibd文件可能会比较久，那么我们可以在删除ibd文件之前先对ibd文件建立一个硬连接来加速删除，减少对数据库造成的影响。

mysql> select * from t1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: xucl

+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
+------+
8 rows in set (0.00 sec)

2、建立硬连接

[root@localhost xucl]# ll
总用量 220
-rw-r----- 1 mysql mysql    61 3月  23 08:21 db.opt
-rw-r----- 1 mysql mysql  8556 3月  23 08:21 t1.frm
-rw-r----- 1 mysql mysql 98304 3月  27 03:50 t1.ibd
-rw-r----- 1 mysql mysql  8556 3月  23 08:22 t2.frm
-rw-r----- 1 mysql mysql 98304 3月  23 08:22 t2.ibd
[root@localhost xucl]# ln  t1.ibd t1.ibd.hdlk
[root@localhost xucl]# ll
总用量 316
-rw-r----- 1 mysql mysql    61 3月  23 08:21 db.opt
-rw-r----- 1 mysql mysql  8556 3月  23 08:21 t1.frm
-rw-r----- 2 mysql mysql 98304 3月  27 03:50 t1.ibd
-rw-r----- 2 mysql mysql 98304 3月  27 03:50 t1.ibd.hdlk
-rw-r----- 1 mysql mysql  8556 3月  23 08:22 t2.frm
-rw-r----- 1 mysql mysql 98304 3月  23 08:22 t2.ibd

可以看到t1.ibd和t1.ibd.hdlk的inode均为2，我们知道，一个磁盘上的文件，可以由多个文件系统的文件引用，这多个文件是完全相同的，都指向同一个磁盘文件，当我们删除任何一个文件的时候，都不会影响真实的文件，只是会将其被引用数目减1，只有当被引用数为1的时候，才会删除真实的物理文件，我们可以利用这个特点，让MySQL在drop table的时候不做物理文件的删除而是使文件的引用数目减1。

3、drop table

mysql> drop table t1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: xucl

Query OK, 0 rows affected (0.00 sec)

[root@localhost xucl]# ll
总用量 208
-rw-r----- 1 mysql mysql    61 3月  23 08:21 db.opt
-rw-r----- 1 mysql mysql 98304 3月  27 03:50 t1.ibd.hdlk
-rw-r----- 1 mysql mysql  8556 3月  23 08:22 t2.frm
-rw-r----- 1 mysql mysql 98304 3月  23 08:22 t2.ibd

再看物理文件的引用数变为1，然后我们可以另外找时间手动删除物理文件。

4、删除物理文件

[root@localhost xucl]# rm -rf t1.ibd.hdlk 
[root@localhost xucl]# ll
总用量 112
-rw-r----- 1 mysql mysql    61 3月  23 08:21 db.opt
-rw-r----- 1 mysql mysql  8556 3月  23 08:22 t2.frm
-rw-r----- 1 mysql mysql 98304 3月  23 08:22 t2.ibd

至此，大表完成删除。