第九周翻译

第九周翻译：聚集索引
聚集索引指示表中数据的物理顺序，该顺序根据聚集索引进行排序。索引键。表只能定义一个聚集索引。让我们假设您想要在包含数据的堆表上创建一个聚集索引。作为第一步，如图2-5所示，SQL Server创建数据的另一个副本，然后根据聚集键的值。数据页链接在一个双链接列表中，其中每个页面都包含指向链中下一页和上一页的指针。这个列表称为索引的叶级，它包含实际表数据。

注意：页面上的排序顺序由槽数组控制，页面上的实际数据未排序。
当叶级由多个页组成时，SQL Server将开始构建索引，如图2-6所示。

中间层为每个叶级页存储一行。它存储两条信息：物理地址及其引用的页中索引键的最小值。唯一的例外是第一页的第一行，其中SQL Server存储空值而不是最小索引键值。通过这种优化，SQL Server在插入行时不需要更新非叶级行。表中的最小键值。中间层的页面也链接到双链接列表。SQL Server添加了更多以及更多中间级别，直到有一个仅包含单页的级别为止。此级别称为根级别，它成为索引的入口点，如图2-7所示。

如你所见，索引总是有一个叶级、一个根级和零个或多个中间级。唯一的例外是当索引数据适合一个页面时。在这种情况下，SQL Server不会创建单独的根级别页，索引仅由单个叶级别页组成。索引中级别的数量在很大程度上取决于行和索引键的大小。例如，4字节整数列的索引在中间和根级别上每行需要13个字节。那些13字节由一个2字节的槽数组项、一个4字节的索引键值、一个6字节的页指针和一个1字节的行组成。开销，这是足够的，因为索引键不包含可变长度和空列。因此，每行可容纳8060字节/13字节=每页620行。这意味着，通过一个中间级别，您可以存储最多620*620=384400页的叶级页面的信息。如果数据行大小为200字节，则每个叶级页可存储40行，最多可存储15376000行只有三个级别的索引。在索引中添加另一个中间级别基本上可以覆盖所有可能的整数值。
注意 在现实生活中，索引碎片会减少这些数字。在第6章中我们将讨论索引碎片化在第6章中。
SQL Server可以通过三种不同的方式从索引中读取数据。第一个是按顺序扫描。假设我们要从dbo.customers order by运行select name。CustomerID查询。索引叶级上的数据已基于customerid列排序价值。因此，SQL Server可以扫描索引的叶级，从第一页到最后一页，并返回按存储顺序排列的行。SQL Server从索引的根页面开始，从中读取第一行。那一行引用
具有表中最小键值的中间页。SQL Server读取该页并重复直到在叶级找到第一页。然后，SQL Server开始逐个读取行，在页面的链接列表中移动，直到所有行都被读取。图2-8说明了这一过程。

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

值得一提的是，有序扫描触发不需要ORDERBY子句。有序扫描只意味着SQL Server根据索引键的顺序读取数据。SQL Server可以在索引中向前和向后导航。但是，有你必须记住的一个重要方面：SQL Server在后退时不使用并行性索引扫描。
提示 您可以通过检查索引扫描或索引查找运算符属性来检查扫描方向执行计划。但是，请记住，Management Studio不会在执行计划的图形表示。您需要打开属性窗口，通过选择
执行计划中的操作员，选择“查看/属性”窗口菜单项或按F4键。
SQL Server的企业版具有一个名为merry go round scan的优化功能允许多个任务共享同一索引扫描。假设您有会话s1，它正在扫描索引。在扫描过程中的某个时刻，另一个会话s2运行一个查询，该查询需要扫描相同的索引。通过旋转扫描，s2在当前扫描位置连接s1。SQL Server读取每一页仅一次，将行传递给两个会话。当s1扫描到达索引的末尾时，s2从索引的开头开始扫描数据。直到S2扫描开始。旋转扫描是你不能依赖的另一个例子。关于索引键的顺序以及当它重要时为什么总是指定ORDERBY子句。顺序扫描之后的下一个访问方法称为分配顺序扫描。SQL Server访问通过IAM页的表数据，类似于它如何处理堆表。从中选择名称带（nolock）查询和图2-10的dbo.customers演示了这种方法。图2-11显示了查询执行计划。


