對SQL Server索引包含列（Include）的一點認識

2024-08-31 00:56:06

字體：大中小

來源：轉載

供稿：網友

轉至http://blog.csdn.net/zhengldg/article/details/9128723

測試數據

[sql] view plain copy

Drop table [dbo].[TestTb]; CREATE TABLE [dbo].[TestTb]( [Number] [char](20) Not NULL, [Dt] [datetime] Not NULL, [Id] [int] IDENTITY(1,1) ) Declare @rn as int = 1; Begin Tran While(@rn <= 100000) Begin Insert Into [dbo].[TestTb](Number,Dt) Select 'Number'+CAST(@rn as CHAR(7)),DATEADD(MM,CHECKSUM(NewId())%60,GETDATE()); Set @rn = @rn + 1 End Commit 創建一個沒有包含列的非聚集索引：

[sql] view%20plain copy

Create Unique Nonclustered Index idx_nc_number On [dbo].[TestTb](Number); --查詢索引信息 Select id.name,p.index_level,p.page_count,p.index_id, avg_record_size_in_bytes,max_record_size_in_bytes,min_record_size_in_bytes from sys.dm_db_index_physical_stats(DB_ID('StudyDB'),Object_id('dbo.testtb'),null,null,'Detailed') as p Inner Join sys.indexes as id On p.index_id = id.index_id And p.object_id = id.object_id And p.index_id > 1 結果

從上圖可以看到，目前索引是3級結構，其中Index_Level為0的表示索引的葉級，為1、2的表示索引的非葉級，目前索引葉級的Size是

這樣計算（索引鍵+RID+行開銷）：20字節鍵列+8字節Rid+1字節系統開銷%20=%2029字節，索引非葉級（索引鍵+子頁指針+行開銷）：20字節鍵列+6字節Rid+1字節系統開銷%20=%2027字節

測試：未添加索引包含列前的查詢

[sql] view%20plain copy

DBCC DropCleanBuffers DBCC FreePRocCache Set Statistics IO On; Select Number,dt From dbo.TestTb Where Number like 'Number99835' Set Statistics IO Off; /* (1 行受影響) 表 'TestTb'。掃描計數 0，邏輯讀取 4 次，物理讀取 1 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 */ [sql] view%20plain copy

<strong>結果</strong> 可以看到，沒添加包含列之前，優化器選擇的是索引Seek%20+%201次Rid查找，

為什么此時要進行Rid查找呢，經過前面的分析我們已經知道，此時索引葉級長度為27，根本就沒有存儲dt列，

因此不得不根據8字節的RID定位到頁并進行物理讀取，因此出現了物理讀取1次。物理讀取是開銷十分大的操作，

如果這樣的操作過多，對查詢效率的影響可想而知。而索引包含列便是為解決此類問題而提出。

[sql] view%20plain copy

--添加包含列 Drop index idx_nc_number On dbo.TestTb Create Unique Nonclustered Index idx_nc_number On dbo.TestTb(Number) Include(dt); 結果

再運行以上查詢索引各級記錄長度，可以看到此時索引的葉級增加了dt列的8個字節，此時長度：20字節鍵列+8字節Rid+1字節系統開銷%20+8字節dt長度%20=%2037字節，因為此時在索引的葉級存儲了dt列，而索引的非葉級長度沒有改變，仍然是27字節。

[sql] view%20plain copy

DBCC DropCleanBuffers DBCC FreeProcCache Set Statistics Io On; Select Number,dt From dbo.TestTb Where Number = 'Number99835' Set Statistics Io Off; /* (1 行受影響) 表 'TestTb'。掃描計數 0，邏輯讀取 3 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。 */ 結果

運行查詢，可以看到，添加包含列后，邏輯掃描3次，其中三層索引占了3頁，物理掃描不見了，因為此時在索引的葉級就可以找到列dt了。

查看此時的執行計劃，是不是已經沒有書簽查找了呢。

[sql] view%20plain copy

Drop index idx_nc_number On dbo.TestTb

以上測試中，dbo.TestTb的組織形式是堆，也就是說該表沒有聚集索引。當表中有聚集索引時，大致情況跟以上類似，只不過此時非聚集索引葉級不再存儲RID，而是存儲聚集鍵或者聚集鍵+唯一標識。

值得注意的是，當非聚集索引不是唯一時，非聚集索引的非葉級會包括：索引鍵，子頁指針，書簽值。其中當表為聚集表時書簽值為聚集索引鍵或者聚集索引鍵值+唯一標識，當表組織為堆時，書簽值為8字節RID以下代碼dbo.TestTb上創建一個非唯一非聚集索引

[sql] view%20plain copy

Create Nonclustered Index idx_nc_number On dbo.TestTb(Number) Include(dt);