SQL Server - 高度傾斜數據分佈的查詢優化

我正在嘗試優化一個類似於此的查詢：

select top(1)
   t1.Table1ID,
   t1.Column1,
   t1....
   ....
   t2.Table2ID,
   t2....
   ....
   c.FirstName,
   c.LastName,
   c....
from BigTable1 t1
join BigTable2 t2
   on t1.Table1ID = t2.Table1ID
join Customer c
   on t2.CustomerID = c.CustomerID
join Table4 t4
   on t4.Table4ID = t2.Table4ID
join Table5 t5
   on t5.Table5ID = t1.Table5ID
join Table6 t6
   on t6.Table6ID = t5.Table6ID
where 
   t4.Column1 = @p1
   and t1.Column1 = @p2
   and t3.FirstName = @FirstName
   and t3.LastName = @LastName
   and t6.Column1 = @p5
   and (@p6 is null or t2.Column6 = @p6)
order by t2.Table2ID desc
option(recompile);

BigTable1、BigTable2、Customer - 是大型事務表（數億行），Table4、Table5、Table6 是相對靜態的小型查找表。問題是這些大表中的數據分佈非常不平衡，因此該查詢通常執行得很差（執行計劃中的估計行數與實際有很大差異）。更新這些大表的統計資訊並沒有幫助（直方圖中的 200 個步驟不足以覆蓋數據分佈中的所有偏差）。例如，在 Customer 表中，有一些 (FirstName, LastName) 組合對應於大約 500k 條記錄。

我看到 2 個選項可以提高此類查詢的性能：

1. 將此查詢拆分為較小的查詢並將中間結果保存到臨時表中。通過這種將中間結果具體化到臨時表中的方法，我們可以為優化器提供更好的基數估計機會，但是我們在 tempdb 上添加了大量的額外負載（因為此查詢執行得相當頻繁，最多每秒幾次）。另一個缺點是它並不是對所有參數值都更快，對於非典型參數值似乎要好得多（當原始查詢可能需要幾分鐘時，這只需要幾秒鐘），但對於對應於更多或更少的參數使用臨時表的方法較慢。
2. 為尖峰值創建過濾統計資訊，例如對於 Customer 表，它看起來像這樣：

declare @sql nvarchar(max) = N'', @i int, @N int;
select top (1000)
identity(int,1,1) as id,
FirstName,
LastName,
count(*) as cnt
into #FL
from Customer
where 
FirstName is not null 
and LastName is not null
group by
FirstName,
LastName
order by cnt desc;

set @N = @@ROWCOUNT;
set @i = 1;

