sql server 2008学习8 sql server存储和索引结构

sql server的存储机制

区段: 是用来为表和索引 分配空间的基本存储单元. 由 8个连续的页面构成,大小为64kb.

区段的注意事项:

  • 一旦区段已满,那么下一记录 将要占据的空间不是记录的大小,而是整个区段的大小. 
  • 通过预先分配空间,sql server节省了为每个记录分配新空间的时间

 

页: 页是在到达实际数据行 之前所能达到的最后一个存储级别.尽管每个区段中的页数是固定的,但是每一页中的行数 不是固定的--这完全取决于行的大小,而行的大小 可以变化的. 可以把 页 看作是 表行和索引行的容器.通常不允许行跨页.就是行的大小 最大为8kb.

下面介绍两种不同的页类型:

  • 数据页: 就是表中的实际数据 
  • 索引页:它们既包括 非聚集索引的非叶级页和页级页. 又包括聚集索引的非叶级页

行:

最大可达8kb,除了 8060字符的限制外,还有最大1024标准列(非稀疏列)的限制.

稀疏列:

使用稀疏列,可以讲单个表中允许的列的总数 提高到 30000.

在内部,标记为稀疏的列 的数据 嵌在单个列中--可以打破之前的1024列的限制,而不用做较大的*结构的更改.

 

理解索引

索引就是能够快速访问数据的方法.

索引中的存储顺序取决于 为数据建立的排列规则信息.可以再数据库或者列级设置排列规则.

 

平衡树:

平衡树或者 B-Tree 仅是提供一种以一致且相对低成本的方式查找特定信息的方法.

sql server 2008学习8 sql server存储和索引结构

页拆分简介:

每次遇到树中的分支时,因为每一边都有约有一半的数据,所以B-树 是平衡的.

通过将数据添加到树上,节点最终将变满,并且需要拆分,因为sql  server中,一个节点相当于一个页--所以这杯称为页拆分.

也拆分做的操作:

  • 创建新页
  • 将行从现有的页移动到新页
  • 将新行添加到其中一页上
  • 在父节点中添加另一个记录项

但是系统开销不仅仅是这些.因为在进行树的排列,就可能导致级联操作.创建新页时(因为拆分的缘故),需要在父节点中

建立另一个记录项,在父节点中的这个记录项在该 级别也可能导致页拆分,而且整个过程会重新开始.

如果根节点拆分,那么实际最终会创建两个额外的页.由于只能有一个根节点,所以之前作为根节点的页被拆分成两个页,而且成为树的新

中间级别.然后创建全新的根节点.并且将有两个记录项.

显然,拆分页对系统性能产生非常负面的影响,其表现是 在服务器上 的处理会暂停几秒.

 

 

sql server 中 访问数据的方式:

广义上讲,sql server检索数据的方式 只有两种.

  • 使用表扫描
  • 使用索引扫描

使用表扫描:

表扫描是个很直观的过程.sql server 从表的物理起点 开始,浏览表中的每一行,当发现和查询条件匹配的行时,

就在结果集中包含它们.

使用索引:

在查询优化过程中,优化器查看所有可用的索引结构,并选择最好的一个(这主要基于在连接和where子句中指定的信息,以及sql server

在索引结构中保存的统计信息). 一旦选择的索引,sql server 将在树结构中导航至与条件匹配的数据位置,并且只提取它所需要的记录.

区别在于,因为数据时排序的,所以查询引擎知道 它何时到达正在查找的当前范围的下界,然后他可以结束查询,或者根据需要移至下一数据范围.

 

索引类型和索引导航

  • 聚集索引
  • 非聚集索引( 有包括两个: 堆上的非聚集索引,  聚集表上的非聚集索引)

 

物理数据的 存储方式 在 聚集索引和非聚集索引中时不同的. 而  sql server 遍历平衡树已到达末端数据的方式 在所有三种

索引类型中,也是不同的.

索引在 聚集表(如果表有聚集索引)或者堆(用于没有聚集索引的表)上创建.

 

聚集表: 是 在其上具有聚集索引的任意表.

它们对于表而言意味着 以 指定  物理顺序 存储数据.通过使用聚集键唯一的标示独立的行---聚集键 即 定义聚集索引的列.

: 在其上没有聚集索引的任意表. 在这种情况下,基于 行的区段,页,以及行偏移量的组合创建唯一的标示符,或者成为RID,

如果没有可用的聚集键,那么RID是唯一必要的内容.

 

 

聚集索引:

聚集索引对于 任意给定的表而言都是唯一的.

聚集索引的叶级 是 实际的数据.

数据重新排序,按照和索引排序条件声明的相同物理顺序存储.这意味着,一旦到达索引的叶级,就到达了 数据.

任何新纪录都根据其正确的物理顺序插入到群集索引中.创建新页的方式也随插入记录的位置而变化.

 

