SQL Server 索引之书签查找

索引的简介：

索引分为聚集索引和非聚集索引，数据库中的索引类似于一本书的目录，在一本书中通过目录可以快速找到你想要的信息，而不需要读完全书。

索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间。

但是索引对于提高查询性能也不是万能的，也不是建立越多的索引就越好。索引建少了，用 WHERE 子句找数据效率低，不利于查找数据。索引建多了，不利于新增、修改和删除等操作，因为做这些操作时，SQL SERVER 除了要更新数据表本身，还要连带立即更新所有的相关索引，而且过多的索引也会浪费硬盘空间。

索引的分类：

索引就类似于中文字典前面的目录，按照拼音或部首都可以很快的定位到所要查找的字。

唯一索引（UNIQUE）：每一行的索引值都是唯一的（创建了唯一约束，系统将自动创建唯一索引）

主键索引：当创建表时指定的主键列，会自动创建主键索引，并且拥有唯一的特性。

聚集索引（CLUSTERED）：聚集索引就相当于使用字典的拼音查找，因为聚集索引存储记录是物理上连续存在的，即拼音 a 过了后面肯定是 b 一样。

非聚集索引（NONCLUSTERED）：非聚集索引就相当于使用字典的部首查找，非聚集索引是逻辑上的连续，物理存储并不连续。

PS：聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。

什么情况下使用索引：

语法：

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name   
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] )   
    [ WITH <backward_compatible_index_option> [ ,...n ] ]  
    [ ON { filegroup_name | "default" } ]  
  
<object> ::=  
{  
    [ database_name. [ owner_name ] . | owner_name. ]   
    table_or_view_name  
}  
  
<backward_compatible_index_option> ::=  
{   
    PAD_INDEX  
  | FILLFACTOR = fillfactor  
  | SORT_IN_TEMPDB  
  | IGNORE_DUP_KEY  
  | STATISTICS_NORECOMPUTE   
  | DROP_EXISTING   
}

参数：

UNIQUE：为表或视图创建唯一索引。唯一索引不允许两行具有相同的索引键值。视图的聚集索引必须唯一。如果要建唯一索引的列有重复值，必须先删除重复值。

CLUSTERED：表示指定创建的索引为聚集索引。创建索引时，键值的逻辑顺序决定表中对应行的物理顺序。聚集索引的底层（或称叶级别）包含该表的实际数据行。

NONCLUSTERED：表示指定创建的索引为非聚集索引。创建一个指定表的逻辑排序的索引。对于非聚集索引，数据行的物理排序独立于索引排序。

index_name：表示指定所创建的索引的名称。

database_name：表示指定的数据库的名称。

owner_name：表示指定所有者。

table：表示指定创建索引的表的名称。

view：表示指定创建索引的视图的名称。

column：索引所基于的一列或多列。指定两个或多个列名，可为指定列的组合值创建组合索引。

[ ASC | DESC]：表示指定特定索引列的升序或降序排序方向。默认值为 ASC。

on filegroup_name：为指定文件组创建指定索引。如果未指定位置且表或视图尚未分区，则索引将与基础表或视图使用相同的文件组。该文件组必须已存在。

on default：为默认文件组创建指定索引。

PAD_INDEX = {ON |OFF }：指定是否索引填充。默认为 OFF。

　　ON 通过指定的可用空间的百分比fillfactor应用于索引中间级别页。

　　OFF 或 fillfactor 未指定，考虑到中间级页上的键集，将中间级页填充到接近其容量的程度，以留出足够的空间，使之至少能够容纳索引的最大的一行。

　　PAD_INDEX 选项只有在指定了 FILLFACTOR 时才有用，因为 PAD_INDEX 使用由 FILLFACTOR 指定的百分比。

FILLFACTOR = fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor 的值为1到100。

SORT_IN_TEMPDB = {ON |OFF }：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。默认为 OFF。

　　ON 用于生成索引的中间排序结果存储在tempdb。这可能会降低仅当创建索引所需的时间tempdb位于不同的与用户数据库的磁盘集。

　　OFF 中间排序结果与索引存储在同一数据库中。

IGNORE_DUP_KEY = {ON |OFF }：指定在插入操作尝试向唯一索引插入重复键值时的错误响应。默认为 OFF。

　　ON 向唯一索引插入重复键值时将出现警告消息。只有违反唯一性约束的行才会失败。

　　OFF 向唯一索引插入重复键值时将出现错误消息。整个 INSERT 操作将被回滚。

STATISTICS_NORECOMPUTE = {ON |OFF}：用于指定过期的索引统计是否自动重新计算。默认为 OFF。

　　ON 不会自动重新计算过时的统计信息。

　　OFF 启用统计信息自动更新功能。

