对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);