极致之处,精彩无限 - 优化了一半的SQL

编辑手记:RWP(Real World Performance)团队是全球最优秀的性能优化团队,他们的目标在于系统性能千倍的提升。感谢刘永甫专家的授权,他从RWP团队转入售后,多年专注于性能优化。我们将会拣选他在职业生涯中一些经典的优化案例跟大家分享。

某次在给某知名通讯设备供应商做性能优化,快接近尾声的时候,偶然发现一个不是很TOP的TOP sql(一般刘老师会收集AWR 的TOP 50 sql,默认只有大概20个)使用了Hint,而其他SQL基本上都没有使用hint,其中必有隐情。顺手分析一下 :

虽然SQL平均执行时间0.25秒,但是执行次数多,因此也在TOP50之列。

SQL语句

SELECT /*+PUSH_PRED(HS)*/* FROM DMD_BOQ_PLAN_HEADER_T DBPH, DMD_PAYMENT_UNIT_V HS, DMD_PAYMENT_UNIT_CONTROL_T PUC WHERE DBPH.PAYMENT_UNIT_ID = HS.PAYMENT_UNIT_ID AND HS.PAYMENT_UNIT_ID = PUC.PAYMENT_UNIT_ID AND DBPH.BOQ_PLAN_HEADER_ID = :B1;

说明:其中 DMD_PAYMENT_UNIT_V是一个view。如下:

SELECT STAGE_ID  AS PAYMENT_UNIT_ID,......
 FROM HT_STAGES 
UNION ALL
SELECT DBT.CCM_BOQ_ID   AS PAYMENT_UNIT_ID,......
 FROM DMD_BOQ_T    DBT
 WHERE DBT.REGISTER_FLAG ='N';

VIEW使用的两个表转换成的PAYMENT_UNIT_ID字段的对应列(HT_STAGES.STAGE_ID和DMD_BOQ_T.CCM_BOQ_ID),都是选择性很好的列;SQL谓词条件使用的几个字段选择性也都非常好,字段上都有索引。

根据以上信息,这个SQL的执行时间,正常应该在1毫秒左右,而不应该是AWR报告中显示的250毫秒。

先来看执行计划:

时间主要消耗在ID=5的全表扫描上,按照正常的情况,这一步应该是最后完成,而且是应该使用DMD_PAYMENT_UNIT_CONTROL_T表PAYMENT_UNIT_ID字段上的索引。当前因为这两个表之间没有直接关联关系,这一步的操作相当于做了笛卡尔积,这不科学。ID=7的步骤是正确的。

我们再来看看没有使用hint的SQL执行计划:

这个执行计划问题更严重,因为没有做谓词推进(push_pred),view使用的两个表做了全表扫描,原来SQL使用push_pred的hint还是起到了重要的优化效果。只是仍没有解决DMD_PAYMENT_UNIT_CONTROL_T表的全表扫描问题,应该算是一个优化了一半的SQL。

尝试使用更多的hint来调整执行计划:

/*+PUSH_PRED(HS) leading(dbph hs puc) use_nl(hs) use_nl(puc) */ 仍然不起作用。 优化尝试1 改写SQL,强制将DBPH和HS放在一个内联视图里先做join(no_merge不能少),然后再与PUC做join,这个是完全等价的SQL: select * from (SELECT /*+ PUSH_PRED(HS) no_merge*/ hs.PAYMENT_UNIT_ID FROM DMD_BOQ_PLAN_HEADER_T DBPH, DMD_PAYMENT_UNIT_V HS WHERE DBPH.PAYMENT_UNIT_ID = HS.PAYMENT_UNIT_ID AND DBPH.BOQ_PLAN_HEADER_ID = :B1 ) hs1, DMD_PAYMENT_UNIT_CONTROL_T PUC where HS1.PAYMENT_UNIT_ID = PUC.PAYMENT_UNIT_ID;

这样改动后,执行计划就完美了:

这个SQL的执行时间大概就是1ms。

