一次夜维SQL的性能优化

最近单位搬家,从国家会议中心,搬往空气清新的顺义后沙峪,搬迁之前的完结上线中,碰见了一些棘手的问题,有一些值得借鉴的地方。

十多年来,创新互联公司不忘初心,以网站建设互联网行业服务标杆为目标,不断提升技术设计服务水平,帮助客户在互联网推广自己的产品、服务和品牌,为客户创造价值从而实现自身价值!

 

这是一个夜维程序的优化。这个夜维的目的,是每日删除30+张表历史数据,其中的主要矛盾,是一张5000万的表,以下仅针对这张表的优化,做下介绍,大致经历了几个阶段,

 

阶段一:


顺序删除每张表,例如表A和B,B为A表子表,由于表有主外键关系,因此需要先删B表,再要删除A,删除条件是从A表检索出历史过期的数据对应的记录id,用B表p_id和A表id关联,执行删除,id字段是A表主键,使用序列赋值,p_id、id和c_date均有索引定义,A表总数据量2000万,A表每日待删除数据量为200万,B表总数据量5000万,B表每日待删除数据量约为800万,为了减小UNDO和REDO压力,需要批量提交,SQL类似如下,

delete from B where B.p_id in (select id from A where c_date<=to_date('xxxx-xx-xx', 'yyyy-mm-dd')) and rownum < ?;

 

一次删除10000条(?值为10000),由于c_date(只有日期无时间,只保存10天)区分度低,因此子查询用了全表扫描,删除B表需要执行200次SQL语句,即200次20000万A表的全表扫描,业务量初期数据有限,A表数据量处于百万级,机器配置较高,因此没有问题,但随着数据量的增加,执行时间变久,毋庸置疑。


 

阶段二:


由于业务量增加,数据库积累的数据有一定量,导致夜维执行时间越来越久,需要进行优化。

 

首先子查询全表扫描,不可避免,为了提升效率,一种思路就是少做事。200次2000万A表的全表扫描操作,是否可以避免?

 

既然每次需要删除的是,2000万中的200万,可以先将这200万存入中间表,即使全表扫描,只扫描200万,要比扫描2000万数据要强些,中间表C只有一个字段,用于存储待删除的id标记,

create table C (id number);

 

insert into C select id from A where c_date<=to_date('xxxx-xx-xx', 'yyyy-mm-dd');

 

然后用中间表,和B表关联,

delete from B where B.p_id in (select id from C) and rownum <= ?;

 

?值为10000,代表每次删除1万。同时从运行同事了解,夜维执行期间,数据库负载不高,因此可以充分利用资源,数据库服务器80C128G,应用开启多线程,除了主子表外,其他表实现并发删除操作。


 

阶段三:


随着业务量逐渐增加,上面的机制仍不能满足要求,而且有几次夜维执行时间,甚至超了20小时,奇怪的是,夜维某些天正常,可能5、6个小时就能完成,某些天就会出现超长,甚至有一次第二天即将执行,然而第一天夜维还未完成,为了不影响执行,手工kill了旧进程。

 

回来再看这条SQL,其中子查询返回的记录,大约200万左右数据,B表和子查询关联,得到所有符合条件的记录,大约800万,即1:4的关系,1条C表的id值,对应B表4条记录,为了批量提交,每次只删除这800万中的1万,

delete from B where B.p_id in (select id from C) and rownum <= ?;

 

这几次超长执行,从数据库层面看,反映的现象就是物理读超高,例如之前这条SQL物理读,值是3000,这几次值就是10000。

 

由于子查询肯定全表扫描,每次执行,都要读取200万数据,第一次执行SQL语句,就需要从磁盘文件读取,放入buffer cache,此时消耗物理读,若这个时间段内,对于数据库缓存消耗高,例如其他大表的频繁加载,就会增加buffer cache的age out刷出操作,进而可能出现,第二次执行这条SQL语句,这200万数据部分、甚至全部,需要从磁盘再次读取,如果待删除800万,一次删除1万记录,就需要执行800次,极端情况,就需要重复加载800次200万数据,平均下来,单次物理读高,就可想而知了。

 

