隨著時間的推移或者業務量的增長，數據庫空間使用率也不斷的呈穩定上升狀態，當數據庫空間將要達到瓶頸的時候，可能我們才會發現數據庫有那么一兩張的超級大表！他們堆積了從業務開始到現在的全部數據，但是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_bak2.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 大表刪除內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！