意外的索引性能(一切都一樣)
我想看看在計算列上設置索引的效果,所以我創建了一個像這樣的表:
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
循環0
到9
.添加了以下索引:
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);
以下是性能數據:
cpu_time total_elapsed_time logical_reads reads 1171 1212 2727 1088 343 406 1798 0
現在索引是覆蓋索引,它是比表掃描更好的表訪問路徑。