while @i <= @N
begin
   select @sql = 'CREATE STATISTICS Customer_FN_LN_' + cast(id as varchar(10)) + ' ON dbo.Customer(CustomerID) WHERE FirstName = ''' + FirstName + ''' AND LastName = ''' + LastName + ''' WITH FULLSCAN, NORECOMPUTE'
from #FL
where id = @i;
exec sp_executesql @sql;
set @i = @i + 1;
end;

因此，如果我們為這 3 個大表創建過濾後的統計資訊並重新創建這些統計資訊，比如說，每週，我們應該可以在原始查詢中進行行估計。

根據我以前的經驗，我通常使用臨時表的方法，但在這種情況下，過濾統計資訊的方法看起來更有吸引力。我還沒有在生產中使用它，我很好奇可能是什麼缺點。

所以我的問題是：

1. 還有其他方法可以幫助優化器處理高度傾斜的數據分佈嗎？
2. 在第二種方法中手動創建和處理過濾統計數據的缺點是什麼？

1. 還有其他方法可以幫助優化器處理高度傾斜的數據分佈嗎？

過濾統計資訊和使用中間臨時表（正確！）分解查詢是主要選項，但您也可以考慮如何使用索引視圖來提供幫助。如果實施得當，索引視圖應該與基表上的額外非聚集索引具有大致相同的影響。

使用WITH (NOEXPAND)而不是依賴於自動匹配（僅限企業版）將允許優化器在索引視圖上創建和使用統計資訊。

更一般地說，如果您能夠提前辨識“安全”或“不安全”值，您可以考慮採用 Kimberly L. Tripp的建構高性能儲存過程中詳述的混合方法（包括動態 SQL）。

您還可以考慮針對不同情況優化多個單獨的過程，在每個過程中使用適當的方法，包括OPTIMIZE FOR.

最後，您可以通過查詢儲存（如果可用）獲得計劃指南和/或強制計劃。

2. 在第二種方法中手動創建和處理過濾統計數據的缺點是什麼？

主要是圍繞過濾統計資訊的問題沒有像人們希望的那樣頻繁更新。您可以通過手動刷新這些統計資訊來解決此問題。

您已經在重新編譯，因此應該解決使用帶有參數化查詢的過濾統計資訊。

我嘗試了一種使用物化視圖的方法，它的查詢性能非常好，執行時間在 20 毫秒內保持一致，這是一個巨大的改進。數據修改的性能下降似乎也很寬容（在我的情況下）。但是，我認為我們無法在生產中使用此解決方案，因為它需要停機。不幸的是，無法在具有 (ONLINE=ON) 的視圖上創建聚集索引。在連接幾個大表的視圖上創建聚集索引實際上需要相當長的時間，在我的情況下大約是半小時。此外，如果我們已經有一個索引視圖，但需要更改基礎表，我們必須刪除視圖並重新創建它，這裡我們回到最初在視圖上創建聚集索引並因此停機的問題再次。

-- indexed view:
create view vBigView with schemabinding
as
select
   t2.Table2ID,
   t2.Table1ID,
   t2.CustomerID,
   t1.Column1,
   t2.Table4ID,
   t5.Table5ID,
   t5.Table6ID.
   t2.Column6,
   c.FirstName,
   c.LastName
from BigTable1 t1
join BigTable2 t2
   on t1.Table1ID = t2.Table1ID
join Customer c
   on t2.CustomerID = c.CustomerID
join Table5 t5
   on t5.Table5ID = t1.Table5ID
go
create unique clustered index UQ_vBigView on vBigView(
   Table4ID,
   Column1,
   Table6ID,
   FirstName,
   LastName,
   Column6,
   Table2ID
) with (sort_in_tempdb = on, online = on, maxdop = 4); -- this what I was hoping to do, unfortunately, ONLINE option does not work for clustered indexes on views, so this will throw an error!
go

-- modified query
declare
   @Table4ID tinyint,
   @Table6ID tinyint;

set @Table4ID = (select Table4ID from Table4 where Column1 = @p1); -- Column1 is unique
set @Table6ID = (select Table6ID from Table6 where Column1 = @p5); -- Column1 is unique

:with cte as(
select top (1)
   Table2ID,
   Table1ID,
   CustomerID,
   Column1,
   Table4ID,
   Table5ID,
   Table6ID.
   Column6,
   FirstName,
   LastName
from vBigView with(noexpand)
where 
   Table4ID = @Table4ID
   and Column1 = @p2
   and Table6ID = @Table6ID
   and FirstName = @FirstName
   and LastName = @LastName
   and (@p6 is null or Column6 = @p6)
order by Table2ID desc
)
select
   t.Table1ID,
   t.Column1,
   t....
   ....
   t.Table2ID,
   t2....
   ....
   t.FirstName,
   t.LastName,
   c....
from cte t
join BigTable2 t2
   on t2.Table2ID = t.Table2ID
join Customer c
   on c.CustomerID = t.CustomerID
join Table4 t4
   on t4.Table4ID = t.Table4ID
join Table5 t5
   on t5.Table5ID = t.Table5ID
join Table6 t6
   on t6.Table6ID = t5.Table6ID
option(recompile);

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