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陳述句沒有上面陳述句簡單和通熟易懂，尤其是查詢條件較多的情況下，只能說各有利弊，這里暫且不討論那種策略的優劣，

下面介紹一種技巧，如何避免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;

測驗對比發現性能改善非常明顯，當然這種優化技巧也是有局限性的，并不能解決所有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后面加上OPTION(RECOMPILE)的話，那么SQL就會走索引查找（Index Seek),其實下面兩個SQL陳述句，如果都加上OPTION(RECOMPILE)的話，它們都會走索引，這是什么情況呢？

接下來我們對比分析一下，看看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中有OPTION(RECOMPILE)的話，執行計劃走Index Seek，預估行數（Estimated Number of Rows）是1， 而實際行數（Actual Number of Rows）是1，從對比我們可以看出，加上OPTION(RECOMPILE)的話，SQL的執行計劃要準確很多，那么為什么呢？這里是因為OPTION(RECOMPILE)開啟了Parameter Embedding Optimization

關于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

標籤：SQL Server

上一篇：使用SQL Server記憶體優化表 In-Memory OLTP

下一篇：Use KTPASS instead of adden to configure mssql.keytab