新來個(gè)技術(shù)總監(jiān)：發(fā)現(xiàn)誰(shuí)再用 delete 刪數(shù)據(jù)直接開除！

Java精選面試題（微信小程序）：5000+道面試題和選擇題，真實(shí)面經(jīng)，簡(jiǎn)歷模版，包含Java基礎(chǔ)、并發(fā)、JVM、線程、MQ系列、Redis、Spring系列、Elasticsearch、Docker、K8s、Flink、Spark、架構(gòu)設(shè)計(jì)、大廠真題等，在線隨時(shí)刷題！

源于一個(gè)讀者實(shí)際工作中遇到的問題，老大一直強(qiáng)調(diào)線上不允許用 delete 來刪除 Mysql 數(shù)據(jù)庫(kù)的數(shù)據(jù)（有一次發(fā)飚了，說如果再發(fā)現(xiàn)誰(shuí)這樣操作直接開除）。為什么 MySQL 不建議使用 delete 刪除數(shù)據(jù)呢？

這就得從以下幾方面說起！

MySQL 是一種關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，它的數(shù)據(jù)存儲(chǔ)是基于磁盤上的文件系統(tǒng)實(shí)現(xiàn)的。MySQL 將數(shù)據(jù)存儲(chǔ)在表中，每個(gè)表由一系列的行和列組成。每一行表示一個(gè)記錄，每一列表示一個(gè)字段。表的結(jié)構(gòu)由其列名、數(shù)據(jù)類型、索引等信息組成。

MySQL 的數(shù)據(jù)存儲(chǔ)采用了多種技術(shù)來優(yōu)化性能和存儲(chǔ)效率。以下是 MySQL 數(shù)據(jù)存儲(chǔ)的一些關(guān)鍵特性。

存儲(chǔ)引擎

MySQL 支持多種不同的存儲(chǔ)引擎，每種引擎都有不同的性能和存儲(chǔ)特性。常見的存儲(chǔ)引擎有 InnoDB、MyISAM、Memory 等。不同的存儲(chǔ)引擎支持不同的數(shù)據(jù)存儲(chǔ)方式，如 B樹索引、哈希索引、全文索引等。、

數(shù)據(jù)頁(yè)

MySQL 使用數(shù)據(jù)頁(yè)來管理存儲(chǔ)在磁盤上的數(shù)據(jù)。數(shù)據(jù)頁(yè)是 MySQL 存儲(chǔ)引擎中最基本的存儲(chǔ)單元，通常情況下每個(gè)數(shù)據(jù)頁(yè)的大小為 16KB。數(shù)據(jù)頁(yè)包含多個(gè)記錄，每個(gè)記錄對(duì)應(yīng)一行數(shù)據(jù)。

索引

MySQL 使用索引來優(yōu)化數(shù)據(jù)的檢索效率。索引是一種特殊的數(shù)據(jù)結(jié)構(gòu)，它能夠快速地查找表中的數(shù)據(jù)。MySQL 支持多種類型的索引，如 B 樹索引、哈希索引、全文索引等。B 樹索引是 MySQL 中最常用的索引類型，它能夠快速地查找表中的數(shù)據(jù)。

事務(wù)

MySQL 支持事務(wù)，事務(wù)可以保證數(shù)據(jù)的一致性、可靠性和安全性。MySQL 的事務(wù)是基于 ACID 模型實(shí)現(xiàn)的，它能夠確保數(shù)據(jù)在事務(wù)中的操作要么全部成功，要么全部回滾。事務(wù)的支持使得 MySQL 在多用戶并發(fā)訪問時(shí)能夠保證數(shù)據(jù)的完整性和一致性。

總之，MySQL 的數(shù)據(jù)存儲(chǔ)基于磁盤上的文件系統(tǒng)實(shí)現(xiàn)，采用多種技術(shù)來優(yōu)化性能和存儲(chǔ)效率，如存儲(chǔ)引擎、數(shù)據(jù)頁(yè)、索引、事務(wù)等。這些特性使得 MySQL 成為一種高性能、可靠和安全的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)。

