快速安全删除MySQL大表

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文链接：https://blog.csdn.net/wzy0623/article/details/102318556

目录

一、表删除过程

1. buffer pool清除

2. 删除表相关的磁盘文件

二、创建硬链接

三、删除表

四、删除文件释放空间

参考：

在一个高负载的生产数据库上删除大表需要一些技巧，倘若直接drop table，将产生大量磁盘I/O，严重的会卡库，这是高可用服务所不能接受的。要优化删除表，需要了解其内部执行过程。

一、表删除过程

表删除原理上分为内存和磁盘两部分操作：

• 清除表相关的buffer pool页面。
• 删除表相关的磁盘文件。

1. buffer pool清除

删除表时，MySQL会将表在buffer pool中对应的页面清除，这个过程中会对buffer pool上一个全局排它锁。在MySQL 5.5.23以后的版本，实现了一个lazy drop table方式，以减轻清理内存结构的操作对系统吞吐量产生的波动，具体的过程：

1. 持有buffer pool mutex； 2. 持有buffer pool中的flush list mutex； 3. 开始扫描flush list； 1. 如果dirty page属于drop table，那么就直接从flush list中remove掉； 2. 如果删除的page个数超过了#define BUF_LRU_DROP_SEARCH_SIZE 1024 这个数目的话，释放buffer pool mutex，flush list mutex，释放cpu资源； . 释放flush list mutex； . 释放buffer pool mutex； . 强制通过pthread_yield进行一次OS context switch，释放剩余的cpu时间片； 3. 重新持有buffer pool mutex； 4. 重新持有flush list mutext； 4. 释放flush list mutex； 5. 释放buffer pool mutex；

上面的过程中持有了buffer pool mutex和flush list mutex，从锁的保护范围来看，buffer pool mutex直观上瓶颈会比较明显。如果buffer pool很大，或者表有很多脏页面需要被清除，则持有mutex的时间会相对较长，导致其它事务在用到相应buffer pool实例时被阻塞，从而影响整个数据库性能。

这部分的优化的难点在于涉及源码，作为应用能做是先要保证被删除表上没有活动事务，最好是没有任何访问，然后在业务低峰期执行表删除操作。

2. 删除表相关的磁盘文件

这里只讨论采用独立表空间（innodb_file_per_table=1 ）的innodb表删除。独立表空间在性能和运维上都大大强于共享表空间，也是当前绝大多数情况下的表存储方式。相对于内存扫描，删除磁盘文件对系统的影响要大得多。问题在于如果表文件过大，直接删除会瞬时占用大量I/O，造成IO阻塞。通常可以使用以下三个步骤删除大表：

• 创建表文件的硬链接。
• drop table删除表。
• 删除表文件释放磁盘空间。

二、创建硬链接

一个磁盘上的存储文件，可以由多个文件名引用。这多个文件是完全相同的，都指向同一个磁盘上的inode index。当我们删除任何一个文件的时候，都不会影响真实的存储文件，只是会将其引用数据减1，只有当被引用数目变为1时，再次删除文件，才会真正被删除。

例如，在没有硬链接时，t1表对应的磁盘文件如下：

-rw-r----- 1 mysql mysql        17973 Jul  3  2018 t1.frm
-rw-rw---- 1 mysql mysql 498115543040 Oct  7 17:14 t1.ibd

其中第二个字段的“1”表示文件只有一个inode指针，此时删除t1表会实际删除磁盘文件。创建硬链接后：

ln t1.frm t1.frm.h
ln t1.ibd t1.ibd.h

每个表文件有两个inode引用：

-rw-r----- 2 mysql mysql        17973 Jul  3  2018 t1.frm
-rw-r----- 2 mysql mysql        17973 Jul  3  2018 t1.frm.h
-rw-rw---- 2 mysql mysql 498115543040 Oct  7 17:14 t1.ibd
-rw-rw---- 2 mysql mysql 498115543040 Oct  7 17:14 t1.ibd.h

再删除表时，只是删除了inode引用，此操作非常快，不会影响正常服务。可以使用以下脚本对一个数据库内的所有表创建硬链接：

# MySQL数据目录
datadir=`mysql -uroot -p123456 -S /data/mysqldata/mysql.sock -e "show variables like 'datadir'" -N -B | awk '{print $2}'`
# 数据库名
dbname='dbname'

cd $datadir/$dbname
# 创建硬链接
ls *.{ibd,frm} | awk '{print "ln "$0" "$0".h"}' | bash

三、删除表

drop table t1;

MySQL的drop table操作会删除系统表中t1的相关记录，同时删除表的t1.frm与t1.ibd文件。如上所述，此操作可以瞬间完成，之后剩下两个.h文件：

-rw-r----- 1 mysql mysql        17973 Jul  3  2018 t1.frm.h
-rw-rw---- 1 mysql mysql 498115543040 Oct  7 17:14 t1.ibd.h

四、删除文件释放空间

t1表虽然秒删，但并没有释放磁盘空间。下面我们需要删除t1.frm.h与t1.ibd.h文件以真正释放空间。此时不能直接rm这个接近500G的文件，原因同样是会造成I/O冲击。相应的对策就是使用coreutils的truncate工具，逐次缩减文件大小，最终执行一个不会对系统产生影响的小文件删除操作。例如要实际删除t1的两个剩余.h文件：

rmtablefile.sh t1

rmtablefile.sh脚本文件内容如下：

#!/bin/bash
# 表定义文件很小，可直接删除
rm $1.frm.h

# 表数据文件大小，单位M
filesize=`ls -l $1.ibd.h | awk '{print int($5/1024/1024)}'`
if (( $filesize < 100 ))
then
    # 小于100直接删除
    rm $1.ibd.h
else
    # 大于等于100，每次截断100M
    for i in `seq $filesize -100 0`
    do
        sleep 2
        echo $i
        truncate -s ${i}M $1.ibd.h
    done

    # 删除小于100M的文件
    rm $1.ibd.h
fi

使用truncate每次将文件大小缩小100M，当文件小于100M时再将其删除。需要说明的一点是，这步实际删除磁盘文件的操作，目标是不影响线上服务，在此基础上只要能够安全删除文件即可。