聚集索引

 

集合索引指示表中数据的物理顺序，该顺序按照集合排序索引键.该表只能定义一个聚集索引。

让我们假设您想要用数据在堆表上创建一个聚集索引。作为第一步，如图2-5所示，SQL Server创建数据的另一个副本，然后根据聚集键的值。数据页链接在一个双链接列表中，每个页面包含

指向链中下一页和上一页的指针。这个列表被称为索引的叶级，它包含实际的表数据。

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

图2-5。聚集索引结构:叶级

注意：页面上的排序顺序由插槽数组控制。页面上的实际数据未排序。

当叶级由多个页面组成时，SQL Server开始构建索引，如图2-6所示。

图2-6。聚集索引结构:中级和叶级

中间层为每个叶级页面存储一行。它存储两条信息物理地址和它引用的页面中索引键的最小值。唯一的例外是第一页的第一行，在这里，SQL Server存储空值，而不是最小索引键值。通过这样的优化，当您插入行时，SQL Server不需要更新非叶级行具有表中最低的键值。

中间级别的页面也链接到双链接列表。SQL Server添加了更多内容和更多中间级别，直到有一个级别仅包括单个页面。这一层叫做根级别，它成为索引的入口点，如图2-7所示。

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

图2-7。聚集索引结构:根级别

如您所见，索引总是有一个叶级、一个根级和零个或多个中间级。唯一的例外是当索引数据适合一个页面时。在这种情况下，SQL Server不会创建单独的根级页面，索引仅由单个叶级页面组成。

索引中的级别数在很大程度上取决于行和索引键的大小。例如4字节整数列上的索引在中间和根级别上将需要每行13字节。那些13字节包括一个2字节的槽数组条目、一个4字节的索引键值、一个6字节的页面指针和一个1字节的行开销，这是足够的，因为索引键不包含可变长度和空列。

因此，您可以容纳8，060字节/每行13字节=每页620行。这意味着，使用一个中间层，您可以存储多达620 * 620 = 384，400页的信息。如果数据行大小为200字节，则每个叶级页面可以存储40行，在中最多可以存储15，376，000行只有三个层次的指数。将另一个中间级别添加到索引中基本上将覆盖所有可能的整数值。

注意；在现实生活中，索引碎片会减少这些数字。我们将讨论索引碎片在第六章。

有三种不同的方法可以让服务器从索引中读取数据。第一个是通过有序扫描。让我们假设我们想要运行从dbo中选择名称。客户订单依据CustomerId查询。索引叶级的数据已经根据CustomerId列进行了排序价值。因此，SQL Server可以从第一页到最后一页扫描索引的叶级别，并返回按存储顺序排列的行。

SQL Server从索引的根页面开始，并从那里读取第一行。那一行引用了表中具有最小键值的中间页。SQL Server读取该页面并重复直到在叶级找到第一页。然后，SQL Server开始逐个读取行，在页面的链接列表中移动，直到所有行都被读取。图2-8说明了这个过程。

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

图2-8。有序索引扫描

前面查询的执行计划显示了带有有序的聚集索引扫描运算符属性设置为true，如图2-9所示。

图2-9。有序索引扫描执行计划

值得一提的是，触发有序扫描不需要order by子句。有序扫描仅仅意味着SQL Server根据索引键的顺序读取数据。

SQL Server可以向前和向后两个方向浏览索引。然而，有您必须记住的一个重要方面是:在向后的过程中，SQL Server不使用并行性索引扫描。

提示：您可以通过检查中的索引扫描或索引寻道操作符属性来检查扫描方向执行计划。但是，请记住，管理工作室不会在执行计划的图形表示。您需要打开属性窗口，通过选择并选择视图/属性窗口菜单项或按下F4键。

SQL Server的企业版有一个称为旋转扫描的优化功能，它允许多个任务共享同一个索引扫描。假设你有S1会议，正在扫描索引。在扫描过程中的某个时刻，另一个会话S2运行一个查询，该查询需要扫描相同的索引。通过旋转扫描，S2在当前扫描位置与S1会合。SQL Server读取每一页只有一次，将行传递给两个会话。

当S1扫描到达索引的末尾时，S2从索引的开头开始扫描数据直到S2扫描开始。旋转扫描是你不能依赖的另一个例子关于索引键的顺序，以及为什么在重要的时候应该总是指定ORDER BY子句。

有序扫描后的下一个访问方法称为分配顺序扫描。SQL服务器访问IAM页面中的表数据，类似于堆表。从中选择名称dbo。具有(NOLOCK)查询的客户和图2-10说明了这种方法。图2-11显示了查询执行计划。

图2-10。分配顺序扫描

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

