mysql大表删除数据库_分享一份mysql数据库大表的标准删除方案：drop + 硬链接

概述

生产环境一张日志表占用达到166G，该表一直有数据写入，如果是直接delete/truncate时间过久，故下面采取drop+硬链方式进行删除。

一、删除表原理与优化

drop表时所有的mysql的相关进程都会停止，直到drop结束，mysql才会恢复执行。出现这个情况的原因就是因为，在drop table的时候，innodb维护了一个全局锁，drop完毕锁就释放了。这意味着，如果在白天，访问量非常大的时候，如果你在不做任何处理措施的情况下，执行了删大表的命令，整个mysql就挂在那了，在删表期间，QPS会严重下滑，然后产品经理就来找你喝茶了。

删除表原理上分为2部分：

1、buffer pool页面清除过程

在删除表的时候，Innodb 会将文件在buffer pool中对应的页面清除。

这会涉及到table_cache的lock，如果持有table_cache的lock，这将导致其他查询都无法执行。这种情况在没有innodb_per_table之前尤为严重。

另外，mysql5.5.23之后添加lazy drop table功能，这个功能用来解决mutex on the LRU list。

其中心思想就是加锁，找到需要被删除的page，删除1024个page之后释放锁让其他thread工作，之后loop。而percona的lazy drop处理起来更优雅一些，其会先加锁，然后找到需要被删除的page，标记，释放锁，后台慢慢删除。

对于删除表的页面清除，只需要将页面从flash队列中删除即可，而不需要去做flush操作，减小对系统的冲击。

1)问题1：如果buffer pool很大，或者是在buffer pool中有很多需要被flush的页面，那么此时遍历扫描页面时就会占用比较长的时间，导致其他事务在用到相应buffer pool实例时被阻塞，从而影响整个数据库性能。

优化：涉及源码，优化困难

2、删除ibd磁盘文件的过程

这步在大容量表的时候更为消耗时间，那就是在os上删除物理文件。

1)问题1：表文件过大，直接删除会瞬时占用大量IO，造成IO阻塞

优化：使用硬链接删除

原理：一个磁盘上的文件，可以由多个文件系统的文件引用，这多个文件的完全相同的，都指向同一个磁盘上的文件，当我们删除任何一个文件的时候，都不会影响真实的文件，只是会将其引用数据减1，只有当被引用数目变为1的时候，再次删除文件，才会真正被删除。删除时，这两种情况的区别很明显，一个是在减少被引用数目，一个是真正做IO来删除它

2)问题2：做完硬链，真正的大文件删除问题，直接rm 删除，会造成IO瞬时高峰

优化：使用工具，多次少量的删除

原理：利用系统文件的truncate，脚本工具为slowrm

二、drop大表实例(以下操作同时执行以下命令观察IO：iostat -d -x -k 5 > /tmp/iostat.txt)

基于以上，我们对于大表的删除，应当先建立硬链，drop table后，再删除表数据文件。

对于大表的数据文件，可能会达到10G，也可以是100G级别，甚至更大。在linux下，这样的大文件在使用rm时，无疑会导致IO资源被强行占用，表现为硬盘的io_util基本上是100%左右，会对其它IO操作造成阻塞。更可怕的是，rm单个文件的过程是个原子过程，无法使用kill或kill -9来杀死rm进程，只能乖乖的等待它结束。如果是在繁忙的线上服务所在的机器上做这样的删除操作，很可能会对线上服务产生影响。

1、查看表大小

可以看到需删除的表已经达到166G。

2、备份表结构

3、建硬链接(如果是主从需在从库也设置硬链接)

在DB server上，找到fsl_rate_log表对应的文件，建立硬链接(一个 inode 号对应多个文件名)

4、删除大表(这里不做rename切换)

1)删除大表时需在业务低峰时间再做删除

2)如果是主从实时同步，删完后需检查从库是否也删除了

5、删除真正的数据文件

如果是生产环境在需要低IO负载删除大文件时，可以使用slowrm。这个后面再介绍吧~

其实想做到对生产环境基本没影响的话可以先对表做rename切换，把要删的表换个名字，然后在用drop+硬链方式删除即可。我这里是因为生产可以停机，所以就直接这样操作了。

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~