Sql-Server

重新排列 OR 條件時 SQL Server 創建不同的計劃

  • May 2, 2019

我正在查看一個表現不佳的查詢，如下所示：

WHERE manymany.Active = -1
 AND manymany.Check1 = -1
 AND manymany.WebsiteID = @P1
 AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
 AND main.Active = -1
 AND main.StatusID = 1
 AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
 AND (main.TextCol1 IS NOT NULL OR main.TextCol2 IS NOT NULL)
ORDER BY aux.SortCode

我不小心在這個查詢上使用了 SSMS 查詢設計器，它重新編寫了查詢，如下所示：

WHERE manymany.Active = -1
 AND manymany.Check1 = -1
 AND manymany.WebsiteID = @P2
 AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
 AND main.Active = -1
 AND main.StatusID = 1
 AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
 AND main.TextCol1 IS NOT NULL

  OR manymany.Active = -1
 AND manymany.Check1 = -1
 AND manymany.WebsiteID = @P2
 AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
 AND main.Active = -1
 AND main.StatusID = 1
 AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
 AND main.TextCol2 IS NOT NULL
ORDER BY aux.SortCode

如果您仔細觀察，您會注意到它只是OR通過重複所有條件來擴展條件，即它更改a AND (b OR c)為(a AND b) OR (a AND c).

生成的查詢在成本方面減少了 50%，在執行時間方面減少了 33%。OR我根本不明白為什麼在兩個查詢相同（？）時重新安排條件會改變計劃。我可以OR自己通過複製粘貼條件來擴展條件，但我為什麼要這樣做？

粘貼計劃和螢幕截圖：

執行計劃

行數：

main     2718
manymany 188761
aux      19

筆記：

  • TextCol1 和 TextCol2 是text數據類型，不能被索引
  • 有平均。每個網站 ID 在 manymany 表中有 170.20 條記錄

但是為什麼 SQL Server 不將這兩個查詢視為一個呢？畢竟，a AND (b OR c) = (a AND b) OR (a AND c)？

從邏輯上講，它是相同的，並且會得到相​​同的結果。

假設

我的假設是，對於“更快”的計劃，優化器不會考慮頂部的OR一些過濾器語句與底部的一些過濾器語句相同。我可能完全不在這兒了。

獲得這些假設的理由是基於這個過濾謂詞：

Main此過濾謂詞使用表和表之間的連接結果manymany。 在此處輸入圖像描述

請注意，此過濾器中的EXPR1021和EXPR1022manymany是從表上的標量運算符創建的表達式。

在此處輸入圖像描述

該過濾器由兩部分組成，第一個帶有普通過濾器(.. AND .. OR .. AND ..) ，第二個帶有普通AND過濾器

(getdate()>=[Expr1021] 
AND getdate()<=[Expr1022] 
AND getdate()>=[DB1].[dbo].[main].[FromDate] 
AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000') 
AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL 
OR getdate()>=[Expr1021] 
AND getdate()<=[Expr1022]
AND getdate()>=[DB1].[dbo].[main].[FromDate] 
AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000') 
AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL) 

AND (getdate()>=[DB1].[dbo].[main].[FromDate] 
AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000') 
AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL OR getdate()>=[DB1].[dbo].[main].[FromDate] 
AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000') 
AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL)

OR如您所見，此過濾器第一部分中 上下的唯一區別是

AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL

VS

AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL

無論如何，第二部分都必須為真，因為它們是AND沒有任何OR’s 的謂詞。

導致對相同函式的額外計算，在我看來是不需要的。同樣，我在這裡的猜測是 sql server 進行這些計算的原因是它不知道它們是相同的。

對於 where 子句的其他部分，它確實知道它們是相同的，例如在主表中，statusid = 1 只計算一次：

在此處輸入圖像描述

在manymany表中，相同的語句被評估了兩次：

在此處輸入圖像描述

在“慢”計劃中，語句不與OR子句一起添加，這就是優化器生成不同計劃的原因，分別在表上應用過濾謂詞（並且沒有重複的過濾器）。