为了缓解,打算这么调整,为C表增加pkid字段,用于存储rownum,如下示例,id仍是待删除的条件值,pkid则为A表id对应的rownum,其目的就是为了,C表每个id都对应一个编号,且这个编号是有序递增,

create table C (id number, pkid number);

 

create index idx_c_01 on c(pkid);

 

insert into C select id, rownum from A where c_date<=to_date('xxxx-xx-xx', 'yyyy-mm-dd');

 

删除B表的时候,首先程序中循环,以id为条件,一次检索1万记录,例如第一次是"where pkid > 0 and pkid < 10001",第二次是"where pkid > 10000 and pkid < 20001",即将B表每次删除1万条,批量删除的逻辑,推至内层循环,

delete from B where B.p_id in (select id from C where pkid > ? and pkid < ?);

 

按照业务评估,C表1个id,对应B表4条记录,因此子查询1万,B表删除4万,虽然一次批量删除较之前,有所增加,但看着是可控,而且可以避免,每次读取C表所有200万数据。


 

阶段四:


可是这种修改,当晚执行,就出现了问题,夜维日志报错,ORA-01555,

一次夜维SQL的性能优化

 

从alert日志中,确认就是这条SQL,导致了这个ORA-01555错误,

ORA-01555 caused by SQL statement below (SQL ID: xxxxxxxxxx, Query Duration=11500 sec, SCN: 0x0001.f10b2hk7):

delete from B where B.p_id in (select id from C where pkid > :1 and pkid < :2);

 

