SQL SERVER大话存储结构（4）_复合索引与包含索引

link

索引这块从存储结构来分，有2大类，聚集索引和非聚集索引，而非聚集索引在堆表或者在聚集索引表都会对其 键值有所影响，这块可以详细查看本系列第二篇文章：SQL SERVER大话存储结构_(2)_非聚集索引如何查找到行记录。



    非聚集索引内又分为多类：单列索引、复合索引、包含索引、过滤索引等。之前文章有具体分析过非聚集索引的存储情况，但是没有对复合索引及包含索引做过多说明，本文来讲讲这两个索引。

   







    如果转载，请注明博文来源： www.cnblogs.com/xinysu/   ，版权归 博客园 苏家小萝卜 所有。望各位支持！



    本系列上一篇博文链接：SQL SERVER大话存储结构（3）_数据行的行结构








1 语法及说明





--复合索引
CREATE INDEX IndexName ON tbname(columna,columnb [,columnc...] )
--包含索引
CREATE INDEX IndexName ON tbname(columna [,columnb,columnc...] ) INCLUDE (column1 [,column2,column3...])

     复合索引，顾名思义，及多个列组成的索引，列的顺序非常重要，关系到查询性能，这点后面会说明。

     包含索引，建索引SQL 中含有 include 字段，索引键值用于WHERE条件过滤，INCLUDE字段用于 SELECT 展示，这点后面也会说明。

     无论是符合索引还是包含索引，都有索引键值长度不能超过900字节的限制，但是要注意一点，包含索引的include字段是不包括在里边的。
2 索引页存储情况


    从索引页的存储情况来分析，分析过程中，重点在查看复合索引跟包含包含索引在 子节点及叶子结点的键值情况。
2.1 创建测试表格


    创建表格 tbindex，建立两个测试索引，同时造数据。




1 CREATE TABLE tbindex(
2 id int identity(1,1) not null primary key ,
3 name varchar(50) not null,
4 type varchar(10) not null,
5 numbers int not null
6 )
7 GO
8  
9 CREATE INDEX ix_number_name ON tbindex(numbers,name)
10 GO
11 CREATE INDEX ix_name ON tbindex(numbers) INCLUDE (name)
12 GO
13  
14 DECLARE @ID INT
15 SET @ID=1
16 WHILE @ID<=5
17 BEGIN
18      INSERT INTO tbindex(name,type,numbers)
19      SELECT
20            name,
21          type,
22          object_id+@id
23      FROM sys.objects
24  
25         SET @ID=@ID+1
26 END
2.2 分析索引行



--查看该表格索引的id情况
SELECT * FROM sys.indexes WHERE object_id=object_id('tbindex')
--PK__tbindex__3213E83F89582AC3    1
--ix_number_name    2
--ix_number    3
DBCC traceon(3604)
DBCC ind('dbpage','tbindex',-1)
DBCC PAGE('dbpage',1,395,3)
DBCC PAGE('dbpage',1,396,3)
DBCC PAGE('dbpage',1,397,3)
DBCC PAGE('dbpage',1,398,3)

    分析查看，得知：

• 复合索引 IX_number_name的索引节点为pageid=395，再挑选一个叶子结点来分析 pageid=396；
  
• 包含索引 IX_number 的索引节点为 pageid=397，再挑选一个叶子节点来分析 pageid=398。
  

--复合索引，395为索引页节点，396为索引页叶子节点

DBCC PAGE('dbpage',1,395,3)

DBCC PAGE('dbpage',1,396,3)



--包含索引，397为索引页节点，398为索引页叶子节点

DBCC PAGE('dbpage',1,397,3)

DBCC PAGE('dbpage',1,398,3)