在此處輸入圖像描述

在此處輸入圖像描述

假設結束

兩個計劃的比較

我認為您對“快速”計劃的表現很幸運，但是當匹配數據增加時，“快速”計劃可能會變得醜陋。它可能取決於您在何時何地應用過濾器*（和其他因素）*。

快速計劃過濾

在“快速”計劃中：由於與兩個+ ( ) 塊的不同組合， sql server 在main表與表連接後應用了一些過濾器。在找到與表的所有可能組合後，過濾來自 的列。manymany``OR``AND ... AND ... AND...``maintable``manymany

結果，相同的謂詞在manymany表上執行了兩次：

在此處輸入圖像描述 對於 . 上方和下方的謂詞OR。

main但是對於表中的一些 seek 謂詞，情況並非如此

在此處輸入圖像描述

在此之後，連接發生，一個更大的過濾器謂詞在和之間的連接結果上main發生manymany，同樣適用於所有可能的組合 在此處輸入圖像描述

請注意，此過濾器中的EXPR1021和EXPR1022manymany是從表上的標量運算符創建的表達式。

在此處輸入圖像描述

該過濾器由兩部分組成，第一個帶有普通過濾器(.. AND .. OR .. AND ..) ，第二個帶有普通AND過濾器

(getdate()>=[Expr1021] 
AND getdate()<=[Expr1022] 
AND getdate()>=[DB1].[dbo].[main].[FromDate] 
AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000') 
AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL 
OR getdate()>=[Expr1021] 
AND getdate()<=[Expr1022]
AND getdate()>=[DB1].[dbo].[main].[FromDate] 
AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000') 
AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL) 

AND (getdate()>=[DB1].[dbo].[main].[FromDate] 
AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000') 
AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL OR getdate()>=[DB1].[dbo].[main].[FromDate] 
AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000') 
AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL)

OR如您所見，此過濾器第一部分中 上下的唯一區別是

AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL

VS

AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL

無論如何，第二部分都必須為真，因為它們是AND沒有任何OR’s 的謂詞。

導致我認為不需要的額外計算。

慢計劃過濾

在“慢”計劃中：由於AND (TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL) 部分，sql server 將過濾器直接應用於主表，然後與manymany表連接以過濾掉其餘部分以獲得 15 行。

Main表格過濾器

在此處輸入圖像描述

在此處輸入圖像描述

manymany表格過濾器

在此處輸入圖像描述

其他一些有時重疊的資訊：

較慢的計劃

當我們查看較慢的計劃時，使用聚集索引 PK_main 到計算標量、過濾器和嵌套循環運算符：

在此處輸入圖像描述

當我們將其與要返回 的估計行進行比較時，我們會發現不同之處：在此處輸入圖像描述

它估計掃描時謂詞將返回 93 行：

在此處輸入圖像描述

這實際上比預期的少 20 倍，即1947 rows。

之後，計算標量或此語句：

, CASE WHEN TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL THEN -1 ELSE 0 END AS MoreFlag
, CASE WHEN Stars BETWEEN 1 AND 5 THEN Stars END AS Rating

在這 1947 行上進行評估。

然後過濾運算符 ( main.TextCol1 IS NOT NULL OR main.TextCol2 IS NOT NULL) 將其減少到 1374 行。

在此之後，將這 1374 行加入到dbo.manymany表中以返回 15 行。

更快的計劃

更快的計劃是使用 NC 索引：CVR_main_4在the dbo.Main表上， 在此處輸入圖像描述

它使用搜尋謂詞進行過濾，將 27 行返回給nested loopsJoin 運算符，再次與dbo.manymany表連接。

並且返回的實際行數甚至低於估計的行數：

在此處輸入圖像描述

27 行，估計為 152 行

過濾

一個很大的區別是過濾發生的地方，在“較慢”的計劃中，這是直接在dbo.Main桌子上完成的：

使用謂詞：TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL

在此處輸入圖像描述

並將此過濾器應用於 1943 行。

其他過濾直接發生在dbo.manymany桌子上

在此處輸入圖像描述 （尋求）謂詞dbo.manymany

