一生可能只有一次耐高,但优化可以不止一次

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 一生可能只有一次耐高,但优化可以不止一次

一生可能只有一次耐高,但优化可以不止一次

最近单位举办了开发者大会,有幸分享了一个话题,《曾经踩过的“坑”-一则应用夜维优化案例》,其实在之前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操作。

林广提出的方案,

  1. 根据A表日期字段,检索出需要删除的主键id,不存中间表了,而是存储于内存变量了。
  2. 使用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:

  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;

  1. 批量执行,总计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,

  1. 检索出需要删除的主表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)); }

  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();

    ...   } ... }

注意:

  1. 两种写法,均使用了绑定变量值。

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. 方案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的个人杂货铺,

一生可能只有一次耐高,但优化可以不止一次 一生可能只有一次耐高,但优化可以不止一次
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 一生可能只有一次耐高,但优化可以不止一次


 上一篇
困扰许久的一个ORA-00060错误解决 困扰许久的一个ORA-00060错误解决
这段时间应用一直被一个诡异的ORA-00060的错误所困扰,众所周知,造成ORA-00060的原因是由于应用逻辑,而非Oracle数据库自己,之所以说诡异(“诡异”可能不准确,只能说这种场景,以前碰见的少,并未刻意关注),是因为这
下一篇 
花有重开日,人无再少年 花有重开日,人无再少年
今天有位朋友,微信群中问eygle曾经讲过的一个SQL语句优化案例,这是2015年的一次Oracle会议上,eygle讲的一个主题,碰巧当时听了这场演讲,回来模拟了整个操作,记录在了博客中,于是搜出来回复了他,也引来了eygle的感慨,转眼