上周同事小姐姐問我：“哈哥你看，我發現MySQL有bug，我下午為了清理磁盤，明明洗掉了100萬條MySQL資料，磁盤不僅沒有變小，反而更滿了呢？？”
那你是怎么洗掉的？
“delete from table 呀”
“怪不得，其實要洗掉MySQL資料是有好幾種方式的，有些場景下是不應該用DELETE的，比如你這種情況，好了，讓我來給你講一下吧，”
MySQL洗掉資料的方式都有哪些？
咱們常用的三種洗掉方式：通過 delete、truncate、drop 關鍵字進行洗掉；這三種都可以用來洗掉資料，但場景不同，
一、從執行速度上來說
drop > truncate >> DELETE
二、從原理上講
1、DELETE
DELETE from TABLE_NAME where xxx
1、DELETE屬于資料庫DML操作語言，只洗掉資料不洗掉表的結構，會走事務，執行時會觸發trigger；
2、在 InnoDB 中，DELETE其實并不會真的把資料洗掉，mysql 實際上只是給洗掉的資料打了個標記為已洗掉，因此 delete 洗掉表中的資料時，表檔案在磁盤上所占空間不會變小，存盤空間不會被釋放，只是把洗掉的資料行設定為不可見，雖然未釋放磁盤空間，但是下次插入資料的時候，仍然可以重用這部分空間（重用 → 覆寫），
3、DELETE執行時，會先將所洗掉資料快取到rollback segement中，事務commit之后生效;
4、delete from table_name洗掉表的全部資料,對于MyISAM 會立刻釋放磁盤空間，InnoDB 不會釋放磁盤空間;
5、對于delete from table_name where xxx 帶條件的洗掉, 不管是InnoDB還是MyISAM都不會釋放磁盤空間;
6、delete操作以后使用 optimize table table_name 會立刻釋放磁盤空間，不管是InnoDB還是MyISAM ，所以要想達到釋放磁盤空間的目的，delete以后執行optimize table 操作，
示例：查看表占用硬碟空間大小的SQL陳述句如下：（用M做展示單位，資料庫名：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';
然后執行空間優化陳述句，以及執行后的表Size變化：
optimize table demo2
再看看這張表的大小，就只剩下表結構size了，
7、delete 操作是一行一行執行洗掉的，并且同時將該行的的洗掉操作日志記錄在redo和undo表空間中以便進行回滾（rollback）和重做操作，生成的大量日志也會占用磁盤空間，
2、truncate
Truncate table TABLE_NAME
1、truncate：屬于資料庫DDL定義語言，不走事務，原資料不放到 rollback segment 中，操作不觸發 trigger，
執行后立即生效，無法找回
執行后立即生效，無法找回
執行后立即生效，無法找回
2、truncate table table_name 立刻釋放磁盤空間 ，不管是 InnoDB和MyISAM ，truncate table其實有點類似于drop table 然后creat,只不過這個create table 的程序做了優化，比如表結構檔案之前已經有了等等，所以速度上應該是接近drop table的速度;
3、truncate能夠快速清空一個表，并且重置auto_increment的值，
但對于不同的型別存盤引擎需要注意的地方是：
-
對于MyISAM，truncate會重置auto_increment（自增序列）的值為1，而delete后表仍然保持auto_increment，
-
對于InnoDB，truncate會重置auto_increment的值為1，delete后表仍然保持auto_increment，但是在做delete整個表之后重啟MySQL的話，則重啟后的auto_increment會被置為1，
也就是說，InnoDB的表本身是無法持久保存auto_increment，delete表之后auto_increment仍然保存在記憶體，但是重啟后就丟失了，只能從1開始，實質上重啟后的auto_increment會從 SELECT 1+MAX(ai_col) FROM t 開始，
4、小心使用 truncate，尤其沒有備份的時候，如果誤洗掉線上的表，記得及時聯系中國民航，訂票電話：400-806-9553
3、drop
Drop table Tablename
1、drop：屬于資料庫DDL定義語言，同Truncate；
執行后立即生效，無法找回
執行后立即生效，無法找回
執行后立即生效，無法找回
2、drop table table_name 立刻釋放磁盤空間 ，不管是 InnoDB 和 MyISAM; drop 陳述句將洗掉表的結構被依賴的約束(constrain)、觸發器(trigger)、索引(index); 依賴于該表的存盤程序/函式將保留,但是變為 invalid 狀態，
3、小心使用 drop ，要刪表跑路的兄弟，請在訂票成功后在執行操作！訂票電話：400-806-9553
可以這么理解，一本書，delete是把目錄撕了，truncate是把書的內容撕下來燒了，drop是把書燒了，
著作權宣告：本文為博主原創文章，遵循 CC 4.0 BY-SA 著作權協議，轉載請附上原文出處鏈接和本宣告，
本文鏈接：https://blog.csdn.net/qq_39390545/article/details/107144859
近期熱文推薦：
1.Java 15 正式發布， 14 個新特性，重繪你的認知！！
2.終于靠開源專案弄到 IntelliJ IDEA 激活碼了，真香！
3.我用 Java 8 寫了一段邏輯，同事直呼看不懂，你試試看，，
4.吊打 Tomcat ，Undertow 性能很炸！！
5.《Java開發手冊（嵩山版）》最新發布，速速下載！
覺得不錯，別忘了隨手點贊+轉發哦！
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/242192.html
標籤：Java