（点击上方公众号，可快速关注）
来源：温国兵
链接：dbarobin.com/2015/01/15/solution-of-droping-large-table-under-mysql/
一 引子
在生产环境中，删除一个大文件，比如一个数十 G 或者上百 G 的文件是很耗时的。
本文介绍一个快速 DROP TABLE 的方法。使用本文提供的方法，不管该表数据量、占用空间有多大，都可以快速的删除。
二 演示
下面做一个演示。
2.1 环境
首先说明环境：
环境
mysql> SHOW VARIABLES LIKE '%version%';+-------------------------+------------------------------+| Variable_name | Value |+-------------------------+------------------------------+| protocol_version | 10 || version | 5.1.73 || version_comment | MySQL Community Server (GPL) || version_compile_machine | i386 || version_compile_os | apple-darwin10.3.0 |+-------------------------+------------------------------+5 rows in set (0.00 sec)
2.2 添加 innodb_file_per_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
2.3 导入数据
接着登录到 MySQL。
mysql --socket=/tmp/mysql_5173.sock -uroot -proot
创建测试表。
mysql> SET storage_engine=INNODB;Query OK, 0 rows affected (0.00 sec)mysql> SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema || mysql || test |+--------------------+3 rows in set (0.01 sec)mysql> USE test;Database changedmysql> CREATE TABLE user
-> (name VARCHAR(20),
-> age int,
-> sex CHAR(2),
-> city VARCHAR(20),
-> work VARCHAR(10)
-> ) DEFAULT CHARSET utf8 ENGINE = INNODB;Query OK, 0 rows affected (0.17 sec)mysql> CREATE TABLE city
-> (name VARCHAR(20),
-> province VARCHAR(20),
-> shortname VARCHAR(4),
-> coma VARCHAR(10),
-> comb VARCHAR(10)
-> ) DEFAULT CHARSET utf8 ENGINE = INNODB;Query OK, 0 rows affected (0.08 sec)
说明：实验主要使用 city 表。user 表只是用于测试 LOAD DATA INFILE 的速度。
创建数据文本。
vim /tmp/user.txt
cat -n /tmp/user.txt
该文件包括 100W 行数据。内容如下：
1 “robin”,19,”M”,”GuangZhou”,”DBA”
……
1000000 “robin”,19,”M”,”GuangZhou”,”DBA”
vim /tmp/city.txt
cat -n /tmp/city.txt
该文件包括 1000W 行数据。内容如下：
1 “GuangZhou”,”GuangDong”,”GZ”,”Wechat”,”Netease”
……
10000000 “GuangZhou”,”GuangDong”,”GZ”,”Wechat”,”Netease”
编辑导入数据脚本。
vim /tmp/load_to_user.sql
cat -n /tmp/load_to_user.sql
该文件包括 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 表的操作类似。
vim /tmp/load_to_city.sql
cat -n /tmp/load_to_city.sql
该文件包括 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。
mysql> source /tmp/load_to_user.sql
其中导入到 user 表共耗时 84.63 秒。
mysql> SHOW TABLE STATUS LIKE 'user' \G;*************************** 1. row ***************************
Name: user
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 10000389
Avg_row_length: 63
Data_length: 632291328Max_data_length: 0
Index_length: 0
Data_free: 179306496
Auto_increment: NULL
Create_time: 2015-01-15 14:38:05
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: 1 row in set (0.22 sec)mysql> SELECT count(*) FROM user;+----------+| count(*) |+----------+| 10000000 |+----------+1 row in set (7.06 sec)
接着导入数据到 city 表。
mysql> source /tmp/load_to_city.sqlQuery OK, 10000000 rows affected (1 min 45.95 sec)Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0......
总共耗时：
bc
共计 2394.30 秒，亦即 39.905 分钟。
2.4 第一次删除表
我们查看数据目录，可以看到该表占用空间为 15G。
sudo ls -FGlAhp test
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 秒。当然，这里数据量还不够大，所以速度还是挺快。
mysql> DROP TABLE city;Query OK, 0 rows affected (1.08 sec)
接下来，我们重新创建表，导入数据。
mysql> CREATE TABLE city
-> (name VARCHAR(20),
-> province VARCHAR(20),
-> shortname VARCHAR(4),
-> coma VARCHAR(10),
-> comb VARCHAR(10)
-> ) DEFAULT CHARSET utf8 ENGINE = INNODB;Query OK, 0 rows affected (0.06 sec)mysql> source /tmp/load_to_city.sql
导入数据耗时跟之前相差不多，不做计算。
2.5 第二次删除表，使用硬链接
创建硬链接。
sudo ls -FGlAhp test
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
sudo ln test/city.ibd test/city.ibd.hl
sudo ls -FGlAhp test
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。
再次删除。
mysql> SHOW TABLE STATUS LIKE 'city' \G;*************************** 1. row ***************************
Name: city
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 200000304
Avg_row_length: 79
Data_length: 15847129088Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: NULL
Create_time: 2015-01-15 17:35:14
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: 1 row in set (0.38 sec)mysql> SELECT count(*) FROM city;+-----------+| count(*) |+-----------+| 200000000 |+-----------+1 row in set (3 min 11.39 sec)mysql> DROP TABLE city;Query OK, 0 rows affected (0.90 sec)
最后，把硬链接文件删除。
sudo ls -FGlAhp test
total 15699968
-rw-rw—- 1 _mysql _mysql 15G Jan 15 18:13 city.ibd.hl
sudo rm -rf test/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!
关注「数据库开发」
看更多精选数据库技术文章
↓↓↓
