聚集索引

                                                                                                                                                                              聚集索引
 聚簇索引指示表中数据的物理顺序，该表根据聚簇进行排序
索引键。该表只能定义一个聚簇索引。
       假设您要在堆表上使用数据创建聚簇索引。作为第一步，
如图2-5所示，SQL Server创建了另一个数据副本，然后根据该数据进行排序
群集密钥的值。数据页链接在每个页面包含的双链表中
指向链中下一页和上一页的指针。此列表称为索引的叶级别
包含实际的表数据。
第2章■表格和索引：内部结构和访问方法

 

■注意页面上的排序顺序由插槽阵列控制。页面上的实际数据未排序。
 当叶级别由多个页面组成时，SQL Server开始构建一个中间级别
index，如图2-6所示。
图2-5。聚集的索引结构：叶级

 图2-6。聚集

 

的索引结构：中级和叶级
 中间级别为每个叶级页面存储一行。它存储了两条信息：
物理地址和它引用的页面中索引键的最小值。唯一的例外是
第一页上的第一行，其中SQL Server存储NULL而不是最小索引键值。
通过这种优化，SQL Server在插入行时不需要更新非叶级行
具有表中最低的键值。
 中间级别的页面也链接到双链表。 SQL Server增加了更多
和更多中间级别，直到有一个只包含单个页面的级别。这个级别叫做
根级别，它成为索引的入口点，如图2-7所示。
第2章■表格和索引：内部结构和访问方法

 

 如您所见，索引始终具有一个叶级别，一个根级别和零个或多个中间级别。
唯一的例外是索引数据适合单个页面。在这种情况下，SQL Server不会创建
单独的根级页面，索引只包含单个叶级页面。
 索引中的级别数很大程度上取决于行和索引键的大小。例如，
4字节整数列上的索引在中间和根级别上每行需要13个字节。那些
13个字节包含一个2字节的插槽数组条目，一个4字节的索引键值，一个6字节的页面指针和一个1字节的行
开销，这是足够的，因为索引键不包含可变长度和NULL列。
 因此，每行可容纳8,060字节/ 13字节=每页620行。这意味着，
使用一个中间级别，您可以存储最多620 * 620 = 384,400个叶级页面的信息。
如果数据行大小为200字节，则每个叶级页面最多可存储40行，最多可存储15,376,000行
只有三个级别的指数。向索引添加另一个中间级别基本上涵盖所有
可能的整数值。

■注意在现实生活中，索引碎片会减少这些数字。我们将讨论索引碎片
在第6章。
 SQL Server可以通过三种不同的方式从索引中读取数据。第一个是
通过有序扫描。假设我们想要运行SELECT Name FROM dbo.Customers ORDER BY
CustomerId查询。索引的叶级别上的数据已根据CustomerId列进行排序
值。因此，SQL Server可以从第一页到最后一页扫描索引的叶级并返回
行按存储顺序排列。
 SQL Server从索引的根页开始，从那里读取第一行。该行引用
具有表中最小键值的中间页面。 SQL Server读取该页面并重复
该过程直到找到叶级别的第一页。然后，SQL Server开始逐个读取行，
移动页面的链接列表，直到读取所有行。图2-8说明了这个过程。
 图2-7。聚簇索引结构：根级别
第2章■表格和索引：内部结构和访问方法
 前一个查询的执行计划显示带有Ordered的Clustered Index Scan操作符
property设置为true，如图2-9所示。

 

 图2-8。有序索引扫描

 

 图2-9。有序索引扫描执行计划
 值得一提的是，触发有序扫描不需要order by子句。
有序扫描只意味着SQL Server根据索引键的顺序读取数据。
 SQL Server可以向前和向后两个方向导航索引。但是，有
您必须牢记的一个重要方面：SQL Server在向后期间不使用并行性
索引扫描。
第2章■表格和索引：内部结构和访问方法
■提示您可以通过检查INDEX SCAN或INDEX SEEK运算符属性来检查扫描方向
执行计划。但请记住，Management Studio不会在中显示这些属性
执行计划的图形表示。您需要打开“属性”窗口以通过选择它来查看它
操作员执行计划并选择“查看/属性窗口”菜单项或按F4键。
 SQL Server企业版有一个称为旋转木马扫描的优化功能
