Sql-Server

索引列順序的 WHERE-JOIN-ORDER-(SELECT) 規則是否錯誤？

  • May 25, 2016

我正在嘗試改進這個（子）查詢，使其成為更大查詢的一部分：

select SUM(isnull(IP.Q, 0)) as Q, 
       IP.OPID 
   from IP
       inner join I
       on I.ID = IP.IID
   where 
       IP.Deleted=0 and
       (I.Status > 0 AND I.Status <= 19) 
   group by IP.OPID

Sentry Plan Explorer 為表 dbo 指出了一些相對昂貴的 Key Lookups。

$$ I $$由上面的查詢執行。

表 dbo.I

   CREATE TABLE [dbo].[I] (
 [ID]  UNIQUEIDENTIFIER NOT NULL,
 [OID]  UNIQUEIDENTIFIER NOT NULL,
 []  UNIQUEIDENTIFIER NOT NULL,
 []  UNIQUEIDENTIFIER NOT NULL,
 [] UNIQUEIDENTIFIER NULL,
 []  UNIQUEIDENTIFIER NOT NULL,
 []  CHAR (3) NOT NULL,
 []  CHAR (3)  DEFAULT ('EUR') NOT NULL,
 []  DECIMAL (18, 8) DEFAULT ((1)) NOT NULL,
 [] CHAR (10)  NOT NULL,
 []  DECIMAL (18, 8) DEFAULT ((1)) NOT NULL,
 []  DATETIME  DEFAULT (getdate()) NOT NULL,
 []  VARCHAR (35) NULL,
 [] NVARCHAR (100) NOT NULL,
 []  NVARCHAR (100) NULL,
 []  NTEXT  NULL,
 []  NTEXT  NULL,
 []  NTEXT  NULL,
 []  NTEXT  NULL,
 [Status]  INT DEFAULT ((0)) NOT NULL,
 []  DECIMAL (18, 2)  NOT NULL,
 [] DECIMAL (18, 2)  NOT NULL,
 [] DECIMAL (18, 2)  NOT NULL,
 [] DATETIME DEFAULT (getdate()) NULL,
 []  DATETIME NULL,
 []  NTEXT  NULL,
 []  NTEXT  NULL,
 [] TINYINT  DEFAULT ((0)) NOT NULL,
 []  DATETIME NULL,
 []  VARCHAR (50) NULL,
 []  DATETIME  DEFAULT (getdate()) NOT NULL,
 []  VARCHAR (50) NOT NULL,
 []  DATETIME NULL,
 []  VARCHAR (50) NULL,
 []  ROWVERSION NOT NULL,
 []  DATETIME NULL,
 []  INT  NULL,
 [] TINYINT DEFAULT ((0)) NOT NULL,
 [] UNIQUEIDENTIFIER NULL,
 []  TINYINT DEFAULT ((0)) NOT NULL,
 []  TINYINT  DEFAULT ((0)) NOT NULL,
 [] NVARCHAR (50)  NULL,
 [] TINYINT DEFAULT ((0)) NOT NULL,
 []  UNIQUEIDENTIFIER NULL,
 [] UNIQUEIDENTIFIER NULL,
 []  TINYINT  DEFAULT ((0)) NOT NULL,
 []  TINYINT DEFAULT ((0)) NOT NULL,
 []  UNIQUEIDENTIFIER NULL,
 []  DECIMAL (18, 2)  NULL,
 []  DECIMAL (18, 2)  NULL,
 [] DECIMAL (18, 2)  DEFAULT ((0)) NOT NULL,
 [] UNIQUEIDENTIFIER NULL,
 [] DATETIME NULL,
 [] DATETIME NULL,
 []  VARCHAR (35) NULL,
 [] DECIMAL (18, 2)  DEFAULT ((0)) NOT NULL,
 CONSTRAINT [PK_I] PRIMARY KEY NONCLUSTERED ([ID] ASC) WITH (FILLFACTOR = 90),
 CONSTRAINT [FK_I_O] FOREIGN KEY ([OID]) REFERENCES [dbo].[O] ([ID]),
 CONSTRAINT [FK_I_Status] FOREIGN KEY ([Status]) REFERENCES [dbo].[T_Status] ([Status])
);                  


GO
CREATE CLUSTERED INDEX [CIX_Invoice]
 ON [dbo].[I]([OID] ASC) WITH (FILLFACTOR = 90);

表 dbo.IP

