性能重構：盡量避免表掃描
我有一個包含查詢連接幾個表的過程，我遇到了一些性能問題。
主表（這是一個巨大的表）有一個 PK 和一些 NC 索引。
CREATE TABLE [dbo].[TableA] ( [TableAID] [bigint] NOT NULL, [UserID] [int] NOT NULL, [IP1] [tinyint] NOT NULL, [IP2] [tinyint] NOT NULL, [IP3] [tinyint] NOT NULL, [IP4] [tinyint] NOT NULL CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED ([TableAID] ASC) ) ON [PRIMARY] CREATE NONCLUSTERED INDEX [idx_1] ON [dbo].[TableA] ( [UserID] ASC ) CREATE NONCLUSTERED INDEX [idx_2] ON [dbo].[TableA] ( [IP1] ASC, [IP2] ASC, [IP3] ASC, [IP4] ASC )這是性能不佳的查詢：
SELECT DISTINCT a.UserID, a.IP1, a.IP2, a.IP3, a.IP4 FROM [dbo].[TableA] a WITH (NOLOCK) JOIN [dbo].[TableB] b WITH (NOLOCK) ON b.UserID = a.UserID JOIN [dbo].[Tablec] c WITH (NOLOCK) ON b.CountryID = c.CountryID JOIN ( SELECT IP1, IP2, IP3, IP4 from @IPs ) as ip ON ((ip.IP1 is NULL) OR (ip.IP1=a.IP1)) AND ((ip.IP2 is NULL) OR (ip.IP2=a.IP2)) AND ((ip.IP3 is NULL) OR (ip.IP3=a.IP3)) AND ((ip.IP4 is NULL) OR (ip.IP4=a.IP4))表的定義
@IPs：DECLARE @IPs TABLE ( IP1 int, IP2 int, IP3 int, IP4 int ) INSERT INTO @IPs(IP1,IP2,IP3,IP4) SELECT T.v.value('(IP1/node())[1]', 'int'), T.v.value('(IP2/node())[1]', 'int'), T.v.value('(IP3/node())[1]', 'int'), T.v.value('(IP4/node())[1]', 'int') FROM @IPAddresses.nodes('//IPAddresses/IPAddress') T(v)
@IPAddresses是xml。剛剛發現xml可以發送更多的IP，所以這意味著IP表中有不止一行。問題是 TableA 上的讀取次數。即使我有 IP 列的 NC 索引，該連接條件也會強制進行表掃描……
我怎樣才能提高性能？如何重構此表/查詢？
我還在想是否有更簡單更好的方法來重寫這段程式碼：
SELECT IP1, IP2, IP3, IP4 from @IPs ) as ip ON ((ip.IP1 is NULL) OR (ip.IP1=a.IP1)) AND ((ip.IP2 is NULL) OR (ip.IP2=a.IP2)) AND ((ip.IP3 is NULL) OR (ip.IP3=a.IP3)) AND ((ip.IP4 is NULL) OR (ip.IP4=a.IP4))…如果我們有更多的 IP。
有幾件事使這具有挑戰性。如果您不小心，這些
NULL檢查可能會阻止索引查找。此外，當列是NULL您顯然無法搜尋它們。所以如果IP1是，NULL那麼四列索引idx_2不會很有用。似乎真的不可能定義一個對任何NULL變數組合都有選擇性的索引。此外，SQL Server在搜尋不等式謂詞後無法繼續索引搜尋：同理，如果我們在兩列上有一個索引，如果我們在第一列上有一個相等謂詞，我們只能使用索引來滿足第二列上的一個謂詞。
這意味著使用
TINYINT數據類型邊界的技巧不太可能有效，例如：a.IP1 >= NULLIF(ip.IP1, 0) AND a.IP1 <= NULLIF(ip.IP1, 255)除此之外，我使用的策略似乎與 SQL Server 2014 中引入的新基數估計器效果更好，並且您的問題被標記為 SQL Server 2008。
我強烈建議將表變數拆分為行並一次處理一行。這主要是為了處理
NULL值，因為您的評論暗示大多數時候您只能得到一行。只要一行的性能足夠好並且您沒有太多行就應該沒問題。如果這是不可接受的，也許您可以檢查臨時表中的任何行（不要使用表變數）NULL並在這種情況下分支您的程式碼。綜上所述，在沒有聚集索引掃描的情況下，只要最多兩個 IP 地址片段不是
NULL. 當其中三個是NULL您要取回大部分錶時，此時進行聚集索引掃描可能是有意義的。以下是我模擬測試各種解決方案的數據。我通過為每個片段隨機選擇一個介於 0 和 255 之間的整數來生成 1 億個 IP 地址。我知道現實生活中的 IP 地址分佈並不是那麼隨機，但沒有辦法生成更好的數據。
CREATE TABLE [dbo].[TableA]( [TableAID] [bigint] NOT NULL, [UserID] [int] NOT NULL, [IP1] [tinyint] NOT NULL, [IP2] [tinyint] NOT NULL, [IP3] [tinyint] NOT NULL, [IP4] [tinyint] NOT NULL CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED ( [TableAID] ASC ) ); -- insert 100 million random IP addresses INSERT INTO [dbo].[TableA] WITH (TABLOCK) SELECT TOP (100000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 10000 , ABS(BINARY_CHECKSUM(NEWID()) % 256) , ABS(BINARY_CHECKSUM(NEWID()) % 256) , ABS(BINARY_CHECKSUM(NEWID()) % 256) , ABS(BINARY_CHECKSUM(NEWID()) % 256) FROM master..spt_values t1 CROSS JOIN master..spt_values t2 CROSS JOIN master..spt_values t3; CREATE TABLE [dbo].[TableB] ( [UserID] [int] NOT NULL, FILLER VARCHAR(100), PRIMARY KEY (UserId) ); -- insert 10k users INSERT INTO [dbo].[TableB] WITH (TABLOCK) SELECT TOP (10000) -1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) , REPLICATE('Z', 100) FROM master..spt_values t1 CROSS JOIN master..spt_values t2;請注意我沒有創建您已經擁有的任何一個非聚集索引。相反，我將為每個 IP 片段創建一個索引：
CREATE NONCLUSTERED INDEX [idx_IP1] ON [dbo].[TableA] ([IP1] ASC); CREATE NONCLUSTERED INDEX [idx_IP2] ON [dbo].[TableA] ([IP2] ASC); CREATE NONCLUSTERED INDEX [idx_IP3] ON [dbo].[TableA] ([IP3] ASC); CREATE NONCLUSTERED INDEX [idx_IP4] ON [dbo].[TableA] ([IP4] ASC);索引所需的空間並非微不足道。這是比較：
╔═══════════╦═════════════╗ ║ IndexName ║ IndexSizeKB ║ ╠═══════════╬═════════════╣ ║ idx_1 ║ 1786488 ║ ║ idx_2 ║ 1786480 ║ ║ idx_IP1 ║ 1487616 ║ ║ idx_IP2 ║ 1487616 ║ ║ idx_IP3 ║ 1487632 ║ ║ idx_IP4 ║ 1487608 ║ ║ PK_TableA ║ 2482056 ║ ╚═══════════╩═════════════╝如有必要，您可以權衡使用行或頁面壓縮來減小索引大小的利弊。但是，如果您不知道哪些 IP 片段將是
NULL並且您需要避免聚集索引掃描，那麼我看不到四個索引的更好替代方案。我將採用的策略稱為索引連接。非聚集索引包括聚集鍵TableAID，這使得將索引連接在一起成為可能。每個索引應該具有大約 0.4% 的選擇性，並且通過非聚集索引查找找到那些行應該相對便宜。將所有索引連接在一起應該會大大減少結果集，此時您可以對錶執行聚集索引搜尋以獲取您需要的其他列值，例如UserID.這是查詢：
DECLARE @ip1 TINYINT = ?; DECLARE @ip2 TINYINT = ?; DECLARE @ip3 TINYINT = ?; DECLARE @ip4 TINYINT = ?; SELECT DISTINCT a.UserID, a.IP1, a.IP2, a.IP3, a.IP4 FROM [dbo].[TableA] a JOIN [dbo].[TableB] b ON b.UserID = a.UserID WHERE ((@ip1 is NULL) OR (@ip1=a.IP1)) AND ((@ip2 is NULL) OR (@ip2=a.IP2)) AND ((@ip3 is NULL) OR (@ip3=a.IP3)) AND ((@ip4 is NULL) OR (@ip4=a.IP4)) OPTION (RECOMPILE, QUERYTRACEON 9481);有了
RECOMPILE提示，我正在利用參數嵌入優化。此優化僅適用於特定服務包（SP4？），因此請確保您已修補。如果它認為合適，查詢優化器能夠將單個表訪問TableA拆分為索引連接。請注意，估計的計劃在這裡很可能會產生誤導。你要注意實際的計劃。
QUERYTRACEON 9481提示不應包含在您的查詢版本中。我正在使用它來強制 SQL Server 使用舊版 CE，這只是必要的，因為我正在針對 SQL Server 2016 進行測試。讓我們進行一些測試。使用以下參數：
DECLARE @ip1 TINYINT = 1; DECLARE @ip2 TINYINT = 102; DECLARE @ip3 TINYINT = 234; DECLARE @ip4 TINYINT = 172;我得到合併連接和循環連接。
使用以下參數：
DECLARE @ip1 TINYINT = NULL; DECLARE @ip2 TINYINT = 102; DECLARE @ip3 TINYINT = 234; DECLARE @ip4 TINYINT = 172;我得到了一個非常相似的計劃，只是
IP1查詢中沒有使用索引。查詢仍然在大約 125 毫秒內完成。使用以下參數：
DECLARE @ip1 TINYINT = 88; DECLARE @ip2 TINYINT = NULL; DECLARE @ip3 TINYINT = NULL; DECLARE @ip4 TINYINT = NULL;查詢在大約 5 秒內完成。我得到一個散列連接和一個聚集索引掃描。這不一定是壞事，但可以通過一些努力來避免（見下一段）：
如果需要有效地強制索引連接（也許 SQL Server 2008 缺少我正在利用的優化），可以這樣做，但要復雜得多。
關於您的性能問題 - 取決於使用 #tempTable 的表變數的大小應該是更好的選擇。查詢優化器期望表變數總是返回 1 行（SQL Server 2016 中為 100），因此它可能會創建一個嵌套循環連接，這會增加額外的成本。使用臨時表，您可以獲得有效的統計數據和基數估計 = 更好的計劃。
此外，您不會從表變數中返回任何行，我認為這僅用於驗證目的，在這種情況下，我會將查詢更改為：
CREATE table #IPs (IP1 tinyint,IP2 tinyint, IP3 tinyint, IP4 tinyint) insert into #IPs values (1,2,3,4),(null,2,3,null) SELECT a.UserID, a.IP1, a.IP2, a.IP3, a.IP4 FROM [dbo].[TableA] a WITH (NOLOCK) WHERE EXISTS (Select 1 from #IPs as b where a.IP1 = b.IP1 or a.IP2 = b.IP2 OR a.IP3 = b.IP3 OR a.IP4 = b.IP4)
EXISTS如果出於驗證目的，我會使用而不是連接。它還將使您免於選擇不同的值，這可能會增加 tempdb（統計數據中的工作表）的成本，對它們進行排序並選擇其中一個。索引
您創建的包含 4 列的列將不起作用，因為您的查詢將這些列用作可選的 IP1、IP2 或 IP3，然後它必須進行查找以獲取您請求的其他剩餘列，因此查詢優化器估計而是更容易搜尋整個表格。
您可以做的是更改非聚集索引並將 UserID 列包括為：
CREATE NONCLUSTERED INDEX [idx_2] ON [dbo].[TableA] ( [IP1] ASC, [IP2] ASC, [IP3] ASC, [IP4] ASC ) INCLUDE ([UserID])