开发者社区> 稀奇古怪> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

从SQL改写到SQL重写,什么样的SQL才是好SQL?

简介: 作者介绍 黄浩,现任职于中国惠普,从业十年,始终专注于SQL。十年一剑,十年磨砺。3年通信行业,写就近3万条SQL;5年制造行业,遨游在ETL的浪潮;2年性能优化,厚积薄发自成一家。   在生活中,很多时候我们会有这样的体悟:问题要么不出,一旦出现,会像多诺米骨牌一样,会连锁引发诸多相关问题,让我们疲于应付。
+关注继续查看

作者介绍

黄浩,现任职于中国惠普,从业十年,始终专注于SQL。十年一剑,十年磨砺。3年通信行业,写就近3万条SQL;5年制造行业,遨游在ETL的浪潮;2年性能优化,厚积薄发自成一家。

 

在生活中,很多时候我们会有这样的体悟:问题要么不出,一旦出现,会像多诺米骨牌一样,会连锁引发诸多相关问题,让我们疲于应付。SQL优化也是如此,那厢,因一个视图代码变更引发的性能事件(参见案例:SQL Hint都无法解救DB性能时,如何通过视图曲线救国?》还恍若昨日,余尘未落;这厢,与该视图相关的首页加载的性能问题又甚嚣尘上杀伐四起。

 

1  涛声依旧,心有余悸

 

就在距离上次视图优化一个星期的时间,一封“红色”的邮件中的“SQL优化”项格外醒目:


20161214101406212.jpg

 

唯一值得庆幸的是,该性能问题被放在8月版本计划里面:时间还是蛮充裕的。于是我按照自己的节奏展开分析。

 

我找到开发责任人,拿到了SQL:

 

20161214101427983.jpg

20161214101437667.jpg
20161214101623704.jpg
20161214101656110.jpg

 

初一看,122行,不算长,也不短。但是我对SQL中出现的视图对象BAS_PROJECT_ALL_V产生了很大的兴趣。这个视图刚刚完成优化,怎么就又出现了性能问题了呢?看了下执行计划:

 

20161214101736890.jpg
20161214101754637.jpg
20161214102230902.jpg
20161214102253740.jpg
20161214102317453.jpg
20161214102330282.jpg

 

自上而下,体型虽然婀娜妙曼,但是“妙曼”得有些让人眩晕。这都要归功于视图对象BAS_PROJECT_ALL_V,该视图有5个union all,而在该SQL中,又被访问了3次。记得上次案例是由于谓词没有被推入引发的,而看执行计划,视图被访问三次,都没有做谓词推入,我就试着强制谓词推入看看效果,但是即便是谓词推入,问题依旧。因临近下班,也就没有深入分析,计划第二天再看看,反正离8月版本还有3周的时间。

 

2  友谊小船,说翻就翻

 

第二天一过来,因为有来自其他同事的性能问题,我暂时将昨天的性能问题搁在一旁。大概在11点钟的时候,托盘上的espace弹出了消息,发消息的是昨天提供SQL的同事,内容是:昨天的那个性能问题必须要在今天内完成优化。这个消息着实把我“震”住了,不是说好的是8月版本吗?友谊的小船咋说翻就翻呢?

 

原来,同样的性能问题在生产环境也出现了,而且生产用户直接提了一个BUG单,用户很生气,后果很严重从测试人员到生产用户,从UAT到生产,从邮件到BUG单,事态的严重程度已完全超出了当下深圳高温天气的黄色预警级别,以至于我立马放下手头的工作,顶着烈烈炎日,大汗淋淋的赶往“事发现场”—开发责任人所在ODC。

 

因为越是时间紧迫,沟通就越显得紧要,何况昨天初步“目测”,一方面执行计划过于复杂,而代码逻辑似乎又并不简单,因此更需要当面沟通。

 

根据开发人员的描述,这个功能的业务需求很简单,如下:

 

根据登录用户ID,获取该用户对应的所有项目列表,用户的项目列表包含两部分:其一是分配至该用户下的项目列表,其二是该用户所在区域的所有初始化过的项目,如下图所示:

 

20161214102341789.jpg

 

在了解到业务需求后,我开始解读SQL,在解读的过程中不时的向开发人员请教、交流。约莫半小时后,SQL的逻辑框架也逐渐明朗起来,如下:

 

从逻辑示意图中,我们至少有如下两个疑问:

 

  1. 子查询route及T在配置项目列表和区域项目列表中都出现了,是否可以进行合并?

  2. 在“用户区域初始化项目列表”中,表SUROT_T与其他结果集没有任何关联条件,只有UID_C的过滤条件,这意味着会发生笛卡尔积。

 

了解完业务逻辑及SQL代码逻辑结构后,我们不能绕过视图BAS_PROJECT_ALL_V,且看该视图的代码逻辑:

 

20161214102355212.jpg

 

单从视图的数据逻辑看,存在以下问题:

 

  1. 表重复访问,图中黄色底纹和蓝色底纹的表对象都是重复访问;

  2. 除了T2结果集外,其他结果集都都访问了三个以上的表对象,模式都是一样的:以主表LEFT JOIN从表。众所周知,LEFT JOIN在SQL中的功能是获取字段,并没有过滤数据的作用。从LEFT JOIN的这些表获取的字段是否被外层的SQL访问了呢?如果没有被访问,那就意味着,就本案例的SQL而言,这些LEFT JOIN是多余的。

 

3  深入“虎穴”

 

带着上述分析后的问题,我深入分析了代码,确认了如下信息:

 

  1. SQL中的部分代码是可以精简的比如子查询ROUTE及T只是为了获取属性字段,完全可以在得到了所需的PN_C清单后,再与子查询ROUTE及T关联获取。

  2. 视图的代码也可以精简比如在视图T5结果集中访问BPOPT_T表对象获取的字段,在SQL中根本没有访问,也就是说在T5结果集中完全可以不访问该表对象。

 

那么还有个问题,那就是笛卡尔积。而通过分析代码,发现并非没有关联条件,而是将关联条件写到了where过滤位置了,如下:

 

20161214102423753.png

 

基于该逻辑的复杂性,我决定将该过滤条件改写成EXISTS子查询。

 

4  大刀阔斧

 

业务需求了解了,大致的逻辑框架也清楚了,病症病因也定位了,接下来就是该大刀阔斧的进行SQL改写了,改写的过程就很简单了,改写后的SQL:

 

20161214102537377.jpg
20161214102604174.jpg
20161214102628330.jpg
20161214102726500.jpg
20161214102744704.jpg
20161214102801591.jpg
20161214102832278.jpg
20161214102906875.jpg

 

改动点如下:

 

  1. 取消视图BAS_PROJECT_ALL_V,用WITH 子查询替代视图。在with子查询中,根据需求关联表对象,砍掉了无关的表对象;

  2. 在获取区域项目列表的代码里,将LEFT JOIN WHERE改成EXISTS;

  3. 在获取了所有的项目列表后,再关联子查询ROUTE和T,获取项目相关属性信息。

 

修改后的执行计划如下:

 

20161214102936527.jpg
20161214102945456.jpg
20161214102959272.jpg
20161214103018269.jpg
20161214103028698.jpg
20161214103038794.jpg

 

在PL SQL DEV中执行,2.5S左右,看来SQL改写还是收到了成效,我也长长的舒了口气,看窗外,已是夕阳西下,一抹余晖透过玻璃窗投射进来,此刻在呼呼空调室内,丝丝暖意,却全然忘记早上发布的高温黄色预警,虽然已近黄昏,窗外依旧炙热。

 

5  分而治之,争分夺秒

 

第二天,正当我准备整理本次优化案例时,ESPACE弹出了消息,是开发人员发过来的。消息的内容让我立马停止了整理。因为开发人员告诉我,2.5S仍然不能满足需求,需要控制在2S内此时此刻我的心情有些忐忑:根据经验,这0.5S的性能提升,其难度远比从10S优化到3S要大。

 

为了这0.5S,我又重新审视了昨天的优化方案。可以说昨天的优化方案已经对之前的代码结构做了很大程度的解构,但是更多的是“精简”SQL。在数据处理流程上还没有变化,优化前后的数据流程都是先集合再过滤,逻辑图如下:

 

20161214103051672.png

 

再看下执行计划:

 

20161214103106405.png

 

发现在UNION ALL六个来源的WITH子查询时,产生了31M的IO写操作,数据量达到了163K,而SQL最终返回的结果集不到1000条。此时,我看到了希望的曙光。我将数据处理流程方案做了优化,如下图所示:

 

20161214103117711.jpg

 

也就是说,将之前合而治之变更成了分而治之,在原理上显然是等价的。

 

根据分而治之的方案,改写后的SQL见附件:

 

20161214103140180.jpg
20161214103158611.jpg
20161214103223240.jpg
20161214103310423.jpg
20161214103336591.jpg
20161214103412157.jpg
20161214103804913.jpg
20161214103830227.jpg
20161214103852574.jpg
20161214103906435.jpg

 

SQL改写后,怀着虔诚又激动的心情按下了F8,焦急得恍若停滞。1.8S,不容易呀。这0.7S的提升在动辄几秒钟、几分钟的优化空间中,如沧海一粟,但此刻却显得弥足珍贵。

6   后记

 

该案例的优化过程其实就是一个SQL改写的过程,而最终演变成了SQL重写。这回到了一个最古老的问题:什么样的SQL才是好SQL?这个问题很难回答,因为这个问题跟哲学问题“什么样的人才是好人”是一样的。然而,我在长期与SQL为伴的过程中,从开发到优化,发现一个准则:简单即高效。这也合乎现在流行的返璞归真、大道至简的追求。

 

简单,并不是表现在代码量,而更在于SQL代码结构的简明、逻辑处理的简练。所在,在优化过程中,我首先考虑的是SQL自身的优化,也就是通常说的等价改写。我坚信,索引、Hint等技术的应用,应该是基于SQL已经极致化的假设。因为无论是索引,还是hint,在纷繁芜杂、不确定性的环境中,其负面影响也是巨大的。在风起云涌大浪淘沙时还能浪遏飞舟的,唯有“简单”的SQL。

原文发布时间为:2016-12-14

本文来自云栖社区合作伙伴DBAplus

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL调优指南—SQL调优进阶—查询改写与下推
下推是查询改写的一项重要优化,利用PolarDB-X的拆分信息来优化执行计划,使得算子尽量下推以达到提前过滤数据、减少网络传输、并行计算等目的。
0 0
SQL调优指南—SQL调优进阶—查询改写与下推
下推是查询改写的一项重要优化,利用PolarDB-X的拆分信息来优化执行计划,使得算子尽量下推以达到提前过滤数据、减少网络传输、并行计算等目的。
0 0
VS2017调用SQL server 2019 和C++连接语句字符串
VS2017调用SQL server 2019 和C++连接语句字符串
0 0
函数计算 Python 连接 SQL Server
函数计算 Python 连接 SQL Server 自制脑图
0 0
SQL Server 2000 函数使用---CAST 和 CONVERT
日期格式样式,借以将 datetime 或 smalldatetime 数据转换为字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型);或者字符串格式样式,借以将 float、real、money 或 smallmoney 数据转换为字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型)。
0 0
【Sql Server】进阶之行数据转为列显示
在开发系统维护阶段,经常需要进行各种数据统计,各种报表之类的。 这个时候,行数据转数据显示就发挥作用了。 场景:行数据的某列值想作为字段列显示
0 0
【Sql Server】时间转换和查询时间范围查询不正确的原因
最近在做时间方法封装的时候发现了一个问题! 如果sql语句输出的时间字段转为了字符串输出,那么在使用此字段作为时间范围筛选时发现无效了,没法过滤对应的时间范围内记录
0 0
【Sql Server】基础之统计库龄语句,仅作为语句使用
知识点的综合使用 分组、数据转换、Case when then、max、min、count、sum、left join多表关联等知识点
0 0
【Sql Server】sql语句文件组分区函数分组方案对应分区表的简单步骤
本篇文章中,主要讲讲sql server数据库中通过sql语句方式对组分区函数的使用 在实际项目中,sql server数据库中有分区的概念,因为在一个表存在大量数据的情况下,需要通过分区方式保存数据来提供查询性能
0 0
【Sql Server】存储过程通过定时执行添加记录作业
通过上篇了解了什么是存储过程,创建存储过程的方法,以及调用存储过程的方法 本次将通过数据库中的作业功能,进行定时执行存储过程,这样就可以完成我们刚开始假设的场景
0 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
SQL Server 2017
立即下载
MaxCompute SQL介绍
立即下载
Flink SQL in 2020
立即下载