《Pro SQL Server Internals, 2nd edition》CHAPTER 2 Tables and Indexes-Clustered Indexes翻译

一．聚集索引

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

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

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

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

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

如您所见，索引始终具有一个叶级别，一个根级别以及零个或多个中间级别。唯一的例外是索引数据适合单个页面时。在这种情况下，SQL Server不会创建单独的根级页面，索引只包含单个叶级页面。

索引中的级别数很大程度上取决于行和索引键的大小。例如，4字节整数列上的索引在中间和根级别上每行需要13个字节。这13个字节由一个2字节的插槽数组条目，一个4字节的索引键值，一个6字节的页面指针和一个1字节的行开销组成，这是足够的，因为索引键不包含变量 - length和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说明了这个过程。

 

上述查询的执行计划显示了“集群索引扫描”运算符，其中Ordered属性设置为true，如图2-9所示

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

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

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

■提示您可以通过检查执行计划中的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-11.分配订单扫描执行计划

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

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

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

最后一个索引访问方法称为索引查找。从SELECT Name FROM dbo.Customers WHERE CustomerId BETWEEN 4 AND 7 ，图2-12说明了该操作。

图2-12.索引查找

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

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

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

图2-13.索引查找执行计划

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

从技术上讲，有两种索引查找操作。第一种方法称为单例查找，有时称为点查找，其中SQL Server查找并返回一行。您可以考虑以customerid=2谓词的位置为例。另一种类型的索引查找操作称为范围扫描，并且它要求SQL Server查找键的最小值或最大值，并扫描（向前或向后）行集，直到达到扫描范围的末尾。CustomerID在4到7之间的谓词到范围扫描。这两种情况都显示为执行计划中的索引查找操作。

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

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

可搜索谓词包括以下运算符：=、>、>=、<、<=、in、between和like（在前缀匹配的情况下）。不可sargable运算符包括not、<>、like（在不匹配前缀的情况下）和not in。

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

图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谓词和数据类型：带字符串参数的执行计划

如您所见，对于varchar列，unicode字符串参数是不可分析的。这是一个比看上去更大的问题。虽然很少用这种方式编写查询，如清单2-8所示，但大多数应用程序开发环境现在将字符串视为Unicode。因此，除非参数数据类型显式指定为varchar，否则SQL Server客户端库会为字符串对象生成unicode（nvarchar）参数。这使得谓词不可分析，并且即使在对varchar列进行索引时，也可能由于不必要的扫描而导致重大性能损失。

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

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