delete、truncate、drop的區(qū)別有哪些，該如何選擇
上周同事小姐姐問(wèn)我：“哈哥你看，我發(fā)現(xiàn)MySQL有bug，我下午為了清理磁盤(pán)，明明刪除了100萬(wàn)條MySQL數(shù)據(jù)，磁盤(pán)不僅沒(méi)有變小，反而更滿(mǎn)了呢？？”
那你是怎么刪除的？
“delete from table 呀”
“怪不得，其實(shí)要?jiǎng)h除MySQL數(shù)據(jù)是有好幾種方式的，有些場(chǎng)景下是不應(yīng)該用DELETE的，比如你這種情況。好了，讓我來(lái)給你講一下吧。”
MySQL刪除數(shù)據(jù)的方式都有哪些？
咱們常用的三種刪除方式：通過(guò) delete、truncate、drop 關(guān)鍵字進(jìn)行刪除；這三種都可以用來(lái)刪除數(shù)據(jù)，但場(chǎng)景不同。
一、從執(zhí)行速度上來(lái)說(shuō)
drop > truncate >> DELETE
二、從原理上講
1、DELETE
- DELETE from TABLE_NAME where xxx
1、DELETE屬于數(shù)據(jù)庫(kù)DML操作語(yǔ)言，只刪除數(shù)據(jù)不刪除表的結(jié)構(gòu)，會(huì)走事務(wù)，執(zhí)行時(shí)會(huì)觸發(fā)trigger；
2、在 InnoDB 中，DELETE其實(shí)并不會(huì)真的把數(shù)據(jù)刪除，mysql 實(shí)際上只是給刪除的數(shù)據(jù)打了個(gè)標(biāo)記為已刪除，因此 delete 刪除表中的數(shù)據(jù)時(shí)，表文件在磁盤(pán)上所占空間不會(huì)變小，存儲(chǔ)空間不會(huì)被釋放，只是把刪除的數(shù)據(jù)行設(shè)置為不可見(jiàn)。雖然未釋放磁盤(pán)空間，但是下次插入數(shù)據(jù)的時(shí)候，仍然可以重用這部分空間（重用 → 覆蓋）。
3、DELETE執(zhí)行時(shí)，會(huì)先將所刪除數(shù)據(jù)緩存到rollback segement中，事務(wù)commit之后生效;
4、delete from table_name刪除表的全部數(shù)據(jù),對(duì)于MyISAM 會(huì)立刻釋放磁盤(pán)空間，InnoDB 不會(huì)釋放磁盤(pán)空間;
5、對(duì)于delete from table_name where xxx 帶條件的刪除, 不管是InnoDB還是MyISAM都不會(huì)釋放磁盤(pán)空間;
6、delete操作以后使用 optimize table table_name 會(huì)立刻釋放磁盤(pán)空間。不管是InnoDB還是MyISAM 。所以要想達(dá)到釋放磁盤(pán)空間的目的，delete以后執(zhí)行optimize table 操作。往期面試題匯總：001期~150期匯總
示例：查看表占用硬盤(pán)空間大小的SQL語(yǔ)句如下：（用M做展示單位，數(shù)據(jù)庫(kù)名：csjdemo，表名：demo2）
- select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') as table_size
- from information_schema.tables
- where table_schema='csjdemo' AND table_name='demo2';
然后執(zhí)行空間優(yōu)化語(yǔ)句，以及執(zhí)行后的表Size變化：
- optimize table demo2
再看看這張表的大小，就只剩下表結(jié)構(gòu)size了。
7、delete 操作是一行一行執(zhí)行刪除的，并且同時(shí)將該行的的刪除操作日志記錄在redo和undo表空間中以便進(jìn)行回滾（rollback）和重做操作，生成的大量日志也會(huì)占用磁盤(pán)空間。往期面試題匯總：001期~150期匯總
2、truncate
- Truncate table TABLE_NAME
1、truncate：屬于數(shù)據(jù)庫(kù)DDL定義語(yǔ)言，不走事務(wù)，原數(shù)據(jù)不放到 rollback segment 中，操作不觸發(fā) trigger。
執(zhí)行后立即生效，無(wú)法找回
執(zhí)行后立即生效，無(wú)法找回
執(zhí)行后立即生效，無(wú)法找回
2、truncate table table_name 立刻釋放磁盤(pán)空間 ，不管是 InnoDB和MyISAM 。truncate table其實(shí)有點(diǎn)類(lèi)似于drop table 然后creat,只不過(guò)這個(gè)create table 的過(guò)程做了優(yōu)化，比如表結(jié)構(gòu)文件之前已經(jīng)有了等等。所以速度上應(yīng)該是接近drop table的速度;
3、truncate能夠快速清空一個(gè)表。并且重置auto_increment的值。
但對(duì)于不同的類(lèi)型存儲(chǔ)引擎需要注意的地方是：
- 對(duì)于MyISAM，truncate會(huì)重置auto_increment（自增序列）的值為1。而delete后表仍然保持auto_increment。
- 對(duì)于InnoDB，truncate會(huì)重置auto_increment的值為1。delete后表仍然保持auto_increment。但是在做delete整個(gè)表之后重啟MySQL的話(huà)，則重啟后的auto_increment會(huì)被置為1。
也就是說(shuō)，InnoDB的表本身是無(wú)法持久保存auto_increment。delete表之后auto_increment仍然保存在內(nèi)存，但是重啟后就丟失了，只能從1開(kāi)始。實(shí)質(zhì)上重啟后的auto_increment會(huì)從 SELECT 1+MAX(ai_col) FROM t 開(kāi)始。
4、小心使用 truncate，尤其沒(méi)有備份的時(shí)候，如果誤刪除線(xiàn)上的表，記得及時(shí)聯(lián)系中國(guó)民航，訂票電話(huà)：400-806-9553
3、drop
- Drop table Tablename
1、drop：屬于數(shù)據(jù)庫(kù)DDL定義語(yǔ)言，同Truncate；
執(zhí)行后立即生效，無(wú)法找回
執(zhí)行后立即生效，無(wú)法找回
執(zhí)行后立即生效，無(wú)法找回
2、drop table table_name 立刻釋放磁盤(pán)空間 ，不管是 InnoDB 和 MyISAM; drop 語(yǔ)句將刪除表的結(jié)構(gòu)被依賴(lài)的約束(constrain)、觸發(fā)器(trigger)、索引(index); 依賴(lài)于該表的存儲(chǔ)過(guò)程/函數(shù)將保留,但是變?yōu)?invalid 狀態(tài)。
3、小心使用 drop ，要?jiǎng)h表跑路的兄弟，請(qǐng)?jiān)谟喥背晒笤趫?zhí)行操作！訂票電話(huà)：400-806-9553
可以這么理解，一本書(shū)，delete是把目錄撕了，truncate是把書(shū)的內(nèi)容撕下來(lái)燒了，drop是把書(shū)燒了
