菜单

4166am金沙下载SERVER大话存储结构,索引阐述系列四

2019年11月9日 - 数据网络
4166am金沙下载SERVER大话存储结构,索引阐述系列四

一.概述  

  说到B-tree组织,就是指索引,它可以提供了对数据的快速访问。索引使数据以一种特定的方式组织起来,使查询操作具有最佳性能。当数据表量变得越来越大,索引就变得十分明显,可以利用索引查找快速满足条件的数据行。某些情况还可以利用索引帮助对数据进行排序,组合,分组,筛选。

   一个B-tree,根是唯一的遍历的起点。中间页
层次数是根据表的行数以及索引行的大小而变化。索引中的底层节点称为叶节点。叶节点它容纳了一行或多行具有指定键值的记录,对于聚集或非聚集,叶节点都是按照键值的顺序组成,对于复合索引就是若干键值的组合。

  1.聚集索引

  在聚集索引的叶节点里不仅包含了索引键,还包含了数据页。也就是说数据本身也是聚集索引的一部分。聚集索引基于键值联系使表中的数据有序。决定哪个键值作为聚集键是重要因素,当遍历到叶级别时,可以获取数据本身,而不是简单地得到一个指向数据的指针(非聚集索引数据未覆盖)。聚集索引在 sys.partitions区中有一行,其中,索引使用每个分区的 index_id =
1
,默认情况下,聚集索引是单个分区。如果聚集索引有四个分区,就有四个 B-tree 结构,每个分区中有一个
B-tree结构,关于分区在sql server
分区(上)中有讲到。由于数据页链只能按一种方式排序,因此表只有一个聚集索引,一般情况查询优化器非常倾向于采用聚集索引,因为可以直接在叶级别找到数据。 
查询优化器也只需要在某一段范围的数据页,进行扫描。聚集索引结构按物理顺序存储不是磁盘上的顺序,聚集索引的排序顺序仅是表数据链在逻辑上有序的。

4166am金沙下载 1

  2.非聚集索引

  非聚集索引与聚集索引有一个相似的 B
-tree索引结构。不同的是,非聚集索引不影响数据行的顺序。什么意思呢,就是说非聚集索引,叶级别不包含全部的数据,只包含了键值以及,在每个叶节点中的索引行包含了一个书签(bookmark),书签在聚集索引里就是相应的数据行的聚集索引键,在堆里就是行标识符RID,该书签告诉sql
server可以在哪里找到与索引键相应的数据行。
理解了非聚集索引叶节点不包含全部数据时,就知道非聚集索引的存在并不影响数据分页的组织,因此每张表上最多249个非聚集索引。
非聚集索引在 sys.partitions 区中有一行, 非聚集索引标识 index_id
>1
。默认情况下,一个非聚集索引一个分区。

4166am金沙下载 2

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
分配单元。

 

    数据链内的页和行将按聚集索引键值进行排序。
所有插入操作都在所插入行中的键值与现有行中的排序顺序相匹配时执行。

 

    下图显式了聚集索引单个分区中的结构。

    4166am金沙下载 3

   
 由此,可以看出,堆表不存在特定的存储顺序,一般按照INSERT的顺序存储,但是有时因为性能需求,也会四处存放数据;而聚集索引表的数据行按照聚集键的排序情况存储,叶子节点即为行记录。

三. B-tree组织存储空间的影响

  我们知道了对于聚集索引,它的叶子层就是数据本身,但当一个表有多个非聚集索引时,就需要对数据库存储空间加倍来支持这些索引的存储,所以从占用存储空间来说,在建非聚集索引时需要好好规划。下面是来自生产环境的一个表,有聚集索引和四个非聚集索引,来看看索引存储空间
在index_id=1的聚集索引中占用的空间total_pages是1448806页,也就是表的数据本身。
而非聚集索引占用空间total_pages是2180034页,
非聚集索引占用空间比表数据本身大了1.5倍。

  4166am金沙下载 4

1 行记录如何存储

    这里引入两个概念:堆跟聚集索引表。本部分参考MSDN。

二. 缺少索引与索引查找的区别

   在简单介绍了索引原理后,我们来直观感觉下索引在查询时的重要性。下面演示一个product表,表中的数据有12236142条,如果用户根据表中的型号(model)来搜索。下面来看看缺少索引(没有使用到索引),以及索引查找(就是应用到了索引功能)。二者的区别

  2.1 缺少索引的演示

--查询型号model 值STI5203 在全表中有三条
SELECT Model FROM dbo.Product WHERE Model='STI5203'

图下告诉我们缺少索引,如果加了索引将提高性能99.94%, 该查询扫描计数5 (扫描了5个区),逻辑读取次数为69951次(一次一页),耗时954毫秒。
执行计划告诉我们是索引扫描也叫缺少索引,索引名是ixUpByMemberID,注意索引扫描不是索引查找,索引扫描是说把索引组织上的页全部扫描了一遍。

4166am金沙下载 5