从这里可以看出，复合索引跟包含索引的 所有索引列都会存储在索引叶子节点跟子节点，但是包含索引 的INCLUDE列，不在索引页的子节点存储，仅存储在 索引页的叶子节点上。

    从这里不难理解，为什么之前说 include列用于 select 列，而不用于 where 列过滤。因为非聚集索引当索引页面有多层的时候，是先查询 索引的子节点，再查询索引的叶子节点，而包含索引的INCLUDE列不在叶子节点中存储，无法根据其来进行过滤。
3  对查询的影响

3.1 复合索引查询注意事项


     由于需要数据量作为实验支持，所以不用之前分析索引行结构的表格tbindex，换个高大上 tb_composite 如下。






1 create table tb_composite(
2 id int identity(1,1) not null primary key,
3 name varchar(50) not null,
4 userid int not null,
5 timepoint datetime not null
6 )
7 GO
8  
9 create index ix_userid_name on tb_composite(userid,name)
10 GO
11  
12 create index ix_userid on tb_composite(userid)
13 GO
14  
15 INSERT INTO tb_composite(name,userid,timepoint)
16 SELECT
17       newid(),orderid%10000 ,CreatedDate
18 FROM ORDERS
大数据表格

    至此，测试表格建立完成，开始分析索引页面信息，统计表格tb_composite信息如下：




1 --查看表格的数据大小跟非聚集索引大小
2 WITH DATA AS (
3 SELECT
4  
5       O.name tb_name,
6       reservedpages = SUM (reserved_page_count),
7       usedpages = SUM (used_page_count),
8       pages = SUM (CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE 0 END ),
9       rowCounts = SUM (CASE WHEN (index_id < 2) THEN row_count ELSE 0 END )
10 FROM sys.dm_db_partition_stats S
11 JOIN sys.objects o on s.object_id=o.object_id
12 WHERE O.type='U'
13 GROUP BY O.name
14 )
15 SELECT
16  
17          tb_name,
18          rowCounts,
19          reservedpages*8/1024 reserved_Mb,
20          pages*8/1024 data_Mb,
21          index_Mb=(usedpages-pages)*8/1024,
22          unused_Mb=case when usedpages>reservedpages then 0 else (reservedpages-usedpages)*8/1024 end
23 FROM DATA
24 WHERE tb_name = 'tb_composite'
25 ORDER BY reserved_Mb DESC
26 Go

--详细分析每一个索引的索引页面数量

create table tbind(PageFID int,   PagePID int,IAMFID int,IAMPID int,ObjectID int,IndexID int,PartitionNumber int,PartitionID varchar(50),iam_chain_type varchar(50) ,PageType int,IndexLevel int,NextPageFID int,NextPagePID int,PrevPageFID int,PrevPagePID int )



INSERT INTO TBIND EXEC ('DBCC IND(''yaochufa'',''tb_composite'',-1) ')



SELECT



      i.name,i.index_id,p.page_nums

FROM sys.indexes i join (SELECT IndexID,count(*) page_nums FROM tbind group by IndexID ) p on i.index_id=p.IndexID

WHERE object_id=object_id('tb_composite')

ORDER BY index_id




    可以看到这个表格的非聚集索引总大小 &asymp;  598Mb &asymp;  （43022+33279）*8k/1024 &asymp;  596Mb 。

    ix_userid_name 明显要比 ix_userid 存储的页面多，这是因为 ix_userid_name 比 ix_userid 多存储了 name 这个索引键值，索引页的增加，意味着使用这个索引就会相应增加 IO 。

    比如一下两个SQL：



SET STATISTICS IO ON

--执行前，按下快捷键：Ctrl+M, 执行SQL后会显示实际执行的执行计划 （注意，Ctrl+L，则为 预估的执行计划）



SELECT * FROM tb_composite WITH(INDEX=ix_userid_name) WHERE userid =6500

SELECT * FROM tb_composite WITH(INDEX=ix_userid) WHERE userid =6500



    查看其IO情况：

   



    走复合索引会比单列索引要多出3个IO，userid 条件的扩大这个IO差别也会逐步加大。

   

    查看执行计划如下：

   

    可以看出，两者都是先根据索引 进行 index seek 查找到相应的索引行，再根据索引行上的 主键，去聚集索引中进行 key lookup查找行记录。两者的执行计划是一模一样的。这里加多一个SQL查询。





