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

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

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

基于大數(shù)據(jù)的業(yè)務(wù)應(yīng)用，在日常管理中，如何能最有效的對垃圾數(shù)據(jù)進(jìn)行刪除處理，需要同時(shí)考慮刪除時(shí)長以及業(yè)務(wù)影響兩個(gè)維度。本文通過做一些分析和比較，證明在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ù)，同時(shí)業(yè)務(wù)又要頻繁的訪問數(shù)據(jù)，數(shù)據(jù)訪問的質(zhì)量將直接影響用戶體驗(yàn)和業(yè)務(wù)運(yùn)營。但是另一方面，數(shù)據(jù)庫在業(yè)務(wù)運(yùn)營過程中，又不可避免的產(chǎn)生大量無用垃圾數(shù)據(jù)。這些數(shù)據(jù)有時(shí)候會(huì)影響正常業(yè)務(wù)運(yùn)營，需要及時(shí)刪除清理。

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

2 解決思想

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

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

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

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

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

3 測試驗(yàn)證

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

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

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

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

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

delete * from test_t where id between 1 and 1000000;

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

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

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

檢查臨時(shí)表記錄數(shù)，即要?jiǎng)h除的目標(biāo)總數(shù)；

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

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

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;

記錄刪除總時(shí)間：157.124秒。

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

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

declare

begin

for in (select id from test_del) loop

delete from test_t where id=i.id;

commit;

end loop;

end;

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

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

7)按照10000條記錄一批次進(jìn)行提交（分別測試了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;

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

8)按照128個(gè)分區(qū)進(jìn)行刪除（基于兩個(gè)表的分區(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í)行總時(shí)間為49.592秒，由此估算單批次刪除時(shí)間：49.592 /128=0.388秒

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

4 結(jié)論

刪除方案總刪除時(shí)間（秒）單次刪除時(shí)間（秒）單次鎖表時(shí)長（秒）安全系數(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ù)庫中，進(jìn)行大數(shù)據(jù)量刪除數(shù)據(jù)時(shí)，通過rowid查詢目標(biāo)再刪除，會(huì)顯著提高效率，同時(shí)結(jié)合實(shí)際應(yīng)用場景，可以酌情按批次或者分區(qū)進(jìn)行刪除，最終目標(biāo)是追求刪除總時(shí)長最短以及業(yè)務(wù)影響最小兩者之間的平衡。

[1]孫秀玲,姜寶華.基于Oracle RowID實(shí)現(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é)院。