你的位置:首页 > 数据库

[数据库]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

where

a.column2 between @paramater1 and @parameter2

 

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

select * from tableA a inner join tableB b on a.id=b.id

where

a.column between @paramater1 and @parameter2

and b.key in (select key from #t)

 

 实际上这个存储过程本身比较复杂,十多张表的一个复杂的join和多钟过滤逻辑,其中有几张大表将近千万级,核心点的不同在于类似上面查询条件的处理方式,

本身的逻辑是用第一种方式去实现的,一开始把重点放在索引,统计信息之类上面,怎么也找不到原因

发现在性能很差,本身这里我只是举一个简单的例子,实际上很难去模拟实际的逻辑

 

这个问题困惑了我好久,

一开始没想到原因在于上述的用inner join中间结果集的方式过滤和直接放在where 条件中的区别,

后来仔细观察执行计划,发现第一种方式的执行计划是这样的:

执行计划最开始对物理表做过滤的时候,没有先用#t中的值去过滤物理表,仅仅用TableA上column2 的过滤条件得到一个结果集,然后用这个较大的结果集去驱动其他表,最后再去跟#t做join,

等于是中间结果集非常大,最后才去跟#t做join过滤,性能上比较差

 

其实一直以来没意识到时上述inner join #t造成的,一开始是对索引,统计信息之类的做各种优化,都没有得到怎么改善

后来换成第二种方式,对于物理表的处理是这样的:用上column2 和 #t共同去过滤TableA,等到一个中间结果集,然后去驱动其他的表

生成的图形化的执行计划,估计两屏都显示不下,通过一步一步的观察,看了好久,才发现是这个差别,

虽然最后的结果是一样的,但是这个查询的效率差别非常大,因为一开始对TableA过滤的时候,得到的是一个比较小的结果,后面再去驱动其他表或者是跟其他表join,代价都比较小

 

这个问题困惑了好多天,本来想自己写个demo验证一下的,无奈实际场景太复杂了,很难模拟出那种数据和表之间的逻辑关系。

不过可以明确的是,上述写法,对于简单的demo,可能性能上区别不大,但是执行计划的差别还是很明显的

以后优化sql的时候,多个思路,尤其是在复杂的条件下,面对查询条件的处理方式,一定要慎重