不幸的是，当SQL Server使用分配顺序扫描时，很难检测到它。即使执行计划中的ordered属性显示为false，表示SQL Server不关心按索引键的顺序读取行，而不是使用分配顺序扫描。对于扫描大型表来说，分配订单扫描可以更快，尽管它具有更高的启动成本。当表很小时，SQL Server不使用此访问方法。另一个重要考虑是数据一致性。SQL Server不在具有聚集索引的表中使用转发指针，并且分配顺序扫描可能会产生不一致的结果。由于由页拆分引起的数据移动。因此，SQL Server通常避免使用分配顺序扫描除非它以读取未提交或可序列化事务隔离级别读取数据。
注意：我们将在第6章“索引碎片”中讨论页面拆分和碎片，并讨论第三部分“锁定、阻塞和并发”中的锁定和数据一致性。
最后一个索引访问方法称为索引查找。从dbo.customers中选择名称，其中CustomerID介于4和7之间，查询和图2-12说明了该操作。

为了从表中读取行的范围，SQL Server需要找到最小值的行范围中键的值，即4。SQL Server从根页面开始，其中第二行引用最小键值为350的页面。它大于我们正在寻找的键值
（4），SQL Server读取根页面第一行引用的中间级数据页（1:170）。类似地，中间页将SQL Server引导到第一个叶级页（1:176）。SQL Server读取然后，它读取customerid为4和5的行，最后读取剩下的两行第二页的行。
执行计划如图2-13所示。

正如你所猜测的，索引查找比索引扫描更有效，因为SQL Server只处理行和数据页的子集，而不是扫描整个表。从技术上讲，有两种索引查找操作。第一个被称为单例查找，或者有时是点查找，其中SQL Server查找并返回一行。你可以想想在哪里以customerid=2谓词为例。另一种类型的索引查找操作称为范围扫描，并且它要求SQL Server查找键的最小值或最大值，并扫描（向前或向后）键的一组行，直到达到扫描范围的结尾。CustomerID在4到7之间的谓词到范围扫描。这两种情况都显示为执行计划中的索引查找操作。正如您所猜测的，范围扫描完全有可能强制SQL Server处理大量数据或甚至索引中的所有数据页。例如，如果将查询更改为使用where customerid>0谓词，SQL Server将读取所有行/页，即使您有索引查找运算符显示在执行计划中。你必须牢记这一行为并始终分析查询性能调整期间的范围扫描。关系数据库中有一个称为sargable谓词的概念，代表s earch参数能力。如果SQL Server可以使用索引查找操作，如果索引存在。简而言之，当SQL Server可以隔离索引的单个值或范围时，谓词是可分配的。要处理的关键值，从而限制谓词评估期间的搜索。显然，写作是有益的使用可搜索谓词进行查询，并尽可能使用索引查找。可搜索谓词包括以下运算符：=、>、>=、<、<=、in、between和like（前缀的情况下）匹配）。不可sargable运算符包括not、<>、like（在不匹配前缀的情况下）和not in。使谓词不可分析的另一种情况是使用函数或数学根据表列进行计算。SQL Server必须调用函数或执行它处理的每一行。幸运的是，在某些情况下，您可以重构查询以生成此类谓词。SARGable 表2-1给出了一些例子。
表2-1 将不可sargable谓词重构为可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© as (select 0 union all select 0) – 2 rows
,N2© as (select 0 from N1 as T1 cross join N1 as T2) – 4 rows
,N3© as (select 0 from N2 as T1 cross join N2 as T2) – 16 rows
,N4© as (select 0 from N3 as T1 cross join N3 as T2) – 256 rows
,N5© 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);
As you can see in Figure 2-14 , in the case of the integer parameter, SQL Server scans the clustered
index, converting varchar to an integer for every row. In the second case, SQL Server converts the integer
parameter to a varchar at the beginning and utilizes a much more efficient clustered index seek operation.

提示：注意连接谓词中的列数据类型。隐式或显式数据类型转换会显著降低查询的性能。
在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

如你所见，对于varchar列，unicode字符串参数是不可分析的。这太多了比看起来更大的问题。虽然很少用这种方式编写查询，如清单2-8所示，现在大多数应用程序开发环境都将字符串视为Unicode。因此，SQL Server客户端库为字符串对象生成Unicode（nvarchar）参数，除非参数数据类型被显式指定为varchar。这使得谓词不可分析，并可能导致由于不必要的扫描而导致性能下降，即使对varchar列进行了索引。
重点：始终在客户端应用程序中指定参数数据类型。例如，在ADO.NET中，使用参数.add（“@paramname”，sqldbtype.varchar，）.value=stringvariable而不是parameters.add（“@paramname”）.value=stringvariable重载。在ORM框架中使用映射到在类中显式指定非Unicode属性。