淺談MySQL如何優(yōu)雅的做大表刪除

隨著時間的推移或者業(yè)務(wù)量的增長，數(shù)據(jù)庫空間使用率也不斷的呈穩(wěn)定上升狀態(tài)，當(dāng)數(shù)據(jù)庫空間將要達(dá)到瓶頸的時候，可能我們才會發(fā)現(xiàn)數(shù)據(jù)庫有那么一兩張的超級大表！他們堆積了從業(yè)務(wù)開始到現(xiàn)在的全部數(shù)據(jù)，但是90%的數(shù)據(jù)都是沒有業(yè)務(wù)價值的，這時候該如何處理這些大表？

既然是沒有價值的數(shù)據(jù)，我們通常一般會選擇直接刪除或者歸檔后刪除兩種，對于數(shù)據(jù)刪除的操作方式來說又可分為兩大類：

• 通過truncate直接刪除表中全部數(shù)據(jù)
• 通過delete刪除表中滿足條件記錄

一、Truncate操作

從邏輯意義上來講，truncate操作就是刪除表中所有記錄行，但是又與delete from table_name wehre 1=1這種操作不一樣。MySQL為了提高刪除整張表數(shù)據(jù)的性能，truncate操作其本質(zhì)上其實(shí)是先drop table然后在re-create table。也真因如此，truncate操作是一個不可回滾的DDL操作。

1.1 MySQL truncate 都做了哪些操作？

• truncate操作實(shí)際上分為drop、re-create兩步
• drop操作的第一個階段，是對Buffer pool頁面進(jìn)行清除的過程，將表相關(guān)的數(shù)據(jù)頁從flush鏈中刪除，而不需要做flush操作。該步驟的瓶頸點(diǎn)主要在于flush隊(duì)列的刪除操作必須持有對應(yīng)buffer pool instance的鎖并進(jìn)行遍歷搜索，如果buffer pool instance比較大且flush鏈中需要刪除的數(shù)據(jù)頁很多，該操作會導(dǎo)致其他事務(wù)在獲取buffer pool instance的鎖時被阻塞，從而影響數(shù)據(jù)庫的性能
• drop操作的第二個階段，是刪除ibd磁盤文件的過程。刪除數(shù)據(jù)庫物理文件越大I/O資源消耗越大，刪除操作耗時越久
• re-create操作階段，只要刪除表的.frm文件完好無損，在drop table之后就可以按照原表結(jié)構(gòu)信息進(jìn)行重建，重建后表的auto_increment值會被重置

1.2 如何優(yōu)化truncate操作帶來的資源消耗？

• 對于truncate操作中的drop表第一階段，當(dāng)分配給MySQL實(shí)例的innodb_buffer_pool_size超過1GB時，合理的設(shè)置innodb_buffer_pool_instances參數(shù)，提高并發(fā)的同時也變相的減少掃描buffer pool instance時鎖資源占用耗時
• 對于truncate操作中的drop表第二階段，在刪除對應(yīng)表之前，先對改表的.ibd文件創(chuàng)建一個硬連接，加快MySQL層面的drop操作執(zhí)行效率，減少對數(shù)據(jù)庫層面的性能損耗。后續(xù)手動對操作系統(tǒng)層面我們做的硬連接進(jìn)行清理

二、Delete操作

2.1 MySQL delete 都做了哪些操作？

• 根據(jù)where條件對刪除表進(jìn)行索引/全表掃描，檢查是否符合where條件，該階段會對掃描中所有行進(jìn)行加鎖。該階段是最大的資源消耗隱患，若表的數(shù)據(jù)量大且delete操作無法有效利用索引減少掃描數(shù)據(jù)量，該步驟對于數(shù)據(jù)庫帶來的鎖爭用、cpu/io資源的消耗都是巨大的
• 對不能夠被where條件匹配的行施加的鎖會在條件檢查后予以釋放，InnoDB僅鎖定需要刪除的行。這可以有效地降低鎖爭用，但是我們?nèi)孕枰P(guān)注的一點(diǎn)是，一次性刪除大批量的數(shù)據(jù)，該操作將會產(chǎn)生巨大的binlog事務(wù)日志，這對于MySQL自身以及主從架構(gòu)中的從庫都是不友好的，可能帶來叫的復(fù)制延遲。

2.2 如何優(yōu)化delete操作？