CREATE TABLE [dbo].[IP] (
[ID] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
[IID] UNIQUEIDENTIFIER NOT NULL,
[OID] UNIQUEIDENTIFIER NOT NULL,
[Deleted] TINYINT DEFAULT ((0)) NOT NULL,
[] UNIQUEIDENTIFIER NULL,
[] UNIQUEIDENTIFIER NULL,
[] UNIQUEIDENTIFIER NULL,
[] UNIQUEIDENTIFIER NULL,
[] UNIQUEIDENTIFIER NULL,
[]UNIQUEIDENTIFIER NULL,
[] UNIQUEIDENTIFIER NULL,
[] UNIQUEIDENTIFIER NULL,
[] INT NOT NULL,
[] VARCHAR (35) NULL,
[] NVARCHAR (100) NOT NULL,
[] NTEXT NULL,
[] DECIMAL (18, 4) DEFAULT ((0)) NOT NULL,
[] NTEXT NULL,
[] NTEXT NULL,
[] DECIMAL (18, 4) DEFAULT ((0)) NOT NULL,
[] DECIMAL (18, 4) DEFAULT ((0)) NOT NULL,
[] DECIMAL (4, 2) NOT NULL,
[] INT DEFAULT ((1)) NOT NULL,
[] DATETIME DEFAULT (getdate()) NOT NULL,
[] VARCHAR (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[] DATETIME NULL,
[] VARCHAR (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[] ROWVERSION NOT NULL,
[] INT DEFAULT ((1)) NOT NULL,
[] DATETIME NULL,
[] UNIQUEIDENTIFIER NULL,
[] DECIMAL (18, 4) DEFAULT ((1)) NOT NULL,
[] DECIMAL (18, 4) DEFAULT ((1)) NOT NULL,
[] INT DEFAULT ((0)) NOT NULL,
[] UNIQUEIDENTIFIER NULL,
[] UNIQUEIDENTIFIER NULL,
[] UNIQUEIDENTIFIER NULL,
[] UNIQUEIDENTIFIER NULL,
[]UNIQUEIDENTIFIER NULL,
[]NVARCHAR (35) NULL,
[] VARCHAR (35) NULL,
[] NVARCHAR (35) NULL,
[] NVARCHAR (35) NULL,
[] NVARCHAR (35) NULL,
[] NVARCHAR (35) NULL,
[] NVARCHAR (35) NULL,
[] NVARCHAR (35) NULL,
[] UNIQUEIDENTIFIER NULL,
[] VARCHAR (12) NULL,
[] VARCHAR (4) NULL,
[] NVARCHAR (50) NULL,
[] NVARCHAR (50) NULL,
[] VARCHAR (35) NULL,
[] UNIQUEIDENTIFIER NULL,
[] UNIQUEIDENTIFIER NULL,
[] TINYINT DEFAULT ((0)) NOT NULL,
[] UNIQUEIDENTIFIER NULL,
[] TINYINT DEFAULT ((0)) NOT NULL,
[] TINYINT DEFAULT ((0)) NOT NULL,
[] NVARCHAR (50) NULL,
[] TINYINT DEFAULT ((0)) NOT NULL,
[] DECIMAL (18, 2) NULL,
[]TINYINT DEFAULT ((1)) NOT NULL,
[] UNIQUEIDENTIFIER NULL,
[] UNIQUEIDENTIFIER NULL,
[] UNIQUEIDENTIFIER NULL,
[] TINYINT DEFAULT ((1)) NOT NULL,
CONSTRAINT [PK_IP] PRIMARY KEY NONCLUSTERED ([ID] ASC) WITH (FILLFACTOR = 90),
CONSTRAINT [FK_IP_I] FOREIGN KEY ([IID]) REFERENCES [dbo].[I] ([ID]) ON DELETE CASCADE NOT FOR REPLICATION,
CONSTRAINT [FK_IP_XType] FOREIGN KEY ([XType]) REFERENCES [dbo].[xTYPE] ([Value]) NOT FOR REPLICATION
);

GO
CREATE CLUSTERED INDEX [IX_IP_CLUST]
ON [dbo].[IP]([IID] ASC) WITH (FILLFACTOR = 90);

表“I”有大約 100,000 行，聚集索引有 9,386 頁。

表 IP 是 I 的“子”表，大約有 175,000 行。

我嘗試按照索引列順序規則添加新索引：“WHERE-JOIN-ORDER-(SELECT)”

https://www.mssqltips.com/sqlservertutorial/3208/use-where-join-orderby-select-column-order-when-creating-indexes/

解決鍵查找並創建索引查找：

CREATE NONCLUSTERED INDEX [IX_I_Status_1]
   ON [dbo].[Invoice]([Status], [ID])

提取的查詢立即使用該索引。但是它所屬的原始較大查詢沒有。當我強迫它使用 WITH(INDEX(IX_I_Status_1)) 時，它甚至沒有使用它。

過了一會兒，我決定嘗試另一個新索引並更改為索引列的順序：

CREATE NONCLUSTERED INDEX [IX_I_Status_2]
   ON [dbo].[Invoice]([ID], [Status])

哇！該索引被提取的查詢和更大的查詢使用！

然後我通過強制它使用來比較提取的查詢 IO 統計資訊

$$ IX_I_Status_1 $$和$$ IX_I_Status_2 $$: 結果

$$ IX_I_Status_1 $$:

Table 'I'. Scan count 5, logical reads 636, physical reads 16, read-ahead reads 574
Table 'IP'. Scan count 5, logical reads 1134, physical reads 11, read-ahead reads 1040
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0

結果

$$ IX_I_Status_2 $$:

Table 'I'. Scan count 1, logical reads 615, physical reads 6, read-ahead reads 631
Table 'IP'. Scan count 1, logical reads 1024, physical reads 5, read-ahead reads 1040
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,  read-ahead reads 0

好的，我可以理解超大型怪物查詢可能過於復雜，無法讓 SQL 伺服器擷取理想的執行計劃，並且可能會錯過我的新索引。但我不明白為什麼索引

$$ IX_I_Status_2 $$似乎更適合和更有效的查詢。 由於查詢首先按列 STATUS 過濾表 I 然後與表 IP 連接，所以我不明白為什麼

$$ IX_I_Status_2 $$更好，由 Sql Server 使用，而不是$$ IX_I_Status_1 $$?

索引列順序的 WHERE-JOIN-ORDER-(SELECT) 規則是否錯誤？

至少它是不完整的並且可能具有誤導性的建議（我沒有費心閱讀整篇文章）。如果您要閱讀 Internet 上的內容（包括此內容），您應該根據您對作者的了解和信任程度調整您的信任程度，但始終要自己驗證。

創建索引有許多“經驗法則”，具體取決於具體情況，但沒有一個可以很好地替代您自己理解核心問題。閱讀 SQL Server 中索引和執行計劃運算符的實現，通過一些練習，對如何使用索引來提高執行計劃的效率有一個很好的理解。獲得這種知識和經驗沒有有效的捷徑。

一般來說，我可以說您的索引通常應該首先具有用於相等性測試的列，最後是任何不等式，和/或由索引上的過濾器提供。這不是一個完整的陳述，因為索引也可以提供順序，這在某些情況下可能比直接查找一個或多個鍵更有用。例如，排序可用於避免排序、降低物理連接選項（如合併連接）的成本、啟用流聚合、快速找到前幾個符合條件的行……等等。

我在這裡有點含糊，因為為查詢選擇理想的索引取決於很多因素 - 這是一個非常廣泛的話題。

無論如何，在查詢中找到“最佳”索引的衝突信號並不罕見。例如，您的連接謂詞希望行以一種方式排序以用於合併連接，group by 希望行以另一種方式排序以用於流聚合，並且使用 where 子句謂詞查找符合條件的行將建議其他索引。

索引既是一門藝術又是一門科學的原因在於，理想的組合在邏輯上並不總是可能的。為工作負載（不僅僅是單個查詢）選擇最佳折衷索引需要分析技能、經驗和特定於系統的知識。如果這很簡單，那麼自動化工具將是完美的，而對性能調整顧問的需求就會少得多。

就缺失的索引建議而言：這些都是機會主義的。當優化器嘗試將謂詞和所需的排序順序與不存在的索引匹配時，它會引起您的注意。因此，這些建議是基於當時正在考慮的特定子計劃變化的特定背景下的特定匹配嘗試。

在上下文中，根據優化器的模型，在降低數據訪問的估計成本方面，這些建議總是有意義的。它不會對整個查詢進行更廣泛的分析（更不用說更廣泛的工作量），因此您應該將這些建議視為技術人員需要查看可用索引的溫和提示，並以這些建議作為開始點（通常僅此而已）。

在您的情況下，該(Status) INCLUDE (ID)建議可能是在考慮雜湊或合併連接的可能性時提出的（稍後範例）。在這種狹隘的背景下，這個建議是有道理的。對於整個查詢，也許不是。該索引(ID, Status)啟用了嵌套循環連接，並ID作為外部引用：每次迭代ID時相等搜尋和不相等。Status

一種可能的索引選擇是：

CREATE INDEX i1 ON dbo.I (ID, [Status]);
CREATE INDEX i1 ON dbo.IP (Deleted, OPID, IID) INCLUDE (Q);

…這會產生一個計劃，例如：

可能的計劃

我並不是說這些索引對您來說是最佳的；他們碰巧為我制定了一個看起來合理的計劃，但無法看到所涉及表的統計資訊，或者完整的定義和現有的索引。此外，我對更廣泛的工作量或實際查詢一無所知。

或者（只是為了展示無數額外的可能性之一）：

CREATE INDEX i1 ON dbo.I ([Status]) INCLUDE (ID);
CREATE INDEX i1 ON dbo.IP (Deleted, IID, OPID) INCLUDE (Q);

給出：

備用計劃

執行計劃是使用SQL Sentry Plan Explorer生成的。

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

相關問答

Sql-Server

查找和刪除重複索引

  • December 18, 2019
檢視問答
Sql-Server

我可以信任數據庫引擎優化顧問的建議嗎？

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

用許多值重建表中的索引

  • December 2, 2014
檢視問答
Sql-Server

大型表的 SQL Server 2008R2 XML 列索引策略？

  • December 2, 2014
檢視問答
Sql-Server

索引交點是否適用於包含的列？

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

如果索引不是最具選擇性的，如何提高性能？

  • March 3, 2014
檢視問答