ORA-01555错误,快照太旧,是Oracle一个非常经典的错误号,简单一句话介绍,我觉得就是“DML语句需要用UNDO记录的数据找到前镜像时,该记录在UNDO中已经被覆盖,导致无法利用UNDO中的记录完成一致性读”,我曾写了一篇小文介绍(http://blog.csdn.net/bisal/article/details/18187635)。

 

再看SQL语句,说明执行delete操作,时间太久,导致期间使用的UNDO前镜像,已经被其他事务覆盖了,因此直接报错ORA-01555。而且怀疑,这条SQL语句,可能没有一次执行成功的,由于使用了绑定变量,缓存未被刷新,检索出来,报错SQL使用的绑定变量值,正是第一次执行需要的0-10000,

(提取方法可参考《 一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法 》)

一次夜维SQL的性能优化

 

要了解为什么执行慢,就需要看一下,SQL语句的执行计划,此处屏蔽了表名,解释一下,

(1) 首先对表B执行全表扫描。

(2) 对表A执行了索引扫描。

(3) 然后以(1)结果集为驱动表,和(2)结果集进行NESTED LOOPS SEMI连接操作。

一次夜维SQL的性能优化一次夜维SQL的性能优化

 

SQL执行慢原因基本清楚了,表B有5000万的数据,表A总计200万数据,1次检索1万数据,相当于执行200次5000万数据的全表扫描再和1万数据进行NESTED LOOPS SEMI表连接操作,进而删除B表数据。

 

这比800次扫描200万的数据,有过之而无不及,不报ORA-01555的错误才怪,

delete from B where B.p_id in (select id from C) and rownum <= ?;

 

问题来了,B表的p_id字段有索引,查看统计信息,无论是表,还是索引,都是每晚22:00,由自动采集任务更新了,夜维执行时间,每日00:30开始执行,可以说每次用的,都是最新的统计信息,这次调整,原义是限制内层数据量,为了减少数据,然后利用B表索引,为何没用上p_id索引?

 

难道子查询1次1万,有些过了?

 

用二分法尝试,0-5000、0-2500、...、甚至使用0-10,都比较慢,没用索引。

 

碰巧测试了下,1910000-1920000区间,这条SQL执行迅速,看其对应的执行计划,正是我们需要的,

(1) 索引范围扫描表C。

(2) 索引范围扫描表B。

(3) (1)和(2)进行NESTED LOOPS连接操作。

一次夜维SQL的性能优化

 

为何这一区间,就可以使用B表的索引,0-10000区间就不能使用?

 

有的同事提问:

“C表id如果排序,和B表中顺序一致的话,会不会有影响?就是在插入C表id前,按照id排下序。”

 

此时看下pkid=1910000-1920000对应的C表id记录,可以发现,基本都是有序,而且间距较小,例如

1000001

1000003

1000010

1000011

...

 

再看一下pkid=1-10000区间,有些是无序的,而且差值较大,例如,

1000021

1000210

1000020

1001000

1000002

...

 

之前我们说了,C表的id来自于A表的主键序列,意味着有序递增,换句话说,id越近的记录数,就越可能位于同一个数据块,id越远的记录数,就越可能不在同一个数据块,区别就是,例如前者读取两个记录,可能只需要1次IO,后者可能需要2次IO,这很像索引的聚簇因子,即索引键值对应的数据记录,在数据块中存储的越有序,clustering factor的值越低,计算索引扫描的成本值,就会越低,此时认为索引扫描更高效,

一次夜维SQL的性能优化

 

C表中id列越有序,对应于表B记录,就越可能位于相同数据块,消耗更小IO操作,因此此时的焦点,就在于如何让C表id有序?

 

之前C表数据用如下语句,

insert into C select id, rownum from A where c_date<=to_date('xxxx-xx-xx', 'yyyy-mm-dd');

 

由于从表A检索,未指定任何order by排序,因此默认会按照数据,在数据块中的排序顺序,进行读取,无法保证有序。此时我们增加order by,让其按照id顺序进行读取,就可以保证表C中id有序,

insert into C select p.*, rownum from (select id from A where c_date<=to_date('xxxx-xx-xx', 'yyyy-mm-dd') order by id) p;

细心的朋友可能注意到,order by id是否主键,对于rownum取值的顺序,可能会有影响。

 

删除语句不变,

delete from B where B.p_id in (select id from C where pkid > ? and pkid < ?);

 

但此时任何区间,都可以按照上面,正确的执行计划,进行删除操作,

一次夜维SQL的性能优化

 

这两天执行夜维,基本保持2.5小时左右用时,这张总计5000万数据量的B表,800万/日删除用时,45分钟左右,一下从主要矛盾,变为次要矛盾了。


 

问题解决过程,属于团队的智慧,感谢开发团队的山山、运维团队的力伟、运行团队的健哥、亚伟和albert兄。

 

总结:

1. 有人曾说,好架构不是设计出来的,而是演进出来的,对于某些数据库开发来说,同样适用,不同的方案在不同阶段,适用程度不同,例如本文示例。

2. 但是从某一方面来讲,这种性能隐患,又是可以设计,可以避免,比如大表的全表扫描,如果开始不考虑,毋庸置疑,就是会随着数据量的增加,产生影响,可以看出,逻辑设计,以及SQL审核,在数据库开发工作中的重要。

3. 出现SQL性能问题,首先要看的就是执行计划,当然你要知道,如何找出真实的执行计划,如何找出绑定变量值,可能还需要看10053的trace文件,这些常用知识点,可能未必记得,但用的时候知道从何检索,Oracle官方文档、Google等等,就可以了,之前曾写过一些小文,仅供参考,

一个执行计划异常变更的案例 - 前传

一个执行计划异常变更的案例 - 外传之绑定变量窥探

一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法

rolling invalidation对子游标产生的影响

一个执行计划异常变更的案例 - 外传之聚簇因子(Clustering Factor)

一个执行计划异常变更的案例 - 外传之查询执行计划的几种方法

一个执行计划异常变更的案例 - 外传之AWR

一个执行计划异常变更的案例 - 外传之ASH

一个执行计划异常变更的案例 - 外传之SQL AWR

一个执行计划异常变更的案例 - 外传之直方图

一个执行计划异常变更的案例 - 外传之SQL Profile(上)

一个执行计划异常变更的案例 - 外传之SQL Profile(下)

一个执行计划异常变更的案例 - 正传


文章题目:一次夜维SQL的性能优化
网址分享:http://myzitong.com/article/ipsoos.html