DROP_EXISTING = {ON |OFF }：表示如果这个索引还在表上就 drop 掉然后在 create 一个新的。默认为 OFF。

　　ON 指定要删除并重新生成现有索引，其必须具有相同名称作为参数 index_name。

　　OFF 指定不删除和重新生成现有的索引。如果指定的索引名称已经存在，SQL Server 将显示一个错误。

ONLINE = {ON |OFF}：表示建立索引时是否允许正常访问，即是否对表进行锁定。默认为 OFF。

　　ON 它将强制表对于一般的访问保持有效，并且不创建任何阻止用户使用索引和/表的锁。

　　OFF 对索引操作将对表进行表锁，以便对表进行完全和有效的访问。

例子：

创建唯一聚集索引：

-- 创建唯一聚集索引
create unique clustered        --表示创建唯一聚集索引
index UQ_Clu_StuNo        --索引名称
on Student(S_StuNo)        --数据表名称（建立索引的列名）
with 
(
    pad_index=on,    --表示使用填充
    fillfactor=50,    --表示填充因子为50%
    ignore_dup_key=on,    --表示向唯一索引插入重复值会忽略重复值
    statistics_norecompute=off    --表示启用统计信息自动更新功能
)

创建唯一非聚集索引：

-- 创建唯一非聚集索引
create unique nonclustered        --表示创建唯一非聚集索引
index UQ_NonClu_StuNo        --索引名称
on Student(S_StuNo)        --数据表名称（建立索引的列名）
with 
(
    pad_index=on,    --表示使用填充
    fillfactor=50,    --表示填充因子为50%
    ignore_dup_key=on,    --表示向唯一索引插入重复值会忽略重复值
    statistics_norecompute=off    --表示启用统计信息自动更新功能
)

--创建聚集索引
create clustered index Clu_Index
on Student(S_StuNo)
with (drop_existing=on)    

--创建非聚集索引
create nonclustered index NonClu_Index
on Student(S_StuNo)
with (drop_existing=on)    

--创建唯一索引
create unique index NonClu_Index
on Student(S_StuNo)
with (drop_existing=on)

PS：当 create index 时，如果未指定 clustered 和 nonclustered，那么默认为 nonclustered。

创建非聚集复合索引：

--创建非聚集复合索引
create nonclustered index Index_StuNo_SName
on Student(S_StuNo,S_Name)
with(drop_existing=on)

--创建非聚集复合索引，未指定默认为非聚集索引
create index Index_StuNo_SName
on Student(S_StuNo,S_Name)
with(drop_existing=on)

在 CREATE INDEX 语句中使用 INCLUDE 子句，可以在创建索引时定义包含的非键列（即覆盖索引），其语法结构如下：

CREATE NONCLUSTERED INDEX 索引名
ON { 表名| 视图名 } ( 列名 [ ASC | DESC ] [ ,...n ] )
INCLUDE (<列名1>, <列名2>, [,… n])

--创建非聚集覆盖索引
create nonclustered index NonClu_Index
on Student(S_StuNo)
include (S_Name,S_Height)
with(drop_existing=on)

--创建非聚集覆盖索引，未指定默认为非聚集索引
create index NonClu_Index
on Student(S_StuNo)
include (S_Name,S_Height)
with(drop_existing=on)

PS：聚集索引不能创建包含非键列的索引。

创建筛选索引：

--创建非聚集筛选索引
create nonclustered index Index_StuNo_SName
on Student(S_StuNo)
where S_StuNo >= 001 and S_StuNo <= 020
with(drop_existing=on)

--创建非聚集筛选索引，未指定默认为非聚集索引
create index Index_StuNo_SName
on Student(S_StuNo)
where S_StuNo >= 001 and S_StuNo <= 020
with(drop_existing=on)

修改索引：

--修改索引语法
ALTER INDEX { 索引名| ALL }
ON <表名|视图名>
{ REBUILD  | DISABLE  | REORGANIZE }[ ; ]

REBUILD：表示指定重新生成索引。

DISABLE：表示指定将索引标记为已禁用。

REORGANIZE：表示指定将重新组织的索引叶级。

--禁用名为 NonClu_Index 的索引
alter index NonClu_Index on Student disable

删除和查看索引：

--查看指定表 Student 中的索引
exec sp_helpindex Student    

--删除指定表 Student 中名为 Index_StuNo_SName 的索引
drop index Student.Index_StuNo_SName

--检查表 Student 中索引 UQ_S_StuNo 的碎片信息
dbcc showcontig(Student,UQ_S_StuNo)

--整理 Test 数据库中表 Student 的索引 UQ_S_StuNo 的碎片
dbcc indexdefrag(Test,Student,UQ_S_StuNo)

--更新表 Student 中的全部索引的统计信息
update statistics Student