• delete全表刪除操作需要謹(jǐn)慎，可考慮使用truncate操作
• delete … where … 中，where過濾條件盡量保證可有效利用索引減少數(shù)據(jù)掃描量，避免全表掃描
• 對于大批量數(shù)據(jù)刪除且where條件無索引的情況，delete操作可額外增加自增長主鍵或者含索引的時間字段，進(jìn)行分批刪除操作，每次刪除少量數(shù)據(jù)，分多批次執(zhí)行。
• 對于保留近期數(shù)據(jù)刪除歷史數(shù)據(jù)的經(jīng)典場景，可創(chuàng)建同結(jié)構(gòu)的xxx_tmp表并通過insert xxx_tmp select …操作將需要的數(shù)據(jù)保留至tmp表中、然后通過rename操作將當(dāng)前業(yè)務(wù)表xxx替換為xxx_bak表，xxx_tmp表替換為當(dāng)前業(yè)務(wù)表名xxx，后續(xù)手動刪除無用的大表xxx_bak

2.3 delete常見的兩個場景

2.3.1 delete where條件無有效索引過濾

比較常見的一個場景是，業(yè)務(wù)上需要刪除t1 condition1=xxx的值，condition字段無法有效利用索引，這種情況下我們通常的做法是：

• 查看當(dāng)前表結(jié)構(gòu)中可有效利用的索引，盡量是表的自增長主鍵或者時間索引字段
• 有效利用自增長主鍵索引或者時間索引，將delete操作添加索引字段的范圍過濾，每次刪除少量數(shù)據(jù)，分多批次執(zhí)行。具體分批需要根據(jù)業(yè)務(wù)實(shí)際進(jìn)行評估，避免一次性刪除大批量數(shù)據(jù)。

-- 利用自增長主鍵索引
delete from t1 where condition1=xxx and id >=1 and id  50000;
delete from t1 where condition1=xxx and id >=50000 and id  100000;

-- 利用時間索引
delete from t1 where condition1=xxx and create_time >= '2021-01-01 00:00:00' and create_time  '2021-02-01 00:00:00';
delete from t1 where condition1=xxx and create_time >= '2021-02-01 00:00:00' and create_time  '2021-03-01 00:00:00';

2.3.2 保留近期數(shù)據(jù)刪除歷史數(shù)據(jù)

比較常見的一個場景是，需要僅保留t1表近3個月數(shù)據(jù)，其余歷史數(shù)據(jù)刪除，我們通常的做法是：

創(chuàng)建一張t1_tmp表用來臨時存儲需要保留的數(shù)據(jù)

create table t1_tmp like t1;

根據(jù)有索引的時間字段，分批次的將需要保留的數(shù)據(jù)寫入t1_tmp表中，該步驟需要注意的是，最后一批次時間的操作可暫時不處理

-- 根據(jù)實(shí)例業(yè)務(wù)數(shù)量進(jìn)行分批，盡量每批次處理數(shù)據(jù)量不要太大
insert into t1_tmp select * from t1 where create_time >= '2021-01-01 00:00:00' and create_time  '2021-02-01 00:00:00';
insert into t1_tmp select * from t1 where create_time >= '2021-02-01 00:00:00' and create_time  '2021-03-01 00:00:00';

-- 當(dāng)前最后一批次數(shù)據(jù)先不操作
-- insert into t1_tmp select * from t1 where create_time >= '2021-03-01 00:00:00' and create_time  '2021-04-01 00:00:00';

通過rename操作將當(dāng)前業(yè)務(wù)表t1替換為t1_bak表，t1_tmp表替換為當(dāng)前業(yè)務(wù)表名t1，被刪除表若有頻繁的DML操作，該步驟會造成短暫的業(yè)務(wù)訪問失敗

alter table t1 rename to t1_bak;
alter table t1_tmp rename to t1;

將最后一批次數(shù)據(jù)寫入當(dāng)前業(yè)務(wù)表，該步驟的目的是為了減少變更操作流程中的數(shù)據(jù)丟失

insert into t1 select * from t1_bak where create_time >= '2021-03-01 00:00:00' and create_time  '2021-04-01 00:00:00';

在rename操作步驟中，還有一點(diǎn)我們需要關(guān)注的是，變更表主鍵是自增長還是業(yè)務(wù)唯一的uuid，若為自增長主鍵，我們還需要注意修改t1_tmp表的自增長值，保證最終設(shè)置值包含變更期間數(shù)據(jù)寫入

alter table t1_tmp auto_increment={t1表當(dāng)前auto值}+{變更期間預(yù)估增長值}

三、Truncate/Delete優(yōu)劣勢對比