允许多个任务共享相同的索引扫描。我们假设您有扫描的会话S1
指数。在扫描过程中的某个时刻，另一个会话S2运行需要扫描的查询
相同的指数。通过旋转木马扫描，S2将S1连接到当前扫描位置。 SQL Server读取每个页面
只有一次，将行传递给两个会话。
 当S1扫描到达索引的末尾时，S2开始从索引的开头扫描数据
直到S2扫描开始的点。旋转木马扫描是您不能依赖的另一个例子
按索引键的顺序以及为什么在重要时应始终指定ORDER BY子句。
 有序扫描之后的下一个访问方法称为分配顺序扫描。 SQL Server访问
表数据通过IAM页面，类似于堆表的方式。 SELECT名称FROM
dbo.Customers WITH（NOLOCK）查询和图2-10说明了这种方法。查询如图2-11所示
执行计划。

 

 图2-10。分配订单扫描

第2章■表格和索引：内部结构和访问方法

 

 图2-11。分配订单扫描执行计划

 

 图2-12。索引寻求
 不幸的是，当SQL Server使用分配顺序扫描时，检测起来并不容易。即便如此
执行计划中的有序属性显示为false，表示SQL Server不关心是否
按索引键的顺序读取行，而不是使用分配顺序扫描。
 尽管扫描大型表的启动成本较高，但分配顺序扫描可以更快地扫描大型表。
当表很小时，SQL Server不使用此访问方法。另一个重要的考虑是
数据一致性。 SQL Server不使用具有聚簇索引的表中的转发指针，以及
分配顺序扫描会产生不一致的结果。由于可以多次跳过或读取行
页面拆分导致的数据移动。因此，SQL Server通常会避免使用分配顺序扫描
除非它读取READ UNCOMMITTED或SERIALIZABLE事务隔离级别中的数据。
■注意我们将在第6章“索引碎片”中讨论页面拆分和碎片，并进行讨论
第三部分“锁定，阻塞和并发”中的锁定和数据一致性。
 最后一个索引访问方法称为索引查找。 SELECT名称来自dbo.Customers WHERE
CustomerId BETWEEN 4和7查询和图2-12说明了操作。
第2章■表格和索引：内部结构和访问方法
 为了从表中读取行的范围，SQL Server需要找到最小的行
从范围中键的值，即4. SQL Server以根页开始，其中第二行
引用最小键值为350的页面。它大于我们要查找的键值
（4），SQL Server读取根页上第一行引用的中级数据页（1：170）。
 同样，中间页面将SQL Server引导到第一个叶级页面（1：176）。 SQL Server读取
该页面，然后它读取CustomerIds等于4和5的行，最后，它读取剩余的两行
第二页的行。
 执行计划如图2-13所示。

 

 图2-13。索引寻求执行计划
 正如您所猜测的，索引搜索比索引扫描更有效，因为SQL Server只处理索引
行和数据页的子集，而不是扫描整个表。
 从技术上讲，索引搜索操作有两种。第一种称为单例查找，
或者有时是点查找，SQL Server寻找并返回单行。你可以考虑一下WHERE
CustomerId = 2谓词作为示例。另一种类型的索引查找操作称为范围扫描，和
它要求SQL Server找到密钥的最低或最高值并扫描（向前或向后）
行集直到它到达扫描范围的末尾。客户IDI 4和7之间的谓词WHERE引导
到范围扫描。这两种情况都在执行计划中显示为INDEX SEEK操作。
 您可以猜到，范围扫描完全可以强制SQL Server处理大量数据或
甚至索引中的所有数据页面。例如，如果您将查询更改为使用WHERE CustomerId> 0
谓词，即使你有一个Index Seek运算符，SQL Server也会读取所有行/页面
显示在执行计划中。你必须记住这种行为，并始终分析效率
查询性能调整期间的范围扫描。
 关系数据库中有一个名为SARGable谓词的概念，它代表S earch
Arg ement能够。如果SQL Server可以使用索引查找操作（如果是索引），则谓词是SARGable
存在。简而言之，当SQL Server可以隔离单个值或索引范围时，谓词是SARGable
要处理的关键值，从而限制谓词评估期间的搜索。显然，写作是有益的
查询使用SARGable谓词并尽可能利用索引查找。
 SARGable谓词包括以下运算符：=，>，> =，<，<=，IN，BETWEEN和LIKE（如果是前缀）
