第9周 翻译——《Pro SQL Server Internals, 2nd edition》中的Clustered Indexes一节

 

原文来源：《Pro SQL Server Internals, 2nd edition》的CHAPTER 2 Tables and Indexes中的Clustered Indexes一节（即P36~P45）

作者：Dmitri Korotkevitch

                                                  聚集索引

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

        我们假设在堆表上使用数据创建一个聚集索引。第一步，如图2-5所示，SQL Server创建数据的另一个副本，然后根据聚集键的值对数据进行排序。数据页链接在一个双链表中，其中每个页面都包含指向链中的下一个和前一个页面的指针。这个列表称为索引的叶级，它包含实际的表数据。

图2-5  聚类索引结构：叶级

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

当叶子级别包含多个页面时，SQL Server开始构建索引是中间层，如图2-6所示。

 

图2-6. 聚类索引结构：中间级和叶级

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

        中间层的页面也链接到双链列表。SQL Server添加了越来越多的中间层，直到出现只包含单个页面的中间层为止。这个级别称为根级别，它成为索引的入口点，如图2-7所示。

        

图2-7  聚集索引结构：根级

        如你所见，索引总是有一个叶级、一个根级和零个或多个中间级。唯一的例外是索引数据适合于单个页面。在这种情况下，SQL Server不创建单独的根级页面，索引只包含单个叶级页面。 

        索引中的级别数量在很大程度上取决于行和索引键大小。例如，4字节整数列上的索引在中间层和根层上每行需要13个字节。这13个字节由2字节位置数组项、4字节索引键值、6字节页面指针和1字节行开销组成，这已经足够了，因为索引键不包含可变长度和空列。 

        因此，每行可以容纳8060个字节/13个字节=每页620行。这意味着，使用一个中间层，最多可以存储620*620=384400个叶级页面的信息。如果数据行大小为200字节，那么每个叶级页面可以存储40行，索引中最多可以存储15376000行，其中只有三个级别。向索引添加另一个中间级将覆盖所有可能的整数值。 

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

        SQL Server可以通过三种不同的方式从索引中读取数据。第一个是有序扫描。假设我们想要运行从客户表中查找名字并且按客户编号排序的相关查询。索引叶级上的数据已经基于客户编号列的值进行了排序。因此，SQL Server可以从第一个到最后一个页面扫描索引的叶级，并按存储的顺序返回行。

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

        

               图2-8. 命令索引扫描

        前面查询的执行方案显示了聚集索引扫描操作符， 命令属性设置为true，如图2-9所示。

图2-9  有序索引扫描执行方案

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

         SQL Server 可以在索引中向前和向后两个方向导航。但是，必须要记住一个重要点：SQL Server在向后索引扫描期间不使用并行性。 

■提示  可以通过检查索引扫描或执行方案中的索引查找操作符属性来检查扫描方向。但是请记住，Management Studio不会再执行方案的图形表示中显示这些属性。需要打开属性o'n'g窗口来查看它，方法是在执行计划中选择操作符并选择视图/属性窗口菜单项，或者按F4键。 

        SQL Server的企业版有一个名为旋转木马扫描的优化特性，允许多个任务共享同一个索引扫描。假设有会话S1，它在扫描索引。在扫描过程中，另一个会话S2运行一个查询，该查询需要扫描相同的索引。使用旋转木马扫描，S2在当前扫描位置加入S1.SQL Server只读取每一个页面一次，将行传递给两个会话。

        当S1扫描到达索引的末尾时，S2从索引的开始处开始扫描数据，直到S2扫描开始的地方。旋转木马扫描是另一个例子，说明了为什么不能依赖索引键的顺序，以及为什么在重要的时候应该始终指定order BY子句。 

        顺序扫描之后的下一个访问方法称为分配顺序扫描。SQL Server通过IAM页面访问表数据，类似于它对堆表的访问方式。无要求限定地从客户表中查询名字的查和图2-10说明了这种方法。图2-11显示了查询执行方案。

        

               图2-10.  分配顺序扫描

         图2-11  分配顺序扫描执行方案

         不幸的是，当SQL Server使用分配顺序扫描时很难检测到/尽管执行方案中的Ordered属性显示为false，但它表明SQL Server并不关心是否按索引值的顺序读取行，而不是是否使用了分配顺序扫描。 

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

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

         最后一种索引访问方法称为索引查找。图2-12演示了从客户表中查找客户编号在4-7之间的客户的名字的查询操作。 

      图2-12. 索引查找

         为了从表中读取行范围，SQL Server需要从范围中找到键值最小按当行，即4。SQL Server从根页面开始，其中第二行引用键值最小为350的页面。它大于我们正在寻找的键值（4），SQL Server读取根页面第一行引用的中间层数据页(1:170) 。

        类似地，中间页面将SQL Server引导到第一个叶级页面（1：176）。SQL Server读取该页，然后读取客户编号为4和5的行，最后从第二页读取剩余的两行。 

         执行方案如图 2-13 所示。 

 

图2-13  索引查找执行方案 

        可以猜到，索引查找比索引扫描更有效，因为SQL Server只处理行和数据的子集，而不是扫描整个表。 

        从技术上讲，有两种索引查找操作。第一个称为单例查找，其中SQL Server查找并返回一行。你可以以客户编号=2为例 。另一种索引查找操作称为范围扫描，它要求SQL Server查找键的最低值或最高值，并扫描（向前或向后）一组行，直到到达扫描范围的末尾。客户编号位于4和7之间的要求将限制范围扫描。这两种情况都显示为执行方案中的索引查找操作。

        如你所猜测一样，范围扫描完全有可能强制SQL Server处理大量甚至所有来自索引的数据页。例如，如果将查询更改为使用WHERE CustomerId > 0predicate，SQL Server将读取所有行/页，即使在执行方案中显示了索引查找操作。你必须记住这种行为，并始终在查询性能调优期间分析范围扫描的效率。 

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

        SARGable谓词包括以下操作符: = , > , >= , < , <= , IN , BETWEEN , 和 LIKE (在前缀匹配的情况下)。 非SARGable 操作符包括 NOT , <> , LIKE （在非前缀匹配的情况下), 和NOT IN 。 

        SARGable谓词的另一种情况是对表中的列使用函数或数学计算。SQL Server必须调用该函数，或者为它处理的每一行执行计算。幸运的是，在某些情况下，你可以重构查询，使这些谓词可SARGable。表2-1显示了一些这样的例子。 

        表2-1.  将非SARGable谓词重构为SARGable谓词的示例

   

     

         另一个必须记住的重要因素是类型转换。在某些情况下，可以使用不正确的数据类型使谓词非SARGable。让我们创建一个带有可变长度列的表，并用一些数据填充它，如列表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，尽管它存储整数值。现在，让我们运行两个select，如列表2-7所示，并查看执行方案。 

列表2-7.  SARG 谓词和数据类型：使用integer参数选择 

declare
@IntParam int = '200'

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

        如图2-14所示，对于integer参数，SQL Server扫描聚集索引，将varchar转换为每一行的整数。在第二种情况下，SQL Server在开始时将integer参数转换为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。这使得谓词非SARGable化，而且由于不必要的扫描，即使索引了varchar列，也会导致性能下降。 

■ 在客户端应用程序中始终指定重要的参数数据类型。例如，在ADO.Net中，使用 Parameters.Add("@ParamName",SqlDbType.Varchar, ).Value = stringVariable而不是Parameters.Add("@ParamName").Value = stringVariable重载。在ORM框架中使用映射来显式地指定类中的非Unicode属性。  

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