技术标签: SQL做的能改成Oracle吗
从SQL改写到SQL重写,什么样的SQL才是好SQL?黄浩 2016-12-14 10:02:26
作者介绍
黄浩,现任职于中国惠普,从业十年,始终专注于SQL。十年一剑,十年磨砺。3年通信行业,写就近3万条SQL;5年制造行业,遨游在ETL的浪潮;2年性能优化,厚积薄发自成一家。
在生活中,很多时候我们会有这样的体悟:问题要么不出,一旦出现,会像多诺米骨牌一样,会连锁引发诸多相关问题,让我们疲于应付。SQL优化也是如此,那厢,因一个视图代码变更引发的性能事件(参见案例:
1 涛声依旧,心有余悸
就在距离上次视图优化一个星期的时间,一封“红色”的邮件中的“SQL优化”项格外醒目:
唯一值得庆幸的是,该性能问题被放在8月版本计划里面:时间还是蛮充裕的。于是我按照自己的节奏展开分析。
我找到开发责任人,拿到了SQL:
初一看,122行,不算长,也不短。但是我对SQL中出现的视图对象BAS_PROJECT_ALL_V产生了很大的兴趣。这个视图刚刚完成优化,怎么就又出现了性能问题了呢?看了下执行计划:
自上而下,体型虽然婀娜妙曼,但是“妙曼”得有些让人眩晕。这都要归功于视图对象BAS_PROJECT_ALL_V,该视图有5个union all,而在该SQL中,又被访问了3次。记得上次案例是由于谓词没有被推入引发的,而看执行计划,视图被访问三次,都没有做谓词推入,我就试着强制谓词推入看看效果,但是即便是谓词推入,问题依旧。因临近下班,也就没有深入分析,计划第二天再看看,反正离8月版本还有3周的时间。
2 友谊小船,说翻就翻
第二天一过来,因为有来自其他同事的性能问题,我暂时将昨天的性能问题搁在一旁。大概在11点钟的时候,托盘上的espace弹出了消息,发消息的是昨天提供SQL的同事,内容是:昨天的那个性能问题必须要在今天内完成优化。这个消息着实把我“震”住了,不是说好的是8月版本吗?友谊的小船咋说翻就翻呢?
原来,同样的性能问题在生产环境也出现了,而且生产用户直接提了一个BUG单,用户很生气,后果很严重。从测试人员到生产用户,从UAT到生产,从邮件到BUG单,事态的严重程度已完全超出了当下深圳高温天气的黄色预警级别,以至于我立马放下手头的工作,顶着烈烈炎日,大汗淋淋的赶往“事发现场”—开发责任人所在ODC。
因为越是时间紧迫,沟通就越显得紧要,何况昨天初步“目测”,一方面执行计划过于复杂,而代码逻辑似乎又并不简单,因此更需要当面沟通。
根据开发人员的描述,这个功能的业务需求很简单,如下:
根据登录用户ID,获取该用户对应的所有项目列表,用户的项目列表包含两部分:其一是分配至该用户下的项目列表,其二是该用户所在区域的所有初始化过的项目,如下图所示:
在了解到业务需求后,我开始解读SQL,在解读的过程中不时的向开发人员请教、交流。约莫半小时后,SQL的逻辑框架也逐渐明朗起来,如下:
从逻辑示意图中,我们至少有如下两个疑问:
子查询route及T在配置项目列表和区域项目列表中都出现了,是否可以进行合并?
在“用户区域初始化项目列表”中,表SUROT_T与其他结果集没有任何关联条件,只有UID_C的过滤条件,这意味着会发生笛卡尔积。
了解完业务逻辑及SQL代码逻辑结构后,我们不能绕过视图BAS_PROJECT_ALL_V,且看该视图的代码逻辑:
单从视图的数据逻辑看,存在以下问题:
表重复访问,图中黄色底纹和蓝色底纹的表对象都是重复访问;
除了T2结果集外,其他结果集都都访问了三个以上的表对象,模式都是一样的:以主表LEFT JOIN从表。众所周知,LEFT JOIN在SQL中的功能是获取字段,并没有过滤数据的作用。从LEFT JOIN的这些表获取的字段是否被外层的SQL访问了呢?如果没有被访问,那就意味着,就本案例的SQL而言,这些LEFT JOIN是多余的。
3 深入“虎穴”
带着上述分析后的问题,我深入分析了代码,确认了如下信息:
SQL中的部分代码是可以精简的,比如子查询ROUTE及T只是为了获取属性字段,完全可以在得到了所需的PN_C清单后,再与子查询ROUTE及T关联获取。
视图的代码也可以精简,比如在视图T5结果集中访问BPOPT_T表对象获取的字段,在SQL中根本没有访问,也就是说在T5结果集中完全可以不访问该表对象。
那么还有个问题,那就是笛卡尔积。而通过分析代码,发现并非没有关联条件,而是将关联条件写到了where过滤位置了,如下:
基于该逻辑的复杂性,我决定将该过滤条件改写成EXISTS子查询。
4 大刀阔斧
业务需求了解了,大致的逻辑框架也清楚了,病症病因也定位了,接下来就是该大刀阔斧的进行SQL改写了,改写的过程就很简单了,改写后的SQL:
改动点如下:
取消视图BAS_PROJECT_ALL_V,用WITH 子查询替代视图。在with子查询中,根据需求关联表对象,砍掉了无关的表对象;
在获取区域项目列表的代码里,将LEFT JOIN WHERE改成EXISTS;
在获取了所有的项目列表后,再关联子查询ROUTE和T,获取项目相关属性信息。
修改后的执行计划如下:
在PL SQL DEV中执行,2.5S左右,看来SQL改写还是收到了成效,我也长长的舒了口气,看窗外,已是夕阳西下,一抹余晖透过玻璃窗投射进来,此刻在呼呼空调室内,丝丝暖意,却全然忘记早上发布的高温黄色预警,虽然已近黄昏,窗外依旧炙热。
5 分而治之,争分夺秒
第二天,正当我准备整理本次优化案例时,ESPACE弹出了消息,是开发人员发过来的。消息的内容让我立马停止了整理。因为开发人员告诉我,2.5S仍然不能满足需求,需要控制在2S内。此时此刻我的心情有些忐忑:根据经验,这0.5S的性能提升,其难度远比从10S优化到3S要大。
为了这0.5S,我又重新审视了昨天的优化方案。可以说昨天的优化方案已经对之前的代码结构做了很大程度的解构,但是更多的是“精简”SQL。在数据处理流程上还没有变化,优化前后的数据流程都是先集合再过滤,逻辑图如下:
再看下执行计划:
发现在UNION ALL六个来源的WITH子查询时,产生了31M的IO写操作,数据量达到了163K,而SQL最终返回的结果集不到1000条。此时,我看到了希望的曙光。我将数据处理流程方案做了优化,如下图所示:
也就是说,将之前合而治之变更成了分而治之,在原理上显然是等价的。
根据分而治之的方案,改写后的SQL见附件:
SQL改写后,怀着虔诚又激动的心情按下了F8,焦急得恍若停滞。1.8S,不容易呀。这0.7S的提升在动辄几秒钟、几分钟的优化空间中,如沧海一粟,但此刻却显得弥足珍贵。6 后记
该案例的优化过程其实就是一个SQL改写的过程,而最终演变成了SQL重写。这回到了一个最古老的问题:什么样的SQL才是好SQL?这个问题很难回答,因为这个问题跟哲学问题“什么样的人才是好人”是一样的。然而,我在长期与SQL为伴的过程中,从开发到优化,发现一个准则:简单即高效。这也合乎现在流行的返璞归真、大道至简的追求。
简单,并不是表现在代码量,而更在于SQL代码结构的简明、逻辑处理的简练。所在,在优化过程中,我首先考虑的是SQL自身的优化,也就是通常说的等价改写。我坚信,索引、Hint等技术的应用,应该是基于SQL已经极致化的假设。因为无论是索引,还是hint,在纷繁芜杂、不确定性的环境中,其负面影响也是巨大的。在风起云涌大浪淘沙时还能浪遏飞舟的,唯有“简单”的SQL。
文章浏览阅读387次。1.字符转换输出递归按位输出_a.c: in function ‘main’:
文章浏览阅读6.9w次,点赞137次,收藏246次。表弟大周末的跑来我家,没事干天天骚扰我,搞得我都不能跟小姐姐好好聊天了,于是为了打发表弟,我决定用Python做一个小游戏来消耗一下他的精力..._python蔡徐坤代码复制
文章浏览阅读1.9k次。blueski推荐 [2007-1-31]出处:Java夜无眠作者:蔡学镛 1、系统分析是什么? 系统分析工作是解决一个问题的工作,目标是将一个对计算机应用系统的需求转化成实际的物理实现,其中复杂就复杂在实际的面太多.在系统分析过程之中注意问以下的问题,可能会所进行的系统分析设计工作有帮助。 1)您所完成的系统目的是什么?注意不是功能要求,而是目的.也就是为什么要建设、为什么要现_信息系统开发中分析与设计的重要性。
文章浏览阅读659次。nginx软件学习nginx是个web服务器,常用作静态文件服务器,反向代理服务器,邮件代理服务器,负载均衡服务器1.安装淘宝nginx,编代码编译安装,先解决模块依赖 yum install gcc patch libffi-devel python-devel zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel..._nginx 使用pipe:rollback
文章浏览阅读6.7k次。pycharm安装库包失败:比如安装matplotlib失败下载库文件:https://www.lfd.uci.edu/~gohlke/pythonlibs/#matplotlib根据python版本等来选择适合的文件,比如python3.8选择cp38,python3.10选择cp310,这里我选择的是matplotlib‑3.4.3‑cp310‑cp310‑win_amd64.whl将下载好的文件放入python安装包的Scripts中,运行cmd命令行,执行pip install .\_one893.app
文章浏览阅读1.2k次。最近在用python写长连接的东西,两三周前才开始接触python,所以一直在看书,写起代码来有点痛苦,基本上是跪着在摸索的。前一篇博客的protobuf,之前写android 只是接json,所以只能去github上看readme,readme好长,配合文档看了一个多小时,拉代码下来拉了两个多小时,基本上是这个状态….python 上的RSA其实用起来挺简单的,找到文档之后就很好解决了 h
文章浏览阅读295次。回溯算法:回溯算法实际上一个类似枚举的搜索尝试过程,主要是在搜索尝试过程中寻找问题的解,当发现已不满足求解条件时,就“回溯”返回,尝试别的路径。回溯法是一种选优搜索法,按选优条件向前搜索,以达到目标。但当探索到某一步时,发现原先选择并不优或达不到目标,就退回一步重新选择,这种走不通就退回再走的技术为回溯法,而满足回溯条件的某个状态的点称为“回溯点”。许多复杂的,规模较大的问题都可以使用回..._leetcode 306、python
文章浏览阅读352次。数据库redis使用优点使用方法redis的简单类型String字符串注意一个键最大能存储512MB哈希list列表set集合注意redis安全可以通过以下命令查看是否设置了密码验证用python实现链接数据库redis-connectredis密码破解redis使用设置数据添加数据redis查看服务器配置插入值追加数据setget数据导出dumprdb 这个即为_redis.redis(host='127.0.0.1', port=6379)
文章浏览阅读1k次。华为以“比特驱动瓦特,共建全联接智能电网”为主题,举办第七届华为全球电力峰会(线上),邀请来自全球各地的客户、伙伴、行业精英和思想领袖共同探讨。面对2020年的全球疫情、政治、经济等不确..._ict数字化解决方案资金分配
文章浏览阅读733次。 1、新建一个类库,例如库名为 ResourcesLibrary;2、添加引用 System.Drawing;3、添加资源文件(添加--新建项--资源文件),例如文件名为 Resource1.resx;4、添加图片(打开Resource1.resx,单击“添加资源”后的小三角,选择添加现有文件,选择需要作为资源的图片),例如添加了图片 Sunset.jpg5、添加类,例如名为 GetImage_易语言dll加入资源
文章浏览阅读4.4k次,点赞4次,收藏15次。今天有学生问个关于MySQL时间操作的问题,直接看图: 查询某日人员的出勤时间问题。我们都知道,企业员工考勤使用打卡方式,每天会记录每个员工的多次打卡记录,最后取每个员工的每天:上班时间:当日最早时间;下班时间:当日最晚时间。所以分析查询每个员工每日考勤记录,需要根据员工分组,当天时间、时间最小和最大,时间格式化等操作,下面举例模拟该问题:学生表student ..._%h:%i
文章浏览阅读1.3w次,点赞4次,收藏31次。Python实战——1_1.网页制作引言网页的组成部分:CSS样式 - 给结构以装饰Html - 结构部分JavaScript- 功能实现代码部分第一部分为网站的基本结构(在IDE中新建网页后既给出) {% for docs in toc_list %} "