SQL优化 查询语句中,用 inner join 作为过滤条件和用where作为过滤条件的区别

时间:2021-08-17 20:05:25

 

结论先行的话就是:
对于相对来说简单的SQL,Inner join的方式过滤和放在Where条件中过滤性能上来说是一样的,
但是对于复杂的SQL,有可能出现Inner join过滤出现性能问题的情况,此时可以尝试将条件放在where中做尝试,这个问题没有什么定论

 

前段时间遇到一个存储过程,参数之一是一个字符串,在存储过程中,把字符串拆分成一个临时表之后存为一个key值的临时表,作为其中一个查询条件,
逻辑实现上有两种处理方式

insert into #t
select key from split_function('传递进来的字符串',',')

 

第一种是与物理表做inner join,类似如下

select *
from tableA a
inner join tableB b on a.id = b.id
inner join #t c on b.key=c.key
inner join otherTables d on a.id=d.id
where a.column2 between @paramater1 and @parameter2
and other query condition

 

第二种是将这个过滤条件放在where 条件中

select *
from tableA a
inner join tableB b
on a.id = b.id
inner join otherTables c on a.id=c.id
where a.column between @paramater1 and @parameter2
and b.key in (select key from #t)
and other query condition

实际上这个存储过程本身比较复杂,十多张表的一个复杂的join和多钟过滤逻辑,其中有几张大表将近千万级,核心点的不同在于类似上面查询条件的处理方式,
本身的逻辑是用第一种方式去实现的,因为有较大的性能问题,一开始把重点放在索引,统计信息之类上面,怎么也找不到原因
发现在性能始终很差,上面我只是举一个简单的例子,实际情况远远比上面复杂,上很难去模拟实际的逻辑

这个问题困惑了我好久,
因为当时没意识到上述第一种写法下的用inner join中间结果集的方式过滤和直接放在where 条件中的区别,
后来仔细观察执行计划,发现第一种方式的执行计划是这样的:
执行计划最开始对物理表做过滤的时候,没有先用#t中的值去过滤物理表,仅仅用TableA上column2 的过滤条件得到一个结果集,然后用这个较大的结果集去驱动其他表,最后再去跟#t做join,
等于是中间结果集非常大,最后才去跟#t做join过滤,性能上比较差


由于当时以来没意识到时上述inner join #t的方式造成的,把问题集中在索引上,对索引,统计信息之类的做各种分析优化,都没有得到怎么改善
后来换成第二种方式,效率提高了很多,
生成的图形化的执行计划,估计两屏都显示不下,通过一步一步的观察,
看了好久,才发现是两种方式的差别在于这里:
对于物理表的处理是这样的:用上column2 和 #t 与TABLEB的结果共同去过滤TableA,得到一个中间结果集,然后去驱动其他的表
虽然最后的结果是一样的,但是这个查询的效率差别非常大,因为一开始对TableA过滤的时候,得到的是一个比较小的结果,后面再去驱动其他表或者是跟其他表join,
由于这里生成的中间结果集事先利用了#t的过滤条件,所以中间结果集比较小,
因此后继跟其他表join起来,整体代价比较小,性能上有一个较大的提升

这个问题困惑了好多天,本来想自己写个demo验证一下的,无奈实际场景太复杂了,很难模拟出那种数据和表之间的逻辑关系。
不过可以明确的是,上述写法,对于简单的demo,可能性能上区别不大,但是执行计划的差别还是很明显的,对于复杂的情况,可能就要具体分析了
以后优化sql的时候,多个思路,尤其是在复杂的条件下,面对查询条件的处理方式,是否有必要用inner join的方式达到过滤数据的目的,一定要慎重。

 

最后上两个简单的demo,看一下执行计划的区别,实际情况是在是模拟不出来,只能这样通过执行计划看一下两者之间的区别
截图可以看到,上述demo中的两种写法,分别用Inner join和Where处理一个表中的数据,逻辑上是一样的,但是在执行计划上并不是完全一样的,如果执行计划不一样,性能上肯定有差别
这里只能建议,遇到类似问题,尝试改写SQL,看看默认情况下执行计划是否会发生变化,从而选择一种性能较好的写法。
(本文不讨论索引,仅仅在相同的表结构情况下,从执行计划差异的角度来看问题的)

SQL优化  查询语句中,用 inner join 作为过滤条件和用where作为过滤条件的区别

 

SQL优化  查询语句中,用 inner join 作为过滤条件和用where作为过滤条件的区别