MySQL DROP TABLE操作以及 DROP 大表時的注意事項

標籤：style   blog   io   ar   使用   for   sp   strong   檔案   

文法：

刪表

DROP TABLE SyntaxDROP [TEMPORARY] TABLE [IF EXISTS]    tbl_name [, tbl_name] ...    [RESTRICT | CASCADE]

可一次刪除一張或多張表。需具有所刪除表上的DROP許可權。表定義檔案和資料檔案均被移除。表被刪除後表上的使用者權限不會被自動刪除。參數裡表中指定的表名不存在則報錯，但對於存在的表仍會刪除。可通過指定IF EXISTS阻止表不存在時引發的錯誤（此時對於不存在的表僅產生一個NOTE）。對於分區表，除了移除表定義，分區、資料外還移除與之關聯的分區定義檔案(.par)。在MySQL5.6中參數[RESTRICT | CASCADE]不做任何事情。[TEMPORARY] 關鍵字表明只刪暫存資料表，語句不會結束進行中的事務（MySQL中DDL語句會隱式提交），不會進行許可權檢查。

刪庫

DROP DATABASE SyntaxDROP {DATABASE | SCHEMA} [IF EXISTS] db_name...

刪除指定庫中的表之後刪除庫。需具有庫上的DROP許可權。庫被刪除後庫上存在的使用者權限不會被自動刪除。IF EXISTS用於阻止庫名不存在時引起的錯誤。庫被刪除後預設庫會被重設。若在使用了符號連結的庫上執行DROP DATABASSE 連結和未經處理資料庫都會被刪除。命令返回被移除的表數量。
該命令會從指定的資料庫目錄中移除常規操作時MySQL自己產生的檔案和目錄，如：.BAK .DAT .HSH .MRG. MYD .MYI .TRG .TRN .db.frm.ibd .ndb .par，若存在db.opt也同樣會刪除。若資料庫目錄中存在其他非MySQL本身產生的檔案或目錄，則整個資料庫目錄不會被移除。此時，需手動清理剩餘的檔案並重新運行DROP DATABASE語句。
刪除資料庫並不會移除庫中建立的暫存資料表。暫存資料表在SESSION結束時自動被清理或者顯示的通過DROP TEMPORARY TABLE 刪除。

刪除大表的注意事項
對於表的刪除，因為InnoDB引擎會在table cache層面維護一個全域獨佔鎖一直到DROP TABLE完成為止，這樣，對於表的其他動作會被HANG住。對於較大的表來說，DROP TABLE操作可能需要很長的時間，因此需要一種有效辦法來提升大表的刪除速度，以儘可能降低HANG住的時間。可以通過設定永久連結來達到此目的。
比如有一個範例表：
example_table

使用InnoDB引擎且指定innodb_file_per_table=ON時在資料目錄中與該表對應的有如下兩個檔案，分別為表定義檔案和資料檔案。

sudo ls  -lh /data/mysql/testdb-rw-rw---- 1 mysql mysql 8.4K Oct 28 13:26 example_table.frm-rw-rw---- 1 mysql mysql 100G Oct 28 13:26 example_table.ibd

該表有100G這麼大，直接使用DROP TABLE來完成刪表動作，那麼這條語句要執行很長時間。此時便可以通過在該表對應的資料檔案上設定永久連結來進行刪除。

sudo ln /data/mysql/testdb/example_table.ibd /data/mysql/testdb/example_table.ibd.hdlksudo ls  -lh /data/mysql/testdb-rw-rw---- 1 mysql mysql 8.4K Oct 28 13:26 example_table.frm-rw-rw---- 2 mysql mysql 100G Oct 28 13:26 example_table.ibd-rw-rw---- 2 mysql mysql 100G Oct 28 13:26 example_table.ibd.hdlk

發現多了一個example_table.ibd.hdlk檔案，且example_table.ibd.hdlk和example_table.ibd的innode均為2。也即當有多個檔案名稱（如永久連結）指向同一innode時，這個innode的引用數大於1，此時，刪除其中任何一個檔案名稱都只會刪除指向innode的指標而並不會直接刪除物理檔案塊，因此會非常快，直至innode的引用計數等於1時才會真正刪除對應的物理檔案塊，真正刪除物理檔案塊時才會比較耗時。

在建立了永久連結後再執行DROP TABLE操作：

DROP TABLE example_table；

發現會很快的完成，查看對應的表定義和資料檔案：

sudo ls  -lh /data/mysql/testdb-rw-rw---- 1 mysql mysql 100G Oct 28 13:26 example_table.ibd.hdlk

只剩下example_table.ibd.hdlk，且innode的引用計數變為了1。也即剛才的DROP TABLE操作實施刪除了物理檔案的一個指標example_table.ibd ，因而非常快。

剩下的任務就是刪除真正的物理檔案了，因為此時innode的引用計數已經變為了1，直接刪除example_table.ibd.hdlk便會真正的刪除物理檔案。但因為物理檔案較大，刪除大檔案仍會引起較高的磁碟IO開銷。因此可以使用少量逐次刪除的方式來刪除大的資料檔案。truncate工具可以用於增加或縮減指定檔案的尺寸，可以用於此目的：

for i in `seq 100 -1 1 ` ;do sleep 2;sudo truncate -s ${i}G /data/mysql/testdb/example_table.ibd.hdlk;donesudo rm -rf /data/mysql/testdb/example_table.ibd.hdlk;

從100G開始，每次縮減1G，停2秒，繼續，直到檔案只剩1G，最後使用rm命令刪除剩餘的部分。

對於整個資料庫的刪除可以先刪除其中較大的表，最後在執行DROP DATABASE刪除整個庫，對大表的刪除可參見上面的方法。

MySQL DROP TABLE操作以及 DROP 大表時的注意事項