SELECT * FROM tb_composite WHERE name='6CDC4A13-36FF-4FA2-94D0-F1CBEA40852C'

   

    name这一列，不存在单列索引，存在于复合索引 ix_userid_name(userid,timepoint,name) 中，那么 这个查询能否根据 这个索引进行查找呢？

    答案是：NO NO NO ，数据库会根据其IO情况来做选择，有两种可能，一种是根据主键做全表scan，另外一种是 对 复合索引 进行 index scan 全扫描，然后再根据键值去 聚集索引上查找相应的 行记录。

    且看执行计划跟IO如下，可以看出，逻辑读基本上把所有数据页（聚集索引叶子节点）都扫描出来，一次IO是一个8kb的data page。





    来吧，总结一下：

• 最左匹配原则：复合索引 键值列假设为（a, b, c, d, e），则等同于索引这几个索引：（a）、（a, b）、（a, b, c）、（a, b, c, d）、（a, b, c, d, e）
  
  
  • 当where条件 符合 最左匹配原则，那么，执行计划则是 INDEX SEEK ，走索引查找；
    
  • 当where条件 不符合 最左匹配原则，则根据性能评估，走primary index scan 或者 非聚集索引扫描再根据键值去 primary key lookup ；
    
  
  
• 根据最左匹配原则，可以在日常管理中，避免添加一些冗余冗余索引
  
• 但是也有一个注意事项：随着复合索引的列增加，索引页也会增加，使用其索引会增加一定量的IO，所以，再判断冗余索引的时候，需要考虑下这种情况，通常很少碰到这种情形。
  

3.2 复合索引与包含索引的查询区别


前面测试已经了解 复合索引 跟 包含索引 的 存储结构，这里进行查询测试。这里注意 索引页数量 = 索引节点页+索引叶子节点页。

    先创建 包含索引表格，造数据。



CREATE TABLE tb_include(

id int identity(1,1) not null primary key,

name varchar(50) not null,

userid int not null,

timepoint datetime not null

)

GO



CREATE INDEX ix_userid on tb_include(userid) INCLUDE (timepoint,name)

GO



INSERT INTO tb_include( name , userid , timepoint ) SELECT name,userid,timepoint FROM tb_composite

GO



    做两个查询如下：



SELECT USERID,name FROM tb_composite  where USERID=71

SELECT USERID,name FROM tb_include  where USERID=71



SELECT USERID,name FROM tb_composite  where USERID=71 AND NAME='010CC1BD-1736-46A8-9497-7F4DBFD082B2'

SELECT USERID,name FROM tb_include  where USERID=71 AND NAME='010CC1BD-1736-46A8-9497-7F4DBFD082B2'

总结：

• 如果where 条件包含include列
  
  
  • include列无法参与 index seek，因为其索引子节点不存在，只存在于索引叶子节点，所以include列一般都是 展示列；
    
  • include列由于无法做 where 过滤的 index seed，同比 复合索引，IO相对会较大
    
  
  
• 如果展示列仅限于索引键值及include列
  
  
  • 包含索引中，根据索引键值找到 索引叶子节点后，无须根据主键值或者RID值 回表 去查询行记录，而是直接把 索引叶子节点的 include 列的内容展示即可，减少 回表 的IO；
    
  
  
• 如果where条件仅含键值列，select 展示列仅含 键值列级include列
  
  
  • 两者性能基本一致，包含索引相对少IO，但是区别不大。
    
  
  
• 所有非聚集索引的限制长度是900个字节，但是 包含索引中的 include列是不计算在索引长度中的，所以如果要是遇到这种索引超过 900 bytes的特殊情况，可以考虑把相关字段放到include中来处理。