drop mysql_【MySQL】drop大表

利用硬链接和truncate降低drop table对线上环境的影响 众所周知drop table会严重的消耗服务器IO性能，如果被drop的table容量较大，甚至会影响到线上的正常。

首先，我们看一下为什么drop容量大的table会影响线上服务

直接执行drop table，mysql会将表定义和表数据全都删除，包括磁盘上的物理文件，也包括buffer pool中的内存数据。 这就分两步，第一步从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，标记，释放锁，后台慢慢删除。

之后就是第二步，这步在大容量表的时候更为消耗时间，那就是在os上删除物理文件。大家都知道在ext3上rm一个200G的文件会非常耗时，这是由于ext3存储数据的结构导致，如果一个很大的文件，ext3的i_block无法直接存放，需要多层嵌套才能完全存储下，在这种情况下由于映射的层次多，并且由于多层映射也不会是顺序存储的，就导致了很大的随机IO，这就导致了删除物理文件非常慢的现象。在这种情况下，建议升级到ext4，这是由于ext4比ext3使用extent分配存储空间，其最大的优势就是顺序存储。

ext3:

ext4:

知道了原因，我们来说说如何解决。具体步骤如下：

1、建立硬链接。

ln table.ibd table.idb.hdlk

2、mysql执行drop table操作。

drop table if exists tablename；

3、使用truncate删除物理文件。

truncate -s xxxG filename

了解下truncate

此命令不仅能把文件的数据删成0字节，还可以缩减(甚至扩大)文件至指定的大小(通过 -s 选项指定文件大小值)

[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]

$echo -n 12345678 >test

[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]

$cat test

12345678

[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]

$truncate -s 4 test

[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]

$cat test

1234

[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]

$ls -l test

-rw-r--r-- 1 zhiqian.ly users 4 Mar 30 21:03 test

[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]

$truncate -s 1M test

[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]

$cat test

1234

[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]

$ls -l test

-rw-r--r-- 1 zhiqian.ly users 1048576 Mar 30 21:03 test

[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]

$wc -c test

1048576 test

[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]

$du test

4 test

这里还可以看到一个“奇怪”的现象，本来已经缩至4字节的文件，把它扩展成1M以后，ls 和 wc 的结果显示大小确实是1M，但是 du 的结果却发现大小还是4字节。这也是要注意的地方之一，这种文件称为“空洞文件”，也就是说，文件的部分内容并没有实际存在于硬盘上(即没有分配对应的inode)，只是“声称”有1M的大小而已。对于不存在于硬盘上的那部分字节，如果去读的话，也是不会报错的，会读到全0的数据。

删除代码

#!/bin/bash

TRUNCATE=/usr/local/bin/truncate

for i in `seq 2194 -10 10 `; #从2194 开始每次递减10 ，输出结果见下面

do

sleep 2

echo "$TRUNCATE -s ${i}G /tmp/ep_bak.ibd.hdlk "

$TRUNCATE -s ${i}G /tmp/ep_bak.ibd.hdlk

done

其实硬链接和drop table就不用多说了，当多个文件共同指向同一inode，inode链接数N>1，删除任何一个文件都是巨快因为，此时删除的仅仅是指向inode的指针而当N=1时，则不一样了，此时删除的文件相关的所有数据块，所以慢在建立硬链接之后，mysql会认为rm了硬链接文件之后就算操作完毕，不会真正去删除物理文件从而提高了速度。但是对于服务器来说，实际的物理文件还在，如果手动rm，还是会产生很多的io影响，这时候就用到了truncate这个工具。这个工具会根据指定的size大小进行逐步删除，会将对IO造成的影响降到最低。

硬链接与软链接的联系与区别

我们知道文件都有文件名与数据，这在 Linux 上被分成两个部分：用户数据 (user data) 与元数据 (metadata)。用户数据，即文件数据块 (data block)，数据块是记录文件真实内容的地方；而元数据则是文件的附加属性，如文件大小、创建时间、所有者等信息。在 Linux 中，元数据中的 inode 号(inode 是文件元数据的一部分但其并不包含文件名，inode 号即索引节点号)才是文件的唯一标识而非文件名。文件名仅是为了方便人们的记忆和使用，系统或程序通过 inode 号寻找正确的文件数据块。下图展示了程序通过文件名获取文件内容的过程。

通过文件名打开文件

为解决文件的共享使用，Linux 系统引入了两种链接：硬链接 (hard link) 与软链接(又称符号链接，即 soft link 或 symbolic link)。链接为 Linux 系统解决了文件的共享使用，还带来了隐藏文件路径、增加权限安全及节省存储等好处。若一个 inode 号对应多个文件名，则称这些文件为硬链接。换言之，硬链接就是同一个文件使用了多个别名(见 图 2.hard link 就是 file 的一个别名，他们有共同的 inode)。硬链接可由命令 link 或 ln 创建。如下是对文件 oldfile 创建硬链接。

ln oldfile newfile

由于硬链接是有着相同 inode 号仅文件名不同的文件，因此硬链接存在以下几点特性： 文件有相同的 inode 及 data block； 只能对已存在的文件进行创建； 不能交叉文件系统进行硬链接的创建； 不能对目录进行创建，只可对文件创建； 删除一个硬链接文件并不影响其他有相同 inode 号的文件。

软链接与硬链接不同，若文件用户数据块中存放的内容是另一文件的路径名的指向，则该文件就是软连接。软链接就是一个普通文件，只是数据块内容有点特殊。软链接有着自己的 inode 号以及用户数据块(见 图 5 )。因此软链接的创建与使用没有类似硬链接的诸多限制： 软链接有自己的文件属性及权限等； 可对不存在的文件或目录创建软链接； 软链接可交叉文件系统； 软链接可对文件或目录创建； 创建软链接时，链接计数 i_nlink 不会增加； 删除软链接并不影响被指向的文件，但若被指向的原文件被删除，则相关软连接被称为死链接(即 dangling link，若被指向路径文件被重新创建，死链接可恢复为正常的软链接)。

软链接的访问

参考：