淺談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優劣勢對比

操作類型 描述 優勢 劣勢
Truncate 表的全量刪除操作 無需掃描表數據,執行效率高,直接進行物理刪除,快速釋放空間占用 DDL操作無法進行回滾,無法按條件進行刪除
Delete 根據指定條件進行過濾刪除操作 可根據指定條件進行過濾刪除 刪除效率依賴where條件的編寫,大表刪除會產品大量的binlog且刪除效率低,刪除操作可能出現較多的碎片空間而不是直接釋放空間占用

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