【MySQL】如何快速删除大表
2018-03-27 本文已影响0人
拾荒者charley
前言：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
至此，大表完成删除。