而另一個，在“更快”計劃中，在連接 from toOR之後被過濾，並在 27 行上產生更大的過濾器。dbo.Main``dbo.manymany

在此處輸入圖像描述

更大的過濾器，OR在 27 行上有多個。

另一個區別是 Key 查找運算符：

在此處輸入圖像描述

它從聚集索引中獲得 10 個額外的列，但只需對 27 行執行此操作。

在此處輸入圖像描述

在此處輸入圖像描述

優化器選擇“較慢”計劃的另一個原因可能是因為優化器認為不查找其他列會更好。

快速計劃會更快，還是總是會“更快”？

我確實認為，如果通過過濾器的數據增加，“慢”計劃會更好。不僅因為鍵查找，還因為計劃中更大的過濾器操作符。

如果發生這種情況，請在索引旁邊。您可以通過使用語句將查詢拆分為多個部分來改進過濾UNION。

像這樣：

SELECT main.MainID, Title, Column1, Column2, Column7, Column4, Column6, Column3, Column5
    , CASE WHEN TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL THEN -1 ELSE 0 END AS MoreFlag
    , CASE WHEN Stars BETWEEN 1 AND 5 THEN Stars END AS Rating
FROM manymany
INNER JOIN main ON manymany.MainID = main.MainID
LEFT JOIN aux ON manymany.AuxID = aux.AuxID
WHERE manymany.WebsiteID = @P1
 AND manymany.Check1 = -1
 AND manymany.Active = -1
 AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
 AND main.Active = -1
 AND main.StatusID = 1
 AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
 AND TextCol1 IS NOT NULL

UNION 

SELECT main.MainID, Title, Column1, Column2, Column7, Column4, Column6, Column3, Column5
    , CASE WHEN TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL THEN -1 ELSE 0 END AS MoreFlag
    , CASE WHEN Stars BETWEEN 1 AND 5 THEN Stars END AS Rating
FROM manymany
INNER JOIN main ON manymany.MainID = main.MainID
LEFT JOIN aux ON manymany.AuxID = aux.AuxID
WHERE manymany.WebsiteID = @P1
 AND manymany.Check1 = -1
 AND manymany.Active = -1
 AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
 AND main.Active = -1
 AND main.StatusID = 1
 AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
 AND TextCol2 IS NOT NULL
ORDER BY SortCode;

在第一個查詢中，它必須以掃描開始，而在第二個查詢中，它能夠利用非聚集索引進行查找。

想像一下，就像您在一個袋子裡有 100 個彈珠，並且您想審核它們以僅挑選出藍色和白色或藍色和紅色的彈珠。

第一個查詢是說，查看 100 個彈珠中的每一個，然後找出所有的藍色。完成後，檢查所有這些，看看是否有白色或紅色。

第二個查詢是說進入袋子，只抓藍色和白色或藍色和紅色的彈珠。

第二個查詢會更快，因為您不必先查看每個彈珠的藍色。您可以將該步驟與您真正想要的藍白或藍紅相結合。

反正我就是這麼看的。最終，第一個查詢需要進行表掃描，而第二個查詢從一開始就使用了 seek。它仍然需要進行鍵查找和掃描，因為非聚集索引沒有它需要的所有資訊，但是到那時它要查看的數據集要少得多，因此速度更快。

引用自：https://dba.stackexchange.com/questions/231428

相關問答

Sql-Server

執行計劃不使用索引，它使用表掃描

  • May 7, 2020
檢視問答
Sql-Server

強制 Sql 伺服器在合併連接中使用索引查找

  • May 7, 2020
檢視問答
Sql-Server

為什麼 OFFSET … FETCH 與舊式 ROW_NUMBER 方案之間存在執行計劃差異？

  • November 15, 2019
檢視問答
Sql-Server

尋求幫助優化自聯接

  • May 13, 2019
檢視問答
Sql-Server

SQL Server 的優化器如何估計連接表中的行數？

  • January 5, 2019
檢視問答
Sql-Server

查看列消除

  • March 19, 2018
檢視問答