描述

刪除表大批量數(shù)據(jù)，這是一個(gè)比較少的事件。 但在實(shí)際的業(yè)務(wù)開發(fā)中或者數(shù)據(jù)測(cè)試也會(huì)遇到這種情況。比如定期從日志大表中刪除幾百萬的數(shù)據(jù)記錄；刪除表數(shù)據(jù)的方式有多種，操作起來也很簡(jiǎn)單。但是這里存在一個(gè)問題， 刪除大量行可能會(huì)很慢。 并且有可能需要更長(zhǎng)的時(shí)間，因?yàn)榱硪粋€(gè)會(huì)話已鎖定您要?jiǎng)h除的數(shù)據(jù)。

根據(jù)我們所熟知的使用SQL刪除數(shù)據(jù)有三個(gè)方式：
1：DELETE，可以添加where條件，速度較慢，鎖表
2：truncate ，會(huì)刪除表所有數(shù)據(jù)，速度快
3：drop,刪除數(shù)據(jù)以及表結(jié)構(gòu)，慎用

實(shí)踐

【1】對(duì)于truncate 和drop不在本次的討論范圍，雖然這倆種方式很快，但是破壞性太大。注意日常開發(fā)中所有刪除操作必須添加條件。

【2】對(duì)于幾十萬以上數(shù)據(jù)的刪除不建議使用DELETE FROM TABLE WHERE的方式，該操作非常耗時(shí)，效率很差。

【3】對(duì)于大批量數(shù)據(jù)的刪除需求實(shí)現(xiàn)可以通過Create-Table-as-Select方式處理，在表中插入行比刪除它們更快。 使用 create-table-as-select (CTAS) 將數(shù)據(jù)加載到新表中的速度更快。

create table table_name_temp
  select * from source_table where XX=?

通過CTAS將不予刪除的數(shù)據(jù)保留到一個(gè)臨時(shí)表中，然后再通過SWAP的方式將臨時(shí)表作為原表，通過這種方式完成大批量數(shù)據(jù)刪除

【4】個(gè)人不建議上述的方式建表，上面的建表方式新表是不會(huì)復(fù)制原表的索引結(jié)構(gòu)的，如果這個(gè)是一個(gè)大表那么后面單獨(dú)加索引也是一個(gè)問題。建議使用 CREATE TABLE XXX (LIKE XXX);方式建表，這個(gè)會(huì)復(fù)制相關(guān)的索引結(jié)構(gòu)數(shù)據(jù)

【5】具體操作步驟

-- 復(fù)制表結(jié)構(gòu) 
CREATE  TABLE  tableB   (LIKE tableA);
-- 插入篩選數(shù)據(jù)
INSERT into  tableB    SELECT *  from tableA  where XXX = ?;
-- 重命名，替換
rename  table  tableA to tableC;
rename  table  tableB to tableA;  
-- 刪除舊表  
DROP TABLE tableC;

注意：其中倆次rename可以先drop然后一次的rename，但是考慮到數(shù)據(jù)安全，畢竟是大數(shù)量數(shù)據(jù)刪除，還是多操作一步，替換后自己檢查下，然后再刪除舊表，穩(wěn)妥些

【6】通過delete刪除上百萬的數(shù)據(jù)耗時(shí)不清楚具體耗時(shí)，反正自己等待了十多鐘都沒有結(jié)果，通過select * from sys.session WHERE conn_id!=connection_id();
查詢看一直在執(zhí)行。通過上面的方式500萬的數(shù)據(jù)不到1分鐘，還是比較快的。

【7】小技巧，如果你的大表有遞增的ID，刪除的或者保留數(shù)據(jù)的能夠以ID作為劃分的那么select的條件可以通過這里進(jìn)行優(yōu)化，那么操作效率會(huì)更快。

【8】如果是oracle，那么還可以使用 alter table … move 來更改存儲(chǔ)行的表空間

alter table tableName   move including rows where XXX=？

到此這篇關(guān)于如何使用 SQL 快速刪除數(shù)百萬行數(shù)據(jù)的文章就介紹到這了,更多相關(guān)SQL 刪除數(shù)百萬行數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論