SQL Server优化技巧——如何避免查询条件OR引起的性能问题

原文:SQL Server优化技巧——如何避免查询条件OR引起的性能问题

之前写过一篇博客“SQL SERVER中关于OR会导致索引扫描或全表扫描的浅析”,里面介绍了OR可能会引起全表扫描或索引扫描的各种案例,以及如何优化查询条件中含有OR的SQL语句的几种方法,其实还有一些方法可以用来优化这种问题,这里简单介绍一下。

如下所示,下面的SQL语句之所有出现这种写法,是因为程序的查询界面,可能有多个输入性的查询条件,往往用户只填了一个或部分查询条件(业务情况,应该不用详细介绍,大家都能明白),但是程序里面没有通过判断查询条件生成不同的SQL语句,而是用一个SQL搞定,不管用户没有填写JobNo这个查询条件,下面这种写法:WHERE ISNULL(@JobNo, '') = ''  OR JobNo = @JobNo都能满足条件,实现逻辑功能。

DECLARE @GenerateDateStart DATETIME ,

    @GenerateDateEnd DATETIME ,

    @JobNo NVARCHAR(200) ,

    @GkNo NVARCHAR(200);

SET @JobNo = 'PT19B030';

SET @GkNo = 'PV19-1-8050'; 

 

  SELECT    *

  FROM      [dbo].[GEW_UnitConsumption] AS A

            LEFT JOIN dbo.UnitConsumption_Relation AS B ON B.UsableFlag = 'Y'

                                                           AND A.GewUnitConsumptionId = B.RootUnitConsumptionID

  WHERE     ( ISNULL(@JobNo, '') = ''

              OR A.JobNo = @JobNo

            )

            AND ( ISNULL(@GkNo, '') = ''

                  OR A.GkNo = @GkNo

                );

其实,如果根据查询条件动态生成SQL语句,的确能避免查询条件中出现OR的情形,但是动态SQL语句没有上面语句简单和通熟易懂,尤其是查询条件较多的情况下。只能说各有利弊。这里暂且不讨论那种策略的优劣。

SQL Server优化技巧——如何避免查询条件OR引起的性能问题

下面介绍一种技巧,如何避免OR引起的索引扫描或全表扫描问题。我们可以使用CASE WHEN改写一下这个SQL语句,就能避免OR引起的执行计划不走索引查找(Index Seek)的情况,如下所示:

DECLARE @GenerateDateStart DATETIME ,

    @GenerateDateEnd DATETIME ,

    @JobNo NVARCHAR(200) ,

    @GkNo NVARCHAR(200);

SET @JobNo = 'PT19B030';

SET @GkNo = 'PV19-1-8050'; 

 

 

SELECT  *

FROM    [dbo].[GEW_UnitConsumption] AS A

        LEFT JOIN dbo.UnitConsumption_Relation AS B ON B.UsableFlag = 'Y'

                                                       AND A.GewUnitConsumptionId = B.RootUnitConsumptionID

WHERE   CASE WHEN ISNULL(@JobNo, '') = '' THEN A.JobNo

             ELSE @JobNo

        END = JobNo

        AND CASE WHEN ISNULL(@GkNo, '') = '' THEN A.GkNo

                 ELSE GkNo

            END = @GkNo;

SQL Server优化技巧——如何避免查询条件OR引起的性能问题

测试对比发现性能改善非常明显,当然这种优化技巧也是有局限性的,并不能解决所有OR引起的性能问题(没有银弹!)。如下所示,对于下面这种情况,这种技巧也是无能为力!

SELECT * FROM TEST1 WHERE A=12 OR B=500

------------------------------------------分割线-------------------------------------------------

网友MSSQL123反馈:他测试的一个案例发现这种技巧无效,个人测试验证发现确实如此,后面发现个人遇到的仅仅是一个特殊个例(当时生产环境那个场景下确实生效了),后面经过大量测试发现,很多情况下CASE WHEN这种技巧无效,也就是说单个案例不具有通用性,后面进一步测试分析,发现我得出的结论是错误的

当然在错误的基础上,进一步测试验证,发现还是有技巧优化OR引起的性能问题的,这也是我后续补充的原因,请见下文分析:

我们首先简单构造一个测试环境案例,测试环境为SQL Server 2014

CREATE TABLE TEST_OPTION_COMPILE (OBJECT_ID  INT, NAME VARCHAR(16));

 

CREATE CLUSTERED INDEX PK_TEST_OPTION_COMPILE ON TEST_OPTION_COMPILE(OBJECT_ID); 

 

 

DECLARE @Index INT =0;

 

WHILE @Index < 100000

