【Oracle/Java】批量删除16张十万数据的表 单线程耗时45秒 多线程耗时38秒

package com.hy.delete.singlethread;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import org.apache.log4j.Logger;import com.hy.DBParam;/** * 多表单线程删除 * Single thread table deleter * @author 逆火 * * 2019年11月17日 上午8:42:41 */public class BatchDeleter { private static Logger log = Logger.getLogger(BatchDeleter.class); // Commit size private static final int commitSize=10000; private String[] tablenames= { "TestTB01", "TestTB02", "TestTB03", "TestTB04", "TestTB05", "TestTB06", "TestTB07", "TestTB08", "TestTB09", "TestTB10", "TestTB11", "TestTB12", "TestTB13", "TestTB14", "TestTB15", "TestTB16", }; /** * 批量插入 */ public void batchDelete(String expiredDate) { Connection conn = null; Statement stmt = null; try{ Class.forName(DBParam.Driver).newInstance(); conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd); stmt = conn.createStatement(); System.out.println("Begin to access "+DBParam.DbUrl+" as "+DBParam.User+"..."); int index=1; for(String table:tablenames) { int total=delete(index,table,expiredDate,conn,stmt); log.info("#"+index+" "+total+" records deleted from table:'"+table+"'."); index++; } } catch (Exception e) { e.printStackTrace(); } finally { try { stmt.close(); conn.close(); } catch (SQLException e) { log.error("Can't close stmt/conn because of " + e.getMessage()); } } } /** * change seconds to DayHourMinuteSecond format * @param stratMs * @param endMs * @return */ private static String sec2DHMS(long stratMs,long endMs) { String retval = null; long secondCount=(endMs-stratMs)/1000; long days = secondCount / (60 * 60 * 24); long hours = (secondCount % (60 * 60 * 24)) / (60 * 60); long minutes = (secondCount % (60 * 60)) / 60; long seconds = secondCount % 60; if (days > 0) { retval = days + "d" + hours + "h" + minutes + "m" + seconds + "s"; } else if (hours > 0) { retval = hours + "h" + minutes + "m" + seconds + "s"; } else if (minutes > 0) { retval = minutes + "m" + seconds + "s"; } else { retval = seconds + "s"; } return retval; } /** * 按日期删一张表的记录 * @param tableIndex * @param table * @param expiredDate * @param conn * @param stmt * @throws SQLException */ private int delete(int tableIndex,String table,String expiredDate,Connection conn,Statement stmt) throws SQLException { int totalDeleted=0; int expiredCount=0; do { String sql="delete from "+table+" WHERE CREATEDTIME < to_date('"+expiredDate+"','yyyy-MM-dd') and rownum<'"+commitSize+"' "; int deleted=stmt.executeUpdate(sql); //log.info("#"+tableIndex+" "+deleted+" records deleted from table:'"+table+"'."); totalDeleted+=deleted; expiredCount=queryExpiredCount(table,expiredDate,stmt); }while(expiredCount>0); return totalDeleted; } /** * 查询过期记录数量 * @param table * @param expiredDate * @param conn * @param stmt * @return * @throws SQLException */ private int queryExpiredCount(String table,String expiredDate,Statement stmt) throws SQLException { String sql="SELECT COUNT (*) as cnt FROM "+table+" WHERE CREATEDTIME < to_date('"+expiredDate+"','yyyy-MM-dd') and rownum<10 "; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { int count = rs.getInt("cnt"); return count; } return 0; } public static void main(String[] args) { long startTime = System.currentTimeMillis(); BatchDeleter bd=new BatchDeleter(); bd.batchDelete("2019-07-17"); long endTime = System.currentTimeMillis(); log.info("Time elapsed:" + sec2DHMS(startTime,endTime) ); }}

Begin to access jdbc:oracle:thin:@127.0.0.1:1521:orcl as ufo...2019-11-17 09:40:44,951 INFO[main]-#1 83077 records deleted from table:'TestTB01'.2019-11-17 09:40:46,165 INFO[main]-#2 83077 records deleted from table:'TestTB02'.2019-11-17 09:40:49,874 INFO[main]-#3 83077 records deleted from table:'TestTB03'.2019-11-17 09:40:53,271 INFO[main]-#4 83077 records deleted from table:'TestTB04'.2019-11-17 09:40:56,728 INFO[main]-#5 83077 records deleted from table:'TestTB05'.2019-11-17 09:40:59,420 INFO[main]-#6 83077 records deleted from table:'TestTB06'.2019-11-17 09:41:02,766 INFO[main]-#7 83077 records deleted from table:'TestTB07'.2019-11-17 09:41:05,983 INFO[main]-#8 83077 records deleted from table:'TestTB08'.2019-11-17 09:41:08,704 INFO[main]-#9 83077 records deleted from table:'TestTB09'.2019-11-17 09:41:11,827 INFO[main]-#10 83077 records deleted from table:'TestTB10'.2019-11-17 09:41:14,895 INFO[main]-#11 83077 records deleted from table:'TestTB11'.2019-11-17 09:41:18,155 INFO[main]-#12 83077 records deleted from table:'TestTB12'.2019-11-17 09:41:20,941 INFO[main]-#13 83077 records deleted from table:'TestTB13'.2019-11-17 09:41:23,992 INFO[main]-#14 83077 records deleted from table:'TestTB14'.2019-11-17 09:41:25,462 INFO[main]-#15 83077 records deleted from table:'TestTB15'.2019-11-17 09:41:28,066 INFO[main]-#16 83077 records deleted from table:'TestTB16'.2019-11-17 09:41:28,068 INFO[main]-Time elapsed:45s

package com.hy.delete.multithread;import java.text.MessageFormat;import java.util.ArrayList;import java.util.List;import org.apache.log4j.Logger;/** * 批量删表管理者 * @author 逆火 * * 2019年11月17日 上午9:54:46 */public class DeleteManager { private static Logger log = Logger.getLogger(DeleteManager.class); private String[] tablenames= { "TestTB01", "TestTB02", "TestTB03", "TestTB04", "TestTB05", "TestTB06", "TestTB07", "TestTB08", "TestTB09", "TestTB10", "TestTB11", "TestTB12", "TestTB13", "TestTB14", "TestTB15", "TestTB16", }; private long startTime;// Start time private List<DeleteJobInfo> jobInfos;// delete table informations /** * 批量插入 */ public void batchDelete(String expiredDate) { startTime=System.currentTimeMillis(); jobInfos=new ArrayList<DeleteJobInfo>(); int index=1; for(String table:tablenames) { DeleteThread dt=new DeleteThread(index,table,expiredDate,this); dt.start(); index++; } } /** * Thread report manager "job done." * @param tbSN * @param tableName * @param timeElasped */ public void reportFinished(String tbSN,String tableName,String timeElasped,String deleted) { jobInfos.add(new DeleteJobInfo(tbSN,tableName,timeElasped,deleted)); if(jobInfos.size()==tablenames.length) { long endTime = System.currentTimeMillis(); log.info(">>> Delete jobs finished.( time elapsed： " + sec2DHMS(startTime,endTime)+") <<<"); log.info("------------ Details ------------"); log.info("#,table,deleted,time elapsed"); for(DeleteJobInfo jobInfo:jobInfos) { String raw="{0},{1},{2}"; Object[] arr={jobInfo.tbSn,jobInfo.tableName,jobInfo.deleted,jobInfo.timeElapsed}; String line=MessageFormat.format(raw, arr); log.info(line); } log.info("------------ Details ------------"); }else { log.info(jobInfos.size()+" deleters completed their jobs."); } } /** * 成员内部类，用来做数据结构 * 参考：https://blog.csdn.net/weixin_42762133/article/details/82890555 * @author 逆火 * * 2019年11月17日 上午9:22:04 */ protected static final class DeleteJobInfo{ String tbSn;// 表序号 String tableName;// 表名 String timeElapsed;// 耗时 String deleted;// count of deleted records public DeleteJobInfo(String tbSn,String tableName,String timeElapsed,String deleted) { this.tbSn=tbSn; this.tableName=tableName; this.timeElapsed=timeElapsed; this.deleted=deleted; } } /** * change seconds to DayHourMinuteSecond format * @param stratMs * @param endMs * @return */ private static String sec2DHMS(long stratMs,long endMs) { String retval = null; long secondCount=(endMs-stratMs)/1000; long days = secondCount / (60 * 60 * 24); long hours = (secondCount % (60 * 60 * 24)) / (60 * 60); long minutes = (secondCount % (60 * 60)) / 60; long seconds = secondCount % 60; if (days > 0) { retval = days + "d" + hours + "h" + minutes + "m" + seconds + "s"; } else if (hours > 0) { retval = hours + "h" + minutes + "m" + seconds + "s"; } else if (minutes > 0) { retval = minutes + "m" + seconds + "s"; } else { retval = seconds + "s"; } return retval; } public static void main(String[] args) { DeleteManager dm=new DeleteManager(); dm.batchDelete("2019-07-17"); }}

package com.hy.delete.multithread;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import org.apache.log4j.Logger;import com.hy.DBParam;public class DeleteThread extends Thread { private static Logger log = Logger.getLogger(DeleteThread.class); // Commit size private static final int commitSize=10000; private int tableIndex;// table serial number private String table;// table will be deleted private String expiredDate;// expired date private DeleteManager menager; // reference to manager public DeleteThread(int tableIndex,String table,String expiredDate,DeleteManager menager) { this.tableIndex=tableIndex; this.table=table; this.expiredDate=expiredDate; this.menager=menager; } public void run() { Connection conn = null; Statement stmt = null; try{ long startTime = System.currentTimeMillis(); Class.forName(DBParam.Driver).newInstance(); conn = DriverManager.getConnection(DBParam.DbUrl, DBParam.User, DBParam.Pswd); stmt = conn.createStatement(); log.info("Begin to access "+DBParam.DbUrl+" as "+DBParam.User+"..."); int total=delete(tableIndex,table,expiredDate,conn,stmt); log.info("#"+tableIndex+" "+total+" records deleted from table:'"+table+"'."); long endTime = System.currentTimeMillis(); String timeElasped=sec2DHMS(startTime,endTime); menager.reportFinished(String.valueOf(tableIndex), table, timeElasped,String.valueOf(total)); } catch (Exception e) { e.printStackTrace(); } finally { try { stmt.close(); conn.close(); } catch (SQLException e) { log.error("Can't close stmt/conn because of " + e.getMessage()); } } } /** * 按日期删一张表的记录 * @param tableIndex * @param table * @param expiredDate * @param conn * @param stmt * @throws SQLException */ private int delete(int tableIndex,String table,String expiredDate,Connection conn,Statement stmt) throws SQLException { int totalDeleted=0; int expiredCount=0; do { String sql="delete from "+table+" WHERE CREATEDTIME < to_date('"+expiredDate+"','yyyy-MM-dd') and rownum<'"+commitSize+"' "; int deleted=stmt.executeUpdate(sql); //log.info("#"+tableIndex+" "+deleted+" records deleted from table:'"+table+"'."); totalDeleted+=deleted; expiredCount=queryExpiredCount(table,expiredDate,stmt); }while(expiredCount>0); return totalDeleted; } /** * 查询过期记录数量 * @param table * @param expiredDate * @param conn * @param stmt * @return * @throws SQLException */ private int queryExpiredCount(String table,String expiredDate,Statement stmt) throws SQLException { String sql="SELECT COUNT (*) as cnt FROM "+table+" WHERE CREATEDTIME < to_date('"+expiredDate+"','yyyy-MM-dd') and rownum<10 "; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { int count = rs.getInt("cnt"); return count; } return 0; } /** * change seconds to DayHourMinuteSecond format * @param stratMs * @param endMs * @return */ private static String sec2DHMS(long stratMs,long endMs) { String retval = null; long secondCount=(endMs-stratMs)/1000; long days = secondCount / (60 * 60 * 24); long hours = (secondCount % (60 * 60 * 24)) / (60 * 60); long minutes = (secondCount % (60 * 60)) / 60; long seconds = secondCount % 60; if (days > 0) { retval = days + "d" + hours + "h" + minutes + "m" + seconds + "s"; } else if (hours > 0) { retval = hours + "h" + minutes + "m" + seconds + "s"; } else if (minutes > 0) { retval = minutes + "m" + seconds + "s"; } else { retval = seconds + "s"; } return retval; }}