一 引子
在生產環境中，刪除一個大檔案，比如一個數十 G 或者上百 G 的檔案是很耗時的。
本文介紹一個快速 DROP TABLE 的方法。使用本文提供的方法，不管該表資料量、佔用空間有多大，都可以快速的刪除。
二 演示
下面做一個演示。
2.1 環境
首先說明環境：
環境
|
1 2 3 4 5 6 7 8 9 10 11 |
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。
|
1 |
mysql --socket=/tmp/mysql_5173.sock -uroot -proot |
建立測試表。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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 changed mysql> 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 的速度。
建立資料文字。
|
1 2 |
vim /tmp/user.txt cat -n /tmp/user.txt |
該檔案包括 100W 行資料。內容如下：
1 “robin”,19,”M”,”GuangZhou”,”DBA”
……
1000000 “robin”,19,”M”,”GuangZhou”,”DBA”
|
1 2 |
vim /tmp/city.txt cat -n /tmp/city.txt |
該檔案包括 1000W 行資料。內容如下：
1 “GuangZhou”,”GuangDong”,”GZ”,”Wechat”,”Netease”
……
10000000 “GuangZhou”,”GuangDong”,”GZ”,”Wechat”,”Netease”
編輯匯入資料指令碼。
|
1 2 |
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 表的操作類似。
|
1 2 |
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。
|
1 |
mysql> source /tmp/load_to_user.sql |
其中匯入到 user 表共耗時 84.63 秒。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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: 632291328 Max_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 表。
|
1 2 3 4 |
mysql> source /tmp/load_to_city.sql Query OK, 10000000 rows affected (1 min 45.95 sec) Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0 ...... |
總共耗時：
|
1 2 3 4 |
bc 105.95+113.84+114.89+111.83+\ 116.20+128.12+131.41+118.94+115.5+\ 122.63+116.12+119.87+140.83+148.78+\ 126.61+129.62+116.2+103.37+108.52+105.07 |
共計 2394.30 秒，亦即 39.905 分鐘。
2.4 第一次刪除表
我們檢視資料目錄，可以看到該表佔用空間為 15G。
|
1 |
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 秒。當然，這裡資料量還不夠大，所以速度還是挺快。
|
1 2 |
mysql> DROP TABLE city; Query OK, 0 rows affected (1.08 sec) |
接下來，我們重新建立表，匯入資料。
|
1 2 3 4 5 6 7 8 9 10 |
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 第二次刪除表，使用硬連結
建立硬連結。
|
1 |
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
|
1 2 |
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。
再次刪除。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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: 15847129088 Max_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) |
最後，把硬連結檔案刪除。
|
1 |
sudo ls -FGlAhp test |
total 15699968
-rw-rw—- 1 _mysql _mysql 15G Jan 15 18:13 city.ibd.hl
|
1 |
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!
五 參考
–EOF–