sql server 2008学习8 sql server存储和索引结构
sql server的存储机制
区段: 是用来为表和索引 分配空间的基本存储单元. 由 8个连续的页面构成,大小为64kb.
区段的注意事项:
- 一旦区段已满,那么下一记录 将要占据的空间不是记录的大小,而是整个区段的大小.
- 通过预先分配空间,sql server节省了为每个记录分配新空间的时间
页: 页是在到达实际数据行 之前所能达到的最后一个存储级别.尽管每个区段中的页数是固定的,但是每一页中的行数 不是固定的--这完全取决于行的大小,而行的大小 可以变化的. 可以把 页 看作是 表行和索引行的容器.通常不允许行跨页.就是行的大小 最大为8kb.
下面介绍两种不同的页类型:
- 数据页: 就是表中的实际数据
- 索引页:它们既包括 非聚集索引的非叶级页和页级页. 又包括聚集索引的非叶级页
行:
最大可达8kb,除了 8060字符的限制外,还有最大1024标准列(非稀疏列)的限制.
稀疏列:
使用稀疏列,可以讲单个表中允许的列的总数 提高到 30000.
在内部,标记为稀疏的列 的数据 嵌在单个列中--可以打破之前的1024列的限制,而不用做较大的体制结构的更改.
理解索引
索引就是能够快速访问数据的方法.
索引中的存储顺序取决于 为数据建立的排列规则信息.可以再数据库或者列级设置排列规则.
平衡树:
平衡树或者 B-Tree 仅是提供一种以一致且相对低成本的方式查找特定信息的方法.
页拆分简介:
每次遇到树中的分支时,因为每一边都有约有一半的数据,所以B-树 是平衡的.
通过将数据添加到树上,节点最终将变满,并且需要拆分,因为sql server中,一个节点相当于一个页--所以这杯称为页拆分.
也拆分做的操作:
- 创建新页
- 将行从现有的页移动到新页
- 将新行添加到其中一页上
- 在父节点中添加另一个记录项
但是系统开销不仅仅是这些.因为在进行树的排列,就可能导致级联操作.创建新页时(因为拆分的缘故),需要在父节点中
建立另一个记录项,在父节点中的这个记录项在该 级别也可能导致页拆分,而且整个过程会重新开始.
如果根节点拆分,那么实际最终会创建两个额外的页.由于只能有一个根节点,所以之前作为根节点的页被拆分成两个页,而且成为树的新
中间级别.然后创建全新的根节点.并且将有两个记录项.
显然,拆分页对系统性能产生非常负面的影响,其表现是 在服务器上 的处理会暂停几秒.
sql server 中 访问数据的方式:
广义上讲,sql server检索数据的方式 只有两种.
- 使用表扫描
- 使用索引扫描
使用表扫描:
表扫描是个很直观的过程.sql server 从表的物理起点 开始,浏览表中的每一行,当发现和查询条件匹配的行时,
就在结果集中包含它们.
使用索引:
在查询优化过程中,优化器查看所有可用的索引结构,并选择最好的一个(这主要基于在连接和where子句中指定的信息,以及sql server
在索引结构中保存的统计信息). 一旦选择的索引,sql server 将在树结构中导航至与条件匹配的数据位置,并且只提取它所需要的记录.
区别在于,因为数据时排序的,所以查询引擎知道 它何时到达正在查找的当前范围的下界,然后他可以结束查询,或者根据需要移至下一数据范围.
索引类型和索引导航
- 聚集索引
- 非聚集索引( 有包括两个: 堆上的非聚集索引, 聚集表上的非聚集索引)
物理数据的 存储方式 在 聚集索引和非聚集索引中时不同的. 而 sql server 遍历平衡树已到达末端数据的方式 在所有三种
索引类型中,也是不同的.
索引在 聚集表(如果表有聚集索引)或者堆(用于没有聚集索引的表)上创建.
聚集表: 是 在其上具有聚集索引的任意表.
它们对于表而言意味着 以 指定 物理顺序 存储数据.通过使用聚集键唯一的标示独立的行---聚集键 即 定义聚集索引的列.
堆: 在其上没有聚集索引的任意表. 在这种情况下,基于 行的区段,页,以及行偏移量的组合创建唯一的标示符,或者成为RID,
如果没有可用的聚集键,那么RID是唯一必要的内容.
聚集索引:
聚集索引对于 任意给定的表而言都是唯一的.
聚集索引的叶级 是 实际的数据.
数据重新排序,按照和索引排序条件声明的相同物理顺序存储.这意味着,一旦到达索引的叶级,就到达了 数据.
任何新纪录都根据其正确的物理顺序插入到群集索引中.创建新页的方式也随插入记录的位置而变化.
当发生页面拆分时,数据自动的四处移动一 保持平衡. 数据的前半部分保留在旧页上,而数据的剩余部分添加到新页.-这样就形成的了对半分,
使得树 保持平衡.
-----------------------------------
堆上的非聚集索引
这个索引 的页级不是数据，而是指向数据的一个指针。 该指针 以RID的形式出现，这种RID由 索引指向的特性行的区段，页以及
偏移量组成。
注意: 堆上的非聚集索引 和聚集索引一样,通常任何已通过读取一次的页 将 仍然 在内存中缓存,而且同样将非常快速的呗检索.
聚集表上的非聚集索引
这种索引和堆上的非聚集索引 一样,索引的非叶级节点的工作与使用 聚集索引时相比几乎一样,区别在于 叶级.
在聚集表上的非聚集索引 ,在 叶级 找到的是 聚集键,也就说,找到足够的信息 继续并利用聚集索引.
何时何地使用何种索引
索引,特别是 非聚集索引,主要在 索引中 有相当高级别的选择性的情况下是有益的.
所谓的选择性就是 列中唯一值的百分比. 唯一值百分比越高,选择性越高.从而索引的用处就越大.
索引的成本:
虽然索引能在查询时 提高效率,但是在修改数据时,实际上花费很高.每次对数据进行修改时,任何与数据有关的索引也将需要更新,
对于每个创建的索引,意味着 创建了 一组 必须更新的条目.
那么为什么是一组条目呢,因为 平衡树有多个级别,每次对叶级进行修改时,就可能产生页拆分,而且也必须修改一个或者多个 非叶级页.
维护索引
就索引维护而言,需要处理一下两个问题:
- 页拆分
- 碎片
这两个问题 都和 液密度 有关,虽然两者在表现形式上有本质区别,但是故障排除工具是一样的.
碎片:
当 数据库增长,页拆分,然后删除数据时,都会产生碎片.
虽然从增长的角度看,平衡树机制 在保持平衡方面做的不错,但是在删除数据时,将没有太多作用.最终可能出现这样一种情况:
其中在这一页上有一个记录,而在那一页有几个记录--意味着页没有被填满,
- 关于碎片 首先会想到的就是 ----浪费空间.前面 说过,sql server 每次分配一个区段的空间,如果 一个页 只有一条记录,仍然会分配一个区段.
- 散步在各处的记录会造成 数据检索时 的额外开销.
虽然如此,碎片也有好的一面,—OLTP系统就喜欢碎片,因为 也拆分.没有许多数据的页 在插入数据时,几乎不用担心也拆分.
所以:大量的碎片 意味着 较差的读取性能,但是也意味着 极好的插入性能.
确定碎片和也拆分的可能性:
sql server 提供了一个元数据 函数 sys.dm_db_index_physical_stats ,有助于确定数据库中的页和区段有多满.
那么次函数的参数如下:
参数说明：数据库id，对象id，索引类别id (1聚集索引 0堆 2 非聚集索引)
a表结果：
一个主键，自带一个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) 填充因子越大,空闲空间就越大.
下面提供两种 查看 表中索引的 函数:
-
select * from sys.dm_db_index_operational_stats(db_id('test'),object_id('a'),null,null)
-
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)
