前言：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文件建立一个硬连接来加速删除，减少对数据库造成的影响。

  1. mysql> select * from t1;
  2. ERROR 2006 (HY000): MySQL server has gone away
  3. No connection. Trying to reconnect...
  4. Connection id:    3
  5. Current database: xucl
  7. +------+
  8. | id   |
  9. +------+
  10. |    1 |
  11. |    2 |
  12. |    3 |
  13. |    4 |
  14. |    5 |
  15. |    6 |
  16. |    7 |
  17. |    8 |
  18. +------+
  19. 8 rows in set (0.00 sec)

2、建立硬连接

  1. [root@localhost xucl]# ll
  2. 总用量 220
  3. -rw-r----- 1 mysql mysql    61 3月  23 08:21 db.opt
  4. -rw-r----- 1 mysql mysql  8556 3月  23 08:21 t1.frm
  5. -rw-r----- 1 mysql mysql 98304 3月  27 03:50 t1.ibd
  6. -rw-r----- 1 mysql mysql  8556 3月  23 08:22 t2.frm
  7. -rw-r----- 1 mysql mysql 98304 3月  23 08:22 t2.ibd
  8. [root@localhost xucl]# ln  t1.ibd t1.ibd.hdlk
  9. [root@localhost xucl]# ll
  10. 总用量 316
  11. -rw-r----- 1 mysql mysql    61 3月  23 08:21 db.opt
  12. -rw-r----- 1 mysql mysql  8556 3月  23 08:21 t1.frm
  13. -rw-r----- 2 mysql mysql 98304 3月  27 03:50 t1.ibd
  14. -rw-r----- 2 mysql mysql 98304 3月  27 03:50 t1.ibd.hdlk
  15. -rw-r----- 1 mysql mysql  8556 3月  23 08:22 t2.frm
  16. -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

  1. mysql> drop table t1;
  2. ERROR 2006 (HY000): MySQL server has gone away
  3. No connection. Trying to reconnect...
  4. Connection id:    4
  5. Current database: xucl
  7. Query OK, 0 rows affected (0.00 sec)
  1. [root@localhost xucl]# ll
  2. 总用量 208
  3. -rw-r----- 1 mysql mysql    61 3月  23 08:21 db.opt
  4. -rw-r----- 1 mysql mysql 98304 3月  27 03:50 t1.ibd.hdlk
  5. -rw-r----- 1 mysql mysql  8556 3月  23 08:22 t2.frm
  6. -rw-r----- 1 mysql mysql 98304 3月  23 08:22 t2.ibd

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

4、删除物理文件

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

至此，大表完成删除。