有没有更好的优化方法?经过测试,答案是有的:

优化尝试2

根据等值传递原理 a.id=b.id and b.id=c.id 等价于 a.id=b.id and a.id=c.id

将 HS.PAYMENT_UNIT_ID = PUC.PAYMENT_UNIT_ID

改成 DBPH.PAYMENT_UNIT_ID = PUC.PAYMENT_UNIT_ID

即:

SELECT *

FROM DMD_BOQ_PLAN_HEADER_T DBPH,

DMD_PAYMENT_UNIT_V HS,

DMD_PAYMENT_UNIT_CONTROL_T PUC

WHERE DBPH.PAYMENT_UNIT_ID = HS.PAYMENT_UNIT_ID

AND DBPH.PAYMENT_UNIT_ID = PUC.PAYMENT_UNIT_ID

--AND HS.PAYMENT_UNIT_ID = PUC.PAYMENT_UNIT_ID

AND DBPH.BOQ_PLAN_HEADER_ID = :B1;

经过这样的修改后,不用任何的hint,执行计划都是完美的。

这个案例应该是优化器的考虑不周所致,遇到这种情况,我们就需要考虑通过改写SQL来实现优化的目的。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-05-08

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏andychai

MySQL模糊查询性能优化

根据模糊查找的业务场景,比对一下上面列出的6种条件,如果你的场景是全都要支持,并且是 大用户量, 接口qps高,海量的数据检索量,那就不要在数据库上做任何挣扎了...

3.1K6
来自专栏更流畅、简洁的软件开发方式

为or、in平反——or、in到底能不能利用索引?

  先说一个笑话,作为开场白。俺也换换风格试一试,呵呵。   在以前,有三个书生赶考,在路上遇到了一个算命先生,于是就问算命先生:我们三个人赶考,结果如何呀?...

20710
来自专栏PHP在线

数据库范式那些事

原文出处: 宋沄剑 简介 数据库范式在数据库设计中的地位一直很暧昧,教科书中对于数据库范式倒是都给出了学术性的定义,但实际应用中范式的应用却不甚乐观,这篇文...

3499
来自专栏数据和云

如何编写更好的SQL查询:终极指南(上)

结构化查询语言(SQL)是数据挖掘分析行业不可或缺的一项技能,总的来说,学习这个技能是比较容易的。对于SQL来说,编写查询语句只是第一步,确保查询语句高效并且适...

2876
来自专栏小俊博客

[测评]云迪Host US-LA-SSD KVM Mini_384M VPS测评

最近博主买了KYRAHOST的LA CN2 VPS这款,SSD硬盘,从测试看,硬盘I/O还可以,有500-800左右,据商家说是SSD阵列RAID10,一个月才...

1171
来自专栏数据和云

12C 新特性 | 标量子查询自动转换

优化器是 Oracle 数据库最引人入胜的部件之一,因为它对每一个 SQL 语句的处理都必不可少。优化器为每个 SQL 语句确定最有效的执行计划,这是基于给定的...

3517
来自专栏软件测试经验与教训

测试工程师SQL面试题

测试人员工作在工作中会用到SQL来辅助测试,求职时也常常会在笔试环节遇到各种各样的sql设计题目,张老师整理了一些工作中常用的sql知识点,希望对大家有所帮助。

1K3
来自专栏jouypub

MySQL查询语句优化

在项目中经常和MySQL数据库打交道,写过各种各样的SQL,也遇到过各种问题,针对遇到的各种场景,记录一些解决方案,主要是MySQL索引问题。

631
来自专栏数据库

关于女神SQLite的疑惑(2)

还是女神SQLite的话题,继续讨论有关她的种种常见疑惑。 1.问:女神SQLite是线程安全的吗? 1.答:SQLite是线程安全的,这点确凿无疑。但我要补充...

2028
来自专栏用户画像

mysql查询实例

select Sno as 学号,Sname as 姓名 from student;

1832

扫码关注云+社区