大数量的数据删除的性能问题

• 作者: 三十而立
• 时间：2009年10月09日 9:00:34
• 本文出自 “inthirties（三十而立）”博客，转载请务必注明作者和保留出处http://blog.youkuaiyun.com/inthirties/archive/2009/10/09/4644418.aspx

 

对于大数据量表的数据表的数据删除问题，总是有带来性能上的顾虑的。

 

那么对于这样的对一个大数据量表的大数据量的记录进行删除，我们有什么样的好方法呢。

 

对于这里的删除主要是io和cpu的考验了，而且delete语句也是不能去通过减少log来做的，所以这里没有其他更好的办法了。

 

这里采用的方法，基本上是

 

1.  建新表，插入需要的数据， 删除老表。

 

2.  用sp来做批量删除

 

 

sp的方式，这里摘录一个

 

 

引用:

create or replace procedure delBigTab 
( 
p_TableName in varchar2, 
p_Condition in varchar2, 
p_Count in varchar2 
) 
as 
pragma autonomous_transaction; 
n_delete number:=0; 
begin 
while 1=1 loop 
EXECUTE IMMEDIATE 
'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn' 
USING p_Count; 
if SQL%NOTFOUND then 
exit; 
else 
n_delete:=n_delete + SQL%ROWCOUNT; 
end if; 
commit; 
end loop; 
commit; 
DBMS_OUTPUT.PUT_LINE('Finished!'); 
DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!'); 
end;

 

 

 

引用:

SQL> set timing on 
SQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY; 

MIN(NUMDLFLOGGUID) 
------------------ 
11000000 

Elapsed: 00:00:00.23 
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11100000','10000'); 

PL/SQL procedure successfully completed. 

Elapsed: 00:00:18.54 
SQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY; 

MIN(NUMDLFLOGGUID) 
------------------ 
11100000 

Elapsed: 00:00:00.18 
SQL> set serveroutput on 
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11200000','10000'); 
Finished! 
Totally 96936 records deleted! 

PL/SQL procedure successfully completed. 

Elapsed: 00:00:18.61 
10万记录大约19s 

SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11300000','10000'); 
Finished! 
Totally 100000 records deleted! 

PL/SQL procedure successfully completed. 

Elapsed: 00:00:18.62 
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11400000','10000'); 
Finished! 
Totally 100000 records deleted! 

PL/SQL procedure successfully completed. 

Elapsed: 00:00:18.85 
SQL> 
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 13000000','10000'); 
Finished! 
Totally 1000000 records deleted! 

PL/SQL procedure successfully completed. 

Elapsed: 00:03:13.87 

100万记录大约3分钟 
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 20000000','10000'); 

Finished! 
Totally 6999977 records deleted! 

PL/SQL procedure successfully completed. 

Elapsed: 00:27:24.69 
700万大约27分钟

 

 

可以把自制(pragma autonomous_transaction)事务去掉。 
而且这里没有rollback的处理，只是一个隐含的bug

 

 

• 三十而立 想流泪，流不出；想说苦，欲说还休
• 专业论坛 http://www.inthirties.com
• 技术博客 http://blog.youkuaiyun.com/inthirties
• 个人站点 http://blog.inthirties.com