背景

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

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

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

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

• id（主鍵）
• type（無索引）
• datachange_lasttime（時間，有索引）

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

刪除數(shù)據(jù)的條件：

1. datachange_lasttime <= 當前時間 - 3 個月
2. type != c

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

早期方案

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

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

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

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

失敗的優(yōu)化方案

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

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

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

查詢 SQL 如下：

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

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

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

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

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

成功的優(yōu)化方案

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

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

這條 SQL 中只涉及主鍵 id，速度是非?？斓模?/p>

1. startId 從 1 開始，一次查詢 limit 條，根據(jù) id 升序查
2. 對查詢出來的記錄做 type != c & datachange_lasttime <= 當前時間 - 3 個月的篩選，從而篩選出需要刪除的 id
3. 根據(jù)篩選出來的 id 進行批量刪除
4. 更新下一次查詢的 startId = 本次查詢結果中最大的 id + 1
5. 停止條件：如果本次查詢結果的第一條記錄的 datachange_lasttime > 當前時間 - 3 個月，后面的數(shù)據(jù)就不需要刪除了

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

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

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

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

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

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