专栏首页bisal的个人杂货铺一次夜维SQL的性能优化

一次夜维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,

从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语句的执行计划,此处屏蔽了表名,解释一下,

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

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

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

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连接操作。

为何这一区间,就可以使用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的值越低,计算索引扫描的成本值,就会越低,此时认为索引扫描更高效,

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

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

这两天执行夜维,基本保持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(下)

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • connect by超乎你想象

    SQL中的connect by主要用在层级关系的查询,乍看确实可能有些绕,但在某些场景下,确实方便,语法格式如下,

    bisal
  • select from update row的实现

    DTCC大会上,阿里江疑的演讲中提到一个:select from update hot row;

    bisal
  • 一道SQL考题的思考

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。

    bisal
  • 面试官:mybatis中#{ }和${ }的区别

    同样可以根据id查询出正确的数据。那么有人可能会说,#{ } 和 ${ }不是一样吗?二者有什么区别呢?

    苏三说技术
  • 私信基本功能数据库设计

    上一篇写了基于resin4.0+websocket实现私信功能服务端消息推送文章,趁热打铁,在写一篇关于私信功能的数据库设计文章,非代码篇,希望想对第一次做设计...

    哎_小羊
  • mongoose实现批量删除的api/方法

    蓓蕾心晴
  • Bypass 360主机卫士SQL注入防御(多姿势)

    在服务器客户端领域,曾经出现过一款 360 主机卫士,目前已停止更新和维护,官网都打不开了,但服务器中依然经常可以看到它的身影。

    信安之路
  • 面试题|无索引如何删除亿级数据?

    存在索引的情况下就比较简单,直接利用索引进行删除,写一个for 循环语句 每次删除500行,每次判断delete 影响的行数可以累加计算删除了多少行,直到删除结...

    用户1278550
  • springmvc实例之修改雇员相关信息(四)

    首先是在EmployeeHandler.java中编写toEditEmployeePage方法:

    绝命生
  • 约束

    一 介绍 约束条件与数据类型的宽度一样,都是可选参数 作用:用于保证数据的完整性和一致性 主要分为: PRIMARY KEY (PK) 标识该字段为该表的...

    用户1214487

扫码关注云+社区

领取腾讯云代金券