基于Oracle的大數據量刪除操作性能分析

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

基于Oracle的大數據量刪除操作性能分析

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

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

垃圾數據；刪除；Rowid

1 問題提出

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

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

2 解決思想

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

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

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

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

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

3 測試驗證

實驗環境為一臺CPU8個、內存64G的服務器，安裝oracle 11g2版本。

1)構造一個表test_t，默認不建索引，按照字段ID進行Hash分區（128個）；

2)構造6000多萬數據進入test表，由于是Hash分區，每個分區記錄數分布較為均勻，25萬條記錄；

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

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

delete * from test_t where id between 1 and 1000000;

觀察到刪除執行時長為82.15秒。

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

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

檢查臨時表記錄數，即要刪除的目標總數；

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毫秒，這個等待時間對于實際業務是沒有感知的。

6)按照字段ID進行逐一刪除，逐一提交。測試時，該字段上先建立有效的唯一性索引，如果沒有索引，時間將會極慢，測試發現沒有索引的情況下，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毫秒。這個等待時間對于實際業務同樣也幾乎是沒有感知的，但是比采用rowid方式增加不少。

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

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秒，這個時間就需要根據實際業務場景判斷是否可以接受。

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

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;

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

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

4 結論

刪除方案總刪除時間（秒）單次刪除時間（秒）單次鎖表時長（秒）安全系數直接全量刪除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按照分區批量刪除49.592 0.388 0.388中

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

[1]孫秀玲,姜寶華.基于Oracle RowID實現批量數據的分頁下載.企業技術開發[J].2010,29(10).

[2]張新建,李輝,陳水南.Oracle數據庫分區優化技術研究與應用.指揮信息系統與技術[J].2010,01(4).

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

王敏（1984—），女，河南安陽人，碩士、講師，現工作于安陽工學院。

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