淺談MySQL如何優雅的做大表刪除

隨著時間的推移或者業務量的增長，數據庫空間使用率也不斷的呈穩定上升狀態，當數據庫空間將要達到瓶頸的時候，可能我們才會發現數據庫有那麼一兩張的超級大表！他們堆積瞭從業務開始到現在的全部數據，但是90%的數據都是沒有業務價值的，這時候該如何處理這些大表？

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

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

一、Truncate操作

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

1.1 MySQL truncate 都做瞭哪些操作？

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

1.2 如何優化truncate操作帶來的資源消耗？

• 對於truncate操作中的drop表第一階段，當分配給MySQL實例的innodb_buffer_pool_size超過1GB時，合理的設置innodb_buffer_pool_instances參數，提高並發的同時也變相的減少掃描buffer pool instance時鎖資源占用耗時
• 對於truncate操作中的drop表第二階段，在刪除對應表之前，先對改表的.ibd文件創建一個硬連接，加快MySQL層面的drop操作執行效率，減少對數據庫層面的性能損耗。後續手動對操作系統層面我們做的硬連接進行清理

二、Delete操作

2.1 MySQL delete 都做瞭哪些操作？

• 根據where條件對刪除表進行索引/全表掃描，檢查是否符合where條件，該階段會對掃描中所有行進行加鎖。該階段是最大的資源消耗隱患，若表的數據量大且delete操作無法有效利用索引減少掃描數據量，該步驟對於數據庫帶來的鎖爭用、cpu/io資源的消耗都是巨大的
• 對不能夠被where條件匹配的行施加的鎖會在條件檢查後予以釋放，InnoDB僅鎖定需要刪除的行。這可以有效地降低鎖爭用，但是我們仍需要關註的一點是，一次性刪除大批量的數據，該操作將會產生巨大的binlog事務日志，這對於MySQL自身以及主從架構中的從庫都是不友好的，可能帶來叫的復制延遲。

2.2 如何優化delete操作？

• delete全表刪除操作需要謹慎，可考慮使用truncate操作
• delete … where … 中，where過濾條件盡量保證可有效利用索引減少數據掃描量，避免全表掃描
• 對於大批量數據刪除且where條件無索引的情況，delete操作可額外增加自增長主鍵或者含索引的時間字段，進行分批刪除操作，每次刪除少量數據，分多批次執行。
• 對於保留近期數據刪除歷史數據的經典場景，可創建同結構的xxx_tmp表並通過insert xxx_tmp select …操作將需要的數據保留至tmp表中、然後通過rename操作將當前業務表xxx替換為xxx_bak表，xxx_tmp表替換為當前業務表名xxx，後續手動刪除無用的大表xxx_bak

2.3 delete常見的兩個場景

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

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

• 查看當前表結構中可有效利用的索引，盡量是表的自增長主鍵或者時間索引字段
• 有效利用自增長主鍵索引或者時間索引，將delete操作添加索引字段的范圍過濾，每次刪除少量數據，分多批次執行。具體分批需要根據業務實際進行評估，避免一次性刪除大批量數據。

-- 利用自增長主鍵索引
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 保留近期數據刪除歷史數據

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

創建一張t1_tmp表用來臨時存儲需要保留的數據

create table t1_tmp like t1;

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

-- 根據實例業務數量進行分批，盡量每批次處理數據量不要太大
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';

-- 當前最後一批次數據先不操作
-- 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操作將當前業務表t1替換為t1_bak表，t1_tmp表替換為當前業務表名t1，被刪除表若有頻繁的DML操作，該步驟會造成短暫的業務訪問失敗

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

將最後一批次數據寫入當前業務表，該步驟的目的是為瞭減少變更操作流程中的數據丟失

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操作步驟中，還有一點我們需要關註的是，變更表主鍵是自增長還是業務唯一的uuid，若為自增長主鍵，我們還需要註意修改t1_tmp表的自增長值，保證最終設置值包含變更期間數據寫入

alter table t1_tmp auto_increment={t1表當前auto值}+{變更期間預估增長值}

三、Truncate/Delete優劣勢對比

到此這篇關於淺談MySQL如何優雅的做大表刪除的文章就介紹到這瞭,更多相關MySQL 大表刪除內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：

None Found