BEGIN

 

    INSERT INTO TEST_OPTION_COMPILE

    SELECT @Index, 'kerry'+CAST(@Index AS VARCHAR(7));

    

    SET @Index = @Index +1;

END

 

 

CREATE INDEX IX_TEST_OPTION_COMPILE_N1 ON TEST_OPTION_COMPILE(NAME);

UPDATE STATISTICS TEST_OPTION_COMPILE WITH FULLSCAN;

如下测试所示,发现这个例子中,CASE WHEN完全无效,使用这种SQL写法,依然走Index Scan

DECLARE @name VARCHAR(8);

SET @name = 'kerry8'

 

SELECT  NAME

FROM    dbo.TEST_OPTION_COMPILE

WHERE   CASE WHEN ISNULL(@name, '') = '' THEN NAME

             ELSE @name

        END = NAME;

 

SELECT  NAME

FROM    dbo.TEST_OPTION_COMPILE

WHERE   ( ISNULL(@name, '') = ''

          OR NAME = @name

        )

SQL Server优化技巧——如何避免查询条件OR引起的性能问题

如果我们在SQL后面加上OPTION(RECOMPILE)的话,那么SQL就会走索引查找(Index Seek),其实下面两个SQL语句,如果都加上OPTION(RECOMPILE)的话,它们都会走索引。这是什么情况呢?

SQL Server优化技巧——如何避免查询条件OR引起的性能问题

接下来我们对比分析一下,看看SQL语句有无OPTION(RECOMPILE)的区别,如下所示:

 

DECLARE @name VARCHAR(8);

SET @name = 'kerry8'

 

SELECT  NAME

FROM    dbo.TEST_OPTION_COMPILE

WHERE   CASE WHEN ISNULL(@name, '') = '' THEN NAME

             ELSE @name

        END = NAME ;

 

SELECT  NAME

FROM    dbo.TEST_OPTION_COMPILE

WHERE   CASE WHEN ISNULL(@name, '') = '' THEN NAME

             ELSE @name

        END = NAME OPTION(RECOMPILE)

如下所示,如果没有OPTION(RECOMPILE)的话,执行计划走Index Scan,预估行数(Estimated Number of Rows)是100000, 而实际行数(Actual Number of Rows)是1,

SQL Server优化技巧——如何避免查询条件OR引起的性能问题

如果SQL中有OPTION(RECOMPILE)的话,执行计划走Index Seek,预估行数(Estimated Number of Rows)是1, 而实际行数(Actual Number of Rows)是1,从对比我们可以看出,加上OPTION(RECOMPILE)的话,SQL的执行计划要准确很多,那么为什么呢?这里是因为OPTION(RECOMPILE)开启了Parameter Embedding Optimization

SQL Server优化技巧——如何避免查询条件OR引起的性能问题

关于Parameter Embedding Optimization,这里简单介绍一下,详情参考Parameter Sniffing, Embedding, and the RECOMPILE Options参考资料的相关文档。

参数嗅探值使优化器可以使用参数值来得出基数估计。 WITH RECOMPILE和OPTION(RECOMPILE)均会生成查询计划,并根据每次执行时的实际参数值计算出估算值。

  相比WITH RECOMPILE这种强制重编译的方式,OPTION(RECOMPILE)中的参数嵌入优化(Parameter Embedding Optimization)的机制更进一步:查询解析期间,查询参数被文字常量值替代。 解析器能够神奇的将复杂问题简单化,并且在随后的查询优化可能会进一步完善这些内容。

Microsoft在SQL Server 2008(后RTM)中引入了参数嵌入优化(Parameter Embedding Optimization)。 这个特性扩展了参数嗅探优化。 它能使用基数估计值来嗅探参数以影响计划。具体参考官方文档“Changed behaviour of OPTION RECOMPILE syntax in SQL Server 2008 SP1 cumulative update #5

总结: 我们可以使用OPTION(RECOMPILE)(确切的说,是Parameter Embedding Optimization)这种技巧来避免查询条件中OR引起的性能问题,这确实是一个SQL Server优化技巧,至于我前面的结论,这是一个错误结论(使用CASE WHEN改写一下这个SQL语句,就能避免OR引起的执行计划不走索引查找(Index Seek))。在缺乏严谨的论证、充分的测试就草率的得出了一个结论,以后要引以为戒!。

参考资料:

https://www.cnblogs.com/wy123/p/6262800.html

https://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options

上一篇:关于javascript在作用域中的变量定义你所不知道的一些东西


下一篇:小猪猪逆袭成博士之C++基础篇(二) 常量、处理类型、自定义头文件