Sql-Server

意外的索引性能(一切都一樣)

  • November 21, 2016

我想看看在計算列上設置索引的效果,所以我創建了一個像這樣的表:

CREATE TABLE [Domain\UserName].[CompColIndexing](
   [a] [int] NOT NULL,
   [nonIndexedNonPersisted]  AS ([a]+(1)),
   [nonIndexedPersisted]  AS ([a]+(1)) PERSISTED,
   [IndexedNonPersisted]  AS ([a]+(1)),
   [IndexedPersisted]  AS ([a]+(1)) PERSISTED
) ON [DATA]

我已經800,000為此添加了行,其值為a循環09.

添加了以下索引:

CREATE NONCLUSTERED INDEX [IX_DJB_CompNonPersisted] ON [Domain\UserName].[CompColIndexing] 
(
   [IndexedNonPersisted] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [DATA]
GO

CREATE NONCLUSTERED INDEX [IX_DJB_CompPersisted] ON [Domain\UserName].[CompColIndexing] 
(
   [IndexedPersisted] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [DATA]
GO

然後我跑了一些ORDER BY子句來看看我會得到什麼性能差異,然後計劃看看改變值a會如何影響事情。

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY a

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY nonIndexedNonPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY IndexedNonPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY nonIndexedPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY IndexedPersisted

但出乎意料的是,我發現每個查詢都得到完全相同的結果:

探查器輸出

我至少期望SORT第一個查詢的操作會更慢,因為那個查詢沒有索引。

這裡發生了什麼事?

基數是故意低的,實際上,我實際上需要對三個不同的值進行排序。

我正在使用Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)

實際執行計劃見: https ://www.brentozar.com/pastetheplan/?id=S10MTuxGg

您擁有的表定義導致一些非常奇怪的優化器行為。我懷疑您遇到了此SE 文章中記錄的問題。為了避免這個問題,我將只使用

$$ a $$和$$ IndexedPersisted $$列。 查詢提示有助於找出優化器未選擇您期望的計劃的原因。在這裡,您希望使用索引,但 SQL Server 沒有使用它。讓我們並排查看兩個查詢計劃:

SELECT *
FROM [dbo].[CompColIndexing]
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

SELECT *
FROM [dbo].[CompColIndexing] WITH (INDEX([IX_DJB_CompPersisted]))
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

查詢計劃

查詢優化器認為表掃描後的排序比 800000 次 RID 查找便宜。也許它是錯誤的,所以讓我們執行查詢並比較它們的性能指標。

cpu_time    total_elapsed_time  logical_reads   reads
1204        1395                3087            1485
1516        1570                851798          0

在單獨的會話中執行查詢並檢查“執行後丟棄結果”後,我通過查看 sys.dm_exec_sessions 獲得了這些數字,因此我不必等待返回給客戶端的行。

這些數字在我看來是合理的。僅僅因為可以使用索引並不意味著應該使用它,特別是如果 SQL Server 需要使用索引讀取整個表。這是我能想到的最糟糕的索引案例。當從表中選擇一小部分行或當它們覆蓋索引時,索引可能非常有用。

如果我只選擇

$$ IndexedPersisted $$柱子。在這種情況下,SQL Server 認為使用索引比進行表掃描更便宜。比較兩種方法的程式碼:

-- force table scan
SELECT [IndexedPersisted]
FROM [dbo].[CompColIndexing] WITH (INDEX(0))
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

SELECT [IndexedPersisted]
FROM [dbo].[CompColIndexing] WITH (INDEX([IX_DJB_CompPersisted]))
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

查詢計劃 2

以下是性能數據:

cpu_time    total_elapsed_time  logical_reads   reads
1171        1212                2727            1088
343         406                 1798            0

現在索引是覆蓋索引,它是比表掃描更好的表訪問路徑。

引用自:https://dba.stackexchange.com/questions/155881