語法到生產(chǎn)環(huán)境避坑實(shí)踐)
1. 先搞清楚DELETE到底解決什么問題很多人第一次寫SQL學(xué)的第一條語句就是SELECT第二條大概率就是DELETE。SQL里最容易被輕視、也最容易出事的恰恰就是這個(gè)DELETE。先給結(jié)論DELETE是SQL里用來刪除表中數(shù)據(jù)的語句它刪的是“行”不是“表”。你可以把它理解成整理房間時(shí)扔掉不要的東西而不是把整個(gè)房間拆掉。DELETE只處理數(shù)據(jù)本身表的結(jié)構(gòu)、索引、約束這些全都保留著這一點(diǎn)必須先刻在腦子里。DELETE解決的核心場景很明確業(yè)務(wù)系統(tǒng)里產(chǎn)生了臟數(shù)據(jù)、測試數(shù)據(jù)、過期數(shù)據(jù)或者用戶主動(dòng)注銷、退訂、清空記錄都需要精準(zhǔn)地把某些行刪掉。比如電商訂單里有大量“已取消”狀態(tài)的廢棄記錄比如用戶表里有幾百個(gè)測試賬號(hào)比如日志表里三個(gè)月前的數(shù)據(jù)占了好幾個(gè)G——這些場景統(tǒng)統(tǒng)靠DELETE來解決。我見過太多新手在寫DELETE時(shí)的第一個(gè)反應(yīng)是“它跟DROP有什么區(qū)別”答案很簡單DROP直接連表帶數(shù)據(jù)一起銷毀TRUNCATE把表里的數(shù)據(jù)全清空但保留表結(jié)構(gòu)而DELETE是可以加WHERE條件、只刪除指定行的。DELETE最大的價(jià)值就是“精準(zhǔn)”二字WHERE條件寫得多好刪除就有多準(zhǔn)。這篇文章適合誰看如果你剛學(xué)SQL不久或者寫了幾年代碼但DELETE總是靠“備份膽量”在撐那你來對地方了。我會(huì)把DELETE的語法、原理、實(shí)操步驟、避坑經(jīng)驗(yàn)都過一遍尤其是那些在正式文檔里不太會(huì)寫、但生產(chǎn)環(huán)境里一定會(huì)踩的坑。2. DELETE的整體設(shè)計(jì)與選型思路2.1 DELETE、TRUNCATE、DROP怎么選刪除數(shù)據(jù)這件事SQL里不止DELETE一條路。很多人上來就刪刪完才發(fā)現(xiàn)選錯(cuò)了工具。先把三者的關(guān)系理清楚才能知道什么場景該用哪個(gè)。操作刪除范圍保留表結(jié)構(gòu)可加WHERE事務(wù)支持速度DELETE指定行或全部行保留支持支持慢TRUNCATE全部行保留不支持部分?jǐn)?shù)據(jù)庫支持快DROP表本身數(shù)據(jù)不保留不支持部分?jǐn)?shù)據(jù)庫支持最快從這個(gè)表能看出一個(gè)關(guān)鍵差異只有DELETE是“可反悔”的刪除。在MySQL、PostgreSQL、SQL Server這些主流數(shù)據(jù)庫里DELETE語句如果包在事務(wù)里執(zhí)行出錯(cuò)或發(fā)現(xiàn)刪錯(cuò)時(shí)可以ROLLBACK回滾TRUNCATE在多數(shù)數(shù)據(jù)庫里不支持事務(wù)回滾PostgreSQL支持但MySQL不支持DROP更狠表都沒了想回滾基本靠備份。我個(gè)人的選型原則很簡單刪除少量指定行、需要條件篩選用DELETE清空一張表但希望保留表結(jié)構(gòu)以備后續(xù)使用且確認(rèn)不需要回滾用TRUNCATE連表結(jié)構(gòu)都不要了才用DROP。越危險(xiǎn)的操作越要留后路這是從業(yè)十幾年最深的體會(huì)。什么時(shí)候DELETE不是最優(yōu)解如果你的目標(biāo)是“把一張大表里的數(shù)據(jù)全部刪掉只留空表”TRUNCATE通常更合適。它不像DELETE那樣逐行生成刪除日志所以速度快得多在MySQL里TRUNCATE是隱式提交的直接釋放表空間。但反過來如果你只需要?jiǎng)h掉表中很小一部分?jǐn)?shù)據(jù)則應(yīng)該毫不猶豫地選DELETE因?yàn)門RUNCATE沒有這個(gè)能力。2.2 為什么DELETE的性能差異這么大用DELETE刪100行數(shù)據(jù)很快但刪1000萬行就可能把數(shù)據(jù)庫卡死原因要從DELETE的執(zhí)行機(jī)制說起。DELETE本質(zhì)上是對目標(biāo)行加鎖然后逐行刪除同時(shí)記錄足夠多的日志以便回滾。在InnoDB存儲(chǔ)引擎下MySQL默認(rèn)每刪除一行都要寫undo日志和redo日志行被標(biāo)記刪除后后續(xù)還要由purge線程真正清理。這就意味著刪除的數(shù)據(jù)量越大產(chǎn)生的日志量越大占用的系統(tǒng)資源越多。在一個(gè)聯(lián)機(jī)交易庫上執(zhí)行大批量DELETE很容易造成主從延遲、鎖等待甚至磁盤滿。我舉一個(gè)實(shí)際案例某個(gè)業(yè)務(wù)表里有8000萬條記錄其中超過一半是三個(gè)月前的過期數(shù)據(jù)。最初方案是直接用一條DELETE把過期數(shù)據(jù)全部刪掉結(jié)果跑了不到兩分鐘線上開始告警——大量查詢出現(xiàn)鎖等待主庫CPU飆升。后來改成“分批刪除”每批只刪5000條每批之間sleep幾秒再配合非高峰時(shí)段執(zhí)行整個(gè)過程平穩(wěn)得多系統(tǒng)一點(diǎn)沒受影響。所以在方案設(shè)計(jì)階段就要根據(jù)數(shù)據(jù)量選擇執(zhí)行策略。一般來說單次DELETE影響行數(shù)在幾千以內(nèi)可以當(dāng)作常規(guī)操作直接執(zhí)行超過幾萬行就要考慮分批、限流超過百萬行基本必須拆任務(wù)。這屬于經(jīng)驗(yàn)值不同硬件環(huán)境下具體閾值會(huì)有差異但思路是通用的。2.3 事務(wù)隔離級(jí)別與DELETE的關(guān)系DELETE和事務(wù)的關(guān)系非常緊密理解了這個(gè)生產(chǎn)環(huán)境里能少踩一半的坑。以MySQL默認(rèn)的REPEATABLE READ隔離級(jí)別為例DELETE執(zhí)行時(shí)除了給目標(biāo)行加排他鎖還會(huì)在可重復(fù)讀隔離級(jí)別下使用當(dāng)前讀鎖定讀也就是說它會(huì)讀取最新已提交版本并加鎖而不是讀取某個(gè)歷史快照。這一點(diǎn)在“先查后刪”的場景里尤其重要。舉個(gè)例子你打算刪除狀態(tài)為“待支付”且創(chuàng)建時(shí)間超過30分鐘的訂單。先單獨(dú)跑一條SELECT確認(rèn)有120條符合條件然后執(zhí)行DELETE。就在這兩條語句的間隙又有新的“待支付”訂單插進(jìn)來了——這沒什么問題因?yàn)槟愕臈l件不會(huì)匹配它。但如果條件本身是動(dòng)態(tài)變化的比如“刪除積分小于0的用戶”而有人同時(shí)在給用戶加積分那刪除的結(jié)果可能和你SELECT時(shí)看到的不完全一致。處理方式很簡單重要?jiǎng)h除操作盡量在低峰期執(zhí)行或者在事務(wù)里配合合適的鎖機(jī)制保證一致性。PostgreSQL和SQL Server也有各自的事務(wù)和鎖機(jī)制但邏輯大同小異DELETE不是“瞬間完成”的操作它會(huì)與并發(fā)讀寫產(chǎn)生相互作用。真正上線之前務(wù)必在測試環(huán)境模擬并發(fā)場景別拿生產(chǎn)庫當(dāng)試驗(yàn)田。3. DELETE的核心語法與細(xì)節(jié)拆解3.1 標(biāo)準(zhǔn)DELETE語法逐段解讀DELETE的官方語法很簡單幾種主流數(shù)據(jù)庫的寫法基本一致DELETE FROM 表名 WHERE 條件 [ORDER BY 排序字段] [LIMIT 行數(shù)]; -- MySQL專屬DELETE FROM 表名必填指定你要從哪張表刪除數(shù)據(jù)。WHERE 條件可選但實(shí)際生產(chǎn)中幾乎必填。不寫WHERE就是清空整張表。ORDER BY LIMITMySQL里可以控制刪除順序和刪除行數(shù)比如DELETE FROM t WHERE status1 ORDER BY create_time ASC LIMIT 100先刪最舊的100條。這里要重點(diǎn)強(qiáng)調(diào)一點(diǎn)DELETE不加WHERE就是一次全表數(shù)據(jù)清空這不是開玩笑的事幾乎每個(gè)DBA都處理過這種事故。有一種習(xí)慣值得養(yǎng)成——執(zhí)行任何DELETE之前先把你準(zhǔn)備要寫的WHERE條件放到SELECT里跑一遍確認(rèn)查出來的行是你要?jiǎng)h的那些。檢查無誤后再把SELECT *換成DELETE這種肌肉記憶會(huì)在關(guān)鍵時(shí)刻救你一命。WHERE條件怎么寫直接決定刪除的精準(zhǔn)度。單個(gè)條件最簡單比如WHERE id 123多個(gè)條件用AND/OR組合比如WHERE status closed AND created_at DATE_SUB(NOW(), INTERVAL 90 DAY)。這里要提醒一個(gè)常見誤區(qū)OR的優(yōu)先級(jí)低于AND所以WHERE a 1 OR b 2 AND c 3會(huì)被解析成a 1 OR (b 2 AND c 3)如果不加括號(hào)很容易刪到意料之外的行。多個(gè)條件組合時(shí)拿不準(zhǔn)就加括號(hào)這不是謹(jǐn)慎過度是保命。3.2 WHERE條件的精準(zhǔn)控制技巧精準(zhǔn)刪除的核心是“讓W(xué)HERE條件恰好圈出目標(biāo)數(shù)據(jù)不多不少”。這個(gè)目標(biāo)說起來容易做起來有幾類典型場景值得單獨(dú)拿出來講。先說日期區(qū)間。刪除歷史數(shù)據(jù)是最高頻的需求比如“刪除一年前的日志”條件一般寫成WHERE create_time DATE_SUB(NOW(), INTERVAL 1 YEAR)。這里的邊界值要特別注意如果你用就會(huì)把剛好一年前那一秒的數(shù)據(jù)也刪掉如果數(shù)據(jù)庫存的create_time是datetime類型建議用而不是配合第二天的00:00:00作為邊界更穩(wěn)。再說字符串匹配。很多業(yè)務(wù)表里有“刪除所有測試數(shù)據(jù)”的需求測試數(shù)據(jù)往往以test或tmp開頭。此時(shí)WHERE name LIKE test%能刪掉前綴是test的行但要注意LIKE的語義是否會(huì)誤傷線上數(shù)據(jù)。比如一個(gè)用戶昵稱恰好是“test_user_001”但你只想刪內(nèi)部測試號(hào)——這時(shí)最好增加一個(gè)額外的標(biāo)記字段比如AND is_test 1。多一個(gè)條件就多一分安全。還有一個(gè)高級(jí)用法用子查詢?nèi)Χ▌h除范圍。比如你想刪除“近30天沒有任何訂單的用戶”直接寫DELETE FROM users WHERE id IN (SELECT user_id FROM orders GROUP BY user_id HAVING MAX(create_time) NOW() - INTERVAL 30 DAY)這種寫法把“判斷邏輯”放在子查詢中WHERE只負(fù)責(zé)匹配ID。在MySQL中要注意子查詢里引用同一張表時(shí)會(huì)有一些限制在老版本里不能直接對同一個(gè)表進(jìn)行SELECT再DELETE需要用臨時(shí)表包一層后面實(shí)操章節(jié)會(huì)具體演示。3.3 多表刪除一條DELETE刪多張表的數(shù)據(jù)實(shí)際業(yè)務(wù)中很少只刪一張表。比如用戶注銷需要把用戶主表、訂單表、登錄日志表里的相關(guān)數(shù)據(jù)一并清理。多表刪除有幾種寫法不同數(shù)據(jù)庫語法差異很大這里重點(diǎn)講兩種主流方案。第一種是逐表DELETE包在一個(gè)事務(wù)里這是兼容性最好、也最容易被理解的方式。先刪訂單表再刪日志表最后刪用戶主表任何一步出錯(cuò)都可以整體回滾保證一致性。第二種是MySQL特有的多表DELETE語法DELETE t1, t2 FROM users t1 LEFT JOIN orders t2 ON t2.user_id t1.id WHERE t1.id 10086;這條語句會(huì)同時(shí)刪除users表里id10086的用戶以及orders表里關(guān)聯(lián)的訂單行。它的好處是一條SQL搞定多張表壞處是——如果不小心把JOIN條件寫錯(cuò)可能刪掉不該刪的數(shù)據(jù)。我個(gè)人對多表DELETE的態(tài)度是能拆成多條就拆拆不開再合最少用事務(wù)包住。SQL Server里也有類似寫法FROM子句后跟JOINPostgreSQL則原生支持DELETE USING語法。不同數(shù)據(jù)庫語法有差異但思路一致關(guān)聯(lián)條件必須寫在WHERE里而不是JOIN條件里否則你會(huì)發(fā)現(xiàn)問題很嚴(yán)重——JOIN把不該刪除的數(shù)據(jù)也關(guān)聯(lián)進(jìn)來了。3.4 DELETE與TRUNCATE的邊界場景補(bǔ)充這一節(jié)再補(bǔ)充幾個(gè)容易忽略的細(xì)節(jié)。TRUNCATE在MySQL里會(huì)隱式提交執(zhí)行之后無法回滾。PostgreSQL的TRUNCATE支持事務(wù)回滾但依然不逐行觸發(fā)刪除觸發(fā)器。如果你的表上有DELETE觸發(fā)器比如數(shù)據(jù)變更審計(jì)TRUNCATE默認(rèn)不會(huì)觸發(fā)這可能導(dǎo)致審計(jì)日志缺失。需要觸發(fā)器記錄刪除行為的表務(wù)必使用DELETE而不是TRUNCATE。還有自增ID的問題。DELETE刪除行之后表的AUTO_INCREMENT計(jì)數(shù)器一般不會(huì)重置而TRUNCATE在大多數(shù)數(shù)據(jù)庫里會(huì)把計(jì)數(shù)器重置。測試環(huán)境里想“刪完數(shù)據(jù)并讓ID重新從1開始”TRUNCATE比DELETE省事但如果你需要保留歷史ID序列語義就別用TRUNCATE。另外補(bǔ)充一個(gè)MySQL特有的坑DELETE時(shí)不建議省略WHERE并只依賴LIMIT來“控制刪除數(shù)量”。雖然語法上DELETE FROM t LIMIT 100合法但它沒有條件地刪除任意100行行為不可預(yù)期生產(chǎn)中幾乎不該出現(xiàn)。4. 實(shí)操過程從備份到執(zhí)行DELETE的完整流程4.1 環(huán)境準(zhǔn)備與刪除前的備份方案不管你是刪1行還是刪100萬行刪除前的備份這步絕對不能省?！拔矣袦y試庫驗(yàn)證過了”和“正式庫出了事能恢復(fù)”是兩件完全不同的事。最穩(wěn)妥的備份方法是物理備份。MySQL里可以用mysqldump把整張表導(dǎo)出mysqldump -u用戶名 -p數(shù)據(jù)庫名 表名 /data/backup/表名_$(date %Y%m%d).sql如果表特別大整表導(dǎo)出太慢至少也要用WHERE條件把將要?jiǎng)h除的數(shù)據(jù)備份出來mysqldump -u用戶名 -p數(shù)據(jù)庫名 表名 \ --wherestatusclosed AND create_time 2023-01-01 \ /data/backup/待刪除數(shù)據(jù)_備份.sqlPostgreSQL可以用pg_dumpSQL Server有導(dǎo)出任務(wù)思路相同刪除前必須能回答一個(gè)問題——如果刪錯(cuò)了這些數(shù)據(jù)還能不能找回來答不上來就不要執(zhí)行DELETE。除了備份我強(qiáng)烈建議在刪除前記錄一些“元信息”比如當(dāng)前符合條件的行數(shù)、表的行數(shù)、鍵的分布范圍。這些信息一是用于核對刪除范圍二是萬一出了問題能快速定位備份要恢復(fù)到什么時(shí)間點(diǎn)。實(shí)際操作中我通常會(huì)順手把SELECT COUNT(*)和執(zhí)行計(jì)劃一起保留下來留檔備查。4.2 先用SELECT驗(yàn)證再轉(zhuǎn)DELETE在正式執(zhí)行DELETE之前有一個(gè)幾乎零成本的驗(yàn)證步驟把DELETE語句用SELECT代替先跑一遍。這個(gè)習(xí)慣我逢人就推薦因?yàn)樗娴哪軗踝〈蟛糠质鹿?。假設(shè)你要執(zhí)行這條刪除DELETE FROM orders WHERE status cancelled AND updated_at 2024-06-01;先改成SELECT COUNT(*), MAX(id), MIN(id) FROM orders WHERE status cancelled AND updated_at 2024-06-01;從返回結(jié)果里能直接看到將要?jiǎng)h除的數(shù)據(jù)量、ID范圍。如果數(shù)據(jù)量和你預(yù)期嚴(yán)重不符說明WHERE條件有問題需要停下來檢查。這一步雖然多花十幾秒但把“刪除操作”和“概率事故”之間隔開了一道安全墻。除了COUNT還可以隨機(jī)抽查幾條數(shù)據(jù)肉眼看一下是不是確實(shí)該刪。比如SELECT id, user_id, status, updated_at FROM orders WHERE status cancelled AND updated_at 2024-06-01 LIMIT 20;抽查數(shù)據(jù)和業(yè)務(wù)方確認(rèn)后再執(zhí)行真正的DELETE。這一步在團(tuán)隊(duì)協(xié)作場景里尤其重要——你以為是“過期數(shù)據(jù)”業(yè)務(wù)方可能正在查詢分析它們。4.3 事務(wù)包裹與分批刪除的正確姿勢驗(yàn)證完成后正式刪除前要做的關(guān)鍵決策是直接執(zhí)行還是分批執(zhí)行這取決于數(shù)據(jù)量。幾千行以內(nèi)可以直接刪幾萬行以上的建議分批。以MySQL為例分批刪除的一種常見寫法是DELETE FROM orders WHERE status cancelled AND updated_at 2024-06-01 LIMIT 5000;反復(fù)執(zhí)行這條語句直到影響行數(shù)為0每次刪除5000行。可以手動(dòng)重復(fù)執(zhí)行也可以寫成存儲(chǔ)過程或腳本循環(huán)調(diào)用。生產(chǎn)環(huán)境中如果條件允許建議把刪除操作顯式包裹在一個(gè)事務(wù)里并設(shè)定好DELAY_KEY_WRITE等參數(shù)。對于小批量刪除顯式事務(wù)的好處是出錯(cuò)可回滾就算連接意外中斷也不會(huì)有半截子數(shù)據(jù)處于一致性問題中。我經(jīng)歷過一個(gè)深層教訓(xùn)有一回刪除某日志表分批刪了二十多批突然發(fā)現(xiàn)條件漏掉一個(gè)過濾項(xiàng)導(dǎo)致多刪了一部分?jǐn)?shù)據(jù)。因?yàn)橹笆亲詣?dòng)提交的那些刪除已經(jīng)生效只能拿著備份去恢復(fù)。后來再做大表刪除我會(huì)先執(zhí)行START TRANSACTION刪完第一批后先不提交而是SELECT驗(yàn)證影響行數(shù)和剩余行數(shù)是否正確確認(rèn)無誤再COMMIT。這樣就算發(fā)現(xiàn)問題回滾的代價(jià)也遠(yuǎn)小于事后恢復(fù)備份。4.4 索引使用與執(zhí)行計(jì)劃檢查DELETE語句的WHERE條件如果沒走索引會(huì)發(fā)生什么最壞的情況是全表掃描——數(shù)據(jù)庫把每一行都讀一遍判斷是否滿足條件符合條件的再加鎖刪除。表越大這個(gè)操作越慢鎖的范圍也會(huì)擴(kuò)大影響在線業(yè)務(wù)。所以在執(zhí)行大批量DELETE之前必須檢查執(zhí)行計(jì)劃。MySQL里用EXPLAINEXPLAIN DELETE FROM orders WHERE status cancelled AND updated_at 2024-06-01;看type列是不是ALL全表掃描如果是說明沒有合適的索引。解決方案是提前創(chuàng)建聯(lián)合索引ALTER TABLE orders ADD INDEX idx_status_updated (status, updated_at);為什么建議聯(lián)合索引而不是兩個(gè)單列索引因?yàn)檫@條刪除條件的過濾邏輯是先按status定位再按updated_at排序或過濾。聯(lián)合索引可以一步定位到目標(biāo)行范圍單列索引則可能需要回表多次。具體使用中MySQL優(yōu)化器會(huì)自行選擇成本更低的方案但提前建好聯(lián)合索引總是更穩(wěn)妥的選擇。這里要提醒一點(diǎn)索引不是越多越好。DELETE之外還有INSERT和UPDATE每多一個(gè)索引寫入數(shù)據(jù)時(shí)要多維護(hù)一份索引。如果這張表刪除操作不頻繁就按需建索引如果刪除是高頻操作索引設(shè)計(jì)就要專門為DELETE優(yōu)化。數(shù)據(jù)庫性能調(diào)優(yōu)從來不是單一操作最優(yōu)而是整體權(quán)衡。5. 常見問題與排查技巧實(shí)錄5.1 刪除卡死或鎖等待怎么辦癥狀DELETE語句執(zhí)行很久不返回或者報(bào)鎖等待超時(shí)錯(cuò)誤如MySQL的Lock wait timeout exceeded。排查步驟第一先看當(dāng)前有哪些事務(wù)持有鎖SELECT * FROM information_schema.innodb_trx;第二通過sys.innodb_lock_waits視圖或performance_schema查看鎖等待關(guān)系找到“源頭事務(wù)”是什么。第三確認(rèn)源頭事務(wù)能否快速結(jié)束。如果是一個(gè)長時(shí)間未提交的UPDATE可以考慮讓對應(yīng)應(yīng)用提交或回滾如果源頭事務(wù)確實(shí)不能立刻結(jié)束可以選擇等它執(zhí)行完或者把DELETE操作安排在業(yè)務(wù)低谷期。防患于未然的方法很簡單大批量DELETE不在高峰時(shí)段執(zhí)行并控制單批刪除影響行數(shù)。此外刪除操作前可以先獲取一個(gè)較小的鎖范圍比如用主鍵范圍分片每次只刪一段ID區(qū)間的數(shù)據(jù)鎖沖突自然減少。5.2 誤刪數(shù)據(jù)后如何快速恢復(fù)誤刪數(shù)據(jù)是SQL領(lǐng)域最讓人頭皮發(fā)麻的事。好在你提前做了備份恢復(fù)流程可以分為兩類。如果刪除操作還在未提交事務(wù)中直接ROLLBACK即可ROLLBACK;如果已經(jīng)提交只能依靠備份恢復(fù)。全量備份配上binlog或歸檔日志可以做時(shí)間點(diǎn)恢復(fù)這也是生產(chǎn)環(huán)境的標(biāo)準(zhǔn)姿勢。MySQL的恢復(fù)思路大致是用備份恢復(fù)出刪除前的快照再通過binlog把從備份時(shí)間點(diǎn)到誤刪時(shí)刻的增量操作重放出來跳過DELETE那條事務(wù)。PostgreSQL有PITR時(shí)間點(diǎn)恢復(fù)SQL Server有日志備份恢復(fù)原理都是類似思路。但恢復(fù)操作本身很繁瑣耗時(shí)也不短所以日常的口訣是能備份就不賭能回滾就不提交。如果實(shí)在沒有備份也沒有日志還有一條下策用Undelete工具掃描InnoDB文件碎片或者用專門的數(shù)據(jù)恢復(fù)軟件掃描磁盤頁。這一條僅作為最后的絕望選項(xiàng)成功率不高且依賴存儲(chǔ)引擎的物理特性我在實(shí)際工作中幾乎沒見過成功案例。所以再次強(qiáng)調(diào)反向操作遠(yuǎn)比正向操作難備份做好了數(shù)據(jù)庫事故就成功一半了。5.3 外鍵約束導(dǎo)致DELETE失敗刪除父表數(shù)據(jù)時(shí)如果子表里有引用外鍵約束會(huì)拒絕刪除報(bào)錯(cuò)信息大概是Cannot delete or update a parent row: a foreign key constraint fails。有兩種處理思路第一種先刪子表再刪父表。比如刪除用戶前先把他的訂單清掉再刪除用戶。這也是之前提到的“逐表DELETE事務(wù)”方案。第二種確認(rèn)業(yè)務(wù)允許后臨時(shí)禁用外鍵檢查。MySQL里可以SET FOREIGN_KEY_CHECKS 0; DELETE FROM users WHERE id 10086; SET FOREIGN_KEY_CHECKS 1;這個(gè)操作務(wù)必謹(jǐn)慎。臨時(shí)禁用外鍵后如果刪除順序和邏輯有問題可能留下孤兒數(shù)據(jù)子表還引用著已經(jīng)不存在的父表記錄。只推薦在明確知道后果、并做好備份的前提下使用。5.4 DELETE后的表空間沒有變小是沒刪干凈嗎這是一個(gè)迷惑性極強(qiáng)的問題。在MySQL InnoDB中執(zhí)行DELETE后表文件大小可能幾乎沒有變化。這不是沒刪干凈而是數(shù)據(jù)的物理空間沒有立刻歸還給操作系統(tǒng)。InnoDB刪除行時(shí)只是把這些行標(biāo)記為“已刪除”后續(xù)新插入的數(shù)據(jù)可能復(fù)用這些空間。如果想要真正把空間釋放給操作系統(tǒng)需要執(zhí)行OPTIMIZE TABLE 表名;或重建表比如ALTER TABLE ... ENGINEInnoDB。但這兩個(gè)操作在表非常大時(shí)都需要較長執(zhí)行時(shí)間并且會(huì)鎖表或消耗大量IO生產(chǎn)環(huán)境務(wù)必安排在維護(hù)窗口執(zhí)行。只有當(dāng)刪除比例非常大比如刪了60%以上且確認(rèn)表不會(huì)再快速增長時(shí)才值得做物理空間收縮。PostgreSQL中類似的概念是VACUUM FULLSQL Server里有收縮數(shù)據(jù)庫功能。核心邏輯都是一樣的DELETE刪的是邏輯數(shù)據(jù)物理空間的回收是另一回事。別因?yàn)長OT尺寸沒變就反復(fù)重跑DELETE那樣只會(huì)白白增加一次全表掃描的成本。5.5 批量刪除時(shí)日志暴漲如何處理DELETE產(chǎn)生的日志量通常比UPDATE大因?yàn)槊啃卸家涗浲暾膁elete操作信息以待恢復(fù)。批量DELETE尤其是一次刪百萬行binlog或事務(wù)日志可能暴漲幾GB甚至幾十GB在磁盤緊張的機(jī)器上可能直接把磁盤寫滿導(dǎo)致業(yè)務(wù)停擺。處理辦法有三板斧第一分批刪除控制單批大小日志產(chǎn)生速度自然回落。第二如果業(yè)務(wù)允許臨時(shí)調(diào)大日志緩沖或切換binlog格式比如從ROW格式調(diào)整但注意這會(huì)影響其他邏輯不建議輕易改動(dòng)。第三條是在磁盤規(guī)劃和監(jiān)控上做文章保證日志目錄所在磁盤有足夠余量。真實(shí)項(xiàng)目里最常用、最穩(wěn)妥的還是分批刪除每批影響行數(shù)5000~20000批間sleep幾秒既控制日志量也給主從同步留出追趕空間。配置文件再優(yōu)化也替代不了分批這個(gè)動(dòng)作。6. 再看一個(gè)完整案例清理過期訂單講完理論和排查用一個(gè)貼近真實(shí)業(yè)務(wù)的完整案例把流程串起來。需求背景某訂單表orders有約3000萬行其中120萬行是“已取消”且超過180天沒有變更的舊訂單需要清理騰出存儲(chǔ)空間并提高查詢性能。第一步備份mysqldump -uops -p commerce orders \ --wherestatuscancelled AND updated_at DATE_SUB(NOW(), INTERVAL 180 DAY) \ /data/backup/orders_cancelled_before_$(date %Y%m%d).sql第二步驗(yàn)證條件與數(shù)據(jù)量SELECT COUNT(*), MIN(id), MAX(id) FROM orders WHERE status cancelled AND updated_at DATE_SUB(NOW(), INTERVAL 180 DAY);確認(rèn)行數(shù)在百萬級(jí)別后檢查執(zhí)行計(jì)劃。如果type為ALL先建立聯(lián)合索引ALTER TABLE orders ADD INDEX idx_status_updated (status, updated_at);第三步分批刪除。這段腳本可以用存儲(chǔ)過程實(shí)現(xiàn)循環(huán)刪除DELIMITER $$ CREATE PROCEDURE batch_delete_old_orders() BEGIN DECLARE affected_rows INT DEFAULT 1; WHILE affected_rows 0 DO DELETE FROM orders WHERE status cancelled AND updated_at DATE_SUB(NOW(), INTERVAL 180 DAY) LIMIT 5000; SET affected_rows ROW_COUNT(); COMMIT; DO SLEEP(2); END WHILE; END$$ DELIMITER ;第四步驗(yàn)證結(jié)果。確認(rèn)DELETE影響行數(shù)接近備份時(shí)的COUNT再巡檢從庫延遲、磁盤空間、慢查詢等指標(biāo)。第五步根據(jù)實(shí)際業(yè)務(wù)需要決定是否優(yōu)化表空間。由于這次刪除只占全表的4%左右我沒有執(zhí)行OPTIMIZE TABLE因?yàn)榛靥盥什桓呖臻g釋放意義有限也避免了維護(hù)窗口的系統(tǒng)負(fù)載。這就是“不做多余操作”的取舍——技術(shù)與業(yè)務(wù)目標(biāo)匹配才是最好的方案。7. 我踩過的坑和最后想說的話寫DELETE相關(guān)的SQL技術(shù)難度真的不高真正的難度在于“敬畏數(shù)據(jù)”。我入行的第三年就闖過一次禍在測試庫上調(diào)試存儲(chǔ)過程一時(shí)疏忽把一條DELETE的WHERE條件寫漏了一層誤刪了將近半張配置表。當(dāng)時(shí)因?yàn)橛袀浞莼謴?fù)花了兩個(gè)小時(shí)算是僥幸沒有造成大影響。但那次之后我養(yǎng)成了幾個(gè)雷打不動(dòng)的習(xí)慣現(xiàn)在分享給你。第一DELETE必須寫WHERE除非你百分之百確定要清空整張表。第二DELETE之前必走SELECT驗(yàn)證這是流程的一部分不是效率低的表現(xiàn)。第三重要數(shù)據(jù)刪除必須放在事務(wù)里任何時(shí)候給自己留一個(gè)ROLLBACK的余地。第四大批量刪除一定要分批這不是性能優(yōu)化是生產(chǎn)安全。SQL里的DELETE是一把鋒利的手術(shù)刀用得好可以精準(zhǔn)切除病灶用不好就是傷及無辜。這篇內(nèi)容里的每個(gè)技巧和習(xí)慣都是實(shí)戰(zhàn)里用教訓(xùn)換來的。刪數(shù)據(jù)這事技術(shù)越熟練越容易大意反而是那些時(shí)刻保持警惕的人才能安安穩(wěn)穩(wěn)地寫完每一條DELETE。 SQL DELETE完全指南：從基礎(chǔ)語法到生產(chǎn)環(huán)境避坑實(shí)踐
1. 先搞清楚DELETE到底解決什么問題很多人第一次寫SQL學(xué)的第一條語句就是SELECT第二條大概率就是DELETE。SQL里最容易被輕視、也最容易出事的恰恰就是這個(gè)DELETE。先給結(jié)論DELETE是SQL里用來刪除表中數(shù)據(jù)的語句它刪的是“行”不是“表”。你可以把它理解成整理房間時(shí)扔掉不要的東西而不是把整個(gè)房間拆掉。DELETE只處理數(shù)據(jù)本身表的結(jié)構(gòu)、索引、約束這些全都保留著這一點(diǎn)必須先刻在腦子里。DELETE解決的核心場景很明確業(yè)務(wù)系統(tǒng)里產(chǎn)生了臟數(shù)據(jù)、測試數(shù)據(jù)、過期數(shù)據(jù)或者用戶主動(dòng)注銷、退訂、清空記錄都需要精準(zhǔn)地把某些行刪掉。比如電商訂單里有大量“已取消”狀態(tài)的廢棄記錄比如用戶表里有幾百個(gè)測試賬號(hào)比如日志表里三個(gè)月前的數(shù)據(jù)占了好幾個(gè)G——這些場景統(tǒng)統(tǒng)靠DELETE來解決。我見過太多新手在寫DELETE時(shí)的第一個(gè)反應(yīng)是“它跟DROP有什么區(qū)別”答案很簡單DROP直接連表帶數(shù)據(jù)一起銷毀TRUNCATE把表里的數(shù)據(jù)全清空但保留表結(jié)構(gòu)而DELETE是可以加WHERE條件、只刪除指定行的。DELETE最大的價(jià)值就是“精準(zhǔn)”二字WHERE條件寫得多好刪除就有多準(zhǔn)。這篇文章適合誰看如果你剛學(xué)SQL不久或者寫了幾年代碼但DELETE總是靠“備份膽量”在撐那你來對地方了。我會(huì)把DELETE的語法、原理、實(shí)操步驟、避坑經(jīng)驗(yàn)都過一遍尤其是那些在正式文檔里不太會(huì)寫、但生產(chǎn)環(huán)境里一定會(huì)踩的坑。2. DELETE的整體設(shè)計(jì)與選型思路2.1 DELETE、TRUNCATE、DROP怎么選刪除數(shù)據(jù)這件事SQL里不止DELETE一條路。很多人上來就刪刪完才發(fā)現(xiàn)選錯(cuò)了工具。先把三者的關(guān)系理清楚才能知道什么場景該用哪個(gè)。操作刪除范圍保留表結(jié)構(gòu)可加WHERE事務(wù)支持速度DELETE指定行或全部行保留支持支持慢TRUNCATE全部行保留不支持部分?jǐn)?shù)據(jù)庫支持快DROP表本身數(shù)據(jù)不保留不支持部分?jǐn)?shù)據(jù)庫支持最快從這個(gè)表能看出一個(gè)關(guān)鍵差異只有DELETE是“可反悔”的刪除。在MySQL、PostgreSQL、SQL Server這些主流數(shù)據(jù)庫里DELETE語句如果包在事務(wù)里執(zhí)行出錯(cuò)或發(fā)現(xiàn)刪錯(cuò)時(shí)可以ROLLBACK回滾TRUNCATE在多數(shù)數(shù)據(jù)庫里不支持事務(wù)回滾PostgreSQL支持但MySQL不支持DROP更狠表都沒了想回滾基本靠備份。我個(gè)人的選型原則很簡單刪除少量指定行、需要條件篩選用DELETE清空一張表但希望保留表結(jié)構(gòu)以備后續(xù)使用且確認(rèn)不需要回滾用TRUNCATE連表結(jié)構(gòu)都不要了才用DROP。越危險(xiǎn)的操作越要留后路這是從業(yè)十幾年最深的體會(huì)。什么時(shí)候DELETE不是最優(yōu)解如果你的目標(biāo)是“把一張大表里的數(shù)據(jù)全部刪掉只留空表”TRUNCATE通常更合適。它不像DELETE那樣逐行生成刪除日志所以速度快得多在MySQL里TRUNCATE是隱式提交的直接釋放表空間。但反過來如果你只需要?jiǎng)h掉表中很小一部分?jǐn)?shù)據(jù)則應(yīng)該毫不猶豫地選DELETE因?yàn)門RUNCATE沒有這個(gè)能力。2.2 為什么DELETE的性能差異這么大用DELETE刪100行數(shù)據(jù)很快但刪1000萬行就可能把數(shù)據(jù)庫卡死原因要從DELETE的執(zhí)行機(jī)制說起。DELETE本質(zhì)上是對目標(biāo)行加鎖然后逐行刪除同時(shí)記錄足夠多的日志以便回滾。在InnoDB存儲(chǔ)引擎下MySQL默認(rèn)每刪除一行都要寫undo日志和redo日志行被標(biāo)記刪除后后續(xù)還要由purge線程真正清理。這就意味著刪除的數(shù)據(jù)量越大產(chǎn)生的日志量越大占用的系統(tǒng)資源越多。在一個(gè)聯(lián)機(jī)交易庫上執(zhí)行大批量DELETE很容易造成主從延遲、鎖等待甚至磁盤滿。我舉一個(gè)實(shí)際案例某個(gè)業(yè)務(wù)表里有8000萬條記錄其中超過一半是三個(gè)月前的過期數(shù)據(jù)。最初方案是直接用一條DELETE把過期數(shù)據(jù)全部刪掉結(jié)果跑了不到兩分鐘線上開始告警——大量查詢出現(xiàn)鎖等待主庫CPU飆升。后來改成“分批刪除”每批只刪5000條每批之間sleep幾秒再配合非高峰時(shí)段執(zhí)行整個(gè)過程平穩(wěn)得多系統(tǒng)一點(diǎn)沒受影響。所以在方案設(shè)計(jì)階段就要根據(jù)數(shù)據(jù)量選擇執(zhí)行策略。一般來說單次DELETE影響行數(shù)在幾千以內(nèi)可以當(dāng)作常規(guī)操作直接執(zhí)行超過幾萬行就要考慮分批、限流超過百萬行基本必須拆任務(wù)。這屬于經(jīng)驗(yàn)值不同硬件環(huán)境下具體閾值會(huì)有差異但思路是通用的。2.3 事務(wù)隔離級(jí)別與DELETE的關(guān)系DELETE和事務(wù)的關(guān)系非常緊密理解了這個(gè)生產(chǎn)環(huán)境里能少踩一半的坑。以MySQL默認(rèn)的REPEATABLE READ隔離級(jí)別為例DELETE執(zhí)行時(shí)除了給目標(biāo)行加排他鎖還會(huì)在可重復(fù)讀隔離級(jí)別下使用當(dāng)前讀鎖定讀也就是說它會(huì)讀取最新已提交版本并加鎖而不是讀取某個(gè)歷史快照。這一點(diǎn)在“先查后刪”的場景里尤其重要。舉個(gè)例子你打算刪除狀態(tài)為“待支付”且創(chuàng)建時(shí)間超過30分鐘的訂單。先單獨(dú)跑一條SELECT確認(rèn)有120條符合條件然后執(zhí)行DELETE。就在這兩條語句的間隙又有新的“待支付”訂單插進(jìn)來了——這沒什么問題因?yàn)槟愕臈l件不會(huì)匹配它。但如果條件本身是動(dòng)態(tài)變化的比如“刪除積分小于0的用戶”而有人同時(shí)在給用戶加積分那刪除的結(jié)果可能和你SELECT時(shí)看到的不完全一致。處理方式很簡單重要?jiǎng)h除操作盡量在低峰期執(zhí)行或者在事務(wù)里配合合適的鎖機(jī)制保證一致性。PostgreSQL和SQL Server也有各自的事務(wù)和鎖機(jī)制但邏輯大同小異DELETE不是“瞬間完成”的操作它會(huì)與并發(fā)讀寫產(chǎn)生相互作用。真正上線之前務(wù)必在測試環(huán)境模擬并發(fā)場景別拿生產(chǎn)庫當(dāng)試驗(yàn)田。3. DELETE的核心語法與細(xì)節(jié)拆解3.1 標(biāo)準(zhǔn)DELETE語法逐段解讀DELETE的官方語法很簡單幾種主流數(shù)據(jù)庫的寫法基本一致DELETE FROM 表名 WHERE 條件 [ORDER BY 排序字段] [LIMIT 行數(shù)]; -- MySQL專屬DELETE FROM 表名必填指定你要從哪張表刪除數(shù)據(jù)。WHERE 條件可選但實(shí)際生產(chǎn)中幾乎必填。不寫WHERE就是清空整張表。ORDER BY LIMITMySQL里可以控制刪除順序和刪除行數(shù)比如DELETE FROM t WHERE status1 ORDER BY create_time ASC LIMIT 100先刪最舊的100條。這里要重點(diǎn)強(qiáng)調(diào)一點(diǎn)DELETE不加WHERE就是一次全表數(shù)據(jù)清空這不是開玩笑的事幾乎每個(gè)DBA都處理過這種事故。有一種習(xí)慣值得養(yǎng)成——執(zhí)行任何DELETE之前先把你準(zhǔn)備要寫的WHERE條件放到SELECT里跑一遍確認(rèn)查出來的行是你要?jiǎng)h的那些。檢查無誤后再把SELECT *換成DELETE這種肌肉記憶會(huì)在關(guān)鍵時(shí)刻救你一命。WHERE條件怎么寫直接決定刪除的精準(zhǔn)度。單個(gè)條件最簡單比如WHERE id 123多個(gè)條件用AND/OR組合比如WHERE status closed AND created_at DATE_SUB(NOW(), INTERVAL 90 DAY)。這里要提醒一個(gè)常見誤區(qū)OR的優(yōu)先級(jí)低于AND所以WHERE a 1 OR b 2 AND c 3會(huì)被解析成a 1 OR (b 2 AND c 3)如果不加括號(hào)很容易刪到意料之外的行。多個(gè)條件組合時(shí)拿不準(zhǔn)就加括號(hào)這不是謹(jǐn)慎過度是保命。3.2 WHERE條件的精準(zhǔn)控制技巧精準(zhǔn)刪除的核心是“讓W(xué)HERE條件恰好圈出目標(biāo)數(shù)據(jù)不多不少”。這個(gè)目標(biāo)說起來容易做起來有幾類典型場景值得單獨(dú)拿出來講。先說日期區(qū)間。刪除歷史數(shù)據(jù)是最高頻的需求比如“刪除一年前的日志”條件一般寫成WHERE create_time DATE_SUB(NOW(), INTERVAL 1 YEAR)。這里的邊界值要特別注意如果你用就會(huì)把剛好一年前那一秒的數(shù)據(jù)也刪掉如果數(shù)據(jù)庫存的create_time是datetime類型建議用而不是配合第二天的00:00:00作為邊界更穩(wěn)。再說字符串匹配。很多業(yè)務(wù)表里有“刪除所有測試數(shù)據(jù)”的需求測試數(shù)據(jù)往往以test或tmp開頭。此時(shí)WHERE name LIKE test%能刪掉前綴是test的行但要注意LIKE的語義是否會(huì)誤傷線上數(shù)據(jù)。比如一個(gè)用戶昵稱恰好是“test_user_001”但你只想刪內(nèi)部測試號(hào)——這時(shí)最好增加一個(gè)額外的標(biāo)記字段比如AND is_test 1。多一個(gè)條件就多一分安全。還有一個(gè)高級(jí)用法用子查詢?nèi)Χ▌h除范圍。比如你想刪除“近30天沒有任何訂單的用戶”直接寫DELETE FROM users WHERE id IN (SELECT user_id FROM orders GROUP BY user_id HAVING MAX(create_time) NOW() - INTERVAL 30 DAY)這種寫法把“判斷邏輯”放在子查詢中WHERE只負(fù)責(zé)匹配ID。在MySQL中要注意子查詢里引用同一張表時(shí)會(huì)有一些限制在老版本里不能直接對同一個(gè)表進(jìn)行SELECT再DELETE需要用臨時(shí)表包一層后面實(shí)操章節(jié)會(huì)具體演示。3.3 多表刪除一條DELETE刪多張表的數(shù)據(jù)實(shí)際業(yè)務(wù)中很少只刪一張表。比如用戶注銷需要把用戶主表、訂單表、登錄日志表里的相關(guān)數(shù)據(jù)一并清理。多表刪除有幾種寫法不同數(shù)據(jù)庫語法差異很大這里重點(diǎn)講兩種主流方案。第一種是逐表DELETE包在一個(gè)事務(wù)里這是兼容性最好、也最容易被理解的方式。先刪訂單表再刪日志表最后刪用戶主表任何一步出錯(cuò)都可以整體回滾保證一致性。第二種是MySQL特有的多表DELETE語法DELETE t1, t2 FROM users t1 LEFT JOIN orders t2 ON t2.user_id t1.id WHERE t1.id 10086;這條語句會(huì)同時(shí)刪除users表里id10086的用戶以及orders表里關(guān)聯(lián)的訂單行。它的好處是一條SQL搞定多張表壞處是——如果不小心把JOIN條件寫錯(cuò)可能刪掉不該刪的數(shù)據(jù)。我個(gè)人對多表DELETE的態(tài)度是能拆成多條就拆拆不開再合最少用事務(wù)包住。SQL Server里也有類似寫法FROM子句后跟JOINPostgreSQL則原生支持DELETE USING語法。不同數(shù)據(jù)庫語法有差異但思路一致關(guān)聯(lián)條件必須寫在WHERE里而不是JOIN條件里否則你會(huì)發(fā)現(xiàn)問題很嚴(yán)重——JOIN把不該刪除的數(shù)據(jù)也關(guān)聯(lián)進(jìn)來了。3.4 DELETE與TRUNCATE的邊界場景補(bǔ)充這一節(jié)再補(bǔ)充幾個(gè)容易忽略的細(xì)節(jié)。TRUNCATE在MySQL里會(huì)隱式提交執(zhí)行之后無法回滾。PostgreSQL的TRUNCATE支持事務(wù)回滾但依然不逐行觸發(fā)刪除觸發(fā)器。如果你的表上有DELETE觸發(fā)器比如數(shù)據(jù)變更審計(jì)TRUNCATE默認(rèn)不會(huì)觸發(fā)這可能導(dǎo)致審計(jì)日志缺失。需要觸發(fā)器記錄刪除行為的表務(wù)必使用DELETE而不是TRUNCATE。還有自增ID的問題。DELETE刪除行之后表的AUTO_INCREMENT計(jì)數(shù)器一般不會(huì)重置而TRUNCATE在大多數(shù)數(shù)據(jù)庫里會(huì)把計(jì)數(shù)器重置。測試環(huán)境里想“刪完數(shù)據(jù)并讓ID重新從1開始”TRUNCATE比DELETE省事但如果你需要保留歷史ID序列語義就別用TRUNCATE。另外補(bǔ)充一個(gè)MySQL特有的坑DELETE時(shí)不建議省略WHERE并只依賴LIMIT來“控制刪除數(shù)量”。雖然語法上DELETE FROM t LIMIT 100合法但它沒有條件地刪除任意100行行為不可預(yù)期生產(chǎn)中幾乎不該出現(xiàn)。4. 實(shí)操過程從備份到執(zhí)行DELETE的完整流程4.1 環(huán)境準(zhǔn)備與刪除前的備份方案不管你是刪1行還是刪100萬行刪除前的備份這步絕對不能省?！拔矣袦y試庫驗(yàn)證過了”和“正式庫出了事能恢復(fù)”是兩件完全不同的事。最穩(wěn)妥的備份方法是物理備份。MySQL里可以用mysqldump把整張表導(dǎo)出mysqldump -u用戶名 -p數(shù)據(jù)庫名 表名 /data/backup/表名_$(date %Y%m%d).sql如果表特別大整表導(dǎo)出太慢至少也要用WHERE條件把將要?jiǎng)h除的數(shù)據(jù)備份出來mysqldump -u用戶名 -p數(shù)據(jù)庫名 表名 \ --wherestatusclosed AND create_time 2023-01-01 \ /data/backup/待刪除數(shù)據(jù)_備份.sqlPostgreSQL可以用pg_dumpSQL Server有導(dǎo)出任務(wù)思路相同刪除前必須能回答一個(gè)問題——如果刪錯(cuò)了這些數(shù)據(jù)還能不能找回來答不上來就不要執(zhí)行DELETE。除了備份我強(qiáng)烈建議在刪除前記錄一些“元信息”比如當(dāng)前符合條件的行數(shù)、表的行數(shù)、鍵的分布范圍。這些信息一是用于核對刪除范圍二是萬一出了問題能快速定位備份要恢復(fù)到什么時(shí)間點(diǎn)。實(shí)際操作中我通常會(huì)順手把SELECT COUNT(*)和執(zhí)行計(jì)劃一起保留下來留檔備查。4.2 先用SELECT驗(yàn)證再轉(zhuǎn)DELETE在正式執(zhí)行DELETE之前有一個(gè)幾乎零成本的驗(yàn)證步驟把DELETE語句用SELECT代替先跑一遍。這個(gè)習(xí)慣我逢人就推薦因?yàn)樗娴哪軗踝〈蟛糠质鹿?。假設(shè)你要執(zhí)行這條刪除DELETE FROM orders WHERE status cancelled AND updated_at 2024-06-01;先改成SELECT COUNT(*), MAX(id), MIN(id) FROM orders WHERE status cancelled AND updated_at 2024-06-01;從返回結(jié)果里能直接看到將要?jiǎng)h除的數(shù)據(jù)量、ID范圍。如果數(shù)據(jù)量和你預(yù)期嚴(yán)重不符說明WHERE條件有問題需要停下來檢查。這一步雖然多花十幾秒但把“刪除操作”和“概率事故”之間隔開了一道安全墻。除了COUNT還可以隨機(jī)抽查幾條數(shù)據(jù)肉眼看一下是不是確實(shí)該刪。比如SELECT id, user_id, status, updated_at FROM orders WHERE status cancelled AND updated_at 2024-06-01 LIMIT 20;抽查數(shù)據(jù)和業(yè)務(wù)方確認(rèn)后再執(zhí)行真正的DELETE。這一步在團(tuán)隊(duì)協(xié)作場景里尤其重要——你以為是“過期數(shù)據(jù)”業(yè)務(wù)方可能正在查詢分析它們。4.3 事務(wù)包裹與分批刪除的正確姿勢驗(yàn)證完成后正式刪除前要做的關(guān)鍵決策是直接執(zhí)行還是分批執(zhí)行這取決于數(shù)據(jù)量。幾千行以內(nèi)可以直接刪幾萬行以上的建議分批。以MySQL為例分批刪除的一種常見寫法是DELETE FROM orders WHERE status cancelled AND updated_at 2024-06-01 LIMIT 5000;反復(fù)執(zhí)行這條語句直到影響行數(shù)為0每次刪除5000行。可以手動(dòng)重復(fù)執(zhí)行也可以寫成存儲(chǔ)過程或腳本循環(huán)調(diào)用。生產(chǎn)環(huán)境中如果條件允許建議把刪除操作顯式包裹在一個(gè)事務(wù)里并設(shè)定好DELAY_KEY_WRITE等參數(shù)。對于小批量刪除顯式事務(wù)的好處是出錯(cuò)可回滾就算連接意外中斷也不會(huì)有半截子數(shù)據(jù)處于一致性問題中。我經(jīng)歷過一個(gè)深層教訓(xùn)有一回刪除某日志表分批刪了二十多批突然發(fā)現(xiàn)條件漏掉一個(gè)過濾項(xiàng)導(dǎo)致多刪了一部分?jǐn)?shù)據(jù)。因?yàn)橹笆亲詣?dòng)提交的那些刪除已經(jīng)生效只能拿著備份去恢復(fù)。后來再做大表刪除我會(huì)先執(zhí)行START TRANSACTION刪完第一批后先不提交而是SELECT驗(yàn)證影響行數(shù)和剩余行數(shù)是否正確確認(rèn)無誤再COMMIT。這樣就算發(fā)現(xiàn)問題回滾的代價(jià)也遠(yuǎn)小于事后恢復(fù)備份。4.4 索引使用與執(zhí)行計(jì)劃檢查DELETE語句的WHERE條件如果沒走索引會(huì)發(fā)生什么最壞的情況是全表掃描——數(shù)據(jù)庫把每一行都讀一遍判斷是否滿足條件符合條件的再加鎖刪除。表越大這個(gè)操作越慢鎖的范圍也會(huì)擴(kuò)大影響在線業(yè)務(wù)。所以在執(zhí)行大批量DELETE之前必須檢查執(zhí)行計(jì)劃。MySQL里用EXPLAINEXPLAIN DELETE FROM orders WHERE status cancelled AND updated_at 2024-06-01;看type列是不是ALL全表掃描如果是說明沒有合適的索引。解決方案是提前創(chuàng)建聯(lián)合索引ALTER TABLE orders ADD INDEX idx_status_updated (status, updated_at);為什么建議聯(lián)合索引而不是兩個(gè)單列索引因?yàn)檫@條刪除條件的過濾邏輯是先按status定位再按updated_at排序或過濾。聯(lián)合索引可以一步定位到目標(biāo)行范圍單列索引則可能需要回表多次。具體使用中MySQL優(yōu)化器會(huì)自行選擇成本更低的方案但提前建好聯(lián)合索引總是更穩(wěn)妥的選擇。這里要提醒一點(diǎn)索引不是越多越好。DELETE之外還有INSERT和UPDATE每多一個(gè)索引寫入數(shù)據(jù)時(shí)要多維護(hù)一份索引。如果這張表刪除操作不頻繁就按需建索引如果刪除是高頻操作索引設(shè)計(jì)就要專門為DELETE優(yōu)化。數(shù)據(jù)庫性能調(diào)優(yōu)從來不是單一操作最優(yōu)而是整體權(quán)衡。5. 常見問題與排查技巧實(shí)錄5.1 刪除卡死或鎖等待怎么辦癥狀DELETE語句執(zhí)行很久不返回或者報(bào)鎖等待超時(shí)錯(cuò)誤如MySQL的Lock wait timeout exceeded。排查步驟第一先看當(dāng)前有哪些事務(wù)持有鎖SELECT * FROM information_schema.innodb_trx;第二通過sys.innodb_lock_waits視圖或performance_schema查看鎖等待關(guān)系找到“源頭事務(wù)”是什么。第三確認(rèn)源頭事務(wù)能否快速結(jié)束。如果是一個(gè)長時(shí)間未提交的UPDATE可以考慮讓對應(yīng)應(yīng)用提交或回滾如果源頭事務(wù)確實(shí)不能立刻結(jié)束可以選擇等它執(zhí)行完或者把DELETE操作安排在業(yè)務(wù)低谷期。防患于未然的方法很簡單大批量DELETE不在高峰時(shí)段執(zhí)行并控制單批刪除影響行數(shù)。此外刪除操作前可以先獲取一個(gè)較小的鎖范圍比如用主鍵范圍分片每次只刪一段ID區(qū)間的數(shù)據(jù)鎖沖突自然減少。5.2 誤刪數(shù)據(jù)后如何快速恢復(fù)誤刪數(shù)據(jù)是SQL領(lǐng)域最讓人頭皮發(fā)麻的事。好在你提前做了備份恢復(fù)流程可以分為兩類。如果刪除操作還在未提交事務(wù)中直接ROLLBACK即可ROLLBACK;如果已經(jīng)提交只能依靠備份恢復(fù)。全量備份配上binlog或歸檔日志可以做時(shí)間點(diǎn)恢復(fù)這也是生產(chǎn)環(huán)境的標(biāo)準(zhǔn)姿勢。MySQL的恢復(fù)思路大致是用備份恢復(fù)出刪除前的快照再通過binlog把從備份時(shí)間點(diǎn)到誤刪時(shí)刻的增量操作重放出來跳過DELETE那條事務(wù)。PostgreSQL有PITR時(shí)間點(diǎn)恢復(fù)SQL Server有日志備份恢復(fù)原理都是類似思路。但恢復(fù)操作本身很繁瑣耗時(shí)也不短所以日常的口訣是能備份就不賭能回滾就不提交。如果實(shí)在沒有備份也沒有日志還有一條下策用Undelete工具掃描InnoDB文件碎片或者用專門的數(shù)據(jù)恢復(fù)軟件掃描磁盤頁。這一條僅作為最后的絕望選項(xiàng)成功率不高且依賴存儲(chǔ)引擎的物理特性我在實(shí)際工作中幾乎沒見過成功案例。所以再次強(qiáng)調(diào)反向操作遠(yuǎn)比正向操作難備份做好了數(shù)據(jù)庫事故就成功一半了。5.3 外鍵約束導(dǎo)致DELETE失敗刪除父表數(shù)據(jù)時(shí)如果子表里有引用外鍵約束會(huì)拒絕刪除報(bào)錯(cuò)信息大概是Cannot delete or update a parent row: a foreign key constraint fails。有兩種處理思路第一種先刪子表再刪父表。比如刪除用戶前先把他的訂單清掉再刪除用戶。這也是之前提到的“逐表DELETE事務(wù)”方案。第二種確認(rèn)業(yè)務(wù)允許后臨時(shí)禁用外鍵檢查。MySQL里可以SET FOREIGN_KEY_CHECKS 0; DELETE FROM users WHERE id 10086; SET FOREIGN_KEY_CHECKS 1;這個(gè)操作務(wù)必謹(jǐn)慎。臨時(shí)禁用外鍵后如果刪除順序和邏輯有問題可能留下孤兒數(shù)據(jù)子表還引用著已經(jīng)不存在的父表記錄。只推薦在明確知道后果、并做好備份的前提下使用。5.4 DELETE后的表空間沒有變小是沒刪干凈嗎這是一個(gè)迷惑性極強(qiáng)的問題。在MySQL InnoDB中執(zhí)行DELETE后表文件大小可能幾乎沒有變化。這不是沒刪干凈而是數(shù)據(jù)的物理空間沒有立刻歸還給操作系統(tǒng)。InnoDB刪除行時(shí)只是把這些行標(biāo)記為“已刪除”后續(xù)新插入的數(shù)據(jù)可能復(fù)用這些空間。如果想要真正把空間釋放給操作系統(tǒng)需要執(zhí)行OPTIMIZE TABLE 表名;或重建表比如ALTER TABLE ... ENGINEInnoDB。但這兩個(gè)操作在表非常大時(shí)都需要較長執(zhí)行時(shí)間并且會(huì)鎖表或消耗大量IO生產(chǎn)環(huán)境務(wù)必安排在維護(hù)窗口執(zhí)行。只有當(dāng)刪除比例非常大比如刪了60%以上且確認(rèn)表不會(huì)再快速增長時(shí)才值得做物理空間收縮。PostgreSQL中類似的概念是VACUUM FULLSQL Server里有收縮數(shù)據(jù)庫功能。核心邏輯都是一樣的DELETE刪的是邏輯數(shù)據(jù)物理空間的回收是另一回事。別因?yàn)長OT尺寸沒變就反復(fù)重跑DELETE那樣只會(huì)白白增加一次全表掃描的成本。5.5 批量刪除時(shí)日志暴漲如何處理DELETE產(chǎn)生的日志量通常比UPDATE大因?yàn)槊啃卸家涗浲暾膁elete操作信息以待恢復(fù)。批量DELETE尤其是一次刪百萬行binlog或事務(wù)日志可能暴漲幾GB甚至幾十GB在磁盤緊張的機(jī)器上可能直接把磁盤寫滿導(dǎo)致業(yè)務(wù)停擺。處理辦法有三板斧第一分批刪除控制單批大小日志產(chǎn)生速度自然回落。第二如果業(yè)務(wù)允許臨時(shí)調(diào)大日志緩沖或切換binlog格式比如從ROW格式調(diào)整但注意這會(huì)影響其他邏輯不建議輕易改動(dòng)。第三條是在磁盤規(guī)劃和監(jiān)控上做文章保證日志目錄所在磁盤有足夠余量。真實(shí)項(xiàng)目里最常用、最穩(wěn)妥的還是分批刪除每批影響行數(shù)5000~20000批間sleep幾秒既控制日志量也給主從同步留出追趕空間。配置文件再優(yōu)化也替代不了分批這個(gè)動(dòng)作。6. 再看一個(gè)完整案例清理過期訂單講完理論和排查用一個(gè)貼近真實(shí)業(yè)務(wù)的完整案例把流程串起來。需求背景某訂單表orders有約3000萬行其中120萬行是“已取消”且超過180天沒有變更的舊訂單需要清理騰出存儲(chǔ)空間并提高查詢性能。第一步備份mysqldump -uops -p commerce orders \ --wherestatuscancelled AND updated_at DATE_SUB(NOW(), INTERVAL 180 DAY) \ /data/backup/orders_cancelled_before_$(date %Y%m%d).sql第二步驗(yàn)證條件與數(shù)據(jù)量SELECT COUNT(*), MIN(id), MAX(id) FROM orders WHERE status cancelled AND updated_at DATE_SUB(NOW(), INTERVAL 180 DAY);確認(rèn)行數(shù)在百萬級(jí)別后檢查執(zhí)行計(jì)劃。如果type為ALL先建立聯(lián)合索引ALTER TABLE orders ADD INDEX idx_status_updated (status, updated_at);第三步分批刪除。這段腳本可以用存儲(chǔ)過程實(shí)現(xiàn)循環(huán)刪除DELIMITER $$ CREATE PROCEDURE batch_delete_old_orders() BEGIN DECLARE affected_rows INT DEFAULT 1; WHILE affected_rows 0 DO DELETE FROM orders WHERE status cancelled AND updated_at DATE_SUB(NOW(), INTERVAL 180 DAY) LIMIT 5000; SET affected_rows ROW_COUNT(); COMMIT; DO SLEEP(2); END WHILE; END$$ DELIMITER ;第四步驗(yàn)證結(jié)果。確認(rèn)DELETE影響行數(shù)接近備份時(shí)的COUNT再巡檢從庫延遲、磁盤空間、慢查詢等指標(biāo)。第五步根據(jù)實(shí)際業(yè)務(wù)需要決定是否優(yōu)化表空間。由于這次刪除只占全表的4%左右我沒有執(zhí)行OPTIMIZE TABLE因?yàn)榛靥盥什桓呖臻g釋放意義有限也避免了維護(hù)窗口的系統(tǒng)負(fù)載。這就是“不做多余操作”的取舍——技術(shù)與業(yè)務(wù)目標(biāo)匹配才是最好的方案。7. 我踩過的坑和最后想說的話寫DELETE相關(guān)的SQL技術(shù)難度真的不高真正的難度在于“敬畏數(shù)據(jù)”。我入行的第三年就闖過一次禍在測試庫上調(diào)試存儲(chǔ)過程一時(shí)疏忽把一條DELETE的WHERE條件寫漏了一層誤刪了將近半張配置表。當(dāng)時(shí)因?yàn)橛袀浞莼謴?fù)花了兩個(gè)小時(shí)算是僥幸沒有造成大影響。但那次之后我養(yǎng)成了幾個(gè)雷打不動(dòng)的習(xí)慣現(xiàn)在分享給你。第一DELETE必須寫WHERE除非你百分之百確定要清空整張表。第二DELETE之前必走SELECT驗(yàn)證這是流程的一部分不是效率低的表現(xiàn)。第三重要數(shù)據(jù)刪除必須放在事務(wù)里任何時(shí)候給自己留一個(gè)ROLLBACK的余地。第四大批量刪除一定要分批這不是性能優(yōu)化是生產(chǎn)安全。SQL里的DELETE是一把鋒利的手術(shù)刀用得好可以精準(zhǔn)切除病灶用不好就是傷及無辜。這篇內(nèi)容里的每個(gè)技巧和習(xí)慣都是實(shí)戰(zhàn)里用教訓(xùn)換來的。刪數(shù)據(jù)這事技術(shù)越熟練越容易大意反而是那些時(shí)刻保持警惕的人才能安安穩(wěn)穩(wěn)地寫完每一條DELETE。