MySQL刪除數(shù)據(jù)的方式都有哪些？

咱們常用的三種刪除方式：通過 delete、truncate、drop 關(guān)鍵字進(jìn)行刪除；這三種都可以用來刪除數(shù)據(jù)，但場(chǎng)景不同。

執(zhí)行速度：drop > truncate >> DELETE

從原理上理解

DELETE

DELETE from TABLE_NAME where xxx

DELETE 屬于數(shù)據(jù)庫(kù)DML操作語(yǔ)言，只刪除數(shù)據(jù)不刪除表的結(jié)構(gòu)，會(huì)走事務(wù)，執(zhí)行時(shí)會(huì)觸發(fā)trigger；

在 InnoDB 中，DELETE其實(shí)并不會(huì)真的把數(shù)據(jù)刪除，mysql 實(shí)際上只是給刪除的數(shù)據(jù)打了個(gè)標(biāo)記為已刪除，因此 delete 刪除表中的數(shù)據(jù)時(shí)，表文件在磁盤上所占空間不會(huì)變小，存儲(chǔ)空間不會(huì)被釋放，只是把刪除的數(shù)據(jù)行設(shè)置為不可見。**雖然未釋放磁盤空間，但是下次插入數(shù)據(jù)的時(shí)候，仍然可以重用這部分空間（重用 → 覆蓋）。

DELETE執(zhí)行時(shí)，會(huì)先將所刪除數(shù)據(jù)緩存到rollback segement中，事務(wù)commit之后生效;

delete from table_name刪除表的全部數(shù)據(jù),對(duì)于MyISAM 會(huì)立刻釋放磁盤空間，InnoDB 不會(huì)釋放磁盤空間;

對(duì)于delete from table_name where xxx帶條件的刪除, 不管是InnoDB還是MyISAM都不會(huì)釋放磁盤空間;

delete操作以后使用optimize table table_name會(huì)立刻釋放磁盤空間。不管是InnoDB還是MyISAM 。所以要想達(dá)到釋放磁盤空間的目的，delete以后執(zhí)行optimize table 操作。

示例：查看表占用硬盤空間大小的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了。

圖片

delete 操作是一行一行執(zhí)行刪除的，并且同時(shí)將該行的的刪除操作日志記錄在redo和undo表空間中以便進(jìn)行回滾（rollback)和重做操作，生成的大量日志也會(huì)占用磁盤空間。

drop

Drop table Tablename

屬于數(shù)據(jù)庫(kù)DDL定義語(yǔ)言，同Truncate，執(zhí)行后立即生效，無法找回。

drop table table_name 立刻釋放磁盤空間 ，不管是 InnoDB 和 MyISAM; drop 語(yǔ)句將刪除表的結(jié)構(gòu)被依賴的約束(constrain)、觸發(fā)器(trigger)、索引(index); 依賴于該表的存儲(chǔ)過程/函數(shù)將保留,但是變?yōu)?invalid 狀態(tài)。

小心使用 drop ，要?jiǎng)h表跑路的兄弟，請(qǐng)?jiān)谟喥背晒笤趫?zhí)行操作！

可以這么理解，一本書，delete是把目錄撕了，truncate是把書的內(nèi)容撕下來燒了，drop是把書燒了

truncate

屬于數(shù)據(jù)庫(kù)DDL定義語(yǔ)言，不走事務(wù)，原數(shù)據(jù)不放到 rollback segment 中，操作不觸發(fā) trigger。執(zhí)行后立即生效，無法找回！

truncate table table_name 立刻釋放磁盤空間 ，不管是 InnoDB和MyISAM 。truncate table其實(shí)有點(diǎn)類似于drop table 然后creat,只不過這個(gè)create table 的過程做了優(yōu)化，比如表結(jié)構(gòu)文件之前已經(jīng)有了等等。所以速度上應(yīng)該是接近drop table的速度;

truncate 能夠快速清空一個(gè)表。并且重置auto_increment的值。

