為什麼我的索引沒有在 SELECT TOP 中使用？

December 19, 2016

這是破敗：我正在做一個選擇查詢。WHEREand子句中的每一列ORDER BY都在一個非聚集索引*IX_MachineryId_DateRecorded中，或者作為鍵的一部分，或者作為INCLUDE列。我選擇了所有*的列，這將導致書籤查找，但我只接受TOP (1)，所以伺服器可以肯定地告訴查找只需要在最後完成一次。
最重要**的是，當我強制查詢使用 index*IX_MachineryId_DateRecorded時，它會在不到一秒的時間內執行。*如果我讓伺服器決定使用哪個索引，它會選擇IX_MachineryId，最多需要一分鐘。這確實向我表明我已經正確地建立了索引，而伺服器只是做出了一個錯誤的決定。為什麼？
CREATE TABLE [dbo].[MachineryReading] (
   [Id]                 INT              IDENTITY (1, 1) NOT NULL,
   [Location]           [sys].[geometry] NULL,
   [Latitude]           FLOAT (53)       NOT NULL,
   [Longitude]          FLOAT (53)       NOT NULL,
   [Altitude]           FLOAT (53)       NULL,
   [Odometer]           INT              NULL,
   [Speed]              FLOAT (53)       NULL,
   [BatteryLevel]       INT              NULL,
   [PinFlags]           BIGINT           NOT NULL,
   [DateRecorded]       DATETIME         NOT NULL,
   [DateReceived]       DATETIME         NOT NULL,
   [Satellites]         INT              NOT NULL,
   [HDOP]               FLOAT (53)       NOT NULL,
   [MachineryId]        INT              NOT NULL,
   [TrackerId]          INT              NOT NULL,
   [ReportType]         NVARCHAR (1)     NULL,
   [FixStatus]          INT              DEFAULT ((0)) NOT NULL,
   [AlarmStatus]        INT              DEFAULT ((0)) NOT NULL,
   [OperationalSeconds] INT              DEFAULT ((0)) NOT NULL,
   CONSTRAINT [PK_dbo.MachineryReading] PRIMARY KEY CLUSTERED ([Id] ASC),
   CONSTRAINT [FK_dbo.MachineryReading_dbo.Machinery_MachineryId] FOREIGN KEY ([MachineryId]) REFERENCES [dbo].[Machinery] ([Id]) ON DELETE CASCADE,
   CONSTRAINT [FK_dbo.MachineryReading_dbo.Tracker_TrackerId] FOREIGN KEY ([TrackerId]) REFERENCES [dbo].[Tracker] ([Id]) ON DELETE CASCADE
);

