​风驰电掣:有效缩短SQL优化过程三步走!

梁敬彬 2017-07-14 10:01:33
作者介绍

梁敬彬福富研究院副理事长、公司唯一四星级内训师,国内一线知名数据库专家,在数据库优化和培训领域有着丰富的经验。多次应邀担任国内外数据库大会的演讲嘉宾,在业界有着广泛的影响力。著有多本畅销书籍,代表作有《收获,不止Oracle》。

 

 
 
 

学习完第一章内容的小王,知道了解决问题要先整体再局部,从此不再盲目解决问题了。关于整体性方面,他不仅知道了五大性能报告的具体用途,还明白了如何获取到这些报告,甚至还知道如何解读这些报告。如今的他会对着获取到的这几张报告眉头紧锁,认真研读,自言自语,不住点头,指点江山,激扬文字……仿佛一位住在地下室的青年,正在思考国家下一步该怎么走?如何突破美国封锁?如何收复台湾?如何保住南沙钓鱼岛……

 

这个结果似乎让我们有些热血澎湃,不过提升效率的重点内容会出现在本章,能否从地下室搬出来,就在此一举了。

 
 
 

 

故事中有一个细节,便是小王光发现问题就整整花了1个小时,这时间可不短啊。如果系统故障要花整整1小时才能找到,那这1小时的时间对客户来说,是多么难熬的1小时啊。

 

而且,更可悲的是,这1小时小王并没有解决问题,故障依旧。

 

有人问,为啥会花费1小时的时间呢?嗯,本章就开始探讨这个问题。首先说明调优时间都花在哪儿,接下来分析如何缩短,并结合案例来巩固知识,最后大家思考回顾。

 

总体学习思路如下图所示:

 

 

一、SQL调优时间都去哪儿了

 

我们先来分析调优时间都去哪儿了。1小时才能得到解决方案固然让人接受不了,事实其实更糟糕,小王是只花费1小时吗?显然不止1小时!

 

他到下午还在解决这个故障,甚至第2天他还在解决这个问题,这已经1天了。小王只花了1天时间吗?显然不止1天!

 

实际情况是小王最后崩溃了无法解决问题。显然这是无限期!

 

面对这么凄惨的事情,我这里总结出几点经验,来分析这个问题。

 

1、不善于批处理频频忙交互
 

 

故事中没有细说小王和求助者之间的交互细节,但是从花了整整1小时才发现需要加索引来看,小王的工作效率肯定不高。我们可以猜测他发生了什么事。也许小王本身无法访问这个系统,需要通过对方执行自己的脚本来反馈结果。于是他想了解这个语句的返回记录的量,又想要了解这个语句的执行计划,又想了解这个语句对应的表和索引的信息……电话、QQ、邮件不断地交互,于是1小时就这么过去了。就像下图所示这样:

 

 

2、无法抓住主要矛盾瞎折腾
 

 

小王没有从整体出发来考虑问题,没有想明白是整体问题还是局部问题,一路抓瞎,无端耗费时间却徒劳无功。

 

 

3、未能明确需求目标白费劲
 

 

有的SQL其实执行得虽然慢,但是客户并不是很在意,这样的SQL除非是耗尽资源影响到全系统,否则不见得就需要立即优化。

 

不过这是我提出的一个重要意识,在小王的案例里,这个问题倒是不明显,因为这个SQL客户已经明显感觉慢,提出要优化了。

 

 

4、没有分析操作难度乱调优
 

 

其实有一个意识非常重要,SQL好调优吗,调优空间大吗?小王遇到这个SQL的时候,其实应该首先知道这个SQL返回记录有多少,如果很少,就说明调优空间很大。反之就要考虑特殊手段了。在小王的案例中,小王根本没有这个意识。

 

 

二、如何缩短SQL调优时间

 

1、先获取有助调优的数据库整体信息
 

 

如何缩短SQL调优时间呢?我觉得要先把你进行SQL优化的思路理顺。当你要优化SQL时,你的一般流程是什么?

 

