点击蓝色“有关SQL”关注我哟
加个“星标”,天天与8000人一起快乐成长
图 | 榖依米
经常看我们【有关SQL】公众号的读者,对SQL执行的理解,一定与别人不一样。
别人看到一条SQL,脑子里除了从上到下执行,就不会有别的想法。但是我们的读者肯定不是这样。比如以下这条简单的不能再简单的SQL,聚合求和:
select flowid,
sum(flowamount) as amount
from workflow
group by flowid
order by 2 desc
不看我们【有关SQL】公众号的SQL开发,就会认为这条语句就是完成以 flowid 为分组依据的求和。而经常看我们公众号的读者,至少心里会想,workflow 表有没有建索引啊,索引结构怎么样,group by 会用到这个索引么?
甚至铁粉读者还会想得更深入,没有任何判断条件的聚合,会不会锁表呢,为什么不加事务控制?如果让我来建这个索引,我该如何定这个索引结构,并且让数据库优化器只选择我建的这个索引。
还没关注的朋友,右上点击关注,再来看文。
趁着这篇文章,我再稍微提一下之前文章的精华,SQL执行步骤:
一条SQL语句达到数据库后,并不马上执行,中间会穿插很多细小的步骤。
第一步,SQL语句会被编译,生成语法树,比如上面的语句,编译成语法树,就是从表读取数据,按列做聚合,此时不会执行SQL;
第二步,将表和列,绑定到对应的语法树对象上,若发现没有对应表,则报一个大家都不愿意看到的错“找不到对象”;
第三步,优化器生成最快的执行计划,在这一步,很多不可见的神秘操作,就会在简单的SQL表象下,执行。而大多数的开发人员,是看不见的;
第四步,执行优化器生成的最快的执行计划,返回结果。
数据库这个行业如果说有门槛,那么优化这块绝对占据第一位
今天,我又要告诉各位一个非常有用的知识点,敲黑板…三次!
在Query Optimization阶段,优化器并不会生成所有可能的执行计划
可能很多开发的朋友都会发懵,所有可能的执行计划是什么意思,执行计划还不止一条?
对,没错!虽然被执行的执行计划只有一条,但优化阶段,产生的执行计划并不止一条,优化器只是挑了一个比较低成本(也就是优化器认为执行最快的)那条。
就拿上面的SQL语句来说,如果我们在这表上加了 2 个索引 idx(flowid) 和 idx(flowamount) ,就会增多执行计划。比如:
直接访问全表
访问索引 idx(flowid)
访问索引 idx(flowamount)
优化器生成的执行计划可能会是以下几种:
select flowid,
sum(flowamount) as amount
from workflow WITH (INDEX (0))
group by flowid
order by 2 desc
select flowid,
sum(flowamount) as amount
from workflow WITH (INDEX ([idx_amt_id]))
group by flowid
order by 2 desc
select flowid,
sum(flowamount) as amount
from workflow WITH (INDEX (0))
group by flowid
order by 2 desc
OPTION (ORDER GROUP)
当然还有其他执行计划,不一一列举了。
所以当执行计划越多,优化器本身试图穷尽这些计划都非常耗时,因为优化器就偷了个懒,如果碰到某个执行计划在合理的时间内,就不再往下分析。也就是优化器也在将就着过日子,经过分析找到一个比较快速的执行计划就急于丢给查询引擎做执行,而不是尝试去遍历所有可能的执行计划,再挑一个真正最优的。
此时,给了优化工程师发挥的余地,如果要追求极致的性能,这些工程师就要替优化器去完成寻找最优的执行计划。那么多少工程师能比计算机快呢,我想没有吧。那么快就一定好吗,那不一定。SQL优化大师,凭借自己的经验,一眼看到某个索引能显著提升性能,而正由于优化器的偷懒,恰好没用到,于是他就给优化器一个提示,这里可以用这个索引去提高性能。
所以,大部分不够优化的SQL,归根结底,是由于两类原因造成的:
要么没有足够的改造空间( plan space),要么SQL写死了
缺少足够多的统计信息,使得判断执行成本错误
在历史的促进下,现在的优化器面临更多的挑战,比如 OLAP,分布式事务,还有并行执行。这么多的优化需要在一个优化器中实现,难度空前增加。
所以要求优化器做到极致,既要实时,还要精准,是不现实的。我们必须发明一种程序,让它可以替代优化器做些事情。比如评估那些没有穷尽的执行计划,在真实的环境中测试每个计划的执行时间,拿到最快的那个。
那有没有这样的神器呢?有,这就是本文的主角,ESR.
ESR:External SQL Rewriter,外部SQL重写器
ESR 的主要优点有 2 个:
可以无止境的寻找最优执行计划,因为它不是实时生产执行计划的
不依赖统计信息,不计算成本,而是把所有的潜在执行计划都执行一遍,太长的自动杀掉,留下短时间内,比如500ms 内执行完毕的执行计划
原理就在这里了,真正实现了 ESR 的工具,介绍一个:Toad
在 Toad 之前,其实还有一个 LECCO SQL Expert 的工具,不过停产了。现在 Toad 已经集成了 ESR 工具,现在带大家看看:
最底下的那个窗格,罗列了所有可以执行的改写过后的SQL,右边的窗格,显示了每个执行计划的成本分析。
好了,今天的分享就到这里。ESR 的概念是从微信交流群看到的,想要与更多的SQL,数据库,大数据玩家一起讨论,欢迎加入我们的微信群,期待有你!
--完--
往期精彩: