本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer，已开源在github ，欢迎star！
最近单位举办了开发者大会，有幸分享了一个话题，《曾经踩过的“坑”-一则应用夜维优化案例》，其实在之前OCMU的活动上，就讲过这个主题，简介可以参考《》中的主题三，
主题三：应用程序夜维优化案例
简介：某核心系统生产环境，对于历史过期的数据，业务上要求每日清理，开发人 员通过部署java程序，每日定时执行delete，删除N张关联表的历史数据，随着业务量增加，清理程序消耗的时间，逐渐增加，30分钟、2小时、5小 时，15小时，甚至24小时，如何找出主要的矛盾？对应不同的业务阶段，采用何种优化的方法？应用设计上是如何演进？
预期收获：
1. 清理过期数据的程序设计。
2. ORA-01555错误。 3. CF聚簇因子含义。 4. 应用设计的演进历程。单位版本的PPT需要审核，所以不能发布，但是OCMU上的PPT已漂白，可以参考《》。
精简介绍下问题，主表A和子表B，外键关联，A表2000万数据，B表5000万数据，其中A表每日需要删除200万数据(日期存储(-7,2))，B表每日需要删除700-800万数据，
删除逻辑：
1. 主表A删除条件为一个日期字段，每⽇删除7天前的数据。 2. 子表B删除条件为符合(1)要删除的外键id。
创建测试表A和B，建立B表外键关联主表主键id，A表日期字段建立索引，B表外键建立索引，
create table a(id number primary key, a_date date);
create table b(id number primary key, a_id number, name varchar2(1));
alter table b add constraint fk_b_a foreign key(a_id) references a(id);
create index idx_b on b(a_id);
create index idx_a on a(a_date);
创建中间表C以及索引，
create table c(id number, pkid number);
create index idx_c on c(pkid);
根据A表日期检索需要删除的id，将其和rownum存储于C表，
insert into C select p.*, rownum from (select id from A where a_date=to_date(‘xxxx-xx-xx', 'yyyy-mm-dd') order by id) p;
利用C表id作为检索条件，一次检索1万条C表id(例如pkid=0 and pkid10000)，执行B表删除操作，
delete from B where B.a_id in (select id from C where pkid = :1 and pkid :2);
以上是优化后的操作步骤，满足业务需求。
下面我要说的是，会上林广同学，提出了另一种优化的方案，会后我们简单交流了一下，听起来很有意思，为此测试了下。
现在的方案是，
1. 根据A表日期字段，检索出需要删除的主键id，一次性存储于中间表C。
2. 一次从C表检索出1万条记录，使用IN子查询删除B表数据，粗略计算，执行一次会删除4万条B表记录，总计需要执行200次删除操作。其中C表使用索引范围检索，B表也会用上索引，这两个结果集，进行NESTED LOOPS操作。
林广提出的方案，
- 根据A表日期字段，检索出需要删除的主键id，不存中间表了，而是存储于内存变量了。
- 使用delete from B where a_id=(1)中的id，执行一万条提交一次。总计执行800万次SQL，提交800次。
两种方案区别1，
方案1：A表待删除id存储于中间表。
方案2：A表待删除id存储于内存变量。
两种方案区别2，
方案1：B表删除需要和C表进行关联。
方案2：B表删除只执行简单的delete ... where a_id=:1操作。
粗略来看，
方案1执行SQL次数少，一次删除数据多，但每次需要两表关联，执行计划略复杂。
方案2执行SQL次数多，一次删除数据少，但每次只需要简单的delete，执行计划更高效。
可能不同的环境、数据量下，结果会略有偏差，我们利用测试表，从数据上看，这两个方案效率。
基于上面的建表语句，根据生产数据分布的特点，我们模拟测试的数据，A表每天200万数据，一共存储了10天(2000万)，B表每一个a_id前5天对应4条的数据(4000万)，后五天则对应2条数据(2000万)，总计6000万数据量。
SQL select count(*) from a; COUNT(*) ---------- 20000000
SQL select a_date, count(*) from a group by a_date order by a_date; A_DATE COUNT(*) --------- ---------- 01-JAN-17 2000000 02-JAN-17 2000000 03-JAN-17 2000000 04-JAN-17 2000000 05-JAN-17 2000000 06-JAN-17 2000000 07-JAN-17 2000000 08-JAN-17 2000000 09-JAN-17 2000000 10-JAN-17 2000000 10 rows selected.
SQL select count(*) from b; COUNT(*) ---------- 60000000
SQL select a_id, count(*) from b where rownum = 100 group by a_id; A_ID COUNT(*) ---------- ---------- 380 4 393 4 378 4 379 4 386 4 387 4 394 4 372 4 377 4 382 4 390 4
...
我用java-jdbc写了测试的程序，
方案1：
- 事先准备C表，200万的纪录，
insert into C select p.*, rownum from (select id from A where
a_date=to_date(‘2017-01-01’, ‘yyyy-mm-dd’) order by id) p;
- 批量执行，总计200次删除语句，
String sql = “delete from b where b.a_id in (select id from c where pkid=? and pkid?)”;
…
for (int i = 0; i 200; i++) {
pstmt.setInt(1, 10000*i); pstmt.setInt(2, 10000*(i+1)); j = pstmt.executeUpdate();
...}
方案2，
- 检索出需要删除的主表id，200万的纪录，利用setFetchSize设置fetch size(原因参考《》)，存储List变量，
String sql = “select id from A wherea_date=to_date(‘2017-01-01’, ‘yyyy-mm-dd’)”;
...
pstmt.setFetchSize(10000); rs = pstmt.executeQuery();
while (rs.next()) { list.add(rs.getInt(1)); }
...while (rs.next()) { list.add(rs.getInt(1)); }
- 执行删除，单次delete一条记录，执行1万次语句，提交一次，
con.setAutoCommit(false);
sql = “delete from b where b.a_id = ?”;
...
for (int i = 0; i list.size(); i++) {pstmt.setInt(1, list.get(i)); pstmt.addBatch(); if (i % 10000 == 0) { pstmt.executeBatch(); con.commit();
... } ... }注意：
- 两种写法，均使用了绑定变量值。
SQL1：delete from b where b.a_id in (select id from c where pkid=:1 and pkid:2)
SQL2：delete from b where b.a_id = :1
- 方案1创建C表约需要几秒钟，方案2存储200万变量，约需要10秒，由于考量的是两种删除，因此上述C表和变量用时，忽略不计。
测试如下，
方案1删除800万B表的记录，总计用时139.611秒，单次执行(SQL1)平均用时698毫秒。
方案2删除800万B表的记录，总计用时174.760秒，执行提交1万条(SQL2)平均用时873.8毫秒，执行1条DELETE语句平均用时0.087毫秒。
批量删除(方案1)，比单次删除(方案2)，快30秒左右，从数据上来看，并不明显，但是可以说明一些问题。
方案1执行一次DELETE语句的操作，会和数据库交互一次，一次删除4万条B表记录，总计需要交互200次，但一次执行DELETE的成本高。
方案2一次DELETE语句的操作，会和数据库交互一次，一次只能删除1条B表的记录，交互800万次，总计提交需要800次，一次执行DELETE成本低。
简而言之，就是数据库交互执行SQL次数，和执行一次DELETE语句成本，两者之间的博弈，谁的效率占据了上风，谁的方案效率就越高。
当前数据量下，方案1的效率高于方案2，就是说一次删除更多数据，但和数据库交互次数少，要比一次删除小量数据，但和数据库交互次数多，效率更高。
由于生产环境，B表记录平均行长，要远高于测试的数据(仅有id和a_id)，因此每次和数据库的交互，网络传输的数据量要高，累积起来的影响就会明显，方案1和方案2之间的差距，可能就会更明显，根据实验结论进行的猜测。
当然若删除的数据量小些，也可能会出现，累积单次DELETE的SQL语句成本，效率高于批量DELETE的SQL语句成本，方案2效率会高于方案1。
从上面测试的数据来看，即使方案1效率高，但方案2的效率还是可以接受，换句话说，针对不同的业务场景、环境以及数据量，可能会有不同的优化方案，有时未必需要最优的方案，但一定需要最适合的方案，毕竟技术是为业务服务的，满足业务需求，是最基本的要求，当然对于技术控来说，可能会追求极致，条件若是允许，当然需要鼓励。
感谢林广老师，各位有其他的推荐方案，欢迎随时提出，一起讨论学习。
如果您觉得本文有帮助，欢迎关注转发：bisal的个人杂货铺，