首先要知道整个数据库的运行情况,我们上一章中介绍过数据库AWR报告等调优工具,不过介绍得并不全面,因为AWR报告等是在数据库出问题时的利器。可是如果数据库当前没有出问题呢?其实不见得,很多时候系统没问题是因为你没触发这个问题,其实是有问题的。比如某表的索引失效了,某SQL访问该列时一定只能走全表扫描;比如某表的属性被设置了并行度,这意味着所有扫描该表的SQL都会并行执行,这可能会产生严重的资源争用从而让系统瘫痪;比如你的全局临时表被收集了统计信息,访问该表的SQL就可能会出现错误的执行计划等。

 

不过你的AWR报告却可能发现不了这些问题,比如该时段和这些对象相关联的SQL根本就没有执行。没发现问题并不代表没有问题!

 

因此我们需要获取所有可能有问题的对象,同时也需要一键获取所有的相关时段的AWR等数据库整体性能报告,获取数据库的整体信息。假如这些信息能一键快速获取,那解决问题的效率肯定会高很多。

 

特别提醒:

这里再次强调获取的相关时间段问题,我们自动获取AWR等几大性能报告是需要选择时间段的。那么如何选择呢,能高效全自动判断吗?其实动动脑子就可以想到,我们可以在数据库里自动判断哪些时段的资源消耗最大,然后直接就提取该时段的性能报告,甚至可以取前几名时段,然后我们去自动提取,这不行吗?

 

2、快速获取SQL运行台前信息
 

 

接下来,在获取到数据库整体信息后,调优的方向就非常明确了,对具体的SQL进行调优。执行计划是SQL调优的重要武器,通过分析SQL计划,我们可以判断SQL的访问路径是否高效,从而进行调整优化。关于执行计划的获取手段有6种之多,这是为啥呢?各有什么区别?答案依然展示在后面的章节中。

 

还需要将执行计划和运行时的统计信息结合在一起分析,这样才会更准确。比如SQL产生了多少逻辑读,多少物理读,是否有排序,是否有递归调用,等等。具体细节依然见后续章节。

 

3、快速拿到SQL关联幕后信息
 

 

当获取到SQL的执行计划后,诸多的确认都和该SQL对应的表和索引有关。比如当我们怀疑驱动表的顺序有错时,我们就会去看看这些表的实际大小和对应的统计信息是否准确;我们也关心表的类型是什么,比如是否是分区表,在哪个列有分区,分区的类型是什么,等等。

 

除了关注表的信息,我们也很关心索引的信息。比如看到执行计划中非常适合走索引的查询走了全表扫描,我们就会去看看是否该列无索引,如果发现有,就看看此列索引是否失效了。

 

一般我们也会关心索引的类型是什么,是Btree索引还是位图索引还是函数索引;是单列索引还是组合索引,如果是组合索引,哪列在前;如果索引建在分区表上,我们还关心是全局索引还是局部索引等。

 

总之,我们希望能一目了然地掌握该SQL涉及的所有表和列的相关信息,最好一键就展现在我们面前。这样,解决问题就非常高效了!

 

大家可能已经猜到了,我们又要来一个一键获取了,这次获取的是SQL对应表和索引的相关信息,没错,你猜对了。干货脚本可通过以下链接获取:

https://github.com/liangjingbin99/shouhuo/tree/master/%E7%AC%AC02%E7%AB%A0

 

总体情况,详见下图:

 

 

说了这么多,你是不是有些跃跃欲试了,可能特别想知道到底该如何一键获取SQL整体信息和SQL的相关信息吧。很显然,这样效率一定能提升很多!好吧,下面就跟随我一起看看吧。

 

三、从案例看快速SQL调优

 

 

1、获取数据库整体的运行情况
 

 

  • 步骤1(构造环境,对当前数据库进行各种操作)

 

sqlplus "/ as sysdba" @d:\mkdb.sql

 

  • 步骤2(运行脚本,对当前数据库进行整体提取)

 

sqlplus "/ as sysdba" @spooldb.sql

 

  • 步骤3(输出对应日志文件,以供后续进行分析)

 

输出如下5个文件,其中ADDM是最近一小时文件,ash是最近半小时文件,而AWR文件是最近一小时和最近7天的两个文件,spool打头的文件输出数据库所有的相关信息。有了这5个文件,基本上数据库的情况可以了解得比较清晰。具体文件如下图所示:

 

 

