翻译《Pro SQL Server Internals, 2nd edition》的CHAPTER 2 Tables and Indexes中的Clustered Indexes一节

【第9周翻译】《Pro SQL Server Internals, 2nd edition》的CHAPTER 2 Tables and Indexes中的Clustered Indexes一节（即P36~P45）

聚集索引

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

        让我们假设您想要在包含数据的堆表上创建一个聚集索引。作为第一步（如图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字节的行头组成，这足够了，因为索引键不包含可变长度列和NULL列。

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

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

        SQL Server可以通过三种不同的方式从索引中读取数据。第一种是有序扫描。假设我们要运行  SELECT Name FROM dbo.Customers ORDER BY CustomerId 这个查询语句。叶级索引上的数据已根据CustomerId 列的值进行排序。因此，SQL Server可以扫描叶级索引，从第一页到最后一页，并按存储的顺序返回行。

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

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

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

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

提示：您可以通过检查执行计划中的索引扫描（ INDEX SCAN ）或索引查找（  INDEX SEEK ）运算符属性来检查扫描方向。但是，请记住，Management Studio不会在执行计划的图形表示中显示这些属性。您需要打开“属性”窗口来查看它，方法是在执行计划中选择操作员，然后选择“查看/属性”窗口菜单项或按F4键。

        SQL Server企业版具有一个名为   merry-go-round 扫描的优化功能，允许多个任务共享同一索引扫描。让我们假设您有会话S1，它正在扫描索引。在扫描过程中的某个时刻，另一个会话S2运行一个需要扫描同一索引的查询。通过merry-go-round 扫描，S2在当前扫描位置连接S1。SQL Server只读取每页一次，将行传递给两个会话。

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

        顺序扫描之后的下一个访问方法称为分配顺序扫描。SQL服务器通过IAM页面访问表数据，类似于堆表的访问方式。  SELECT Name FROM dbo.Customers WITH (NOLOCK)  查询和图2-10说明了这种方法，图2-11显示了查询执行计划。

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

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

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

        最后一个索引访问方法称为索引查找（ index seek）。 SELECT Name FROM dbo.Customers WHERE CustomerId BETWEEN 4 AND 7  查询和图2-12说明了这种操作。

        为了从表中读取行的范围，SQL Server需要从该范围中找到键的最小值为4的行。SQL Server从根页开始，第二行引用最小键值为350的页面。它大于我们要查找的键值（4），并且SQL Server读取根页上第一行引用的中间级数据页（1:170）。

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

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

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

        从技术上讲，有两种索引查找操作。第一种方法称为单例查找，有时称为点查找，其中SQL Server查找并返回一行。您可以考虑以 WHERE CustomerId = 2 谓词为例。另一种类型的索引查找操作称为范围扫描，它要求SQL Server查找键的最低或最高值，并扫描（向前或向后）行集，直到达到扫描范围的结尾。谓词  WHERE CustomerId BETWEEN 4 AND 7  导致范围扫描。这两种情况都显示为执行计划中的索引查找（INDEX SEEK）操作。

        正如您所猜测的，范围扫描完全有可能强制SQL Server处理索引中的大量甚至所有数据页。例如，如果将查询更改为使用  WHERE CustomerId > 0  谓词，则SQL Server将读取所有行/页，即使在执行计划中显示了索引查找（  Index Seek ）运算符。您必须记住这种性能，并始终在查询性能优化期间分析范围扫描的效率。

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

        可搜索谓词（SARGable）包括以下运算符：=、>、>=、<、<=、IN、BETWEEN和LIKE（在前缀匹配的情况下）。不可搜索运算符（ Non-SARGable ）包括NOT、<>、LIKE（在不匹配前缀的情况下）和NOT IN。

        使谓词不可分析的另一种情况是对表列使用函数或数学计算。SQL Server必须调用函数或对其处理的每一行执行计算。幸运的是，在某些情况下，您可以重构查询，使这些谓词具有可分析性。表2-1给出了一些例子。

        另一个必须记住的重要因素是类型转换。在某些情况下，可以通过使用不正确的数据类型使谓词不可分析。让我们用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所示，对于integer参数，SQL Server扫描聚集索引，将varchar转换为每行的整数。在第二种情况下，SQL Server在开始时将integer参数转换为varchar，并使用更高效的聚集索引查找操作。

提示：注意连接谓词中的列的数据类型。隐式或显式数据类型转换可以显著降低查询的性能。

        在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。因此，除非参数数据类型显式指定为varchar，否则SQL Server客户端库会为字符串对象生成unicode（nvarchar）参数。这使得谓词不可分析，并且即使在对varchar列进行索引时，也可能由于不必要的扫描而导致重大性能损失。

重要：始终在客户端应用程序中指定参数数据类型。例如，在ADO.Net中，使用Parameters.Add("@ParamName",SqlDbType.Varchar, <Size>).Value = stringVariable代替Parameters.Add("@ParamName").Value = stringVariable 超载。 使用ORM框架中的映射在类中显式指定非unicode属性。

        值得一提的是，varchar参数可用于nvarchar unicode数据列。

 

 

原著信息：

此篇文章翻译自《Pro SQL Server Internals, 2nd edition》的CHAPTER 2 Tables and Indexes中的Clustered Indexes一节（即P36~P45）。

原著：《Pro SQL Server Internals, 2nd edition》    原文作者：Dmitri Korotkevitch