(3.7)sql server存储引擎--索引的结构与分类
1 行记录如何存储
这里引入两个概念:堆跟聚集索引表。本部分参考MSDN。
1.1 堆表
堆表,没有聚集索引的表格,可以创建一个或者多个非聚集索引。没有按照某个规则进行存储,一般来说,按照行记录入表的顺序,但是由于性能要求,可能会在不同区域移动入库数据。像一堆沙子一样,没有明确的组织顺序。
堆的 sys.partitions 中具有一行,对于堆使用的每个分区,都有 index_id = 0。默认情况下,一个堆有一个分区。 当堆有多个分区时,每个分区有一个堆结构,其中包含该特定分区的数据。例如,如果一个堆有四个分区,则有四个堆结构,每个分区有一个堆结构。
根据堆中的数据类型,每个堆结构将有一个或多个分配单元来存储和管理特定分区的数据。每个堆中每个分区至少有一个 IN_ROW_DATA 分配单元。
如果堆包含大型对象 (LOB) 列,则该堆的每个分区还将有一个 LOB_DATA 分配单元。如果堆包含超过 8,060 字节的行大小限制的变量长度列,则它的每个分区中还会有一个 ROW_OVERFLOW_DATA 分配单元。
sys.system_internals_allocation_units系统视图中的列 first_iam_page 指向 IAM 页链中的第一个 IAM 页,该 IAM 页链可管理分配给特定分区中的堆的空间。
SQL Server 使用 IAM 页在堆之间移动。堆内的数据页和行没有任何特定的顺序,也不链接在一起。数据页之间唯一的逻辑连接是记录在 IAM 页内的信息。
拥有聚集索引的表格,称为聚集索引表,每个表格按照其聚集索引的排序规则进行存储,但是这里注意一点,在一个页面中,并非 行记录 按照 其聚集索引排序规则,而是 行偏移量 按照其排序规则存储。
1.2 聚集索引表格
在 SQL Server 中,索引是按 B 树结构进行组织的。 索引 B 树中的每一页称为一个索引节点。
B 树的顶端节点称为根节点。 索引中的底层节点称为叶节点。 根节点与叶节点之间的任何索引级别统称为中间级。
在聚集索引中,叶节点包含基础表的数据页。 根节点和中间级节点包含存有索引行的索引页。
每个索引行包含一个键值和一个指针,该指针指向 B 树上的某一中间级页或叶级索引中的某个数据行。 每级索引中的页均被链接在双向链接列表中。
聚集索引在 sys.partitions 中有一行,其中,索引使用的每个分区的 index_id = 1。
默认情况下,聚集索引有单个分区。 当聚集索引有多个分区时,每个分区都有一个包含该特定分区相关数据的 B 树结构。 例如,如果聚集索引有四个分区,就有四个 B 树结构,每个分区中有一个 B 树结构。
根据聚集索引中的数据类型,每个聚集索引结构将有一个或多个分配单元,将在这些单元中存储和管理特定分区的相关数据。 每个聚集索引的每个分区中至少有一个 IN_ROW_DATA 分配单元。
如果聚集索引包含大型对象 (LOB) 列,则它的每个分区中还会有一个 LOB_DATA 分配单元。 如果聚集索引包含的变量长度列超过 8,060 字节的行大小限制,则它的每个分区中还会有一个 ROW_OVERFLOW_DATA 分配单元。
数据链内的页和行将按聚集索引键值进行排序。 所有插入操作都在所插入行中的键值与现有行中的排序顺序相匹配时执行。
下图显式了聚集索引单个分区中的结构。
由此,可以看出,堆表不存在特定的存储顺序,一般按照INSERT的顺序存储,但是有时因为性能需求,也会四处存放数据;而聚集索引表的数据行按照聚集键的排序情况存储,叶子节点即为行记录。
2 非聚集索引结构
无论是堆表还是聚集索引表格,都可以创建非聚集索引。非聚集索引页也是B-TREE结构,但是,有几点不同:非聚集索引不影响基础表的存储顺序,其叶子节点是有索引页组成而非数据页组成。
当需要通过非聚集索引寻找行记录时,先是在非聚集索引所在的B-TREE树查找,找到相应的叶子节点后,在根据该键值上的相应 行定位器 去查找其所指向的 行记录位置。
那么,行定位器是怎么样的呢?
这个还需要去分析 非聚集索引的键值内容,才可以清晰了解,详见下文的分析案例。
3 非聚集索引键值内容
创建3个表格:堆表、聚集索引非唯一表及聚集索引唯一表,并且创建非聚集索引,同时INSERT 部分数据。
--创建堆表 create table tb_heap(id int ,name varchar(100),age int) --创建聚集索引(非唯一)表 create table tb_clu_no_unique(id int identity(1,1) ,name varchar(100),age int) create CLUSTERED index ix_clu_id on tb_clu_no_unique(id) --创建聚集索引且键值唯一表 create table tb_pk(id int primary key identity(1,1) ,name varchar(100),age int) --创建非聚集索引 create index ix_tb_pk_name on tb_pk(name) create index ix_tb_heap_name on tb_heap(name) create index ix_tb_clu_no_unique_name on tb_clu_no_unique(name) --造数据 insert into tb_pk(name,age) select name,cast(rand()*100 as int) from master.dbo.spt_values where name is not null insert into tb_clu_no_unique(name,age) select name,age from tb_pk insert into tb_heap(id,name,age) select id,name,age from tb_pk
3.1 堆表上的非聚集索引
#会话窗口查看ind,需要打开 3604跟踪
dbcc traceon(3604)
dbcc ind('dbpage','tb_heap',2)
可以得出这些结论:
- pageid=238是IAM页,判断依据是:IAMFID=NULL;
- tb_heap上的非聚集索引ix_tb_heap_name的B tree结构有2层,判断依据是:IndexLevel最大值为1;
- B-tree树中,根页为 pageid=239,叶子节点的最左节点叶是 235
依据IndexLevel、NextPagePid及PrevPagePid,可以画出 ix_tb_heap_name 的数据结构如下(画图工具崩了,用自带画图小工具话的,这图丑出天际):
选取pageid=235,来分析非聚集索引页上的结构。
dbcc traceon(3604)
dbcc page('dbpage',1,235,3)
查看 ` 消息` ,可以看到,这个是索引页,目前上面存储260行索引键值,该页空闲空间12个字节,空闲空间从第7660字节开始。
查看 `结果` ,如下:
可以看到在这个页面上,每一行的行记录情况,可以看到 非聚集索引的键值有2部分:name 跟 HEAD RID,name因为是非聚集索引的列,所以理应存储,RID是什么呢?
RID除了可以从dbcc page中查询,也可以通过伪列查询:%%physloc%%。
select *,%%physloc%% as RID from tb_heap
RID实际上是用来 唯一标识 堆表中的每一行数据,占8个字节,按以下格式标识行:{ file id }:{ page id }:{ slot id},文件号:数据页号:槽位,从存储的角度唯一表示了一行数据。
但是从dbcc的结果看,这是一个16进制的数值,该如何转化呢?
转换规则:分为8个字节->前4bytes为page id->中间2bytes为file id->最后2bytes为slot id->反序排列->取10进制
用中的RID来实验下如何反解析。
--1 分为8个字节
E9 00 00 00 01 00 95 00
--2 前4bytes为page id
E9 00 00 00
--3 中间2bytes为file id
01 00
--4 最后2bytes为slot id
95 00
--5 反序排列并取10进制
pageid,反序后为 00 00 00 E9,十进制为16*14+9=233
fileid,反序后为 00 01,十进制为 1
slotid,反序后为 00 95,十进制为 149
则可以推算出,name='backup device'中,有一行行数据存储在 第一个文件中的第233页面的149槽位
dbcc page('dbpage',1,233,3)
由此,可以推出:在堆表中,非聚集索引的键值包含两部分:索引列 以及 RID,RID用于查找索引键值对应的行记录。
3.2 聚集索引表(唯一)的非聚集索引
#会话窗口查看ind,需要打开 3604跟踪
dbcc traceon(3604)
dbcc ind('dbpage','tb_pk',2)
根据2.1的推论,一样可以得出这些结论:
- pageid=121是IAM页,判断依据是:IAMFID=NULL;
- tb_pk上的非聚集索引ix_tb_pk_name的B tree结构有2层,判断依据是:IndexLevel最大值为1;
- B-tree树中,根页为 pageid=126,叶子节点的最左节点叶是 120。
依据IndexLevel、NextPagePid及PrevPagePid,可以画出 ix_tb_pk_name 的数据结构如下:
选取pageid=120,来分析非聚集索引页上的结构。
dbcc traceon(3604)
dbcc page('dbpage',1,120,3)
查看 ` 消息` ,可以看到,这个是索引页,目前上面存储296行索引键值,该页空闲空间86个字节,空闲空间从第7514字节开始。
查看 ` 结果` ,可发现,在 聚集索引且唯一的表格里边,非聚集索引有2部分:键值列+主键列。
这个相对比较好理解,因为在建立了聚集唯一索引的表格里边,其聚集索引键值可以唯一标识每一行的行记录,所以,在非聚集索引上,只需要包含这两部分。
3.3 聚集索引表(非唯一)的非聚集索引
#会话窗口查看ind,需要打开 3604跟踪
dbcc traceon(3604)
dbcc ind('dbpage','tb_clu_no_unique',2)
根据2.1的推论,一样可以得出这些结论:
- pageid=172是IAM页,判断依据是:IAMFID=NULL;
- tb_pk上的非聚集索引tb_clu_no_unique的B tree结构有2层,判断依据是:IndexLevel最大值为1;
- B-tree树中,根页为 pageid=174,叶子节点的最左节点叶是 171
选取pageid=171,来分析非聚集索引页上的结构。
dbcc traceon(3604)
dbcc page('dbpage',1,171,3)
查看 ` 消息` ,可以看到,这个是索引页,目前上面存储298行索引键值,该页空闲空间4个字节,空闲空间从第7592字节开始。
查看 ` 结果` ,注意列后面括号'(key)',这个表明为键值对组成部分,这里,发现有之前没有看到的键值列 UNIQUIFIER列。
那么,UNIQUIFIER列,这一列是用来做什么的呢?
这里,为了更好的理解UNIQUIFIER列,需要新建一个新表,INSERT少量重复聚集索引键值的行记录。
create table tb_clu_no_unique_2(id int ,name varchar(100),age int)
create CLUSTERED index ix_clu_i_2 on tb_clu_no_unique_2(id)
CREATE INDEX IX_tb_clu_no_unique_2_NAME ON tb_clu_no_unique_2(NAME)
INSERT INTO tb_clu_no_unique_2(ID,NAME,AGE) SELECT 1,'A',3;
INSERT INTO tb_clu_no_unique_2(ID,NAME,AGE) SELECT 1,'B',3;
INSERT INTO tb_clu_no_unique_2(ID,NAME,AGE) SELECT 2,'C',3;
INSERT INTO tb_clu_no_unique_2(ID,NAME,AGE) SELECT 2,'D',3;
INSERT INTO tb_clu_no_unique_2(ID,NAME,AGE) SELECT 2,'E',3;
DBCC TRACEON(3604)
DBCC IND('dbpage','tb_clu_no_unique_2',2)
DBCC PAGE('dbpage',1,306,3)
可发现,在 聚集索引且非唯一的表格里边,非聚集索引有3部分:键值列+主键列+UNIQUIFIER列。建立了聚集非唯一索引,表的存储顺序按照聚集索引顺序,但是仅靠聚集索引无法唯一标识每一行的行记录,所以,需要添加 UNIQUIFIER列来唯一标识。
总结:
- 堆表 的 非聚集索引 键值内容:索引列+RID
- 聚集且唯一索引表 的非聚集索引 键值内容:索引列+主键列
- 聚集且非唯一索引表 的非聚集索引 键值内容:索引列+主键列+UNIQUIFIER列
4 非聚集索引如何查找页
根据第二部分,可以很清楚每类型的非聚集索引的组成部分。
在堆表中,非聚集索引根据其键值内的RID列,直接进行物理查找,从fileid找到pageid,在找到slotid来定位到行记录,这个也就是所谓的书签查找,根据RID查找。
在聚集且唯一的索引表中,非聚集索引根据其键值内部的 聚集索引列,找到聚集索引的B-TREE,根据 B-TREE 树找到聚集索引的键值,键值下的叶子节点则为行记录。
在聚集其非唯一索引表中,非聚集索引根据其键值内部的 聚集索引列,找到聚集索引的B-TREE,根据 B-TREE 树找到聚集索引的键值,这里会有些不一样了,根据找到的键值,键值下的叶子节点可能会有多行记录,这个时候,就需要uniquifier来识别行记录。
参考文档
《SQL Server性能调优实战》
5.复合与包含索引,索引页存储情况
5.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字段是不包括在里边的。
从索引页的存储情况来分析,分析过程中,重点在查看复合索引跟包含包含索引在 子节点及叶子结点的键值情况。
5.2 创建测试表格
创建表格 tbindex,建立两个测试索引,同时造数据。
CREATE TABLE tbindex( id int identity(1,1) not null primary key , name varchar(50) not null, type varchar(10) not null, numbers int not null ) GO CREATE INDEX ix_number_name ON tbindex(numbers,name) GO CREATE INDEX ix_name ON tbindex(numbers) INCLUDE (name) GO DECLARE @ID INT SET @ID=1 WHILE @ID<=5 BEGIN INSERT INTO tbindex(name,type,numbers) SELECT name, type, object_id+@id FROM sys.objects SET @ID=@ID+1 END
5.3 分析索引行
--查看该表格索引的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列不在叶子节点中存储,无法根据其来进行过滤。
6 复合索引对查询的影响
6.1 复合索引查询注意事项
由于需要数据量作为实验支持,所以不用之前分析索引行结构的表格tbindex,换个高大上 tb_composite 如下。
大数据表格
至此,测试表格建立完成,开始分析索引页面信息,统计表格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
可以看到这个表格的非聚集索引总大小 ≈ 598Mb ≈ (43022+33279)*8k/1024 ≈ 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,所以,再判断冗余索引的时候,需要考虑下这种情况,通常很少碰到这种情形。
6.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'
6.3总结
- 如果where 条件包含include列
- include列无法参与 index seek,因为其索引子节点不存在,只存在于索引叶子节点,所以include列一般都是 展示列;
- include列由于无法做 where 过滤的 index seed,同比 复合索引,IO相对会较大
- 如果展示列仅限于索引键值及include列
- 包含索引中,根据索引键值找到 索引叶子节点后,无须根据主键值或者RID值 回表 去查询行记录,而是直接把 索引叶子节点的 include 列的内容展示即可,减少 回表 的IO;
- 如果where条件仅含键值列,select 展示列仅含 键值列级include列
- 两者性能基本一致,包含索引相对少IO,但是区别不大。
- 所有非聚集索引的限制长度是900个字节,但是 包含索引中的 include列是不计算在索引长度中的,所以如果要是遇到这种索引超过 900 bytes的特殊情况,可以考虑把相关字段放到include中来处理。