mysql快速删表_【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

至此，大表完成删除。