隨著時間的推移或者業(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操作就是刪除表中所有記錄行，但是又與delete from table_name wehre 1=1這種操作不一樣。MySQL為了提高刪除整張表數(shù)據(jù)的性能，truncate操作其本質(zhì)上其實(shí)是先drop table然后在re-create table。也真因如此，truncate操作是一個不可回滾的DDL操作。
2.3.1 delete where條件無有效索引過濾
比較常見的一個場景是，業(yè)務(wù)上需要刪除t1 condition1=xxx的值，condition字段無法有效利用索引，這種情況下我們通常的做法是：
-- 利用自增長主鍵索引 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ù)估增長值}
| 操作類型 | 描述 | 優(yōu)勢 | 劣勢 |
|---|---|---|---|
| Truncate | 表的全量刪除操作 | 無需掃描表數(shù)據(jù)，執(zhí)行效率高，直接進(jìn)行物理刪除，快速釋放空間占用 | DDL操作無法進(jìn)行回滾,無法按條件進(jìn)行刪除 |
| Delete | 根據(jù)指定條件進(jìn)行過濾刪除操作 | 可根據(jù)指定條件進(jìn)行過濾刪除 | 刪除效率依賴where條件的編寫，大表刪除會產(chǎn)品大量的binlog且刪除效率低，刪除操作可能出現(xiàn)較多的碎片空間而不是直接釋放空間占用 |
到此這篇關(guān)于淺談MySQL如何優(yōu)雅的做大表刪除的文章就介紹到這了,更多相關(guān)MySQL 大表刪除內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
標(biāo)簽：錦州 資陽 隨州 吉林 甘南 荊州 黑河 滄州
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《淺談MySQL如何優(yōu)雅的做大表刪除》，本文關(guān)鍵詞 淺談,MySQL,如何,優(yōu)雅,的,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。
