加入 NULL 鍵列優化為表和索引掃描

我對此查詢計劃有疑問。

我們在測試環境中有一個表 Order_Details_Taxes，它有 11,225,799 行。該表有一個列 OrdTax_PLtax_LoadDtl_Key，在每一行上都為 NULL。此測試環境的配置方式使該列始終為 NULL。此列上有一個索引。

我使用列的 NULL 值對該表執行了一些查詢。NULL INNER JOIN 永遠不會產生任何結果。

declare @Keys table (KeyValue decimal(15,0))
insert into @Keys (KeyValue) values (null)

select OrdTax_PLTax_LoadDtl_Key
from @Keys
inner join Order_Details_Taxes
   on OrdTax_PLTax_LoadDtl_Key = KeyValue

select *
from @Keys
inner join Order_Details_Taxes
   on OrdTax_PLTax_LoadDtl_Key = KeyValue

這些是查詢計劃中的第一個查詢。第一個select從億行表開始並加入@Keys。第二個select從@Keys 開始，但它在這個表上進行聚集索引掃描。

我知道臨時@Tables 在大多數情況下都是有問題的，所以我將查詢更改為使用臨時#Table：

if object_id ('tempdb..#Keys') is not null
   drop table #Keys
create table #Keys (KeyValue decimal(15,0))
insert into #Keys (KeyValue) values (null)

select OrdTax_PLTax_LoadDtl_Key
from #Keys
inner join Order_Details_Taxes
   on OrdTax_PLTax_LoadDtl_Key = KeyValue

select *
from #Keys
inner join Order_Details_Taxes
   on OrdTax_PLTax_LoadDtl_Key = null

這些查詢已經過優化並完全按照我的預期執行——首先獲取#Keys NULL 值並尋找Order_Details_Taxes。它們是連結的查詢計劃中的最後一個查詢。

為什麼我使用 @Table 變數的查詢在這個大表上執行索引和表掃描，當我使用從具有單個 NULL 值的表連接到此鍵值中只有 NULL 的表時？

我認為答案是@Table 變數的統計和/或基數限制，但生成的查詢計劃對我來說並不直覺。

ANSI_NULLs此表和我的 SQL 會話已啟用。

您看到的行為是由於表變數缺少統計資訊造成的。當我想更多地了解查詢優化器選擇特定計劃的原因時，我有時會添加提示並並排比較查詢。這種方法在這裡很有幫助。

首先，我將創建一個在結構上與您的表足夠接近的表，以查看相同的行為：

CREATE TABLE dbo.Order_Details_Taxes (
   OrdTax_PLTax_LoadDtl_Key decimal(15,0),
   FILLER VARCHAR(30)
);

INSERT INTO dbo.Order_Details_Taxes WITH (TABLOCK)
SELECT NULL, REPLICATE('Z', 30)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

CREATE INDEX [IX_OrdTax_PLTax_LoadDtl_Key] ON Order_Details_Taxes (OrdTax_PLTax_LoadDtl_Key);

要查看查詢優化器如何花費不同的連接類型，我可以獲得以下估計計劃：

declare @Keys table (KeyValue decimal(15,0))
insert into @Keys (KeyValue) values (null)

select OrdTax_PLTax_LoadDtl_Key
from @Keys
inner join Order_Details_Taxes
   on OrdTax_PLTax_LoadDtl_Key = KeyValue;

select OrdTax_PLTax_LoadDtl_Key
from @Keys
inner join Order_Details_Taxes
   on OrdTax_PLTax_LoadDtl_Key = KeyValue
OPTION (LOOP JOIN, MAXDOP 1);

select OrdTax_PLTax_LoadDtl_Key
from @Keys
inner join Order_Details_Taxes
   on OrdTax_PLTax_LoadDtl_Key = KeyValue
OPTION (HASH JOIN, MAXDOP 1);

這是估計計劃的螢幕截圖：

SQL Server 對錶變數中行的值一無所知，因此它使用OrdTax_PLTax_LoadDtl_Key. 所有行在統計數據中都具有相同的值，因此密度為 1。查詢優化器模型的一般假設之一是，如果最終使用者正在查找數據，則數據存在。因此，儘管直方圖僅包含 NULL，但您的索引查找預計將返回與掃描相同的行數並具有相同的成本。在這種情況下，優化器不會返回並應用有關 NULL 的特殊知識來更改計劃。您可能會爭辯說可以改進優化器來做到這一點，但這似乎是一種不常見的情況。

計劃的成本差異最終歸結為加入運營商本身的成本。無論出於何種原因，查詢優化器的循環連接成本都高於合併連接。散列連接的成本也很高，但 SQL Server 預計需要計算數百萬個散列，因此更高的成本在 imo 中更容易理解。

如果您使用沒有統計資訊的臨時表獲得相同的計劃會發生什麼？正確的方法是禁用表的自動統計創建，但我會採取捷徑：

if object_id ('tempdb..#Keys') is not null
   drop table #Keys
create table #Keys (KeyValue decimal(15,0))
CREATE STATISTICS s1 on #Keys (KeyValue) WITH NORECOMPUTE;
insert into #Keys (KeyValue) values (null)

一切看起來都與表變數計劃相同：

這就是為什麼我說這種行為是由於缺乏統計數據造成的。當您使用臨時表並允許創建自動統計資訊時，優化器在臨時表的列上有一個直方圖。它可以使用該資訊為嵌套循環連接計劃和索引查找生成更準確的基數估計：

直方圖表明不會匹配任何列，因此您最終會得到 1 行的最小基數估計值。循環連接和查找的成本相應降低，嵌套循環連接計劃是迄今為止三種連接類型中成本最低的。

在連接的外部表中有一些 NULL 值是比連接到所有 NULL 的表更常見的情況。換句話說，我希望有更好的模型支持來比較兩個都包含 NULL 的直方圖（與直方圖相比）與僅 NULL 與未知值相比。借助更好的模型支持，您可以獲得更好的基數估計，在這種情況下，更好的基數估計會產生更有效的查詢計劃。

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