基于Oracle的大數(shù)據(jù)量刪除操作性能分析

安陽工學(xué)院計算機科學(xué)與信息工程學(xué)院 王 敏 張捐凈

基于Oracle的大數(shù)據(jù)量刪除操作性能分析

安陽工學(xué)院計算機科學(xué)與信息工程學(xué)院 王 敏 張捐凈

基于大數(shù)據(jù)的業(yè)務(wù)應(yīng)用，在日常管理中，如何能最有效的對垃圾數(shù)據(jù)進行刪除處理，需要同時考慮刪除時長以及業(yè)務(wù)影響兩個維度。本文通過做一些分析和比較，證明在oracle數(shù)據(jù)庫中，通過rowid刪除具有最優(yōu)的效率。

垃圾數(shù)據(jù)；刪除；Rowid

1 問題提出

當(dāng)前無論是互聯(lián)網(wǎng)、電商、電信等業(yè)務(wù)，還是未來物聯(lián)網(wǎng)業(yè)務(wù)，它們都擁有海量的數(shù)據(jù)，同時業(yè)務(wù)又要頻繁的訪問數(shù)據(jù)，數(shù)據(jù)訪問的質(zhì)量將直接影響用戶體驗和業(yè)務(wù)運營。但是另一方面，數(shù)據(jù)庫在業(yè)務(wù)運營過程中，又不可避免的產(chǎn)生大量無用垃圾數(shù)據(jù)。這些數(shù)據(jù)有時候會影響正常業(yè)務(wù)運營，需要及時刪除清理。

基于上面的場景，在對Oracle大數(shù)據(jù)表進行刪除的時候，假如我們直接刪除，將會有很長的時間代價，且由于delete操作會鎖表，使得外部訪問無法正常進行，這樣就會使得正常的業(yè)務(wù)受阻，顯然這是不可接受的。

2 解決思想

針對該應(yīng)用場景，在刪除數(shù)據(jù)的時候需要做到：1)外部業(yè)務(wù)對底層數(shù)據(jù)庫的訪問必須要通暢；2)總的刪除操作時間需要盡可能的短。

基于此情況，我們的解決思路為利用rowid，同時結(jié)合分區(qū)思想處理，主要步驟如下：

1)建立臨時表，插入需要刪除的記錄，同時帶出每條記錄的rowid，在做插入的時候，由于對于目標(biāo)表只有讀操作，因此影響是極小的；

2)按照rowid逐一刪除提交，由于是逐一刪除，所以單條刪除操作時間很短，這樣業(yè)務(wù)訪問的等待時間相對于人的感知來說，是可以忽略的，但是整個刪除時間會相對持續(xù)較長；

3)根據(jù)實際業(yè)務(wù)對數(shù)據(jù)庫訪問的實時性要求，可以按照適量的記錄數(shù)進行批量提交（實際情況允許下可以按照分區(qū)），這樣就可以盡量在總刪除時間和業(yè)務(wù)影響之間做最佳平衡。

3 測試驗證

實驗環(huán)境為一臺CPU8個、內(nèi)存64G的服務(wù)器，安裝oracle 11g2版本。

1)構(gòu)造一個表test_t，默認(rèn)不建索引，按照字段ID進行Hash分區(qū)（128個）；

2)構(gòu)造6000多萬數(shù)據(jù)進入test表，由于是Hash分區(qū)，每個分區(qū)記錄數(shù)分布較為均勻，25萬條記錄；

select count(*) from test; ---62976945

3)一次性直接刪除ID從1-1000000之間的所有記錄，并記錄時間T1，如下：

delete * from test_t where id between 1 and 1000000;

觀察到刪除執(zhí)行時長為82.15秒。

4)恢復(fù)test-t表的記錄完整性，取test_t表的ID字段以及增加存儲rowid的字段，創(chuàng)建臨時表test_del，同時插入ID從1-1000000之間的所有記錄；

insert into test_del select t.id,t_rowid from test_t where ID between 1 and 1000000;

檢查臨時表記錄數(shù)，即要刪除的目標(biāo)總數(shù)；

select count(*) from test_del; ---100W