具体这5个文件我们就不一一打开给大家看了,否则篇幅显得过大。其中spool打头的这个文件基本涵盖了你想获取的所有相关数据库信息。如:数据库版本、数据库参数、主机参数、异常的表和索引信息(表分区有无异常、并行度问题、索引是否失效、是否过大、是否为分区索引、索引类型、哪个列上有索引、索引过多、组合索引的组合列过多、全局临时表的异常信息收集情况,等等)、日志切换情况、序列情况、异常触发器、异常外键设置等。

 

如果要在书中展现,预计要有20多面,这里只告诉读者大致的内容。在线上会展现脚本,并进行视频演示,给读者看实际操作。

 

2、获取SQL的各种详细信息
 

 

  • 步骤1(构造环境,执行部分效率低下的SQL)

 

sqlplus "/ as sysdba" @d:\mksql.sql

 

  • 步骤2(对当前的性能低下的SQL进行收集)

 

 

  • 步骤3(对当前的性能低下的SQL进行收集)

 

我们获取到了这个SQL的详细执行计划和对应的表及索引信息,从而大大提升了效率,如下图所示:

 

 

 

 

大家不要小看了这个一键获取SQL相关详细信息的威力。当你认为一条SQL有问题时,只要获取到这些信息,根本就不需要进行任何与现场人员的交互动作,因为你要的东西都已经被收集在一起了。让我们一起看看下面一个技术人员解决问题的一个思维片段。

 

这语句该如何优化,让我先瞧瞧这语句的执行计划是啥?

 

哦,原来执行计划就在这里,太详细了!

 

奇怪,这里的执行计划不对啊,应该是要用索引啊,怎么会没有,难道是统计信息不准确,还是说没建索引,还是说索引失效了?

 

咦,相关统计信息的收集是正常的,奇怪。我再看看,这列有索引吗?咦,是有,奇怪了。哦,索引失效了,原来如此。看来我重建一下索引问题应该可以解决……

 

看完这段话有何感触,如果没有高效的一键收集手段,你的所有信息都要在数据库中查询而获取,如果需要让他人给你信息,那就更麻烦了,小王1小时后才得出如何优化的事情,正是因为无法直接获取全部有效信息,不断交互导致的。

 

四、总结与延伸

 

1、场景再现

 

我们来假想一下学过这两章内容和拥有工具脚本的小王,是什么样的场景吧。

 

场景1:

一键获取数据库整体信息,发现整体主机资源不足,并定位到耗尽资源的外部应用程序,然后协调相关人员,将外部应用移走或者优化,问题迅速准确地得到解决。

 

场景2:

一键获取数据库整体信息,根据在第1章中学到的相关知识,发现是某些SQL需要优化,然后根据一键所得的SQL详细信息,判定SQL问题在哪里,问题迅速准确地得到解决。

 

场景3:

一键获取数据库整体信息,也获取到SQL相关详细信息,不过不知该如何下手优化,求助他人。由于提交的信息详细,无须交互迅速得到对方的建议帮助,问题迅速准确地得到解决。

 

2、脑洞大开

 

学到这里,大家已经明白笔者的用意了,后续请读者下载一下脚本(链接https://github.com/liangjingbin99/shouhuo/tree/master/%E7%AC%AC02%E7%AB%A0,体验一下高效的过程。其实完善是没有尽头的,这只是一个大概,更细节的实现会一直在公众号上更新,比如一键获取数据库整体信息中我就没有提到数据库告警日志、监听日志、主机和数据库是否需要打哪些补丁。比如获取的AWR和ASH等信息里,能否直接对这些报告的SQL进行超链接,进入到详细SQL信息中……有没有脑洞大开的感觉?

 

这两章如果展开讲,可以单独成书。这两章的知识落地到软件中,就是一个非常好的数据库高效分析诊断工具。是不是再次脑洞大开?

 

嗯,笔者会无偿地将自己的经验分享给大家,能帮到大家也是笔者最大的快乐!

最新评论
访客 2017年07月21日

写得好,打赏!

访客 2017年07月21日

那什么是分割relay日志的正确姿势呢

访客 2017年07月20日

有思想的dba

访客 2017年07月20日

应该在延伸一下,慢查询的监控,告警和优化提示,类似…

访客 2017年07月19日

赞一个

活动预告