sql server 2008学习8 sql server存储和索引结构

当发生页面拆分时,数据自动的四处移动一 保持平衡. 数据的前半部分保留在旧页上,而数据的剩余部分添加到新页.-这样就形成的了对半分,

使得树 保持平衡.

-----------------------------------

 

sql server 2008学习8 sql server存储和索引结构

 

 

堆上的非聚集索引

这个索引 的页级不是数据,而是指向数据的一个指针。 该指针 以RID的形式出现,这种RID由 索引指向的特性行的区段,页以及

偏移量组成。

sql server 2008学习8 sql server存储和索引结构

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

注意: 堆上的非聚集索引 和聚集索引一样,通常任何已通过读取一次的页 将 仍然 在内存中缓存,而且同样将非常快速的呗检索.

 

聚集表上的非聚集索引

这种索引和堆上的非聚集索引 一样,索引的非叶级节点的工作与使用 聚集索引时相比几乎一样,区别在于 叶级.

在聚集表上的非聚集索引 ,在 叶级 找到的是 聚集键,也就说,找到足够的信息 继续并利用聚集索引.

 

何时何地使用何种索引

索引,特别是 非聚集索引,主要在 索引中 有相当高级别的选择性的情况下是有益的. 

所谓的选择性就是 列中唯一值的百分比.   唯一值百分比越高,选择性越高.从而索引的用处就越大.

索引的成本:

虽然索引能在查询时 提高效率,但是在修改数据时,实际上花费很高.每次对数据进行修改时,任何与数据有关的索引也将需要更新,

对于每个创建的索引,意味着 创建了 一组 必须更新的条目.

那么为什么是一组条目呢,因为 平衡树有多个级别,每次对叶级进行修改时,就可能产生页拆分,而且也必须修改一个或者多个 非叶级页.

 

维护索引

就索引维护而言,需要处理一下两个问题:

  • 页拆分
  • 碎片

这两个问题 都和  液密度  有关,虽然两者在表现形式上有本质区别,但是故障排除工具是一样的.

 

碎片:

当 数据库增长,页拆分,然后删除数据时,都会产生碎片.

虽然从增长的角度看,平衡树机制 在保持平衡方面做的不错,但是在删除数据时,将没有太多作用.最终可能出现这样一种情况:

其中在这一页上有一个记录,而在那一页有几个记录--意味着页没有被填满,

  1. 关于碎片 首先会想到的就是 ----浪费空间.前面 说过,sql server 每次分配一个区段的空间,如果 一个页 只有一条记录,仍然会分配一个区段.
  2. 散步在各处的记录会造成 数据检索时  的额外开销.

虽然如此,碎片也有好的一面,—OLTP系统就喜欢碎片,因为 也拆分.没有许多数据的页 在插入数据时,几乎不用担心也拆分.

所以:大量的碎片 意味着 较差的读取性能,但是也意味着  极好的插入性能.

 

确定碎片和也拆分的可能性:

sql server 提供了一个元数据 函数  sys.dm_db_index_physical_stats ,有助于确定数据库中的页和区段有多满.

那么次函数的参数如下:

sql server 2008学习8 sql server存储和索引结构

参数说明:数据库id,对象id,索引类别id (1聚集索引  0堆  2 非聚集索引)

a表结果:sql server 2008学习8 sql server存储和索引结构一个主键,自带一个clustered index

下面看sql :

declare @db_id smallint
declare @ob_id int
set @db_id=DB_ID('test')
set @ob_id=object_id('a')
select * from sys.dm_db_index_physical_stats(@db_id,@ob_id,null,null,null)

下面看一下 返回的部分数据:

 
表中明确给出, 索引类型, 平均碎片百分比, 和 索引深度.
 
下面取消 a表的id主键,再次查看:结果如下:
 

注意 索引类型:  由 clustered index 变成了 heap  ,说明了 没有聚集索引的表 称为 堆表.

 

查看到相关信息后,那么就像去改变索引,

使用 fillfactor 填充因子 来改变页面的密度:

alter index PK_a(键名) on a(表名) rebuild with (fillfactor = 90) 填充因子越大,空闲空间就越大.

 

下面提供两种 查看 表中索引的  函数:

  1. select * from sys.dm_db_index_operational_stats(db_id('test'),object_id('a'),null,null)
  2. declare @db_id smallint
    declare @ob_id int
    set @db_id=DB_ID('test')
    set @ob_id=object_id('a')
    select * from sys.dm_db_index_physical_stats(@db_id,@ob_id,null,null,null)
本文转自左正博客园博客,原文链接:http://www.cnblogs.com/soundcode/archive/2012/09/11/2679998.html,如需转载请自行联系原作者
上一篇:消息与.NET Remoting分布式处理架构


下一篇:.Net Core----关于MVC中TempData持久化问题