5)按照rowid 逐一刪除，逐一提交的辦法，進行刪除：

declare

begin

for i in (select t_rowid from test_del) loop

delete from test_t where rowed=i.t_rowid;

commit;

end loop;

end;

記錄刪除總時間：157.124秒。

157.124 秒和82.15秒相比，總時間幾乎增加了一倍。但是我們再來看單位時間：157.124/1000000=0.000157124秒，也就是大約0.15毫秒，這個等待時間對于實際業(yè)務(wù)是沒有感知的。

6)按照字段ID進行逐一刪除，逐一提交。測試時，該字段上先建立有效的唯一性索引，如果沒有索引，時間將會極慢，測試發(fā)現(xiàn)沒有索引的情況下，1個小時，只能刪除18萬多條記錄。

declare

begin

for in (select id from test_del) loop

delete from test_t where id=i.id;

commit;

end loop;

end;

記錄總時間為272.892秒，總時間相比rowid模式增加約7～8成。由此估算單批次刪除時間：

272.892 /1000000=0.000272892秒，也就是大約0.27毫秒。這個等待時間對于實際業(yè)務(wù)同樣也幾乎是沒有感知的，但是比采用rowid方式增加不少。

7)按照10000條記錄一批次進行提交（分別測試了1萬、2萬、5萬三種不同提交量，發(fā)現(xiàn)1萬是最優(yōu)的）：

declare

v_cnt number;

begin

for i in (select t_rowid from test_del) loop

delete from test_t where rowed=i.t_rowid;

v_cnt :=v_cnt+1;

if v_cnt＞=20000 then

commit;

end if;

commit;

end loop;

end;

記錄總時間為143.303秒，由此估算單批次刪除時間：143.303/100=1.433秒，這個時間就需要根據(jù)實際業(yè)務(wù)場景判斷是否可以接受。

8)按照128個分區(qū)進行刪除（基于兩個表的分區(qū)是一致的）：

declare

v_sql varchar2(2000);

begin

for i in 0..127 loop

v_sql :=’delete from test_t artition(P’||i||’) a

where exists (select 1 from test_del partition(P’||i||’) b where b.t_rowid=a.rowid)’;

execute immediate v_sql;

commit;

end loop;

end;

記錄執(zhí)行總時間為49.592秒，由此估算單批次刪除時間：49.592 /128=0.388秒

這個間隔時間相比前面批量提交有不小提升，且總時間大大縮短。

4 結(jié)論

刪除方案總刪除時間（秒）單次刪除時間（秒）單次鎖表時長（秒）安全系數(shù)直接全量刪除82.15 N/A 82.15最低按照rowid逐一刪除、逐一提交157.124 0.00015 0.00015最高按照唯一索引字段（ID）逐一刪除、逐一提交272.892 0.00027 0.00027次高按照rowid逐一刪除、批量提交（10000條）143.303 1.433 1.433次低基于rowid按照分區(qū)批量刪除49.592 0.388 0.388中

由上表可以看到，在Oracle數(shù)據(jù)庫中，進行大數(shù)據(jù)量刪除數(shù)據(jù)時，通過rowid查詢目標(biāo)再刪除，會顯著提高效率，同時結(jié)合實際應(yīng)用場景，可以酌情按批次或者分區(qū)進行刪除，最終目標(biāo)是追求刪除總時長最短以及業(yè)務(wù)影響最小兩者之間的平衡。

[1]孫秀玲,姜寶華.基于Oracle RowID實現(xiàn)批量數(shù)據(jù)的分頁下載.企業(yè)技術(shù)開發(fā)[J].2010,29(10).

[2]張新建,李輝,陳水南.Oracle數(shù)據(jù)庫分區(qū)優(yōu)化技術(shù)研究與應(yīng)用.指揮信息系統(tǒng)與技術(shù)[J].2010,01(4).

[3]孫鳳棟.Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程(第2版)[M].北京:電子工業(yè)出版社.

王敏（1984—），女，河南安陽人，碩士、講師，現(xiàn)工作于安陽工學(xué)院。

張捐凈（1985—），女，河南駐馬店人，碩士、講師，現(xiàn)工作于安陽工學(xué)院。