mysql 快速删除_Mysql快速删除大表

Mysql做了什么

如果在我们的生产环境中遇到业务下线或者其它需求，需要对一个大表做删除操作的时候，DBA就需要考虑删除表操作对生产环境的影响。对于数据量较小的表可以直接对表做drop table的操作，但是对于数据量较大的表来说，直接使用drop table可能会导致数据库QPS的下降、主机IO量的增加，进而对生产系统产生一些负面的影响。

要明白怎么去优化，首先需要知道在drop table的过程中Mysql做了什么。在整个过程中Mysql主要做了两个步骤，一个是对buffer pool的清理，一个就是对ibd文件的删除。

清理buffer pool

获取所有buffer pool instance的buffer pool mutex

获取 buffer pool中的flush list mutex

扫描flush list上面所有的数据，如果某个页面属于将要被删除的ibd文件(也就是需要被drop的那个表)就直接将页面从flush list中移除，不需要做flush的操作。由于在这个过程中会长时间的持有flush list mutex，会影响到业务对数据库的修改操作，mysql在后台定义了一个机制，如果占用CPU或者mutex的时间过长，会自动做释放操作，然后后在重新获取。

flush list结束之后释放所有的mutex资源

由于会遍历所有的buffer pool instance，在buffer pool较大或者buffer pool缓存了较多目标表数据的时候这个过程将会是一个比较耗时的操作。

删除IBD文件

这个很好理解，在清理完成buffer pool数据之后就需要对文件做清理了，在这个过程中会产生大量的IO操作

怎么优化

了解了在drop table的时候Mysql做了什么就可以做对应的优化，对于drop table的第一阶段来说，涉及到源码的操作优化的空间相对不大，主要是对第二阶段做优化，优化的方式就是通过硬连接去完成。

在linux系统中，存储方式都是通过filename-->inode-->data blocks方式进行的。对于软链接来说就是通过soft link-->soft link的inode-->找到数据块，数据块中存储的是真实文件的filename，然后在通过iNode获取真实的数据。

对于硬链接来说，file和hard link都是指向同一个inode。

为什么需要使用硬链接？因为在linux系统中，如果一个inode同时被一个以上的filename引用，那么在对期中的一个做删除的时候是不需要对data block做处理的，也就意味着删除会非常快的完成。

所以说，想要优化drop table，可以对ibd文件创建硬连接，然后在做drop table操作。最后通过truncate的方式一点点的在os层面删除大文件。

##示例

注意，如果要使用这种方式，必须要开启独立表空间模式。

#ln t.ibd t.ibd.hl

--检查发现两个表都具有相同的inode

[root@node1 tpcc]# ls -i t.ibd*

262166 t.ibd 262166 t.ibd.hl

--数据库中删除表

drop table t;

--在合适的时间使用truncate命令删除数据

vi /home/oracle/truncate_t.sh

#!/bin/bash

TRUNCATE=/usr/local/bin/truncate

for i in `seq 165 -10 10 `;

do

sleep 2

echo "$TRUNCATE -s ${i}M /data/my3306/data/tpcc/t.ibd.hl "

$TRUNCATE -s ${i}M /data/my3306/data/tpcc/t.ibd.hl

done

注意：其中的165为文件的大小。每次shink 10M的大小。

nohup sh /home/oracle/truncate_t.sh>/home/oracle/truncate_t.sh.out &

--删除文件

rm /data/my3306/data/tpcc/t.ibd.hl

当文件足够小的时候就可以直接使用rm命令删除了。

复制代码

参考

《Mysql运维内参》第17章