GO
CREATE NONCLUSTERED INDEX [IX_MachineryId]
   ON [dbo].[MachineryReading]([MachineryId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_TrackerId]
   ON [dbo].[MachineryReading]([TrackerId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_MachineryId_DateRecorded]
   ON [dbo].[MachineryReading]([MachineryId] ASC, [DateRecorded] ASC)
   INCLUDE([OperationalSeconds], [FixStatus]);
該表被劃分為月份範圍（儘管我仍然不太了解那裡發生了什麼）。
ALTER PARTITION SCHEME PartitionSchemeMonthRange NEXT USED [Primary]
ALTER PARTITION FUNCTION [PartitionFunctionMonthRange]() SPLIT RANGE(N'2016-01-01T00:00:00.000') 

ALTER PARTITION SCHEME PartitionSchemeMonthRange NEXT USED [Primary]
ALTER PARTITION FUNCTION [PartitionFunctionMonthRange]() SPLIT RANGE(N'2016-02-01T00:00:00.000') 
...

CREATE UNIQUE CLUSTERED INDEX [PK_dbo.MachineryReadingPs] ON MachineryReading(DateRecorded, Id) ON PartitionSchemeMonthRange(DateRecorded)
我通常會執行的查詢：
SELECT TOP (1) [Id], [Location], [Latitude], [Longitude], [Altitude], [Odometer], [ReportType], [FixStatus], [AlarmStatus], [Speed], [BatteryLevel], [PinFlags], [DateRecorded], [DateReceived], [Satellites], [HDOP], [OperationalSeconds], [MachineryId], [TrackerId]
   FROM [dbo].[MachineryReading]
   --WITH(INDEX(IX_MachineryId_DateRecorded)) --This makes all the difference
   WHERE ([MachineryId] = @p__linq__0) AND ([DateRecorded] &gt;= @p__linq__1) AND ([DateRecorded] &lt; @p__linq__2) AND ([OperationalSeconds] &gt; 0)
   ORDER BY [DateRecorded] ASC
查詢計劃：https ://www.brentozar.com/pastetheplan/?id=r1c-RpxNx
帶強制索引的查詢計劃：https ://www.brentozar.com/pastetheplan/?id=SywwTagVe
包含的計劃是實際的執行計劃，但在暫存數據庫上（大約是 live 大小的 1/100）。我對擺弄實時數據庫猶豫不決，因為我大約一個月前才開始在這家公司工作。
我有一種感覺，這是因為分區，而我的查詢通常跨越每個分區（例如，當我想OperationalSeconds為一台機器獲取第一個或最後一個記錄時）。但是，我一直在手工編寫的查詢的執行速度都比EntityFramework生成的快 10 到 100 倍，所以我只是要創建一個儲存過程。

如果我讓伺服器決定使用哪個索引，它會選擇*IX_MachineryId*，最多需要一分鐘。
該索引未分區，因此優化器辨識出它可用於提供查詢中指定的排序而無需排序。作為非唯一的非聚集索引，它也有聚集索引的鍵作為子鍵，所以可以使用索引來查找MachineryId和DateRecorded範圍：
該索引不包括OperationalSeconds，因此該計劃必須在（分區）聚集索引中的每行查找該值才能測試OperationalSeconds > 0：
優化器估計需要從非聚集索引中讀取一行並進行查找以滿足TOP (1). 此計算基於行目標（快速找到一行），並假設值的均勻分佈。
從實際計劃中，我們可以看到 1 行的估計是不准確的。實際上，必須處理 19,039 行才能發現沒有行滿足查詢條件。這是行目標優化的最壞情況（估計 1 行，實際需要所有行）：
您可以使用跟踪標誌 4138禁用行目標。這很可能會導致 SQL Server 選擇不同的計劃，可能是您強制的計劃。*IX_MachineryId*在任何情況下，可以通過包含使索引更加優化OperationalSeconds。
具有非對齊的非聚集索引（索引以與基表不同的方式分區，包括根本不分區）是非常不尋常的。
這確實向我表明我已經正確地建立了索引，而伺服器只是做出了一個錯誤的決定。為什麼？
像往常一樣，優化器正在選擇它認為最便宜的計劃。
計劃的估計成本*IX_MachineryId*為 0.01 個成本單位，基於（不正確的）行目標假設，即將測試並返回一行。
該*IX_MachineryId_DateRecorded*計劃的估計成本要高得多，為 0.27 個單位，主要是因為它希望從索引中讀取 5,515 行，對它們進行排序，然後返回排序最低的行（按DateRecorded）：
該索引是分區的，不能DateRecorded直接按順序返回行（見後文）。它可以查找每個分區內MachineryId的DateRecorded範圍，但需要進行排序：
如果此索引未分區，則不需要排序，並且它與具有額外包含列的其他（未分區）索引非常相似。未分區的過濾索引仍然會稍微更有效。
您應該更新源查詢，以便和參數的數據類型與列 ( )匹配。目前，由於執行時類型不匹配，SQL Server 正在計算一個動態範圍（使用 Merge Interval 運算符及其子樹）：@From``@To``DateRecorded``datetime
&lt;ScalarOperator ScalarString="GetRangeWithMismatchedTypes([@From],NULL,(22))"&gt;
&lt;ScalarOperator ScalarString="GetRangeWithMismatchedTypes([@To],NULL,(22))"&gt;
這種轉換會阻止優化器正確推理升序分區ID（按升序覆蓋一系列DateRecorded值）和不等式謂詞 on之間的關係DateRecorded。
分區 ID 是分區索引的隱式前導鍵。通常，優化器可以看到按分區 ID 排序（其中升序 ID 映射到的升序、不相交的值DateRecorded）與單獨DateRecorded排序相同（假設它是常數）。這個推理鏈被類型轉換打破了。DateRecorded``MachineryID
展示
一個簡單的分區表和索引：
CREATE PARTITION FUNCTION PF (datetime)
AS RANGE LEFT FOR VALUES ('20160101', '20160201', '20160301');

CREATE PARTITION SCHEME PS AS PARTITION PF ALL TO ([PRIMARY]);

CREATE TABLE dbo.T (c1 integer NOT NULL, c2 datetime NOT NULL) ON PS (c2);

CREATE INDEX i ON dbo.T (c1, c2) ON PS (c2);

INSERT dbo.T (c1, c2) 
VALUES (1, '20160101'), (1, '20160201'), (1, '20160301');
查詢匹配類型
-- Types match (datetime)
DECLARE 
   @From datetime = '20010101',
   @To datetime = '20090101';

-- Seek with no sort
SELECT T2.c2 
FROM dbo.T AS T2 
WHERE T2.c1 = 1 
AND T2.c2 &gt;= @From
AND T2.c2 &lt; @To
ORDER BY 
   T2.c2;
類型不匹配的查詢
-- Mismatched types (datetime2 vs datetime)
DECLARE 
   @From datetime2 = '20010101',
   @To datetime2 = '20090101';

-- Merge Interval and Sort
SELECT T2.c2 
FROM dbo.T AS T2 
WHERE T2.c1 = 1 
AND T2.c2 &gt;= @From
AND T2.c2 &lt; @To
ORDER BY 
   T2.c2;