但對(duì)于不同的類型存儲(chǔ)引擎需要注意的地方是：

• 對(duì)于MyISAM，truncate會(huì)重置auto_increment（自增序列）的值為1。而delete后表仍然保持auto_increment。

• 對(duì)于InnoDB，truncate會(huì)重置auto_increment的值為1。delete后表仍然保持auto_increment。但是在做delete整個(gè)表之后重啟MySQL的話，則重啟后的auto_increment會(huì)被置為1。

• 也就是說，InnoDB 的表本身是無法持久保存auto_increment。delete表之后 auto_increment仍然保存在內(nèi)存，但是重啟后就丟失了，只能從1開始。實(shí)質(zhì)上重啟后的auto_increment會(huì)從 SELECT 1+MAX(ai_col) FROM t 開始。

小心使用 truncate，尤其沒有備份的時(shí)候，如果誤刪除線上的表，記得及時(shí)跑路。

為什么不建議使用DELETE 刪除數(shù)據(jù)

了解上面的一些原理之后，可以知道！

• 無法恢復(fù)數(shù)據(jù)

在使用DELETE操作刪除數(shù)據(jù)時(shí)，如果沒有事先備份數(shù)據(jù)，一旦誤操作就會(huì)導(dǎo)致數(shù)據(jù)無法恢復(fù)。因此，在進(jìn)行任何數(shù)據(jù)操作之前，最好先備份數(shù)據(jù)，以便在出現(xiàn)錯(cuò)誤時(shí)可以輕松地恢復(fù)數(shù)據(jù)。

• 刪除數(shù)據(jù)會(huì)導(dǎo)致索引失效，影響查詢性能。

• 刪除數(shù)據(jù)會(huì)導(dǎo)致表空間的浪費(fèi)，因?yàn)閯h除的數(shù)據(jù)并不會(huì)立即釋放，而是留在表中，只是被標(biāo)記為“已刪除”。

• 數(shù)據(jù)不一致。

如果在刪除數(shù)據(jù)時(shí)表中存在外鍵關(guān)聯(lián)，使用DELETE操作可能會(huì)導(dǎo)致其他表中的數(shù)據(jù)不一致。例如，如果一個(gè)表有一個(gè)外鍵，指向另一個(gè)表中的一行，如果從主表中刪除行而不更新外鍵引用，則外鍵引用將成為無效引用。這可能會(huì)導(dǎo)致查詢時(shí)出現(xiàn)錯(cuò)誤，或者在更新時(shí)導(dǎo)致數(shù)據(jù)不一致。

總結(jié)

在工作當(dāng)中執(zhí)行數(shù)據(jù)庫(kù)刪除的時(shí)候一定要慎重再慎重，建議每次進(jìn)行數(shù)據(jù)刪除的使用最好數(shù)據(jù)表的備份工作，這樣就會(huì)大大減少你刪除跑路的幾率。很多時(shí)候不要過于相信自己的動(dòng)手能力，老虎還有打盹的時(shí)候，萬(wàn)一手滑了呢。盡可能養(yǎng)成好的數(shù)據(jù)庫(kù)運(yùn)維習(xí)慣，這樣會(huì)讓自己少跌跟頭，你的事業(yè)才會(huì)更加順利。

來源：www.rstk.cn/news/98507.htmlblog.csdn.net/qq_39390545/article/details/107144859

公眾號(hào)“Java精選”所發(fā)表內(nèi)容注明來源的，版權(quán)歸原出處所有（無法查證版權(quán)的或者未注明出處的均來自網(wǎng)絡(luò)，系轉(zhuǎn)載，轉(zhuǎn)載的目的在于傳遞更多信息，版權(quán)屬于原作者。如有侵權(quán)，請(qǐng)聯(lián)系，筆者會(huì)第一時(shí)間刪除處理！

最近有很多人問，有沒有讀者交流群！加入方式很簡(jiǎn)單，公眾號(hào)Java精選，回復(fù)“加群”，即可入群！

文章有幫助的話，點(diǎn)在看，轉(zhuǎn)發(fā)吧！