索引定义原则：

避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。

在条件表达式中经常用到的、不同值较多的列上建立索引，在不同值少的列上不要建立索引。

在频繁进行排序或分组（即进行 GROUP BY 或 ORDER BY 操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

在选择索引键时，尽可能采用小数据类型的列作为键以使每个索引页能容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必需遍历的索引页面降低到最小，此外，尽可能的使用整数做为键值，因为整数的访问速度最快。

一、书签查找的概念

　　书签可以帮助SQL Server快速从非聚集索引条目导向到对应的行，其实这东西几句话我就能说明白。

　　如果表有聚集索引(区段结构)，那么书签就是从非聚集索引找到聚集索引后，利用聚集索引定位到数据。此处的书签就是聚集索引。如果表没有聚集索引(堆结构)。那么扫描非聚集索引后，通过RID定位到数据，那么此处书签就是RID。

　　所谓的书签查找，就是通过聚集索引，然后利用聚集索引或RID定位到数据。

　　不论表示堆结构还是区段结构，数据的存放都是数据库文件的某文件->某页->某行，因此定位数据的文件组合起来就是
文件号:页号:行号。这三个数字就是RID。如文件1的第77页的第12行的RID就是1:77:12。

　　堆结构与区段结构不同，通常堆上的行不会改变位置，一旦他们被插入某个页中，他们就会一直在那个位置。在堆上的行很少移动，如果行被移动的话，他们会在原来的位置留下指向其移动到的新位置的指针。而区段结构的行，是可以移动的，在添加数据或整理索引时，都可以会被移动位置。

　　因为在堆上的行很少移动，所以RID就可以唯一标识某一行，RID的值不仅仅不变，RID所表示的行的物理位置也不会变，这使得RID的值更适宜作为书签。这也是为什么SQL Server在堆上建立的非聚集索引的书签都使用RID。

　　1、堆上的非聚集索引：基于RID的书签

CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate 　　--主键不是聚集索引，没有聚集索引
ON 
Sales.SalesOrderDetail(ProductID, ModifiedDate) 
INCLUDE (OrderQty, 
UnitPrice, LineTotal)

　　部分数据顺序：

　　注意到以上数据是无序的。

　　上面建立的非聚集索引因为使用了RID作为书签，直接指向对应行所在的物理位置，因此效率不错。虽然RID值用于键查找非常高效，但书签中包含的值与具体的用户数据无关。

　　2、在聚集索引上的非聚集索引：基于聚集键的书签

　　如果表示基于聚集索引的，则表内数据可以在表移动。因此，对于聚集索引来说，RID并不能一直不变的定位一个相同的行。因此必须用另外的方法定位行，这个方法就是使用聚集索引的索引键。
　　使用聚集索引键作为书签可以使得当数据在页中的行改变时，不需要非聚集索引的书签的值进行变动，因此非聚集索引的键就可以用于去找底层表的数据，即根据书签取数据不再基于物理位置，而是基于聚集索引查找。

　　
　　以聚集索引键作为非聚集索引的书签最好要聚集索引键满足如下标准：
　　索引应该具有唯一性：每一个索引条目书签都应该使得书签可以通过聚集索引的键值唯一的确认表中的一行，如果你创建的聚集索引键值不唯一，SQL Server将会为有重复键值的每一行自动加上一个叫uniquifier的东西使得每一行唯一。这个uniquifier对客户端是透明的。对于是否可以允许聚集索引键重复，要考虑以下两点：

　　生成uniquifier增加SQL Server插入操作的额外负担，在插入时SQL Server还需要判断插入的值在表中是否唯一，如果不唯一生成uniquifier值再进行插入。
　　uniquifier本身对业务数据来说是没有意义的，但是这个uniquifier本身不仅仅需要占用聚集索引键的空间，还同时占用非聚集索引书签的空间

　　索引键应该短：索引键所占的字节数应该短.因为这个键还会占用非聚集索引书签的空间。比如Contact表中以Last name / first name / middle name / street组合作为索引键看上去不错，但如果表中存在多个非聚集索引的话情况就有些微妙了。n个非聚集索引使得Last name / first name / middle name / street这些字段被存储在n+1个位置。

　　索引键最好不要变动：也就是索引键的值最好不要变动。对于聚集索引键的修改会使得基于这个聚集索引的所有非聚集索引同样进行修改。所以对于聚集索引的一次update会造成n个非聚集索引书签的update+1个聚集索引键值本身的update。

　　下面以一个示例来帮助理解书签查找:

　　假设数据库有一张表如下：

　　我们再Name列建一个非聚集索引，然后执行下面的语句：

　　从执行计划我们可以看到，因为Age列并不在非聚集索引中，所以SQL Server通过“键查找”引导到聚集表获取数据，这就是书签查找。

