MySQL大表清空和刪除正確方法

MySQL大表清空和刪除正確方法

1 清空大表

1.1 truncate

刪除表中的數據的方法有delete,truncate, 其中TRUNCATE TABLE用于刪除表中的所有行，而不記錄單個行刪除操作。TRUNCATE TABLE 與沒有 WHERE 子句的 DELETE 語句類似；但是，TRUNCATE TABLE 速度更快，使用的系統資源和事務日志資源更少。

truncate 清空表數據，50G的表大概需要20秒以上；

mysql> truncate table old_table;

1.2 rename

1.2.1 格式：

mysql> rename table 原表名 to 新表名;

當你執行 RENAME 時，你不能有任何鎖定的表或活動的事務。你同樣也必須有對原初表的 ALTER 和 DROP 權限，以及對新表的 CREATE 和 INSERT 權限。

1.2.2 實例

# 新建一個空表（測試）；
mysql> create table old_table(id int);

# new_table克隆old_table的表結構；
mysql> create table new_table like old_table;

# old_table改名為backup_table，new_table改名為old_table；
mysql> RENAME TABLE old_table TO backup_table, new_table TO old_table;

# 刪除舊表及數據；
mysql> DROP TABLE old_table;    

2 刪除

2.1 背景

在生產環境有可能有刪除某個不重要大表的需求，因為大表占用的大量磁盤空間，如果我們直接drop掉此表，通常需要20秒以上的時間，總會覺得會卡主MySQL，現在給大家一個正確的刪除方法。

2.2 首先我們查看此大表

# shell
[root@ras221 db]# du -shc old_table*
12K old_table.frm
49G old_table.ibd
[root@ras221 db]# ls -l old_table*
-rw-r----- 1 mysql mysql      9075 11月  8 11:39 old_table.frm
-rw-r----- 1 mysql mysql 52273610752 1月  10 14:12 old_table.ibd
[root@ras221 db]# ln old_table.ibd old_table.ibd.bak        # 硬鏈接；
[root@ras221 db]# ls -l old_table*
-rw-r----- 1 mysql mysql      9075 11月  8 11:39 old_table.frm
-rw-r----- 1 mysql mysql 52273610752 1月  10 14:12 old_table.ibd
-rw-r----- 1 mysql mysql 52273610752 1月  10 14:12 old_table.ibd.bak
[root@ras221 db]# 

# mysql 
mysql> drop table old_table;
Query Ok, 0 rows affacted(0.92 sec)

通過這樣的操作，可以減少mysql drop大表hang住的時間，然后在業務低峰期再去刪除真實的那個.b文.bak件。

至于原理:
就是利用OS HARD LINK的原理,
當多個文件名同時指向同一個INODE時,這個INODE的引用數N>1, 刪除其中任何一個文件名都會很快.
因為其直接的物理文件塊沒有被刪除.只是刪除了一個指針而已;
當INODE的引用數N=1時, 刪除文件需要去把這個文件相關的所有數據塊清除,所以會比較耗時;

對于大表操作操作的其實幾個Tips：
可以先用rename table替代drop table
mysql> rename table old_table to old_table_bak;
這個動作也很快只是相當于改一個文件名。
另外也可以考慮使用XFS文件系統，對于Drop表動作也比較快。