mysql drop 几十g的表_MySQL Drop 大表的解决方案

一 引子

在生产环境中，删除一个大文件，比如一个数十 G 或者上百 G 的文件是很耗时的。

本文介绍一个快速 DROP TABLE 的方法。使用本文提供的方法，不管该表数据量、占用空间有多大，都可以快速的删除。

下面做一个演示。

首先说明环境：

环境

由于我使用 mysql_multi 的形式启动 MySQL。所以我们需要在 MySQL 的配置文件 my.cnf 中加入 innodb_file_per_table 参数。

我的 my.cnf 配置如下：

[mysqld_multi]

mysqld = /usr/local/mysql/mysql-5.1.73-osx10.6-x86_64/bin/mysqld_safe

mysqladmin = /usr/local/mysql/mysql-5.1.73-osx10.6-x86_64/bin/mysqladmin

log = /var/log/mysqld_mutil.err

user = root

[mysqld5173]

port=5173

socket=/tmp/mysql_5173.sock

basedir=/usr/local/mysql/mysql-5.1.73-osx10.6-x86_64

datadir=/usr/local/mysql/data/5.1

user=_mysql

log-error=/var/log/mysqld_5173.log

pid-file=/tmp/mysqld_5173.pid

innodb_file_per_table

[mysqld5540]

port=5540

socket=/tmp/mysql_5540.sock

basedir=/usr/local/mysql/mysql-5.5.40-osx10.6-x86_64

datadir=/usr/local/mysql/data/5.5

user=_mysql

log-error=/var/log/mysqld_5540.log

pid-file=/tmp/mysqld_5540.pid

innodb_file_per_table

[mysqld5612]

port=5612

socket=/tmp/mysql_5612.sock

basedir=/usr/local/mysql/mysql-5.6.21-osx10.8-x86_64

datadir=/usr/local/mysql/data/5.6

user=_mysql

log-error=/var/log/mysqld_5612.log

pid-file=/tmp/mysqld_5612.pid

innodb_file_per_table

接着登录到 MySQL。

创建测试表。

说明：实验主要使用 city 表。user 表只是用于测试 LOAD DATA INFILE 的速度。

创建数据文本。

该文件包括 100W 行数据。内容如下：

1 “robin”,19,”M”,”GuangZhou”,”DBA”

……

1000000 “robin”,19,”M”,”GuangZhou”,”DBA”

该文件包括 1000W 行数据。内容如下：

1 “GuangZhou”,”GuangDong”,”GZ”,”Wechat”,”Netease”

……

10000000 “GuangZhou”,”GuangDong”,”GZ”,”Wechat”,”Netease”

编辑导入数据脚本。

该文件包括 10 行相同的导入数据命令。成功导入到 user 表后，会有 1000W 的数据。内容如下：

1 LOAD DATA INFILE ‘/tmp/user.txt’ \

INTO TABLE user \

FIELDS TERMINATED BY ‘,’ \

LINES TERMINATED BY ‘\n’;

……

10 LOAD DATA INFILE ‘/tmp/user.txt’ \

INTO TABLE user \

FIELDS TERMINATED BY ‘,’ \

LINES TERMINATED BY ‘\n’;

导入到 city 表的操作类似。

该文件包括 20 行相同的导入数据命令。成功导入到 city 表后，会有两亿条数据。内容如下：

1 LOAD DATA INFILE ‘/tmp/city.txt’ \

INTO TABLE city FIELDS \

TERMINATED BY ‘,’ \

LINES TERMINATED BY ‘\n’;

……

20 LOAD DATA INFILE ‘/tmp/city.txt’ \

INTO TABLE city FIELDS \

TERMINATED BY ‘,’ \

LINES TERMINATED BY ‘\n’;

导入数据到 MySQL。

其中导入到 user 表共耗时 84.63 秒。

接着导入数据到 city 表。

总共耗时：

共计 2394.30 秒，亦即 39.905 分钟。

我们查看数据目录，可以看到该表占用空间为 15G。

total 15699980

-rw-rw—- 1 _mysql _mysql 8.5K Jan 15 16:46 city.frm

-rw-rw—- 1 _mysql _mysql 15G Jan 15 17:33 city.ibd

删除表，耗时 1.08 秒。当然，这里数据量还不够大，所以速度还是挺快。

接下来，我们重新创建表，导入数据。

导入数据耗时跟之前相差不多，不做计算。

创建硬链接。

total 15699980

-rw-rw—- 1 _mysql _mysql 8.5K Jan 15 17:35 city.frm

-rw-rw—- 1 _mysql _mysql 15G Jan 15 18:13 city.ibd

total 31399948

-rw-rw—- 1 _mysql _mysql 8.5K Jan 15 17:35 city.frm

-rw-rw—- 2 _mysql _mysql 15G Jan 15 18:13 city.ibd

-rw-rw—- 2 _mysql _mysql 15G Jan 15 18:13 city.ibd.hl

可以看到，iNode 由 1 变为 2。

再次删除。

最后，把硬链接文件删除。

total 15699968

-rw-rw—- 1 _mysql _mysql 15G Jan 15 18:13 city.ibd.hl

第一次删除，耗时 1.08 秒。第二次，建立硬链接后，删除表耗时 0.90 秒。两次删除表耗时差异不是太明显，那是因为我的数据只有 15 G。如果在生产环境中，数据量达到数十 G、上百 G、甚至 T 级，就会显示这种方法的威力了。本来打算模拟出 100 G 的数据，但由于机器配置和时间关系，就没有做了。

PS：两次插入数据，每次 两亿，已经耗去我 1 个多小时的时间。时间宝贵啊，不在这里浪费了。

本文中快速 DROP TABLE 利用了操作系统的 Hard Link(硬链接) 的原理。当多个文件名同时指向同一个 iNode 时，这个 iNode 的引用数 N > 1，删除其中任何一个文件名都会很快。因为其直接的物理文件块没有被删除，只是删除了一个指针而已；当 iNode 的引用数 N = 1 时，删除文件需要去把这个文件相关的所有数据块清除，所以会比较耗时。

最后，吐槽下 Windows。这次测试环境为 Mac OS X 10.9.5，i5，8G 内存。vim 打开一个 458 M 的文本，只需要数秒(N

截个图给读者欣赏欣赏。

Enjoy!

–EOF–