對SQL Server索引包含列（Include）的一點認(rèn)識

2024-08-31 00:56:06

字體：大中小

來源：轉(zhuǎn)載

供稿：網(wǎng)友

轉(zhuǎn)至http://blog.csdn.net/zhengldg/article/details/9128723

測試數(shù)據(jù)

[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 創(chuàng)建一個沒有包含列的非聚集索引：

[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 結(jié)果

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

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

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

[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'。掃描計數(shù) 0，邏輯讀取 4 次，物理讀取 1 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 */ [sql] view%20plain copy

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

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

因此不得不根據(jù)8字節(jié)的RID定位到頁并進(jìn)行物理讀取，因此出現(xiàn)了物理讀取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); 結(jié)果

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

[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'。掃描計數(shù) 0，邏輯讀取 3 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。 */ 結(jié)果

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

查看此時的執(zhí)行計劃，是不是已經(jīng)沒有書簽查找了呢。

[sql] view%20plain copy

Drop index idx_nc_number On dbo.TestTb

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

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

[sql] view%20plain copy

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

再運行以上查詢索引各級記錄長度，此時，相比較之前創(chuàng)建的唯一非聚集索引，非唯一非聚集索引的非葉級居然多了8字節(jié)RID書簽值。

我覺此時似乎是嚴(yán)重冗余了，盡管是非常有必要的。當(dāng)表組織為堆時還好，最多也就增加8字節(jié)長度，然而如果是聚集表時，則鍵列可能增加900多字節(jié)，

如果索引中每行都存儲這900多字節(jié)的書簽值，勢必會導(dǎo)致索引級數(shù)的增加，從而增加IO讀取次數(shù)而嚴(yán)重影響查詢效率。總結(jié)

索引包含列的優(yōu)勢：1、索引包含列可以減少書簽查找，提高查找效率；2、索引包含列的包含列不會增加非葉級索引寬度，意味著不會因為非葉級寬度而增加頁數(shù)。3、索引包含列不受900字節(jié)、最多16個鍵列以及無法在大類型數(shù)據(jù)列如（varchar(max)）上創(chuàng)建鍵列的限制。

當(dāng)然，使用索引包含列也會增加索引葉級的寬度，可能會導(dǎo)致更多頁存儲甚至索引級數(shù)增加，這就要看你如果適當(dāng)處理了。

上一篇：SQL Server 排名函數(shù)

下一篇：對SQL Server索引包含列（Include）的一點認(rèn)識