4166am金沙下载 6

  再通过下图我们清楚知道,ixUpByMemberID有5个区。5个区加起来的data_pages总页数是69730。上图逻辑读取是69951。相当于把索引中的页全部扫描了一遍。也可说是把12236142条数据全扫描了一次。

4166am金沙下载 7

  在锁的介绍中我们知道,锁越多,发生阻塞和死锁的几率就越大。
  通过下图,对于page资源来说,就有IS锁(意向共享锁)上1000个。IS锁与X排它锁又不兼容,此时多用户在修改,删除表中数据时,将会发生阻塞或死锁的影响。

4166am金沙下载 8

4166am金沙下载 9

4166am金沙下载 10

    总结:如果在生产环境,面对大数据表,条件查询很频繁,又缺失索引,系统整体性能将会被拖垮。

   2.2 查询索引查找的演示

  用户根据model查询,缺少了一个索引,在给model建立索引后,再来看

--查询型号model 值STI5203 在全表中有三条
SELECT Model FROM dbo.Product WHERE Model='STI5203'

  下图的执行计划告诉我们是索引查找,也就是索引使用上了,该索引名叫ix_mdoel.
扫描计数1 个区,逻辑读取次数为4次,耗时0毫秒.

4166am金沙下载 11

4166am金沙下载 12

  再来看下索引查找的锁状态,下图告诉我们,只有锁往了一个page资源。

4166am金沙下载 13

  总结:在大表上,合理使用了索引查找后,不但查询响应时间变快了,而且没有了大量的锁,相应的在其它page页上的修改,删除应不会受到影响。


 

 

3.2 聚集索引表(唯一)的非聚集索引

#会话窗口查看ind,需要打开 3604跟踪

dbcc traceon(3604)

dbcc ind(‘dbpage’,’tb_pk’,2)

 

4166am金沙下载 14.png)

4166am金沙下载 15

    根据2.1的推论,一样可以得出这些结论:

 4166am金沙下载 16

 

    依据IndexLevel、NextPagePid及PrevPagePid,可以画出 ix_tb_pk_name
的数据结构如下:

     4166am金沙下载 17.png)

选取pageid=120,来分析非聚集索引页上的结构。

 

dbcc traceon(3604)

dbcc page(‘dbpage’,1,120,3)

 

    查看 ` 消息` 
,可以看到,这个是索引页,目前上面存储296行索引键值,该页空闲空间86个字节,空闲空间从第7514字节开始。

 4166am金沙下载 18

    

    查看 ` 结果`  ,可发现,
聚集索引且唯一的表格里边,非聚集索引有2部分:键值列+主键列
。这个相对比较好理解,因为在建立了聚集唯一索引的表格里边,其聚集索引键值可以唯一标识每一行的行记录,所以,在非聚集索引上,只需要包含这两部分。

 4166am金沙下载 19

2 非聚集索引结构

   
无论是堆表还是聚集索引表格,都可以创建非聚集索引。非聚集索引页也是B-TREE结构,但是,有几点不同:非聚集索引不影响基础表的存储顺序,其叶子节点是有索引页组成而非数据页组成。

    4166am金沙下载 20

   
 当需要通过非聚集索引寻找行记录时,先是在非聚集索引所在的B-TREE树查找,找到相应的叶子节点后,在根据该键值上的相应
行定位器 去查找其所指向的 行记录位置。

 

      那么,行定位器是怎么样的呢?

      这个还需要去分析
非聚集索引的键值内容,才可以清晰了解,详见下文的分析案例。


3.3 聚集索引表(非唯一)的非聚集索引

#会话窗口查看ind,需要打开 3604跟踪

dbcc traceon(3604)

dbcc ind(‘dbpage’,’tb_clu_no_unique’,2)

4166am金沙下载 21.png)

 4166am金沙下载 22

  根据2.1的推论,一样可以得出这些结论:

 4166am金沙下载 24

选取pageid=171,来分析非聚集索引页上的结构。

 

dbcc traceon(3604)

dbcc page(‘dbpage’,1,171,3)

 

    查看 ` 消息` 
,可以看到,这个是索引页,目前上面存储298行索引键值,该页空闲空间4个字节,空闲空间从第7592字节开始。

 
  4166am金沙下载 25.png)4166am金沙下载 26

 

    查看 ` 结果` 
,注意列后面括号’(key)’,这个表明为键值对组成部分,这里,发现有之前没有看到的键值列
UNIQUIFIER列。

     4166am金沙下载 27  
4166am金沙下载 28.png)

 

   那么,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)

4166am金沙下载 29.png)

 4166am金沙下载 30

    可发现,
聚集索引且非唯一的表格里边,非聚集索引有3部分:键值列+主键列+UNIQUIFIER列
。建立了聚集非唯一索引,表的存储顺序按照聚集索引顺序,但是仅靠聚集索引无法唯一标识每一行的行记录,所以,需要添加
UNIQUIFIER列来唯一标识。

 

    总结:


相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图