不幸的是，当SQL Server使用分配顺序扫描时，并不容易检测到。尽管执行计划中的Ordered属性显示为false，这表示SQL Server不关心按索引键的顺序读取行，而不是使用分配顺序扫描。

对于扫描大型表，分配顺序扫描可能更快，尽管启动成本更高。当表很小时，SQL Server不使用此访问方法。另一个重要的考虑是数据一致性。在具有聚集索引和分配顺序扫描会产生不一致的结果。由于以下原因，可以跳过或多次读取行页面拆分导致的数据移动。因此，SQL Server通常避免使用分配顺序扫描除非它以READ UNCOMMITTED或SERIALIZABLE事务隔离级别读取数据。

注意:我们将在第6章“索引碎片”中讨论页面碎片和碎片，并讨论第三部分“锁定、阻塞和并发”中的锁定和数据一致性

最后一种索引访问方法称为索引查找。从数据库中选择名称。客户在哪里4到7之间的客户标识查询和图2-12说明了操作。

图2-12。索引搜索

为了从表中读取行的范围，SQL Server需要找到具有最小值的行范围内的键的值，即4。SQL Server从根页面开始，第二行引用最小键值为350的页面。它大于我们正在寻找的关键值

1. ，并且SQL Server读取根页面上第一行引用的中级数据页面(1:170)。

类似地，中间页面将SQL Server引导到第一个叶级页面(1:176)。SQL Server读取该页面读取客户编号等于4和5的行，最后读取剩余的两行第二页的行。

执行计划如图2-13所示。

图2-13。索引搜索执行计划

正如您所猜测的，索引查找比索引扫描更有效，因为SQL Server只处理行和数据页的子集，而不是扫描整个表。

从技术上讲，有两种索引查找操作。第一种叫做单例查找，或者有时是点查找，在这里，SQL Server查找并返回一行。你可以想想在哪里CustomerId = 2谓词为例。另一种索引查找操作称为范围扫描，并且它要求SQL Server查找键的最低或最高值，并扫描(向前或向后)一组行，直到它到达扫描范围的末端。客户识别在4到7之间的谓词距离扫描。这两种情况在执行计划中都显示为INDEX SEEK操作。

正如您所猜测的，范围扫描完全有可能迫使SQL Server处理大量数据，或者甚至索引中的所有数据页。例如，如果您将查询更改为使用WHERE CustomerId > 0谓词，即使您有一个索引查找操作符，SQL Server也会读取所有行/页显示在执行计划中。你必须牢记这一行为，并始终分析查询性能优化期间的范围扫描。

关系数据库中有一个概念叫做SARGable谓词，代表搜索有参数。如果SQL Server可以利用索引查找操作，如果索引存在。简而言之，当SQL Server可以隔离单个值或索引范围时，谓词是可标记的要处理的键值，从而限制谓词评估期间的搜索。显然，写作是有益的使用SARGable谓词的查询，并尽可能使用索引搜索。

可匹配谓词包括以下运算符:=、>、> =、<、< =、IN、BETWEEN和LIKE(在前缀的情况下

匹配)。非可匹配运算符包括NOT、<>、LIKE(在非前缀匹配的情况下)和NOT IN。

使谓词不可匹配的另一种情况是使用函数或数学针对表列的计算。SQL Server必须调用函数或为执行计算它处理的每一行。幸运的是，在某些情况下，您可以重构查询来生成这样的谓词萨贾布尔。表2-1给出了几个例子。

表2-1。将不可匹配谓词重构为可匹配谓词的示例

您必须记住的另一个重要因素是类型转换。在某些情况下，你可以通过使用不正确的数据类型断言是非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-7。SARG谓词和数据类型:用整数参数选择

declare

@IntParam int = '200'

select * from dbo.Data where VarcharKey = @IntParam;

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

如图2-14所示，在整数参数的情况下，SQL Server扫描集群索引，将每一行的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-15。SARG谓词和数据类型:带字符串参数的执行计划

如您所见，unicode字符串参数对于varchar列来说是非SARGable的。这是一个很大的比看起来更大的问题。虽然您很少用这种方式编写查询，如清单2-8所示，如今，大多数应用程序开发环境都将字符串视为unicode。因此，SQL Server客户端库为字符串对象生成unicode ( nvarchar)参数，除非参数数据类型被显式指定为varchar。这使得谓词不可搜索，并可能导致

不必要的性能冲击.

重要信息始终在客户端应用程序中指定参数数据类型。例如，在ADO.Net，使用参数。添加(“@ParamName”，SqlDbType)。Varchar，< Size >)。Value = stringVariable，而不是参数。添加(“@ParamName”)。Value = stringVariable重载。在ORM框架中使用映射来显式指定类中的非unicode属性。

还值得一提的是，varchar参数对于nvarchar unicode数据列是SARGable的。