RWP谈SQL优化-创新互联
Oracle Real-World Performance团队是一个人数不多的天才团队,整个团队的数据库性能优化经验有超过400人年。团队成员分布在美国,中国和欧洲,不断的寻找和创造新的方法分析诊断当今世界业务系统的性能。Oracle Real-World Performance团队有着很多出类拔萃的战绩,在工作中,多次将客户系统性能提升几十上百倍,给客户系统性能提升1000倍或更多也并不罕见。
在中国,业界同仁将Oracle Real-World Performance团队简称为RWP团队。目前RWP团队在中国共有7名成员。
说到SQL优化,做为读者的您,头脑中第一反应是什么?索引?Hint?分区?参数?执行计划?哈哈哈有被言中吧 ;-),今天我们就来谈谈SQL优化的整体思路,希望能够对您有所启发。
1. 设定一个高的目标
如果您把一个SQL从一个小时优化到了1分钟,您会停止工作吗?会不会考虑是否能给它优化到1秒钟?
工作中,每个人都有压力,压力之下,很容易疏于思考。一个SQL多长时间能跑完,依赖于它跑在什么样的硬件和软件环境上。一个SQL能不能跑的更快,本质上是:它是否能够更加充分的利用硬件资源和软件能力。
做SQL优化,给自己设定一个高的目标非常重要!
2. 去优化那些好的SQL
有了高的目标,接下来,还要找到那些好的SQL进行优化。那么,什么是好的SQL?
(1) 有效的 SQL
数据库是为了执行SQL设计的,不是为了一执行就报错的无效SQL设计的。
如果执行一个SQL,报ORA的错误,那么这是一个无效的SQL,它不应该存在于您的系统里面,当然更不应该成为您优化的对象。
如果执行一个SQL,报ORA的错误,那么在数据库里面会是一个failure parse。如果您系统的AWR报告里面有failure parse,那么您要注意了,后果可能很严重。
(2) 您知道业务含义的SQL
有很多时候,一些SQL和PL/SQL存储过程是根本就不需要被执行的。但是由于种种原因,那些SQL和PL/SQL存储过程存在在系统中,可能都已存在了很长时间,写那些SQL和PL/SQL存储过程的人可能早就跳槽了,为了所谓的“稳定”,没有人去动那些SQL和PL/SQL存储过程。去优化这些根本就不需要被执行的SQL和PL/SQL存储过程当然是没有任何意义的。
所以,在优化任何一条SQL之前,应该首先知道那条SQL业务上的含义,确定它确实是需要被执行的,再去优化它。
(3) 构造好的SQL
如果一个SQL语句里面有IN列表,IN列表里面有几百个值,那么那几百个值,很有可能是来源于另外一个SQL,而非人工输入。由于IN列表中值的个数有一个允许的上限,有些SQL甚至会长成下面的样子:
几百几千几万个值在IN列表里面,那是不是SQL构造的不好,是不是应该先将它改成一个JOIN再去考虑其他?
(4) 没有编写错误的SQL
N个表做JOIN的话,一般情况应该有N-1个JOIN条件。如果JOIN条件小于N-1个的话,就会有CARTESIAN JOIN出现,结果集里面会有重复值。在SELECT LIST里面加上DISTINCT,通常就可以使得SQL得到功能上正确的结果集。这就好比您去银行取钱,实际只要取1000块钱,可是您先取了2000块钱,再把余下的1000存回去,多此一举,虽然实际结果是对的,您确实是取了1000块钱。
当SQL处理的数据量小的时候,这个多此一举对于响应时间的影响并不会很大。可是当SQL处理的数据量大的时候,这个影响就会完全凸显出来。还是那个取钱的例子,如果您实际只要取1000块钱,可是您先取了10001000块钱,再把余下的10000000块钱存回去。最后您也会得到1000块钱,可是银行员工为您取钱的时候数出10001000块钱的时间,和把钱存回去的时候再数好10000000块钱的时间,都是您办业务的时间,您取钱的时间就会变得相当长了。
SQL语句中WHERE条件里面的值的数据类型,应该与相应的列的数据类型一致。否则SQL语句虽不会报错,会隐式的用函数将那个列转换成与相应的值的数据类型一致,去执行SQL。这种隐式数据类型转换,可能会导致ORA-01722的错误,可能会导致相应的列上的索引不能被使用到,可能会导致明明可以使用分区裁剪但却用不上的情况,响应时间可能差好几个数量级。
3. 给SQL一个好的执行环境
SQL需要在好的环境上执行才能够性能好。那么什么是好的执行环境呢?
正确的给软件打上补丁,是打造好的执行环境的第一步。明明您都花了钱买软件,明明人家软件厂家都出了补丁可以让软件跑的更好更快,为什么不打补丁呢?当然了,打补丁是个技术活,怎么正确的给软件打上补丁,肯定是要按照软件厂家的说明来,或者咨询软件厂家啦。
使用默认的init.ora参数设置,也是打造好的执行环境的重要一环。使用默认的init.ora参数设置,意味着您是按照Oracle内部研发团队设计软件的方法去使用它,意味着您使用的是经过Oracle内部测试团队严格测试的软件。当然了,有一些特定的应用软件,比如Oracle的EBS,要求修改init.ora参数,这种情况是要修改,因为那些修改是经过应用软件厂家严格测试过的。
如果是因为遇到bug,需要修改某些参数做为临时解决方案,那么当那个bug修复之后,您应该及时将相应的参数改回去,否则后果可能也会很严重噢。
另外,若随意修改init.ora参数,可能会导致售后的问题。
4. 从数据库设计的角度优化SQL
现在Oracle数据库软件使用的是Cost Based Optimizer(CBO),基于成本的优化器。
本质上来讲,优化器就是一系列的算法。优化器会接受输入的信息来生成SQL的执行计划。输入的信息包括:
(1) 统计信息
统计信息包括两个方面,系统的统计信息,和实际用户数据的统计信息。
系统的统计信息,推荐大家使用默认设置。实际用户数据的统计信息,最重要的是要有代表性,要能够反应数据的特征。
(2) 约束
NOT NULL, PK, FK, UK等等约束,若实际数据是需要符合约束的,那么那些约束应该存在于数据库里面,应该让优化器知道这些约束的存在。
举个例子。多个表做JOIN,如果某张表只是被JOIN了,比如下面这样事儿的
customer表只出现在了JOIN部分,但是并没有出现在SELECTlist里面,也没有出现在查询条件里面,也没有出现在GROUP BY和ORDER BY的部分里面。那么如果lineorder表上的JOIN key(lo_custkey)上存在外键约束的话,优化器就会知道lo_custkey = c_custkey这个JOIN总是能够JOIN的上,那么在实际执行的时候就不会去JOIN customer这个表了。执行计划可以是下面这样事儿的:
您擦亮双眼看好了么,customer表压根儿就没有出现在执行计划里面!您能做的最快的JOIN就是不JOIN啊哈哈哈。这种情况我们叫做JOIN elimination,发生的前提条件是相关约束的存在。
(3) Schema设计
Schema的设计,包括数据模型,索引,分区,压缩,clustering(数据根据相应的KEY值物理上存放在一起)等等,对SQL性能都有非常重要的影响。
有些SQL里面,一个表和自己JOIN几十次,就是因为数据模型设计得不好导致的。此时若只是专注于SQL本身,能够取得的性能提升恐怕就非常有限了。
Schema设计是门大学问,每一个方面都可以对SQL的性能有几个数量级的影响。想做好SQL优化的话,您必须要将schema设计重视起来。
5. 从执行角度优化SQL
从执行的角度去优化SQL,主要是要考虑以下方面:
Access method,是通过索引访问数据,还是全表扫描。
Join方法,是Nested Loop Join,Hash Join,还是Merge Join。
Join顺序,是表A Join表B,再Join表C,还是反之。
并行执行时,生产者进程组和消费者进程组之间的数据分发方法,是hash,还是broadcast,还是其他的分发方法。
数据是否有倾斜,是否某些KEY值对应的数据特别多,其他KEY值对应的数据特别少。
总结
以上几点给您提供了一个SQL优化的整体思路。整体思路总是很重要。
那么具体的,如果一个性能差的SQL摆在您的面前,必须去优化它,要从那里入手呢?SQL Monitor Report将会是您的好朋友,欢迎一见钟情。后续我们会推出系列文章,举例说明如何用SQL Monitor Report诊断SQL的性能问题,欢迎您持续关注。
当前标题:RWP谈SQL优化-创新互联
文章位置:http://myzitong.com/article/cohhjo.html