mysql 安全删除_快速安全删除MySQL大表

目录

一、表删除过程

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实例时被阻塞mysql删除表，从而影响整个数据库性能。

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

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时再将其删除。需要说明的一点是，这步实际删除磁盘文件的操作，目标是不影响线上服务，在此基础上只要能够安全删除文件即可。

参考：

MySQL · 特性分析 · drop table的优化

Innodb中mysql如何快速删除2T的大表

（编辑：沈阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!