| 注册
请输入搜索内容

热门搜索

Java Linux MySQL PHP JavaScript Hibernate jQuery Nginx
MorrisPresl
8年前发布

SQL效率之where子句中的子查询和函数

   <p>工作中,曾有同事问我以下 sql 的效率如何,这里扩展一下这个问题并进行分析,主要说明 where 子句中的子查询和函数执行次数及索引使用情况 。</p>    <p>select * from trd_fundjour a  </p>    <p>where oc_date = (select collect_date from hscon.sys_arg);</p>    <p>首先来看一下表的数据分布情况:</p>    <p><img src="https://simg.open-open.com/show/6a04d4db76a6cb972f867c58d407895b.png"></p>    <p>表 trd_fundjour 是分区表, 里面是按月分区的,以oc_date为索引。</p>    <p>先看一下 201605 分区全扫描产生多少逻辑读。</p>    <p><img src="https://simg.open-open.com/show/688a3f0de0afa4487e6f1379263147a6.png"></p>    <p>这里产生了 88 个一致读, 接下来执行前面的 sql 。</p>    <p><img src="https://simg.open-open.com/show/0c106f4ee0a0610c5a9d6c660529be96.png"></p>    <p>这里比前一次多出了 7 个一致读,也多出了对 sys_arg 表的扫描,单独查询 sys_arg 表看看。</p>    <p><img src="https://simg.open-open.com/show/24c64c7bc3863d22945889882e115524.png"></p>    <p>一致读刚好是 7 个,从这里可以看出,子查询只执行了一次,而不是针对分区中的每条记录比较一次,因为这个分区中有 3279 条记录。</p>    <p>下面来看能否用上索引,这里强制走索引看看。</p>    <p><img src="https://simg.open-open.com/show/ee260605f7e75ec5e0d9998842812785.png"></p>    <p>显然,在用 hint 强制的情况下索引是可以走的。就本例而言,走索引比全表扫描效率会更高,但在不用 hint 的情况下 Oracle 选的却是全表扫描。虽然从表面上看是选择了较差的执行计划,其实是正常的,因为在生成执行计划的时候 Oracle 得不到子查询的值,无法判断全表扫描和索引扫描哪个更优,因此选择全表扫描就无可厚非了。所以,在可能的情况下应该将这些非相关子查询执行结果赋给变量,把上面的 sql 拆成两个语句来执行。</p>    <p>下面来看一下函数的表现, hscon.f_get_collectdate 的实现逻辑就是封装前面 sql 中的子查询,返回查询结果。</p>    <p><img src="https://simg.open-open.com/show/9897fd5622a64f99f2828006dca6f9dd.png"></p>    <p>将子查询改成函数,可以看到递归调用和一致读都大了很多,是哪里产生的呢?我们来单独查一下函数看看。</p>    <p><img src="https://simg.open-open.com/show/c7f48aea2620a1cecfa96fe95bc87086.png"></p>    <p>从这里可以看出,函数调用一次就产生 1 个递归调用和 7 个一致读。 3286*7 (函数执行一次 7 个一致读)加上 88 (分区全扫描产生的一致读)刚好是 23090 ,也就是说在前面的语句中函数执行了 3286 次!上文这一数字其实可以被拆分成 3279+7 两部分来看, 3279 是分区的记录数, 7 应该是和分区消除相关。我们可以得出这样的结论:针对分区中的每一条记录,函数都会调用一次进行比较。</p>    <p>既然前面的写法会导致函数频繁调用,我们修改一下 sql 写法,先把函数生成一个结果集 。</p>    <p><img src="https://simg.open-open.com/show/637c15368815ed01f18384e776ec6bf8.png"></p>    <p>从上图看,情况更加糟糕,但其实并不是我们的想法错了,而是 Oracle 太“聪明”了。从执行计划的 filter 中可以看出, Oracle 重写了我们的 sql ,合并了子查询,基本上相当于又给改回了原样。下面加个 hint 来防止 Oracle 的这种自作聪明。</p>    <p><img src="https://simg.open-open.com/show/2484ec89d2898e6a740f4a422aaca033.png"></p>    <p>情况有了很大的改善。虽说与直接使用子查询方式相比,使用函数在一致读上还是大了一些,这个差别怀疑是和分区表有关(非分区表应该没有差别,未做验证),并且实际上可以通过调整执行计划来达到无差别,这里不做详细说明。</p>    <p>下面看一下索引的使用情况。</p>    <p><img src="https://simg.open-open.com/show/9ab3a01efdd01e18fb104a873a57020a.png"></p>    <p>同样地,也是可以走索引的,但 Oracle 不会主动使用,需要用 hint 强制,理由前面已经提过了。需要注意的是,这里递归调用只有 8 次,和分区全扫描时的表现完全不同。</p>    <p>再看下面的语句。</p>    <p><img src="https://simg.open-open.com/show/09b65a795784c51192e31d76040e8cd6.png"></p>    <p>这应该是比较合理的执行计划了。</p>    <p>综上所述,通常使用子查询的效率比用函数都要来得好一些,个别情况下用函数的效率很糟糕。</p>    <p>最高效的方法就是尽可能拆分成两个语句,用变量来代替子查询和函数。</p>    <p> </p>    <p>来自: <a href="/misc/goto?guid=4959674363769948030" rel="nofollow">http://rdcqii.hundsun.com/portal/article/473.html</a></p>    <p> </p>    
 本文由用户 MorrisPresl 自行上传分享,仅供网友学习交流。所有权归原作者,若您的权利被侵害,请联系管理员。
 转载本站原创文章,请注明出处,并保留原始链接、图片水印。
 本站是一个以用户分享为主的开源技术平台,欢迎各类分享!
 本文地址:https://www.open-open.com/lib/view/open1465990522413.html
SQL Oracle数据库