匹配）。非SARGable运算符包括NOT，<>，LIKE（在非前缀匹配的情况下）和NOT IN。
 使谓词非SARGable的另一种情况是使用函数或数学
针对表列的计算。 SQL Server必须调用该函数或执行计算
它处理的每一行。幸运的是，在某些情况下，您可以重构查询以生成此类谓词
优化搜索。表2-1列出了一些例子。
第2章■表格和索引：内部结构和访问方法

表2-1。将非SARGable谓词重构为SARGable的示例
 操作非SARGable实施SARGable实施
 数学计算列 - 1 = @Value Column = @Value + 1
ABS（列）= 1列IN（-1,1）
 日期操作CAST（列为日期）= @Date
（在2008年之前的SQL Server中）
转换（datetime，转换
（VARCHAR（10），柱，121））
列> = @Date和
列<DATEADD（day，1，@ Date）
DATEPART（年份，专栏）= @Year Column> = @Year and
列<DATEADD（年，1，@年）
DATEADD（第7天，第列）>
 GETDATE（）
专栏>
 DATEADD（天，-7，GETDATE（））
 前缀搜索LEFT（Column，3）='ABC'列LIKE'ABC％'
 子字符串搜索列LIKE'％ABC％'使用全文搜索或其他
技术
 您必须牢记的另一个重要因素是类型转换。在某些情况下，你可以做
通过使用不正确的数据类型来预测非SARGable。让我们创建一个包含varchar列的表
用一些数据填充它，如清单2-6所示。
 清单2-6 SARG谓词和数据类型：测试表创建

create table dbo.Data
(
 VarcharKey varchar(10) not null,
 Placeholder char(200)
);
create unique clustered index IDX_Data_VarcharKey
on dbo.Data(VarcharKey);
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536 rows
,IDs(ID) as (select row_number() over (order by (select null)) from N5)
insert into dbo.Data(VarcharKey)
 select convert(varchar(10),ID) from IDs;

 

聚簇索引键列定义为varchar，即使它存储整数值。 现在，我们来吧
运行两个选择，如清单2-7所示，并查看执行计划。
第2章■表格和索引：内部结构和访问方法
  清单2-7 SARG谓词和数据类型：使用整数参数选择
宣布
  @IntParam int ='200'

select * from dbo.Data where VarcharKey = @IntParam;
select * from dbo.Data where VarcharKey = convert(varchar(10),@IntParam);

 

如图2-14所示，对于整数参数，SQL Server扫描聚簇
index，将varchar转换为每行的整数。 在第二种情况下，SQL Server转换整数
在开头的varchar参数，并利用更有效的聚簇索引查找操作。

 

  图2-14。 SARG谓词和数据类型：带整数参数的执行计划
■提示请注意连接谓词中的列数据类型。 隐式或显式数据类型转换
可以显着降低查询的性能。
  在unicode字符串参数的情况下，您将观察到非常类似的行为。 我们来运行查询
如清单2-8所示。 图2-15显示了语句的执行计划。
  清单2-8 SARG谓词和数据类型：使用字符串参数选择

select * from dbo.Data where VarcharKey = '200';
select * from dbo.Data where VarcharKey = N'200'; -- unicode parameter 

第2章■表格和索引：内部结构和访问方法

图2-15。 SARG谓词和数据类型：带字符串参数的执行计划
 如您所见，对于varchar列，unicode字符串参数是非SARGable。这是一个很大的问题
比它看起来更大的问题。虽然您很少以这种方式编写查询，如清单2-8所示，
现在大多数应用程序开发环境都将字符串视为unicode。结果，SQL Server
客户端库为字符串对象生成unicode（nvarchar）参数，除非参数数据
type显式指定为varchar。这使谓词非SARGable，并且它可以导致主要
由于不必要的扫描导致性能下降，即使对varchar列进行索引也是如此。
■重要始终在客户端应用程序中指定参数数据类型例如，在ADO.Net中，使用
Parameters.Add（“@ ParamName”，SqlDbType.Varchar，<Size>）。Value = stringVariable而不是
Parameters.Add（“@ ParamName”）。Value = stringVariable overload。在ORM框架中使用映射
在类中显式指定非unicode属性。
 值得一提的是，对于nvarchar unicode数据列，varchar参数是SARGable。