SQL Server 查詢中的索引使用率問題

這些天來，我必須優化某人很久以前編寫的查詢，並且我沒有他們背後的業務資訊。因此嘗試以安全的方式優化它們，而不會妨礙業務邏輯。在調整查詢時，我遇到了一些我想澄清的疑問。

表 source_price_curve 如下：

CREATE TABLE [dbo].[source_price_curve](
   [source_curve_def_id] [int] NOT NULL,
   [as_of_date] [datetime] NOT NULL,
   [Assessment_curve_type_value_id] [int] NOT NULL,
   [curve_source_value_id] [int] NOT NULL,
   [maturity_date] [datetime] NOT NULL,
   [curve_value] [float] NOT NULL,
   [create_user] [varchar](50) NULL,
   [create_ts] [datetime] NULL,
   [update_user] [varchar](50) NULL,
   [update_ts] [datetime] NULL,
   [bid_value] [float] NULL,
   [ask_value] [float] NULL,
   [is_dst] [int] NOT NULL,
CONSTRAINT [IX_unique_source_curve_def_id_index] UNIQUE NONCLUSTERED 
(
   [as_of_date] ASC,
   [source_curve_def_id] ASC,
   [maturity_date] ASC,
   [is_dst] ASC,
   [curve_source_value_id] ASC,
   [Assessment_curve_type_value_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[source_price_curve]  WITH NOCHECK ADD  CONSTRAINT [FK_source_curve_def_id] FOREIGN KEY([source_curve_def_id])
REFERENCES [dbo].[source_price_curve_def] ([source_curve_def_id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[source_price_curve] CHECK CONSTRAINT [FK_source_curve_def_id]
GO

它有大約 1.3 億條記錄。在此表中定義了以下索引。

CREATE CLUSTERED INDEX [source_curve_def_id_index] ON [dbo].[source_price_curve]
(
   [as_of_date] ASC,
   [source_curve_def_id] ASC,
   [maturity_date] ASC,
   [is_dst] ASC,
   [curve_source_value_id] ASC
)
CREATE NONCLUSTERED INDEX [source_price_curve_123] ON [dbo].[source_price_curve]
(
   [source_curve_def_id] ASC,
   [as_of_date] ASC
)
INCLUDE (   [maturity_date]) 
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_as_of_date_curve_source_value_id] 
ON [dbo].[source_price_curve]
(
   [as_of_date] ASC,
   [curve_source_value_id] ASC
)
INCLUDE (   [curve_value],
   [maturity_date],
   [source_curve_def_id])
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_as_of_date_curve_source_value_id_Assessment_curve_type_value_id] 
ON [dbo].[source_price_curve]
(
   [as_of_date] ASC,
   [curve_source_value_id] ASC,
   [Assessment_curve_type_value_id] ASC
)
INCLUDE (   [curve_value],
   [maturity_date],
   [source_curve_def_id]) 
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_maturity_date] ON [dbo].[source_price_curve]
(
   [maturity_date] ASC
)
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_source_curve_def_id_curve_source_value_id]
ON [dbo].[source_price_curve]
(
   [source_curve_def_id] ASC,
   [curve_source_value_id] ASC
)
INCLUDE (   [as_of_date],
   [Assessment_curve_type_value_id],
   [curve_value],
   [is_dst],
   [maturity_date]) 
CREATE NONCLUSTERED INDEX [IX_PT_source_price_curve_source_curve_def_id111] 
ON [dbo].[source_price_curve]
(
   [source_curve_def_id] ASC,
   [Assessment_curve_type_value_id] ASC,
   [curve_source_value_id] ASC,
   [maturity_date] ASC,
   [as_of_date] ASC
)

我執行如下查詢：

SELECT @tenor_from =CONVERT(DATETIME,MIN(spc.maturity_date),103)  
FROM source_price_curve spc  
WHERE spc.as_of_date >= @as_of_date_from

此查詢的執行計劃在以下連結中提供： https ://www.brentozar.com/pastetheplan/?id=BJ2_3boSZ

我想知道為什麼當過濾條件為 as_of_date（從 source_price_curve 選擇數據時）時，它使用索引 IX_PT_source_price_curve_maturity_date（僅在到期日期列上具有索引）。為什麼不使用以 as_of_date 作為索引順序的第一列的聚集索引？

提前致謝。

您的查詢正在尋找大於給定值maturity_date的行中的最小值。as_of_date

優化器必須在兩種主要執行策略之間進行選擇：

1. 掃描按順序排列的索引，maturity_date ASC直到找到第一行where as_of_date >= @as_of_date_from。

一旦查詢處理器找到第一行，索引的掃描就會停止。這是閱讀執行計劃時經常忽略的一點：索引掃描不一定總是掃描整個索引。

如果可以快速找到第一個符合條件的行，則對有序索引進行部分掃描是您查詢的一個很好的策略。另一方面，如果與as_of_date謂詞匹配的第一行出現在索引的末尾（按maturity_date順序），則這是一個糟糕的策略。

as_of_date因此，優化器會評估在找到與謂詞匹配的行之前必須掃描索引中的行數。該評估基於均勻分佈的假設（在實踐中通常不正確）。

您提供的計劃表明，索引掃描必須讀取 1,101,479 行（按到期日期順序），然後才能找到與as_of_date謂詞匹配的第一個行。 2. 尋找一個索引（以as_of_date作為前導鍵）來查找所有行 where as_of_date >= @as_of_date_from，然後計算在這些行中找到的最小值 maturity_date。

如果as_of_date謂詞匹配相對較少的行，這是一個很好的策略。如果預期有很多行符合條件，那就不太好了。此策略的預期成本取決於 的值@as_of_date_from和有關列的統計資訊as_of_date。

您的查詢使用局部變數，因此優化器無法“看到”其中包含的值，並猜測未知值的選擇性。

對於不等式謂詞，預設猜測是 30% 的行符合條件。對於 1.3 億行的表，優化器因此會猜測 30% * 130,000,000 = 39,000,000 行將匹配。這是很多行來計算最小值maturity_date，優化器更喜歡其他策略。

OPTION (RECOMPILE)您可以通過向查詢添加提示來允許優化器在執行時“查看”變數內的值。這啟用了參數嵌入優化，代價是每次執行查詢時都必須重新編譯查詢計劃。

如果@as_of_date_from變數值在執行之間變化很大，那麼使用OPTION (RECOMPILE)提示可能對您來說是值得的。優化器可能仍然選擇部分有序掃描策略，這取決於它對兩種策略的相對成本的評估。

---

在任何情況下，您都應該更改 to 的數據類型@as_of_date_from以datetime匹配as_of_date列以避免隱式轉換。

的數據類型@tenor_from應該可能datetime不是varchar(20)。或將查詢更改為CONVERT(varchar(20),MIN(spc.maturity_date),103). 電流CONVERT沒有意義。

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