據(jù)批量刪除：5種方案與實戰(zhàn)踩坑指南)
咱們搞MySQL的十有八九都撞上過這么個需求線上有個幾千萬行的大表要刪掉其中大部分?jǐn)?shù)據(jù)。可能是一批歷史日志、一批過期訂單或者按某個業(yè)務(wù)維度清理無效數(shù)據(jù)。我見過太多新手上來就是一條DELETE FROM big_table WHERE create_time 2023-01-01然后執(zhí)行進(jìn)去數(shù)據(jù)庫瞬間卡死主庫連接打滿從庫延遲一路飆升最后鬧到要重啟實例。這篇文章就專門聊這個——MySQL里批量刪除海量數(shù)據(jù)到底有哪些靠譜路子各種方案適合什么場景實際操作中又會踩到什么坑。先說清楚一個核心認(rèn)知刪除海量數(shù)據(jù)的難點(diǎn)從來不在“刪除”本身而在刪除動作引發(fā)的一連串連鎖反應(yīng)。你要刪1000萬行MySQL要逐行標(biāo)記刪除、記錄binlog、維護(hù)二級索引、寫入undo log供事務(wù)回滾和MVCC使用。這些操作都會落在磁盤和內(nèi)存上加上行鎖、間隙鎖互相爭搶最終表現(xiàn)為數(shù)據(jù)庫性能斷崖式下跌。理解了這一層你就能理解為什么所有人都說“大表不能直接DELETE”了。這篇文章覆蓋的分批刪除、按主鍵范圍分段刪除、分區(qū)表清空、建新表切換、pt-archiver工具歸檔這幾類主流方案都是我實際用過的。我會把每個方案的原理講明白給出可以直接抄的SQL和腳本再把我踩過的坑一并列出來。適合正在為清理大表發(fā)愁的DBA、后端開發(fā)以及所有被慢查詢?nèi)罩緡樀竭^的同學(xué)。1. 先搞明白為什么直接DELETE會“刪崩”數(shù)據(jù)庫1.1 你刪的不只是數(shù)據(jù)還有一堆“隱形成本”很多人以為DELETE就是把磁盤上那幾行數(shù)據(jù)劃掉但實際上在InnoDB存儲引擎里一次DELETE消耗的資源遠(yuǎn)超你想象。咱們一條條拆。第一行鎖和間隙鎖的覆蓋范圍。一條不帶精確條件的DELETE比如DELETE FROM orders WHERE status expired在RR隔離級別下InnoDB會掃描所有匹配到的記錄并對它們加鎖。如果這個條件沒走索引那就是全表掃描加鎖等于把整張表鎖了個遍。就算走了索引大量行同時加鎖鎖沖突的概率也急劇上升直接影響同一張表所有其他讀寫請求。第二undo log膨脹帶來的連鎖反應(yīng)。InnoDB的事務(wù)隔離靠MVCCMVCC靠undo log保存歷史版本。你刪除多少行就要往undo log里寫多少條反向操作記錄。1000萬行的DELETEundo log隨便就是好幾GB。這還不是最要命的更要命的是這些undo log在事務(wù)結(jié)束前不能清理它們對應(yīng)的舊版本數(shù)據(jù)還會被其他事務(wù)讀到于是在repeatable read隔離級別下長事務(wù)大刪除組合會讓undo表空間暴漲到撐爆磁盤。第三binlog的傳輸放大。MySQL主從同步依賴binlog默認(rèn)情況下DELETE產(chǎn)生的binlog是statement格式一條SQL在主庫刷一遍、在從庫也要刷一遍。但如果你開了row格式很多生產(chǎn)環(huán)境為了數(shù)據(jù)安全都會開那么1000萬行就會生成1000萬條binlog記錄事件主從同步的網(wǎng)絡(luò)開銷、從庫應(yīng)用日志的CPU開銷立刻拉滿。我見過一個案例主庫刪了500萬行用了8分鐘從庫追了2個小時沒追上。1.2 二級索引和碎片刪完之后表反而“變大”了InnoDB的二級索引和聚簇索引是分開存儲的。刪除數(shù)據(jù)時聚簇索引里的記錄被標(biāo)記刪除但二級索引里對應(yīng)的索引條目不一定能及時回收。大量刪除之后索引B樹的葉子節(jié)點(diǎn)出現(xiàn)大量空位索引掃描效率下降物理文件.ibd不會自動縮小。結(jié)果就是你辛辛苦苦刪了5000萬行SELECT COUNT(*)確實變少了但磁盤占用一點(diǎn)沒降查詢速度和刪除前幾乎沒區(qū)別。后面我講“重建表”方案時你會看到這才是徹底清理物理空間的唯一出路。1.3 看清楚場景再選方案既然直接刪不行那就得找替代方案。但替代方案不是一根筋走到黑得看你具體什么場景。我把實際工作中常見的場景歸成三類你可以對號入座場景特征典型例子推薦方案刪少量數(shù)據(jù)幾萬行以內(nèi)清理一批用戶標(biāo)記的錯誤數(shù)據(jù)直接DELETE加上合適索引刪大量數(shù)據(jù)但希望保留表結(jié)構(gòu)、保留在線服務(wù)清理過期日志、過期訂單但表還要繼續(xù)使用分批DELETE、按主鍵分段刪除、pt-archiver刪掉大部分?jǐn)?shù)據(jù)、或要徹底釋放磁盤表內(nèi)數(shù)據(jù)基本都沒用了只留最近一小部分新建表切換、分區(qū)表清空這里頭的關(guān)鍵區(qū)別在于你要“溫和地刪”還是“徹底地?fù)Q”。如果是前一種重點(diǎn)是控制每次刪除的量、控制鎖和主從延遲如果是后一種重點(diǎn)是讓MySQL在極短時間內(nèi)完成“無痕切換”靠的是DDL級別的原子操作和元數(shù)據(jù)替換。2. 最通用的方案分批循環(huán)DELETE把大事務(wù)拆成小事務(wù)2.1 核心思路與SQL寫法分批DELETE的本質(zhì)是把一個大事務(wù)拆成N個小事務(wù)。每刪幾千行就提交一次鎖持有時間短、undo log及時釋放、binlog分批傳輸對在線業(yè)務(wù)的影響被壓縮到最小。這個方案不挑表結(jié)構(gòu)、不挑數(shù)據(jù)分布適用面最廣我建議所有場景都先從這個方案起步。最基礎(chǔ)的寫法是靠著主鍵ID范圍來分批DELETE FROM big_table WHERE id BETWEEN 1 AND 10000;執(zhí)行完后繼續(xù)刪下一批:DELETE FROM big_table WHERE id BETWEEN 10001 AND 20000;如果你不想手動算范圍可以選擇每次取一批要刪的主鍵ID然后用主鍵IN去刪除DELETE FROM big_table WHERE id IN ( SELECT id FROM ( SELECT id FROM big_table WHERE create_time 2023-01-01 LIMIT 5000 ) t );注意這里為什么套了一層子查詢MySQL不允許直接在DELETE的WHERE里SELECT同一張表的子查詢錯誤碼1093包一層派生表就能繞過這個限制。外層每次觸發(fā)會掃描到滿足條件的5000個ID然后刪除。這種方式不依賴ID連續(xù)比BETWEEN方式更通用。但它的缺點(diǎn)是子查詢本身也要掃描如果篩選條件沒走索引性能同樣會很差。2.2 批次大小怎么定別拍腦袋看三個指標(biāo)分批大小的選擇直接決定方案的成敗。批次太大會退化成大事務(wù)太小則刪除效率低下循環(huán)幾萬次能把人急死。我從實際操作經(jīng)驗里總結(jié)了三個參考指標(biāo)。一是看主庫的鎖等待和活躍會話數(shù)。單批次DELETE執(zhí)行期間SHOW ENGINE INNODB STATUS里面的History list length不能持續(xù)暴漲活躍會話也不能長時間趴滿。如果單批次5000行執(zhí)行時間超過2秒就要減小批次。二是看從庫的延遲情況。設(shè)置定時任務(wù)循環(huán)刪除的場景每次循環(huán)之間SLEEP幾秒鐘。用SHOW SLAVE STATUS觀察Seconds_Behind_Master如果延遲在增長說明刪除速度超過從庫應(yīng)用binlog的速度必須加大間隔或減小批次。三是看undo表空間增長速度。一次性刪太多可用SHOW GLOBAL STATUS LIKE Innodb_history_list_length觀察這個值代表未清理的undo日志量如果它持續(xù)高位不下降說明系統(tǒng)里有長事務(wù)或者刪除太快來不清理這種時候加大SLEEP時間。以一個線上案例說某張2億行的用戶行為日志表按天清理60天前的數(shù)據(jù)每次刪8000行刪除耗時約1.2秒循環(huán)之間SLEEP 20秒。整體算下來每秒約刪除400行左右主庫負(fù)載穩(wěn)定在20%以內(nèi)從庫延遲控制在5秒內(nèi)。如果按每次50000行去刪單次執(zhí)行時間就飆到8秒從庫延遲直接漲到兩分鐘以上這就不行了。2.3 循環(huán)腳本的完整寫法生產(chǎn)環(huán)境里我更推薦用存儲過程來做一來避免應(yīng)用層頻繁發(fā)起連接二來可以在數(shù)據(jù)庫端精確控制循環(huán)邏輯。下面這個存儲過程是我經(jīng)常用的模板你按自己的表結(jié)構(gòu)調(diào)整下就能用DELIMITER $$ CREATE PROCEDURE batch_delete_big_data(IN p_batch_size INT, IN p_sleep_seconds INT) BEGIN DECLARE v_rows INT DEFAULT 1; WHILE v_rows 0 DO DELETE FROM big_table WHERE create_time DATE_SUB(NOW(), INTERVAL 90 DAY) ORDER BY id LIMIT p_batch_size; SET v_rows ROW_COUNT(); COMMIT; IF v_rows 0 THEN DO SLEEP(p_sleep_seconds); END IF; END WHILE; END$$ DELIMITER ;這個存儲過程有兩個核心設(shè)計值得說說。第一用了ORDER BY id LIMIT ?的形式保證每次刪除的都是最老的一批數(shù)據(jù)而且走主鍵有序掃描效率高。第二每刪完一批就COMMIT一次讓事務(wù)及時結(jié)束undo log和行鎖都在每個批次結(jié)束后立即釋放。還有一個細(xì)節(jié)很多人在循環(huán)里糾結(jié)要不要commitMySQL默認(rèn)是自動提交的但存儲過程的事務(wù)控制最好還是顯式寫清楚防止將來修改隔離級別時行為變化。調(diào)用方式很簡單CALL batch_delete_big_data(5000, 5);這里我解釋一下參數(shù)怎么配p_batch_size從5000起步往上調(diào)p_sleep_seconds從5起步往上加。每次加參數(shù)跑一小段觀察主庫負(fù)載和從庫延遲找到當(dāng)前機(jī)器配置下的最優(yōu)組合。內(nèi)存大、磁盤快SSD、從庫延遲容錯高的環(huán)境可以把批次加大到10000。我自己的經(jīng)驗是寧肯稍保守一點(diǎn)別追求一次刪太多導(dǎo)致業(yè)務(wù)抖動。2.4 分批DELETE的兩個主要局限這個方案雖然通用但有兩個短板你得知道。第一它只解決了“刪除動作”對數(shù)據(jù)庫的壓力沒解決“物理文件不釋放”的問題。分批刪完數(shù)據(jù)后表空間依然是滿的.ibd文件大小不會縮。如果你清理數(shù)據(jù)的目的是為了騰出磁盤空間那分批DELETE并不能直接滿足你需要配合后面說的重建表。第二如果業(yè)務(wù)高峰期刪數(shù)據(jù)即使每批只有幾千行依然可能影響性能。這個方案更適合業(yè)務(wù)低峰期執(zhí)行比如凌晨1點(diǎn)到5點(diǎn)盡量不要在白天大流量時段跑。3. 主鍵有序場景下的高效方案按主鍵范圍分段刪除3.1 原理順序掃描遠(yuǎn)比隨機(jī)掃描劃算如果說分批DELETE是“小步快跑”那按主鍵范圍分段刪除就是“化整為零分區(qū)塊推平”。它的思路是先把要刪除的數(shù)據(jù)主鍵范圍劃分成若干連續(xù)的區(qū)間然后逐個區(qū)間刪除。比如你要刪掉id從1到1億之間的部分?jǐn)?shù)據(jù)就可以分成1000個區(qū)間每個區(qū)間10萬行按順序依次刪。為什么這樣更快因為InnoDB聚簇索引本身是按主鍵組織的B樹以主鍵范圍刪除時掃描和刪除都是順序的充分利用了磁盤預(yù)讀和內(nèi)存緩存。相反如果按create_time等條件去隨機(jī)找數(shù)據(jù)每一次定位都要走二級索引再回表頻繁的隨機(jī)IO會大大拉低速度。這個方案比較適合那些主鍵和業(yè)務(wù)刪除條件高度相關(guān)的場景。比如說訂單表主鍵就是訂單自增ID而你要刪的都是半年前的訂單那這些訂單的ID一定集中在一個相對靠前的區(qū)間按ID范圍分段就非常合適。如果主鍵是UUID或者完全無序的字符串這個方案優(yōu)勢就不明顯了。3.2 怎么找分段邊界MAX/MIN查詢代替COUNT(*)很多人在分段前習(xí)慣先執(zhí)行一次SELECT COUNT(*) FROM big_table WHERE create_time xxx想確認(rèn)總共有多少數(shù)據(jù)要刪。我勸你不要這么做。大表上COUNT(*)是全表掃描或大范圍索引掃描光這一下就能把數(shù)據(jù)庫拖垮。正確做法是直接用MIN(id)和MAX(id)定位邊界然后按固定步長推進(jìn)SELECT MIN(id), MAX(id) FROM big_table WHERE create_time 2023-01-01;比如查出來要刪的數(shù)據(jù)id分布在 1000000 到 58000000 之間那你就可以從1000000開始每100000為一個區(qū)間依次刪除DELETE FROM big_table WHERE id 1000000 AND id 1100000; DELETE FROM big_table WHERE id 1100000 AND id 1200000;如果你希望全流程自動化也可以寫一個存儲過程按主鍵游標(biāo)推進(jìn)。但要注意分段刪除的每個區(qū)間刪除量并不均勻——有些區(qū)間可能恰好有大量要刪的行有些區(qū)間則很少。所以單個區(qū)間執(zhí)行時間可能有波動批次間SLEEP仍然不能省。3.3 一個重要優(yōu)化在刪除期間暫停二級索引更新這個技巧我在實踐中覺得非常好用但知道的人不多。如果你那張表有多個二級索引每次DELETE都要同時維護(hù)這些索引。索引多了刪除一行要更新的索引條目也多代價翻好幾倍。有一種做法是先拿到要刪除數(shù)據(jù)的id集合存到一張臨時表然后刪除原表上的二級索引刪完數(shù)據(jù)再重建索引。這里有一個典型Trade-off刪除索引期間查原表會變慢因為少了索引但刪除數(shù)據(jù)的整體速度會顯著提升。如果你清理的是“要刪除大比例歷史數(shù)據(jù)、但近期數(shù)據(jù)仍然要被業(yè)務(wù)頻繁查詢”的表二級索引的維護(hù)成本占了刪除總開銷的大頭這個優(yōu)化效果會非常明顯。但它也有風(fēng)險刪除索引和重建索引都是大操作重建索引期間表上所有依賴這個索引的查詢都會受影響。因此這個技巧我建議只在“刪除后要重建索引”的前提下使用并且放在低峰期配合后面的“刪除后重建表”一起做。3.4 分段刪除的邊界與最佳實踐這個方案最大的坑是刪除的區(qū)間里如果混著“不該刪”的數(shù)據(jù)你有多大概率誤刪所以分段必須建立在“主鍵范圍和業(yè)務(wù)條件強(qiáng)相關(guān)”的基礎(chǔ)上。如果兩者的相關(guān)性弱你得在DELETE的WHERE里同時加上業(yè)務(wù)條件比如DELETE FROM orders WHERE id BETWEEN 1000000 AND 1100000 AND status expired;這樣寫會稍微降低刪除效率因為多了條件判斷但安全性高很多。我自己一般會在條件里保守一點(diǎn)寧可多掃一點(diǎn)數(shù)據(jù)也不冒誤刪的險。原因很簡單海量刪除一旦誤刪恢復(fù)成本比多跑幾分鐘高得多。4. 表結(jié)構(gòu)允許時的最優(yōu)解用分區(qū)表刪數(shù)據(jù)就是刪文件4.1 為什么分區(qū)表能“秒刪千萬行”如果你的表本身做了分區(qū)那海量刪除的問題幾乎被降維化解了。MySQL 8.0支持的分區(qū)類型主要是RANGE、LIST、HASH、KEY對時間維度的歷史數(shù)據(jù)清理來說最常用的是RANGE分區(qū)。比如訂單表按月份做RANGE分區(qū)每個分區(qū)存一個月的數(shù)據(jù)。要刪除某個月之前的數(shù)據(jù)直接ALTER TABLE orders DROP PARTITION p202301;注意這個操作是DDL不是DML。InnoDB在DROP PARTITION時做的本質(zhì)是刪除分區(qū)對應(yīng)的物理文件整個操作不產(chǎn)生逐行刪除的行為所以速度極快——一個1億行的分區(qū)DROP它可能也就幾秒鐘。這是所有DELETE方案都比不了的。如果你的業(yè)務(wù)周期劃分不是按月也可以按季度、按年。只要你在分區(qū)創(chuàng)建時預(yù)留好未來分區(qū)的余量也就是提前建好后面幾個月的分區(qū)清理數(shù)據(jù)的時候直接drop掉過期分區(qū)根本不用寫任何DELETE語句。4.2 分區(qū)表改造的步驟與注意事項如果你現(xiàn)在這個表還沒分區(qū)想改成分區(qū)表那就要謹(jǐn)慎了。對已有大數(shù)據(jù)量的表執(zhí)行分區(qū)操作有幾種方式最穩(wěn)妥的是“新建分區(qū)表導(dǎo)入數(shù)據(jù)切換”的方式-- 1. 創(chuàng)建帶分區(qū)的目標(biāo)表 CREATE TABLE orders_partitioned ( id BIGINT NOT NULL, order_no VARCHAR(64), create_time DATETIME, ... PRIMARY KEY (id, create_time) ) ENGINEInnoDB PARTITION BY RANGE (YEAR(create_time) * 100 MONTH(create_time)) ( PARTITION p202301 VALUES LESS THAN (202302), PARTITION p202302 VALUES LESS THAN (202303), PARTITION p202303 VALUES LESS THAN (202304), PARTITION p202304 VALUES LESS THAN (202305), PARTITION pMax VALUES LESS THAN MAXVALUE );注意分區(qū)表要求分區(qū)字段必須包含在主鍵和唯一索引中。這是MySQL的一條硬性規(guī)定“every unique key on the partitioned table must include every column in the partitioning expression”。比如上面我把id和create_time放一起做了聯(lián)合主鍵才滿足要求。這個規(guī)定非?？尤撕芏啾斫Y(jié)構(gòu)改分區(qū)失敗都是栽在這里。如果你的業(yè)務(wù)主鍵是單列id又想按create_time分區(qū)那要么改主鍵為聯(lián)合主鍵對現(xiàn)有業(yè)務(wù)有影響要么放棄分區(qū)方案。-- 2. 將歷史數(shù)據(jù)按分區(qū)規(guī)則插入 INSERT INTO orders_partitioned SELECT * FROM orders WHERE create_time 2023-04-01; -- 3. 業(yè)務(wù)停寫或低峰期切換表名 RENAME TABLE orders TO orders_old, orders_partitioned TO orders;4.3 沒有提前規(guī)劃那就用在線DDL工具做分區(qū)如果你的表已經(jīng)幾千萬行了尺寸巨大直接在線上執(zhí)行ALTER語句分區(qū)的耗時和風(fēng)險都讓人望而卻步。可以考慮使用gh-ost或者pt-online-schema-change這類在線DDL工具來執(zhí)行分區(qū)操作。它們通過觸發(fā)器或者binlog復(fù)制的方式在目標(biāo)表上重建新結(jié)構(gòu)切換過程幾乎不影響讀寫。但因為涉及復(fù)雜的表結(jié)構(gòu)轉(zhuǎn)換我只建議有一定經(jīng)驗的同學(xué)操作并且一定要在測試環(huán)境完整演練一遍再上生產(chǎn)。4.4 分區(qū)的維護(hù)成本別把“分區(qū)”當(dāng)萬能藥講了分區(qū)這么多好處我也要潑一盆冷水。分區(qū)表不是沒有代價的。最明顯的一點(diǎn)是某些查詢沒帶分區(qū)鍵時性能可能不升反降。比如你設(shè)置了按create_time分區(qū)但業(yè)務(wù)查詢經(jīng)常只按order_no查那MySQL會掃所有分區(qū)等于退化成了全表掃描。所以是否上分區(qū)要先梳理清楚核心查詢的WHERE條件。另外分區(qū)本身也是DDL操作每次拆分、合并、刪除分區(qū)都要小心全局元數(shù)據(jù)鎖MDL的影響。生產(chǎn)環(huán)境的表要操作分區(qū)依然建議低峰期做最好配合在線DDL工具一起用。5. 騰出磁盤空間的一勞永逸方案新建表 切換 重建5.1 核心思路數(shù)據(jù)“搬家”而不是逐行“刪除”前面說過普通DELETE不管你怎么刪物理文件都不會變小。如果你清理海量數(shù)據(jù)的最終目的是釋放磁盤空間或者讓表恢復(fù)到干凈緊湊的狀態(tài)那正確思路是“把要保留的數(shù)據(jù)復(fù)制到一張新表然后用新表替換舊表”。保留的數(shù)據(jù)越少新表就越小舊表直接DROP掉即可。這個方案的執(zhí)行流程是根據(jù)業(yè)務(wù)條件把要保留的數(shù)據(jù)導(dǎo)到新表。業(yè)務(wù)停寫或低峰期做一次數(shù)據(jù)補(bǔ)齊和表名切換。DROP掉舊表磁盤空間隨之釋放。5.2 完整操作步驟以保留近3個月數(shù)據(jù)為例第一步創(chuàng)建新表。最簡單的方式是復(fù)制舊表結(jié)構(gòu)CREATE TABLE orders_new LIKE orders;這個語法會復(fù)制表結(jié)構(gòu)、索引、自增屬性但不會復(fù)制數(shù)據(jù)非常適合用來做換表。第二步把需要保留的數(shù)據(jù)搬進(jìn)新表。INSERT INTO orders_new SELECT * FROM orders WHERE create_time DATE_SUB(NOW(), INTERVAL 3 MONTH);這里有一點(diǎn)要注意如果保留數(shù)據(jù)量也很大比如上億行INSERT不進(jìn)去那么快而且會產(chǎn)生大量binlog。可以考慮在業(yè)務(wù)低峰期執(zhí)行或者用INSERT INTO ... SELECT ... WHERE ...加上分批處理邏輯和前文的分批刪除邏輯相通。無論如何絕對不要在業(yè)務(wù)高峰期執(zhí)行這個INSERT SELECT它會對源表加鎖可能導(dǎo)致線上寫入被阻塞。第三步切換表名。這里最安全的是用兩個原子性RENAMERENAME TABLE orders TO orders_old, orders_new TO orders;MySQL的RENAME TABLE是原子操作也就是說這一步要么全部成功要么全部失敗中間不會有“表不存在”的間隙。這也是為什么推薦用兩條RENAME而不是先DROP舊表再RENAME新表。如果先DROP再RENAME中間那幾秒你線上所有訪問orders的語句都會報“table doesnt exist”。切換完成后應(yīng)用立刻開始訪問新表。新表里只有近3個月數(shù)據(jù)所有查詢都會比原來快不少。第四步驗證后DROP舊表。-- 驗證新表數(shù)據(jù)量、索引、自增值都正常 SELECT COUNT(*) FROM orders; SHOW INDEX FROM orders; -- 確認(rèn)無誤后刪除舊表釋放磁盤空間 DROP TABLE orders_old;DROP表的瞬間會釋放磁盤文件這個過程中I/O會有些波動但對業(yè)務(wù)基本無感。需要注意的是DROP一張幾十GB的大表本身也可能產(chǎn)生瞬時I/O壓力。如果表特別大比如超過100GB可以在低峰期執(zhí)行或者考慮用硬鏈接方式分批釋放文件這里不展開知道有這回事就行。5.3 這個方案有一個必須處理的臟數(shù)據(jù)問題新建表切換方案最大的隱患在于數(shù)據(jù)一致性。從“把數(shù)據(jù)復(fù)制到新表”到“切換表名”之間舊表可能又有新的寫入或修改如果你沒有完全停寫的話。這些增量數(shù)據(jù)不會自動出現(xiàn)在新表里。要規(guī)避這個問題要么在完全停寫窗口內(nèi)完成第二步和第三步適合可以接受短時間停服的場景要么利用binlog或應(yīng)用層雙寫來補(bǔ)齊增量。對于大部分互聯(lián)網(wǎng)業(yè)務(wù)來說最現(xiàn)實的是選一個業(yè)務(wù)量最低的時間窗口比如凌晨2點(diǎn)申請5到10分鐘的只讀維護(hù)窗口一口氣完成復(fù)制和切換。這個方案在架構(gòu)上是最干凈的也是我推薦你在數(shù)據(jù)量特別大、清理比例又特別高時優(yōu)先考慮的路徑。5.4 什么時候“換表”優(yōu)于“刪除”這里給個簡單的經(jīng)驗總結(jié)。假設(shè)表總行數(shù)X要保留行數(shù)Y。如果Y/X的比例低于30%換表方案的綜合效率顯著高于DELETE方案。因為這種情況下你要拷貝的數(shù)據(jù)少新表很小切換后所有查詢受益磁盤也徹底釋放。如果Y/X超過50%新建表拷貝的數(shù)據(jù)量太大反而不如分批DELETE碎片整理劃算。記住這個比例分界線你做技術(shù)選型時就不用再糾結(jié)了。6. 生產(chǎn)環(huán)境更優(yōu)雅的歸檔工具聊聊pt-archiver6.1 pt-archiver到底解決了什么問題如果你不想自己寫存儲過程、又要批量刪除歸檔數(shù)據(jù)Percona Toolkit里的pt-archiver就是為這個場景量身定做的。它是業(yè)界最常用的MySQL歸檔和清理工具核心優(yōu)勢有三點(diǎn)一是自動分批每次操作一行或一批控制鎖粒度二是可以同時把刪除的數(shù)據(jù)插入到歸檔表三是不要求你手寫循環(huán)邏輯參數(shù)化配置就行。它適合的場景包括定時清理歸檔歷史數(shù)據(jù)、大表冷熱數(shù)據(jù)分離、刪除時保留一份備份到歸檔庫比如歸檔到另一張表或者另一個實例。我自己在生產(chǎn)上用過它清理過一張幾千萬行的流水表工具平滑度非常高沒有出現(xiàn)鎖等待或從庫延遲異常。6.2 一條命令教會你用pt-archiver基本用法如下需求是把orders表里2023年之前的數(shù)據(jù)刪除同時把刪除的數(shù)據(jù)備份到orders_archive表pt-archiver \ --source h127.0.0.1,P3306,uarchive_user,pxxx,Dtest,torders \ --dest h127.0.0.1,P3306,uarchive_user,pxxx,Dtest,torders_archive \ --where create_time 2023-01-01 \ --limit 1000 \ --txn-size 1000 \ --sleep 0.5 \ --bulk-delete \ --bulk-insert \ --statistics參數(shù)含義拆解一下--source和--dest源表和目標(biāo)表。如果只做刪除不需要?dú)w檔可以省略--dest。--where刪除條件這是核心篩選條件注意這里必須走索引否則工具會提示全表掃描風(fēng)險。--limit 1000每批取多少行。--txn-size 1000多少行提交一次事務(wù)。這兩個參數(shù)配合控制鎖粒度。--sleep 0.5每批之間的休息時間單位秒用來控制刪除速率這是保護(hù)從庫不延遲的重要參數(shù)。--bulk-delete和--bulk-insert使用批量刪除、批量插入的方式比逐行操作高效得多。--statistics輸出執(zhí)行統(tǒng)計方便事后分析。在實際執(zhí)行前官方工具支持--dry-run參數(shù)只做語法檢查而不真正執(zhí)行可以拿它先驗證命令是否正確pt-archiver --source ... --where ... --dry-run6.3 使用pt-archiver的3條實踐經(jīng)驗第一連接賬號權(quán)限最小化就好只需要源表SELECT、DELETE目標(biāo)表INSERT的權(quán)限別一上來就給root。這不僅是安全習(xí)慣也能防止操作失誤時影響面擴(kuò)大。第二如果目標(biāo)歸檔表和源表結(jié)構(gòu)完全一樣--dest指定的表可以提前建好否則工具會自動嘗試創(chuàng)建但字段映射可能出錯。我建議手動先建好歸檔表不要依賴工具自動建表。第三必須確認(rèn)WHERE條件能走索引。pt-archiver執(zhí)行時會做 explain 檢查如果發(fā)現(xiàn)全表掃描它有--no-check-charset之類的參數(shù)跳過一些檢查但全表掃描刪除依然會拖垮庫。執(zhí)行前用EXPLAIN看一眼執(zhí)行計劃最穩(wěn)妥。7. 刪除之后的善后工作表空間整理與索引維護(hù)7.1 為什么刪完數(shù)據(jù)表文件還是那么大回到前面提到的那個歷史遺留問題DELETE只做邏輯刪除標(biāo)記記錄為“已刪除”物理空間不會立刻返還給操作系統(tǒng)。InnoDB表空間內(nèi)部雖然可以復(fù)用這些空間給后續(xù)INSERT但是文件大小不縮。因此如果你執(zhí)行完大批量DELETE之后用ls -lh看.ibd文件會發(fā)現(xiàn)文件大小一點(diǎn)沒變。如果你的目標(biāo)是壓縮物理文件目前通用的做法是重建表。在MySQL 5.7之后的版本里執(zhí)行ALTER TABLE big_table ENGINEInnoDB;可以重建主表數(shù)據(jù)。這個操作在MySQL 5.6以后是Online DDL允許在重建期間繼續(xù)讀寫但它仍然會消耗額外的磁盤空間因為要生成臨時表文件耗時也比較長依舊建議在低峰期操作。MySQL 8.0還有一種新姿勢用ALTER TABLE ... ALGORITHMINPLACE配合在線操作但也要看版本和表結(jié)構(gòu)具體情況。最穩(wěn)妥的仍是按前面第5節(jié)說的“建新表切換”方案徹底搬家一次物理和邏輯上都干干凈凈。7.2 清理碎片和重建二級索引另外一個容易被忽略的問題是索引碎片。大量刪除后二級索引葉子節(jié)點(diǎn)會有很多空位索引空間利用率下降查詢性能受影響。除了重建表你還可以對某個二級索引單獨(dú)做整理ALTER TABLE big_table DROP INDEX idx_create_time, ADD INDEX idx_create_time (create_time);這個操作的本質(zhì)是刪索引再重新建索引索引重建期間相關(guān)查詢會變慢。如果有多個索引需要重建建議一個一個來別批量操作避免表上長時間沒有可用索引。7.3 統(tǒng)計信息更新ANALYZE TABLE不能省大量刪除之后MySQL的優(yōu)化器基于采樣得到的統(tǒng)計信息可能已經(jīng)嚴(yán)重過時于是執(zhí)行計劃就可能出錯本該走索引的查詢走了全表掃描本該用小索引的查詢選了個大索引。高負(fù)載下你還會看到information_schema.tables里的數(shù)據(jù)行數(shù)完全對不上那里面的值是估算的不是實時的。這時候就需要主動更新統(tǒng)計信息ANALYZE TABLE big_table;這個操作在MySQL 8.0里執(zhí)行很快因為它只是重新采樣計算統(tǒng)計信息不需要重建數(shù)據(jù)。在批量刪除后的當(dāng)天夜里或第二天的低峰期執(zhí)行一次能有效避免優(yōu)化器誤判。8. 常見問題排查與踩坑實錄8.1 問題一刪著刪著從庫延遲越來越大這應(yīng)該是最常遇到的現(xiàn)象。分批DELETE本身沒問題但如果你批次太大、SLEEP太短從庫應(yīng)用binlog的速度就跟不上主庫產(chǎn)生binlog的速度。排查流程先執(zhí)行SHOW SLAVE STATUS\G看Seconds_Behind_Master確認(rèn)延遲數(shù)值和趨勢再看主庫上是不是有長事務(wù)在跑information_schema.innodb_trx里如果有TIME很大的事務(wù)優(yōu)先確認(rèn)是不是自己的刪除腳本沒提交。最終解法就是把批次調(diào)小、SLEEP調(diào)大或者在腳本里增加“延遲超過閾值自動暫?！钡倪壿嬤@個邏輯不算復(fù)雜但非常實用。8.2 問題二DELETE走了索引還是很慢很多情況下你確實給WHERE條件加了索引但執(zhí)行計劃依然選擇了全表掃描。原因多半是數(shù)據(jù)分布讓優(yōu)化器認(rèn)為走索引不如全表掃描劃算。比如要刪除的數(shù)據(jù)占了表中大部分行優(yōu)化器估算下來全表掃更快。排查方法是執(zhí)行EXPLAIN DELETE ...看type列和key列如果發(fā)現(xiàn)是ALL或者key為NULL那么要么用FORCE INDEX強(qiáng)制走索引要么換成“按主鍵分段刪除”的寫法繞開優(yōu)化器的錯誤判斷。8.3 問題三刪除過程中出現(xiàn)鎖等待超時錯誤信息一般是Lock wait timeout exceeded; try restarting transaction。這說明你的DELETE在等一把別人持有的鎖。排查方式用SHOW ENGINE INNODB STATUS查看當(dāng)前鎖等待也可以直接查sys.innodb_lock_waits視圖看誰堵了誰。通常的原因有兩個一是業(yè)務(wù)本身正在高頻更新你要刪除的數(shù)據(jù)范圍這種場景要錯峰刪除二是你的刪除批次太大單批持有大量行鎖導(dǎo)致后續(xù)刪除互相等待。解法減小批次、增加SLEEP、盡量在低峰期執(zhí)行。8.4 問題四刪除期間undo表空間暴漲刪除動作寫undo是必然的但暴漲到撐爆磁盤就有問題了。先看SHOW GLOBAL STATUS LIKE innodb_history_list_length如果這個值一直增加不降說明有老事務(wù)一直沒結(jié)束導(dǎo)致undo日志無法purge。排查information_schema.innodb_trx里是否有長時間未提交的事務(wù)尤其注意是不是有APP服務(wù)開啟了事務(wù)但沒提交。一個連接空閑但沒commit就能讓你所有的刪除產(chǎn)生的undo都清不掉。遇到這種情況先確認(rèn)沒有長事務(wù)再說刪除的事否則你刪得越快undo漲得越兇。8.5 問題五DROP舊表時磁盤I/O瞬間打滿DROP大表時操作系統(tǒng)需要釋放對應(yīng)的大文件。InnoDB刪除表空間文件的過程不是瞬間完成的但對磁盤I/O的沖擊是真實存在的。如果舊表有100GB以上建議在業(yè)務(wù)最低峰DROP或者考慮分硬鏈接釋放文件的技巧先把.ibd文件硬鏈接到另一個路徑然后通過TRUNCATE一點(diǎn)點(diǎn)截斷文件來釋放空間讓I/O壓力平滑化。這個技巧屬于進(jìn)階操作普通開發(fā)環(huán)境不需要用但生產(chǎn)上遇到“不敢DROP大表”的情況可以嘗試搜索一下官方文檔和相關(guān)文章來深入。寫在最后的經(jīng)驗聊到這兒批量刪除海量數(shù)據(jù)的幾條主流路線基本都過了一遍。我個人的體會是沒有萬能的方案只有適合當(dāng)下場景的方案。如果只是日常刪幾十萬行把分批DELETE寫好、參數(shù)調(diào)對就夠了如果刪的數(shù)據(jù)占比特別高優(yōu)先考慮新建表切換如果表結(jié)構(gòu)設(shè)計時就想清楚了數(shù)據(jù)生命周期那么分區(qū)表是最省心的長期方案如果公司有Percona Toolkit的運(yùn)維基礎(chǔ)pt-archiver能讓你的活變得非常輕松。另外還有一點(diǎn)小建議不管用哪種方案動手前先把表結(jié)構(gòu)和數(shù)據(jù)分布摸清楚備份必須做好。海量刪除不是不能做而是要在可控的節(jié)奏里做。你每一次刪除前多花十分鐘做EXPLAIN、做備份、確認(rèn)時間窗口線上就能少一次血淚教訓(xùn)。希望這篇文章能幫你把那塊壓在心口的大石頭平穩(wěn)落地。 MySQL海量數(shù)據(jù)批量刪除：5種方案與實戰(zhàn)踩坑指南
咱們搞MySQL的十有八九都撞上過這么個需求線上有個幾千萬行的大表要刪掉其中大部分?jǐn)?shù)據(jù)。可能是一批歷史日志、一批過期訂單或者按某個業(yè)務(wù)維度清理無效數(shù)據(jù)。我見過太多新手上來就是一條DELETE FROM big_table WHERE create_time 2023-01-01然后執(zhí)行進(jìn)去數(shù)據(jù)庫瞬間卡死主庫連接打滿從庫延遲一路飆升最后鬧到要重啟實例。這篇文章就專門聊這個——MySQL里批量刪除海量數(shù)據(jù)到底有哪些靠譜路子各種方案適合什么場景實際操作中又會踩到什么坑。先說清楚一個核心認(rèn)知刪除海量數(shù)據(jù)的難點(diǎn)從來不在“刪除”本身而在刪除動作引發(fā)的一連串連鎖反應(yīng)。你要刪1000萬行MySQL要逐行標(biāo)記刪除、記錄binlog、維護(hù)二級索引、寫入undo log供事務(wù)回滾和MVCC使用。這些操作都會落在磁盤和內(nèi)存上加上行鎖、間隙鎖互相爭搶最終表現(xiàn)為數(shù)據(jù)庫性能斷崖式下跌。理解了這一層你就能理解為什么所有人都說“大表不能直接DELETE”了。這篇文章覆蓋的分批刪除、按主鍵范圍分段刪除、分區(qū)表清空、建新表切換、pt-archiver工具歸檔這幾類主流方案都是我實際用過的。我會把每個方案的原理講明白給出可以直接抄的SQL和腳本再把我踩過的坑一并列出來。適合正在為清理大表發(fā)愁的DBA、后端開發(fā)以及所有被慢查詢?nèi)罩緡樀竭^的同學(xué)。1. 先搞明白為什么直接DELETE會“刪崩”數(shù)據(jù)庫1.1 你刪的不只是數(shù)據(jù)還有一堆“隱形成本”很多人以為DELETE就是把磁盤上那幾行數(shù)據(jù)劃掉但實際上在InnoDB存儲引擎里一次DELETE消耗的資源遠(yuǎn)超你想象。咱們一條條拆。第一行鎖和間隙鎖的覆蓋范圍。一條不帶精確條件的DELETE比如DELETE FROM orders WHERE status expired在RR隔離級別下InnoDB會掃描所有匹配到的記錄并對它們加鎖。如果這個條件沒走索引那就是全表掃描加鎖等于把整張表鎖了個遍。就算走了索引大量行同時加鎖鎖沖突的概率也急劇上升直接影響同一張表所有其他讀寫請求。第二undo log膨脹帶來的連鎖反應(yīng)。InnoDB的事務(wù)隔離靠MVCCMVCC靠undo log保存歷史版本。你刪除多少行就要往undo log里寫多少條反向操作記錄。1000萬行的DELETEundo log隨便就是好幾GB。這還不是最要命的更要命的是這些undo log在事務(wù)結(jié)束前不能清理它們對應(yīng)的舊版本數(shù)據(jù)還會被其他事務(wù)讀到于是在repeatable read隔離級別下長事務(wù)大刪除組合會讓undo表空間暴漲到撐爆磁盤。第三binlog的傳輸放大。MySQL主從同步依賴binlog默認(rèn)情況下DELETE產(chǎn)生的binlog是statement格式一條SQL在主庫刷一遍、在從庫也要刷一遍。但如果你開了row格式很多生產(chǎn)環(huán)境為了數(shù)據(jù)安全都會開那么1000萬行就會生成1000萬條binlog記錄事件主從同步的網(wǎng)絡(luò)開銷、從庫應(yīng)用日志的CPU開銷立刻拉滿。我見過一個案例主庫刪了500萬行用了8分鐘從庫追了2個小時沒追上。1.2 二級索引和碎片刪完之后表反而“變大”了InnoDB的二級索引和聚簇索引是分開存儲的。刪除數(shù)據(jù)時聚簇索引里的記錄被標(biāo)記刪除但二級索引里對應(yīng)的索引條目不一定能及時回收。大量刪除之后索引B樹的葉子節(jié)點(diǎn)出現(xiàn)大量空位索引掃描效率下降物理文件.ibd不會自動縮小。結(jié)果就是你辛辛苦苦刪了5000萬行SELECT COUNT(*)確實變少了但磁盤占用一點(diǎn)沒降查詢速度和刪除前幾乎沒區(qū)別。后面我講“重建表”方案時你會看到這才是徹底清理物理空間的唯一出路。1.3 看清楚場景再選方案既然直接刪不行那就得找替代方案。但替代方案不是一根筋走到黑得看你具體什么場景。我把實際工作中常見的場景歸成三類你可以對號入座場景特征典型例子推薦方案刪少量數(shù)據(jù)幾萬行以內(nèi)清理一批用戶標(biāo)記的錯誤數(shù)據(jù)直接DELETE加上合適索引刪大量數(shù)據(jù)但希望保留表結(jié)構(gòu)、保留在線服務(wù)清理過期日志、過期訂單但表還要繼續(xù)使用分批DELETE、按主鍵分段刪除、pt-archiver刪掉大部分?jǐn)?shù)據(jù)、或要徹底釋放磁盤表內(nèi)數(shù)據(jù)基本都沒用了只留最近一小部分新建表切換、分區(qū)表清空這里頭的關(guān)鍵區(qū)別在于你要“溫和地刪”還是“徹底地?fù)Q”。如果是前一種重點(diǎn)是控制每次刪除的量、控制鎖和主從延遲如果是后一種重點(diǎn)是讓MySQL在極短時間內(nèi)完成“無痕切換”靠的是DDL級別的原子操作和元數(shù)據(jù)替換。2. 最通用的方案分批循環(huán)DELETE把大事務(wù)拆成小事務(wù)2.1 核心思路與SQL寫法分批DELETE的本質(zhì)是把一個大事務(wù)拆成N個小事務(wù)。每刪幾千行就提交一次鎖持有時間短、undo log及時釋放、binlog分批傳輸對在線業(yè)務(wù)的影響被壓縮到最小。這個方案不挑表結(jié)構(gòu)、不挑數(shù)據(jù)分布適用面最廣我建議所有場景都先從這個方案起步。最基礎(chǔ)的寫法是靠著主鍵ID范圍來分批DELETE FROM big_table WHERE id BETWEEN 1 AND 10000;執(zhí)行完后繼續(xù)刪下一批:DELETE FROM big_table WHERE id BETWEEN 10001 AND 20000;如果你不想手動算范圍可以選擇每次取一批要刪的主鍵ID然后用主鍵IN去刪除DELETE FROM big_table WHERE id IN ( SELECT id FROM ( SELECT id FROM big_table WHERE create_time 2023-01-01 LIMIT 5000 ) t );注意這里為什么套了一層子查詢MySQL不允許直接在DELETE的WHERE里SELECT同一張表的子查詢錯誤碼1093包一層派生表就能繞過這個限制。外層每次觸發(fā)會掃描到滿足條件的5000個ID然后刪除。這種方式不依賴ID連續(xù)比BETWEEN方式更通用。但它的缺點(diǎn)是子查詢本身也要掃描如果篩選條件沒走索引性能同樣會很差。2.2 批次大小怎么定別拍腦袋看三個指標(biāo)分批大小的選擇直接決定方案的成敗。批次太大會退化成大事務(wù)太小則刪除效率低下循環(huán)幾萬次能把人急死。我從實際操作經(jīng)驗里總結(jié)了三個參考指標(biāo)。一是看主庫的鎖等待和活躍會話數(shù)。單批次DELETE執(zhí)行期間SHOW ENGINE INNODB STATUS里面的History list length不能持續(xù)暴漲活躍會話也不能長時間趴滿。如果單批次5000行執(zhí)行時間超過2秒就要減小批次。二是看從庫的延遲情況。設(shè)置定時任務(wù)循環(huán)刪除的場景每次循環(huán)之間SLEEP幾秒鐘。用SHOW SLAVE STATUS觀察Seconds_Behind_Master如果延遲在增長說明刪除速度超過從庫應(yīng)用binlog的速度必須加大間隔或減小批次。三是看undo表空間增長速度。一次性刪太多可用SHOW GLOBAL STATUS LIKE Innodb_history_list_length觀察這個值代表未清理的undo日志量如果它持續(xù)高位不下降說明系統(tǒng)里有長事務(wù)或者刪除太快來不清理這種時候加大SLEEP時間。以一個線上案例說某張2億行的用戶行為日志表按天清理60天前的數(shù)據(jù)每次刪8000行刪除耗時約1.2秒循環(huán)之間SLEEP 20秒。整體算下來每秒約刪除400行左右主庫負(fù)載穩(wěn)定在20%以內(nèi)從庫延遲控制在5秒內(nèi)。如果按每次50000行去刪單次執(zhí)行時間就飆到8秒從庫延遲直接漲到兩分鐘以上這就不行了。2.3 循環(huán)腳本的完整寫法生產(chǎn)環(huán)境里我更推薦用存儲過程來做一來避免應(yīng)用層頻繁發(fā)起連接二來可以在數(shù)據(jù)庫端精確控制循環(huán)邏輯。下面這個存儲過程是我經(jīng)常用的模板你按自己的表結(jié)構(gòu)調(diào)整下就能用DELIMITER $$ CREATE PROCEDURE batch_delete_big_data(IN p_batch_size INT, IN p_sleep_seconds INT) BEGIN DECLARE v_rows INT DEFAULT 1; WHILE v_rows 0 DO DELETE FROM big_table WHERE create_time DATE_SUB(NOW(), INTERVAL 90 DAY) ORDER BY id LIMIT p_batch_size; SET v_rows ROW_COUNT(); COMMIT; IF v_rows 0 THEN DO SLEEP(p_sleep_seconds); END IF; END WHILE; END$$ DELIMITER ;這個存儲過程有兩個核心設(shè)計值得說說。第一用了ORDER BY id LIMIT ?的形式保證每次刪除的都是最老的一批數(shù)據(jù)而且走主鍵有序掃描效率高。第二每刪完一批就COMMIT一次讓事務(wù)及時結(jié)束undo log和行鎖都在每個批次結(jié)束后立即釋放。還有一個細(xì)節(jié)很多人在循環(huán)里糾結(jié)要不要commitMySQL默認(rèn)是自動提交的但存儲過程的事務(wù)控制最好還是顯式寫清楚防止將來修改隔離級別時行為變化。調(diào)用方式很簡單CALL batch_delete_big_data(5000, 5);這里我解釋一下參數(shù)怎么配p_batch_size從5000起步往上調(diào)p_sleep_seconds從5起步往上加。每次加參數(shù)跑一小段觀察主庫負(fù)載和從庫延遲找到當(dāng)前機(jī)器配置下的最優(yōu)組合。內(nèi)存大、磁盤快SSD、從庫延遲容錯高的環(huán)境可以把批次加大到10000。我自己的經(jīng)驗是寧肯稍保守一點(diǎn)別追求一次刪太多導(dǎo)致業(yè)務(wù)抖動。2.4 分批DELETE的兩個主要局限這個方案雖然通用但有兩個短板你得知道。第一它只解決了“刪除動作”對數(shù)據(jù)庫的壓力沒解決“物理文件不釋放”的問題。分批刪完數(shù)據(jù)后表空間依然是滿的.ibd文件大小不會縮。如果你清理數(shù)據(jù)的目的是為了騰出磁盤空間那分批DELETE并不能直接滿足你需要配合后面說的重建表。第二如果業(yè)務(wù)高峰期刪數(shù)據(jù)即使每批只有幾千行依然可能影響性能。這個方案更適合業(yè)務(wù)低峰期執(zhí)行比如凌晨1點(diǎn)到5點(diǎn)盡量不要在白天大流量時段跑。3. 主鍵有序場景下的高效方案按主鍵范圍分段刪除3.1 原理順序掃描遠(yuǎn)比隨機(jī)掃描劃算如果說分批DELETE是“小步快跑”那按主鍵范圍分段刪除就是“化整為零分區(qū)塊推平”。它的思路是先把要刪除的數(shù)據(jù)主鍵范圍劃分成若干連續(xù)的區(qū)間然后逐個區(qū)間刪除。比如你要刪掉id從1到1億之間的部分?jǐn)?shù)據(jù)就可以分成1000個區(qū)間每個區(qū)間10萬行按順序依次刪。為什么這樣更快因為InnoDB聚簇索引本身是按主鍵組織的B樹以主鍵范圍刪除時掃描和刪除都是順序的充分利用了磁盤預(yù)讀和內(nèi)存緩存。相反如果按create_time等條件去隨機(jī)找數(shù)據(jù)每一次定位都要走二級索引再回表頻繁的隨機(jī)IO會大大拉低速度。這個方案比較適合那些主鍵和業(yè)務(wù)刪除條件高度相關(guān)的場景。比如說訂單表主鍵就是訂單自增ID而你要刪的都是半年前的訂單那這些訂單的ID一定集中在一個相對靠前的區(qū)間按ID范圍分段就非常合適。如果主鍵是UUID或者完全無序的字符串這個方案優(yōu)勢就不明顯了。3.2 怎么找分段邊界MAX/MIN查詢代替COUNT(*)很多人在分段前習(xí)慣先執(zhí)行一次SELECT COUNT(*) FROM big_table WHERE create_time xxx想確認(rèn)總共有多少數(shù)據(jù)要刪。我勸你不要這么做。大表上COUNT(*)是全表掃描或大范圍索引掃描光這一下就能把數(shù)據(jù)庫拖垮。正確做法是直接用MIN(id)和MAX(id)定位邊界然后按固定步長推進(jìn)SELECT MIN(id), MAX(id) FROM big_table WHERE create_time 2023-01-01;比如查出來要刪的數(shù)據(jù)id分布在 1000000 到 58000000 之間那你就可以從1000000開始每100000為一個區(qū)間依次刪除DELETE FROM big_table WHERE id 1000000 AND id 1100000; DELETE FROM big_table WHERE id 1100000 AND id 1200000;如果你希望全流程自動化也可以寫一個存儲過程按主鍵游標(biāo)推進(jìn)。但要注意分段刪除的每個區(qū)間刪除量并不均勻——有些區(qū)間可能恰好有大量要刪的行有些區(qū)間則很少。所以單個區(qū)間執(zhí)行時間可能有波動批次間SLEEP仍然不能省。3.3 一個重要優(yōu)化在刪除期間暫停二級索引更新這個技巧我在實踐中覺得非常好用但知道的人不多。如果你那張表有多個二級索引每次DELETE都要同時維護(hù)這些索引。索引多了刪除一行要更新的索引條目也多代價翻好幾倍。有一種做法是先拿到要刪除數(shù)據(jù)的id集合存到一張臨時表然后刪除原表上的二級索引刪完數(shù)據(jù)再重建索引。這里有一個典型Trade-off刪除索引期間查原表會變慢因為少了索引但刪除數(shù)據(jù)的整體速度會顯著提升。如果你清理的是“要刪除大比例歷史數(shù)據(jù)、但近期數(shù)據(jù)仍然要被業(yè)務(wù)頻繁查詢”的表二級索引的維護(hù)成本占了刪除總開銷的大頭這個優(yōu)化效果會非常明顯。但它也有風(fēng)險刪除索引和重建索引都是大操作重建索引期間表上所有依賴這個索引的查詢都會受影響。因此這個技巧我建議只在“刪除后要重建索引”的前提下使用并且放在低峰期配合后面的“刪除后重建表”一起做。3.4 分段刪除的邊界與最佳實踐這個方案最大的坑是刪除的區(qū)間里如果混著“不該刪”的數(shù)據(jù)你有多大概率誤刪所以分段必須建立在“主鍵范圍和業(yè)務(wù)條件強(qiáng)相關(guān)”的基礎(chǔ)上。如果兩者的相關(guān)性弱你得在DELETE的WHERE里同時加上業(yè)務(wù)條件比如DELETE FROM orders WHERE id BETWEEN 1000000 AND 1100000 AND status expired;這樣寫會稍微降低刪除效率因為多了條件判斷但安全性高很多。我自己一般會在條件里保守一點(diǎn)寧可多掃一點(diǎn)數(shù)據(jù)也不冒誤刪的險。原因很簡單海量刪除一旦誤刪恢復(fù)成本比多跑幾分鐘高得多。4. 表結(jié)構(gòu)允許時的最優(yōu)解用分區(qū)表刪數(shù)據(jù)就是刪文件4.1 為什么分區(qū)表能“秒刪千萬行”如果你的表本身做了分區(qū)那海量刪除的問題幾乎被降維化解了。MySQL 8.0支持的分區(qū)類型主要是RANGE、LIST、HASH、KEY對時間維度的歷史數(shù)據(jù)清理來說最常用的是RANGE分區(qū)。比如訂單表按月份做RANGE分區(qū)每個分區(qū)存一個月的數(shù)據(jù)。要刪除某個月之前的數(shù)據(jù)直接ALTER TABLE orders DROP PARTITION p202301;注意這個操作是DDL不是DML。InnoDB在DROP PARTITION時做的本質(zhì)是刪除分區(qū)對應(yīng)的物理文件整個操作不產(chǎn)生逐行刪除的行為所以速度極快——一個1億行的分區(qū)DROP它可能也就幾秒鐘。這是所有DELETE方案都比不了的。如果你的業(yè)務(wù)周期劃分不是按月也可以按季度、按年。只要你在分區(qū)創(chuàng)建時預(yù)留好未來分區(qū)的余量也就是提前建好后面幾個月的分區(qū)清理數(shù)據(jù)的時候直接drop掉過期分區(qū)根本不用寫任何DELETE語句。4.2 分區(qū)表改造的步驟與注意事項如果你現(xiàn)在這個表還沒分區(qū)想改成分區(qū)表那就要謹(jǐn)慎了。對已有大數(shù)據(jù)量的表執(zhí)行分區(qū)操作有幾種方式最穩(wěn)妥的是“新建分區(qū)表導(dǎo)入數(shù)據(jù)切換”的方式-- 1. 創(chuàng)建帶分區(qū)的目標(biāo)表 CREATE TABLE orders_partitioned ( id BIGINT NOT NULL, order_no VARCHAR(64), create_time DATETIME, ... PRIMARY KEY (id, create_time) ) ENGINEInnoDB PARTITION BY RANGE (YEAR(create_time) * 100 MONTH(create_time)) ( PARTITION p202301 VALUES LESS THAN (202302), PARTITION p202302 VALUES LESS THAN (202303), PARTITION p202303 VALUES LESS THAN (202304), PARTITION p202304 VALUES LESS THAN (202305), PARTITION pMax VALUES LESS THAN MAXVALUE );注意分區(qū)表要求分區(qū)字段必須包含在主鍵和唯一索引中。這是MySQL的一條硬性規(guī)定“every unique key on the partitioned table must include every column in the partitioning expression”。比如上面我把id和create_time放一起做了聯(lián)合主鍵才滿足要求。這個規(guī)定非?？尤撕芏啾斫Y(jié)構(gòu)改分區(qū)失敗都是栽在這里。如果你的業(yè)務(wù)主鍵是單列id又想按create_time分區(qū)那要么改主鍵為聯(lián)合主鍵對現(xiàn)有業(yè)務(wù)有影響要么放棄分區(qū)方案。-- 2. 將歷史數(shù)據(jù)按分區(qū)規(guī)則插入 INSERT INTO orders_partitioned SELECT * FROM orders WHERE create_time 2023-04-01; -- 3. 業(yè)務(wù)停寫或低峰期切換表名 RENAME TABLE orders TO orders_old, orders_partitioned TO orders;4.3 沒有提前規(guī)劃那就用在線DDL工具做分區(qū)如果你的表已經(jīng)幾千萬行了尺寸巨大直接在線上執(zhí)行ALTER語句分區(qū)的耗時和風(fēng)險都讓人望而卻步。可以考慮使用gh-ost或者pt-online-schema-change這類在線DDL工具來執(zhí)行分區(qū)操作。它們通過觸發(fā)器或者binlog復(fù)制的方式在目標(biāo)表上重建新結(jié)構(gòu)切換過程幾乎不影響讀寫。但因為涉及復(fù)雜的表結(jié)構(gòu)轉(zhuǎn)換我只建議有一定經(jīng)驗的同學(xué)操作并且一定要在測試環(huán)境完整演練一遍再上生產(chǎn)。4.4 分區(qū)的維護(hù)成本別把“分區(qū)”當(dāng)萬能藥講了分區(qū)這么多好處我也要潑一盆冷水。分區(qū)表不是沒有代價的。最明顯的一點(diǎn)是某些查詢沒帶分區(qū)鍵時性能可能不升反降。比如你設(shè)置了按create_time分區(qū)但業(yè)務(wù)查詢經(jīng)常只按order_no查那MySQL會掃所有分區(qū)等于退化成了全表掃描。所以是否上分區(qū)要先梳理清楚核心查詢的WHERE條件。另外分區(qū)本身也是DDL操作每次拆分、合并、刪除分區(qū)都要小心全局元數(shù)據(jù)鎖MDL的影響。生產(chǎn)環(huán)境的表要操作分區(qū)依然建議低峰期做最好配合在線DDL工具一起用。5. 騰出磁盤空間的一勞永逸方案新建表 切換 重建5.1 核心思路數(shù)據(jù)“搬家”而不是逐行“刪除”前面說過普通DELETE不管你怎么刪物理文件都不會變小。如果你清理海量數(shù)據(jù)的最終目的是釋放磁盤空間或者讓表恢復(fù)到干凈緊湊的狀態(tài)那正確思路是“把要保留的數(shù)據(jù)復(fù)制到一張新表然后用新表替換舊表”。保留的數(shù)據(jù)越少新表就越小舊表直接DROP掉即可。這個方案的執(zhí)行流程是根據(jù)業(yè)務(wù)條件把要保留的數(shù)據(jù)導(dǎo)到新表。業(yè)務(wù)停寫或低峰期做一次數(shù)據(jù)補(bǔ)齊和表名切換。DROP掉舊表磁盤空間隨之釋放。5.2 完整操作步驟以保留近3個月數(shù)據(jù)為例第一步創(chuàng)建新表。最簡單的方式是復(fù)制舊表結(jié)構(gòu)CREATE TABLE orders_new LIKE orders;這個語法會復(fù)制表結(jié)構(gòu)、索引、自增屬性但不會復(fù)制數(shù)據(jù)非常適合用來做換表。第二步把需要保留的數(shù)據(jù)搬進(jìn)新表。INSERT INTO orders_new SELECT * FROM orders WHERE create_time DATE_SUB(NOW(), INTERVAL 3 MONTH);這里有一點(diǎn)要注意如果保留數(shù)據(jù)量也很大比如上億行INSERT不進(jìn)去那么快而且會產(chǎn)生大量binlog。可以考慮在業(yè)務(wù)低峰期執(zhí)行或者用INSERT INTO ... SELECT ... WHERE ...加上分批處理邏輯和前文的分批刪除邏輯相通。無論如何絕對不要在業(yè)務(wù)高峰期執(zhí)行這個INSERT SELECT它會對源表加鎖可能導(dǎo)致線上寫入被阻塞。第三步切換表名。這里最安全的是用兩個原子性RENAMERENAME TABLE orders TO orders_old, orders_new TO orders;MySQL的RENAME TABLE是原子操作也就是說這一步要么全部成功要么全部失敗中間不會有“表不存在”的間隙。這也是為什么推薦用兩條RENAME而不是先DROP舊表再RENAME新表。如果先DROP再RENAME中間那幾秒你線上所有訪問orders的語句都會報“table doesnt exist”。切換完成后應(yīng)用立刻開始訪問新表。新表里只有近3個月數(shù)據(jù)所有查詢都會比原來快不少。第四步驗證后DROP舊表。-- 驗證新表數(shù)據(jù)量、索引、自增值都正常 SELECT COUNT(*) FROM orders; SHOW INDEX FROM orders; -- 確認(rèn)無誤后刪除舊表釋放磁盤空間 DROP TABLE orders_old;DROP表的瞬間會釋放磁盤文件這個過程中I/O會有些波動但對業(yè)務(wù)基本無感。需要注意的是DROP一張幾十GB的大表本身也可能產(chǎn)生瞬時I/O壓力。如果表特別大比如超過100GB可以在低峰期執(zhí)行或者考慮用硬鏈接方式分批釋放文件這里不展開知道有這回事就行。5.3 這個方案有一個必須處理的臟數(shù)據(jù)問題新建表切換方案最大的隱患在于數(shù)據(jù)一致性。從“把數(shù)據(jù)復(fù)制到新表”到“切換表名”之間舊表可能又有新的寫入或修改如果你沒有完全停寫的話。這些增量數(shù)據(jù)不會自動出現(xiàn)在新表里。要規(guī)避這個問題要么在完全停寫窗口內(nèi)完成第二步和第三步適合可以接受短時間停服的場景要么利用binlog或應(yīng)用層雙寫來補(bǔ)齊增量。對于大部分互聯(lián)網(wǎng)業(yè)務(wù)來說最現(xiàn)實的是選一個業(yè)務(wù)量最低的時間窗口比如凌晨2點(diǎn)申請5到10分鐘的只讀維護(hù)窗口一口氣完成復(fù)制和切換。這個方案在架構(gòu)上是最干凈的也是我推薦你在數(shù)據(jù)量特別大、清理比例又特別高時優(yōu)先考慮的路徑。5.4 什么時候“換表”優(yōu)于“刪除”這里給個簡單的經(jīng)驗總結(jié)。假設(shè)表總行數(shù)X要保留行數(shù)Y。如果Y/X的比例低于30%換表方案的綜合效率顯著高于DELETE方案。因為這種情況下你要拷貝的數(shù)據(jù)少新表很小切換后所有查詢受益磁盤也徹底釋放。如果Y/X超過50%新建表拷貝的數(shù)據(jù)量太大反而不如分批DELETE碎片整理劃算。記住這個比例分界線你做技術(shù)選型時就不用再糾結(jié)了。6. 生產(chǎn)環(huán)境更優(yōu)雅的歸檔工具聊聊pt-archiver6.1 pt-archiver到底解決了什么問題如果你不想自己寫存儲過程、又要批量刪除歸檔數(shù)據(jù)Percona Toolkit里的pt-archiver就是為這個場景量身定做的。它是業(yè)界最常用的MySQL歸檔和清理工具核心優(yōu)勢有三點(diǎn)一是自動分批每次操作一行或一批控制鎖粒度二是可以同時把刪除的數(shù)據(jù)插入到歸檔表三是不要求你手寫循環(huán)邏輯參數(shù)化配置就行。它適合的場景包括定時清理歸檔歷史數(shù)據(jù)、大表冷熱數(shù)據(jù)分離、刪除時保留一份備份到歸檔庫比如歸檔到另一張表或者另一個實例。我自己在生產(chǎn)上用過它清理過一張幾千萬行的流水表工具平滑度非常高沒有出現(xiàn)鎖等待或從庫延遲異常。6.2 一條命令教會你用pt-archiver基本用法如下需求是把orders表里2023年之前的數(shù)據(jù)刪除同時把刪除的數(shù)據(jù)備份到orders_archive表pt-archiver \ --source h127.0.0.1,P3306,uarchive_user,pxxx,Dtest,torders \ --dest h127.0.0.1,P3306,uarchive_user,pxxx,Dtest,torders_archive \ --where create_time 2023-01-01 \ --limit 1000 \ --txn-size 1000 \ --sleep 0.5 \ --bulk-delete \ --bulk-insert \ --statistics參數(shù)含義拆解一下--source和--dest源表和目標(biāo)表。如果只做刪除不需要?dú)w檔可以省略--dest。--where刪除條件這是核心篩選條件注意這里必須走索引否則工具會提示全表掃描風(fēng)險。--limit 1000每批取多少行。--txn-size 1000多少行提交一次事務(wù)。這兩個參數(shù)配合控制鎖粒度。--sleep 0.5每批之間的休息時間單位秒用來控制刪除速率這是保護(hù)從庫不延遲的重要參數(shù)。--bulk-delete和--bulk-insert使用批量刪除、批量插入的方式比逐行操作高效得多。--statistics輸出執(zhí)行統(tǒng)計方便事后分析。在實際執(zhí)行前官方工具支持--dry-run參數(shù)只做語法檢查而不真正執(zhí)行可以拿它先驗證命令是否正確pt-archiver --source ... --where ... --dry-run6.3 使用pt-archiver的3條實踐經(jīng)驗第一連接賬號權(quán)限最小化就好只需要源表SELECT、DELETE目標(biāo)表INSERT的權(quán)限別一上來就給root。這不僅是安全習(xí)慣也能防止操作失誤時影響面擴(kuò)大。第二如果目標(biāo)歸檔表和源表結(jié)構(gòu)完全一樣--dest指定的表可以提前建好否則工具會自動嘗試創(chuàng)建但字段映射可能出錯。我建議手動先建好歸檔表不要依賴工具自動建表。第三必須確認(rèn)WHERE條件能走索引。pt-archiver執(zhí)行時會做 explain 檢查如果發(fā)現(xiàn)全表掃描它有--no-check-charset之類的參數(shù)跳過一些檢查但全表掃描刪除依然會拖垮庫。執(zhí)行前用EXPLAIN看一眼執(zhí)行計劃最穩(wěn)妥。7. 刪除之后的善后工作表空間整理與索引維護(hù)7.1 為什么刪完數(shù)據(jù)表文件還是那么大回到前面提到的那個歷史遺留問題DELETE只做邏輯刪除標(biāo)記記錄為“已刪除”物理空間不會立刻返還給操作系統(tǒng)。InnoDB表空間內(nèi)部雖然可以復(fù)用這些空間給后續(xù)INSERT但是文件大小不縮。因此如果你執(zhí)行完大批量DELETE之后用ls -lh看.ibd文件會發(fā)現(xiàn)文件大小一點(diǎn)沒變。如果你的目標(biāo)是壓縮物理文件目前通用的做法是重建表。在MySQL 5.7之后的版本里執(zhí)行ALTER TABLE big_table ENGINEInnoDB;可以重建主表數(shù)據(jù)。這個操作在MySQL 5.6以后是Online DDL允許在重建期間繼續(xù)讀寫但它仍然會消耗額外的磁盤空間因為要生成臨時表文件耗時也比較長依舊建議在低峰期操作。MySQL 8.0還有一種新姿勢用ALTER TABLE ... ALGORITHMINPLACE配合在線操作但也要看版本和表結(jié)構(gòu)具體情況。最穩(wěn)妥的仍是按前面第5節(jié)說的“建新表切換”方案徹底搬家一次物理和邏輯上都干干凈凈。7.2 清理碎片和重建二級索引另外一個容易被忽略的問題是索引碎片。大量刪除后二級索引葉子節(jié)點(diǎn)會有很多空位索引空間利用率下降查詢性能受影響。除了重建表你還可以對某個二級索引單獨(dú)做整理ALTER TABLE big_table DROP INDEX idx_create_time, ADD INDEX idx_create_time (create_time);這個操作的本質(zhì)是刪索引再重新建索引索引重建期間相關(guān)查詢會變慢。如果有多個索引需要重建建議一個一個來別批量操作避免表上長時間沒有可用索引。7.3 統(tǒng)計信息更新ANALYZE TABLE不能省大量刪除之后MySQL的優(yōu)化器基于采樣得到的統(tǒng)計信息可能已經(jīng)嚴(yán)重過時于是執(zhí)行計劃就可能出錯本該走索引的查詢走了全表掃描本該用小索引的查詢選了個大索引。高負(fù)載下你還會看到information_schema.tables里的數(shù)據(jù)行數(shù)完全對不上那里面的值是估算的不是實時的。這時候就需要主動更新統(tǒng)計信息ANALYZE TABLE big_table;這個操作在MySQL 8.0里執(zhí)行很快因為它只是重新采樣計算統(tǒng)計信息不需要重建數(shù)據(jù)。在批量刪除后的當(dāng)天夜里或第二天的低峰期執(zhí)行一次能有效避免優(yōu)化器誤判。8. 常見問題排查與踩坑實錄8.1 問題一刪著刪著從庫延遲越來越大這應(yīng)該是最常遇到的現(xiàn)象。分批DELETE本身沒問題但如果你批次太大、SLEEP太短從庫應(yīng)用binlog的速度就跟不上主庫產(chǎn)生binlog的速度。排查流程先執(zhí)行SHOW SLAVE STATUS\G看Seconds_Behind_Master確認(rèn)延遲數(shù)值和趨勢再看主庫上是不是有長事務(wù)在跑information_schema.innodb_trx里如果有TIME很大的事務(wù)優(yōu)先確認(rèn)是不是自己的刪除腳本沒提交。最終解法就是把批次調(diào)小、SLEEP調(diào)大或者在腳本里增加“延遲超過閾值自動暫?！钡倪壿嬤@個邏輯不算復(fù)雜但非常實用。8.2 問題二DELETE走了索引還是很慢很多情況下你確實給WHERE條件加了索引但執(zhí)行計劃依然選擇了全表掃描。原因多半是數(shù)據(jù)分布讓優(yōu)化器認(rèn)為走索引不如全表掃描劃算。比如要刪除的數(shù)據(jù)占了表中大部分行優(yōu)化器估算下來全表掃更快。排查方法是執(zhí)行EXPLAIN DELETE ...看type列和key列如果發(fā)現(xiàn)是ALL或者key為NULL那么要么用FORCE INDEX強(qiáng)制走索引要么換成“按主鍵分段刪除”的寫法繞開優(yōu)化器的錯誤判斷。8.3 問題三刪除過程中出現(xiàn)鎖等待超時錯誤信息一般是Lock wait timeout exceeded; try restarting transaction。這說明你的DELETE在等一把別人持有的鎖。排查方式用SHOW ENGINE INNODB STATUS查看當(dāng)前鎖等待也可以直接查sys.innodb_lock_waits視圖看誰堵了誰。通常的原因有兩個一是業(yè)務(wù)本身正在高頻更新你要刪除的數(shù)據(jù)范圍這種場景要錯峰刪除二是你的刪除批次太大單批持有大量行鎖導(dǎo)致后續(xù)刪除互相等待。解法減小批次、增加SLEEP、盡量在低峰期執(zhí)行。8.4 問題四刪除期間undo表空間暴漲刪除動作寫undo是必然的但暴漲到撐爆磁盤就有問題了。先看SHOW GLOBAL STATUS LIKE innodb_history_list_length如果這個值一直增加不降說明有老事務(wù)一直沒結(jié)束導(dǎo)致undo日志無法purge。排查information_schema.innodb_trx里是否有長時間未提交的事務(wù)尤其注意是不是有APP服務(wù)開啟了事務(wù)但沒提交。一個連接空閑但沒commit就能讓你所有的刪除產(chǎn)生的undo都清不掉。遇到這種情況先確認(rèn)沒有長事務(wù)再說刪除的事否則你刪得越快undo漲得越兇。8.5 問題五DROP舊表時磁盤I/O瞬間打滿DROP大表時操作系統(tǒng)需要釋放對應(yīng)的大文件。InnoDB刪除表空間文件的過程不是瞬間完成的但對磁盤I/O的沖擊是真實存在的。如果舊表有100GB以上建議在業(yè)務(wù)最低峰DROP或者考慮分硬鏈接釋放文件的技巧先把.ibd文件硬鏈接到另一個路徑然后通過TRUNCATE一點(diǎn)點(diǎn)截斷文件來釋放空間讓I/O壓力平滑化。這個技巧屬于進(jìn)階操作普通開發(fā)環(huán)境不需要用但生產(chǎn)上遇到“不敢DROP大表”的情況可以嘗試搜索一下官方文檔和相關(guān)文章來深入。寫在最后的經(jīng)驗聊到這兒批量刪除海量數(shù)據(jù)的幾條主流路線基本都過了一遍。我個人的體會是沒有萬能的方案只有適合當(dāng)下場景的方案。如果只是日常刪幾十萬行把分批DELETE寫好、參數(shù)調(diào)對就夠了如果刪的數(shù)據(jù)占比特別高優(yōu)先考慮新建表切換如果表結(jié)構(gòu)設(shè)計時就想清楚了數(shù)據(jù)生命周期那么分區(qū)表是最省心的長期方案如果公司有Percona Toolkit的運(yùn)維基礎(chǔ)pt-archiver能讓你的活變得非常輕松。另外還有一點(diǎn)小建議不管用哪種方案動手前先把表結(jié)構(gòu)和數(shù)據(jù)分布摸清楚備份必須做好。海量刪除不是不能做而是要在可控的節(jié)奏里做。你每一次刪除前多花十分鐘做EXPLAIN、做備份、確認(rèn)時間窗口線上就能少一次血淚教訓(xùn)。希望這篇文章能幫你把那塊壓在心口的大石頭平穩(wěn)落地。