慢 SQL 打爆監控！億級數據表的刪除問題

作者：小牛肉 2024-09-27 08:44:43

type 由于沒有索引放在 SQL 中是巨大瓶頸，必須得去掉！datachange_lasttime 也可以從 SQL 中拿出來，查出來之后在內存中再做 type 和 datachange_lasttime 的篩選（也就是在 Java 代碼中寫這個邏輯），然后再根據 id 批量刪除。

背景

簡單抽象下業務場景，有一張 MySQL 表用來存儲用戶的操作日志，需要依賴這個日志來做一些業務邏輯的判斷，并且每個用戶可以存在多條日志，所以可想而知，隨著時間的推移，這張表肯定是會越來越大的，必須要做治理。

秉持著最簡原則，我們暫時不考慮分庫分表，數據能刪則刪，因為表中數據其實并不是每一條都有用，梳理了下業務之后，我們最終的治理方向就是：

Job 每個月定時執行一次來刪除數據
保留近 3 個月的數據，之前的數據可以刪除
刪除并不是無腦刪除，每條日志有一個對應的類型 type（取值固定，假設是 a、b、c、d、e 吧），當 type = c 的時候該條日志不能刪除（忽略這個奇怪的邏輯，純屬業務需求）

我們可以抽象出這樣一張表就命名為 log 吧，它有如下字段：

type 沒有索引并且也不適合做索引。

刪除數據的條件：

以上就是背景，應該比較清楚了

首先大表刪除的基本方針一定是批量刪除，即分批查，分批刪。

最基本的方案就是把 datachange_lasttime 和 type 的要求都放在 SQL 中，直接通過 SQL 找到我們要刪的數據：

select id from log
where 
    datachange_lasttime <= '2023-06-17 00:00:00' 
    and type != 'c'
limit #{limit}

查一次就根據 id 批量刪除一次，每次查 limit 條，停止條件就是查不出來數據了

早期方案在數據量級幾千萬的時候還是沒問題的，因為我們這個刪除只需要離線運行，所以用定時 job 跑就可以，對業務基本沒啥影響。

但隨著表越來越大，上億之后，這條 SQL 直接卡住，慢查詢告警猛增，已經沒有辦法正常完成刪除了。

查詢 SQL 如下：

select id from log
from t_user_pop_log
order by id
limit #{offset}, #{limit}

分頁查詢圖方便我直接用的 MyBatis PageHelper，但是很快我就為此付出代價，就是總是有臟數據沒刪干凈，我們舉個例子分析下：

假設表中總數據 300 條
第一次查詢：select * from log limit 0,100; 查出了 100 條數據，但是經過我們 type != c 的過濾后，最終只刪除了 50 條數據，那么表中還剩余 250 條數據
第二次查詢，表中有 250 條數據，select * from log limit 101,200; 查出了 100 條數據，但是經過我們 type != c 的過濾后，最終只刪除了 60 條數據，那么表中還剩余 190 條數據
第三次查詢，表中有 190 條數據，select * from log limit 201,300; 這次查詢就出問題了，因為表中只有 190 條數據了，offset = 201 是查不出來數據的，所以這就導致總有一部分數據是沒有經過處理的

想到的解決方案是一直查第一頁（也就是 offset = 0），直到第一頁沒數據，那就停止查詢

但是很明顯這個停止查詢的條件存在問題，如果恰好這一頁的所有數據全都是 “type=c”，也就是這一頁的數據都是不能刪的數據，那么循環就會卡在這一頁，因為這一頁的數據永遠不會發生變化

我們看失敗方案，其實可以發現失敗的最根本原因是 MyBatis Pagehelper 的 offset 的計算不對，考慮我們自己做分頁，不用 MyBatis Pagehelper，這樣就改成如下方式來分批查詢：

select *
from t_user_pop_log
where id >= #{startId}
order by id
limit #{limit}

這條 SQL 中只涉及主鍵 id，速度是非常快的：

上述方案很容易想到一個點，那就是 startId 可以不需要每次都從 1 開始。

每個月刪除一次，那其實除了第一次，后續的刪除只需要刪除一個月的數據，只有第一次刪除是需要掃描三個月前的所有數據。舉個例子：

5.1 執行第一次刪除，保留近三個月即 2.1 之后的數據，2.1 之前的數據要全部掃描并刪除
6.1 執行第二次刪除，保留近三個月即 3.1 之后的數據，2.1 之前的數據已經被刪除了，所以這次刪除其實只需要刪除從 2.1 開始到 3.1 這一個月的數據就可以了

那么 startId 的初始取值邏輯就是：

首次刪除：startId = 1
非首次刪除：startId = datachange_lastime >=【當前時間 - 3 個月 - 1 個月】的最小 id（還可以給這個時間加一點容錯空間，多掃描幾天的數據也無妨，比如 15 天，startId = datachange_lastime >=【當前時間 - 3 個月 - 1 個月 - 15 天】的最小 id）

以上，在首次刪除的時候，掃描的數據量非常大，可以考慮加一點 sleep，防止 DB 進程被打滿。

責任編輯：武曉燕來源：飛天小牛肉