　　书签查找的目的，就是为了从非聚集索引导航到基本表获取非聚集索引中并未包含的信息。

二、书签查找的缺点

　　书签查找要求访问索引页面之外的数据页面，访问两组页面增加了查询逻辑读操作次数。而且，如果页面不在内存中，书签查找可能需要在磁盘上一个随机I/O操作来从索引页面跳转到数据页面，还需要必要的CPU能力来汇集这一数据并执行必要的操作。这是因为对于大的表，索引页面和对应的数据页面通常在磁盘上并不临近。

　　如果需要增加逻辑读操作或者开销较大的物理读操作使书签查找的数据检索操作开销相当大，这个开销因素是非聚集索引更适合于返回较小的数据行数的原因。随着查询检索的行数增加，书签查找的开销将变得无法接受。

　　为了理解书签查找随着检索行数增加而使feu聚集索引无效，下面来看一个实例:

　　还是那张Person表,一万数据。这次，我把索引建在Id列，Id列的唯一性是1，因为原来Id列是做主键+聚集索引的，但被我删掉了。

　　我们来看看下面两个查询的执行计划，

　　返回100条：

　　返回300条：

　　我们看到，当要求返回300条数据的时候，SQL Server就不在使用Id列上的非聚集索引，而是直接进行表扫描了。因为SQL Server认为执行300次书签查找还不如直接对一张1万条记录的表进行全表扫描。

　　由上面的实例可以得出结论，返回大的结果集将增加书签查找的开销，甚至低于表扫描。因此在返回较大结果集的情况下，必须考虑避免书签查找的可能性。

三、书签查找的起因

　　书签查找可能是一个开销较大的操作，所以应该分析查询计划，在执行计划中选择一个关键字查找步骤的原因。可能发现可以通过在非聚集索引键中包含丢失的行，或者作为索引页面级别上的包含列来避免书签查找，从而避免与书签查找相关的开销。

　　从上面的实例，我们可以提出观点：如果查询的各部分(不只是选择列表)中引用的列不都包含在使用的非聚集索引中，就会发生书签查找操作。

　　下面介绍一个技巧，我们点击某一个执行计划的图标之后，就能在右侧的属性信息栏里获取到相关的执行信息。例如，输出列表就是本执行计划的要返回的列。

四、避免书签查找的方法

　　因为书签查找的相对开销可能非常高，所以应该尽可能尝试摆脱书签查找操作。下面给出一下方案。

　　1、使用聚集索引

　　对于聚集索引，索引的叶子页面和表的数据页面相同。因此，当读取聚集索引键列的值时，数据引擎可以读取其他列的值而不需要任何导航。例如前面的区间数据查询的操作，SQL Server通过B树结构进行查找是非常快速的。

　　把非聚集索引转换为一个聚集索引说起来很简单。但是，这个例子和大部分可能遇到的情况下，这不可能做到，因为表已经有了一个聚集索引。这个表的聚集索引恰好是主键。必须卸载掉所有的外键约束，卸载并且重建为一个非聚集索引。这不仅要考虑所涉及的工作，还可能严重地影响依赖于现有聚集索引的其他查询。

　　2、使用覆盖索引

　　为了理解覆盖索引是如何避免书签查找，我们还是对于Person来执行如下两个查询：

　　下面修改索引增加Name列。

　　由于非聚集索引上已经有了需要查询的Id和Name列的数据，所以不在需要书签查找定位到基本表。

　　3、使用索引连接

　　如果覆盖索引变得非常宽，那么可能要考虑索引连接技术。索引连接技术使用两个或更多索引之间的一个索引交叉来完全覆盖一个查询。因为索引连接技术需要访问多余一个索引，它必须在所有索引连接中使用的索引上执行逻辑读。因此，索引连接需要比覆盖索引更高的逻辑读数量。但是，因为索引连接所用的多个窄索引能够比宽的覆盖索引服务更多的查询。所以索引连接也可以作为避免书签查找的一种技术来考虑。

　　我们来看下面的实例：

　　留意到，上面的例子我们创建了两个非聚集索引，一个在 Id列，一个在Name列。但是我们的查询需要同时返回Id列和Name列。而这两个非聚集索引都不完全包含要返回列。这个时候，哈希匹配目的就是通过定位到索引，而不用定位到基本表就能够获得我们所需要的全部数据，这样索引连接就避免了书签查找。

posted on 2023-09-15 10:51 大西瓜3721 阅读(68) 评论(0) 编辑收藏举报

刷新页面返回顶部

大西瓜3721

SQL Server 索引 之 书签查找

一、书签查找的概念

二、书签查找的缺点

三、书签查找的起因

四、避免书签查找的方法

导航

SQL Server 索引之书签查找