【翻译】SQL Server索引进阶：第四级，页和区

jiang1799

　　原文地址：
　　Stairway to SQL Server Indexes: Level 4, Pages and Extents
　　本文是SQL Server索引进阶系列（Stairway to SQL Server Indexes）的一部分。
　　在之前的级别中，我们分别在有索引和没有索引的表中执行查询，比较了他们需要做的工作。我们的主要度量标准是“login read逻辑读”。我们总是比较在有索引和没有索引的表执行查询的逻辑读。现在，是时候解释为什么“逻辑读”是一个优秀的度量标准，同时也解释了实际上读取了什么。
　　当你向SQL Server提交一个查询请求，它知道通过扫描表可以满足你的要求。SQL Server理解一个索引在什么情况下对查询有用，只是在使用索引比扫描表做的工作要少的时候。如果你被问到“SQL Server做了什么工作？”，答案就是“磁盘I/O”。一个查询需要的IO，是一个用来衡量查询消耗的，很好的指标。主要因为IO会消耗两个关键的资源：时间和内存。
　　扫描整张表所需要的IO，是一个经常被误解的度量标准，因为SQL Server读取的不是行，它读取的是页。读取一页比读取一行是更难的一个工作单元。
　　这一级会比较短，因为它只聚焦于SQL Server如何执行IO。理解SQL Server的IO是必要的，可以理解在查询的时候，为什么一些索引起作用，而另外一些索引没有起作用；还有为什么一些数据的修改会快于其他的数据；还有为什么一些索引的维护所需要的时间要比其他的索引少。简单来说，SQL Server的IO的基本知识是必要的，对于理解这个系列的其他文章是很必要的。
　　页
　　在你创建数据库的时候，你指定了数据文件的存放位置。SQL Server把这些文件看做是一个长的字符串。逻辑上，而不是物理上，被分作很多文件，每块儿的大小是8K。这些8K大小的块儿被叫做页。因此第一个8K的文件就是page#0，接下来就是page#1，等等。一页是最小的IO单元。SQL Server每次IO读写都至少是一页。如果需要读取或者写入多个连续的页，SQL Server也可能会在一次IO中进行操作。
　　一页不仅仅是一个IO的单元，也是一个所有权的单元。如果一页包含表TableA的一行数据，它就只会包含表TableA中的行数据。如果一页包含一个非聚集索引的一个入口，它就只会包含这个非聚集索引的入口。除了数据，每页还包含一些头部信息，还有一些偏移的指针，用来帮助SQL Server定位页中的单行数据，或者是页中的入口信息。
　　在前面的级别中，我们在看了一些有索引和没有索引的表的数据的顺序，下面是我们从页的角度看这些数据的顺序。
　　SalesOrderID SalesOrderDetailID ProductID   OrderQty UnitPrice
　　Page n-1:
　　43668        106                722         3          178.58
　　43668        107                708         1           20.19
　　Page n:
　　43668        108                733         3          356.90
　　43668        109                763         3          419.46
　　43669        110                747         1          714.70
　　43670        111                710         1            5.70
　　43670        112                709         2            5.70
　　43670        113                773         2        2,039.99
　　43670        114                776         1        2,024.99
　　43671        115                753         1        2,146.96
　　43671        116                714         2           28.84
　　43671        117                756         1          874.79
　　Page n+1:
　　43671        118                768         2          419.46
　　43671        119                732         2          356.90
　　43671        120                763         2          419.46
　　43671        121                755         2          874.79
　　43671        122                764         2          419.46
　　43671        123                716         1           28.84
　　43671        124                711         1           20.19
　　43671        125                708         1           20.19
　　43672        126                709         6            5.70
　　43672        127                776         2        2,024.99
　　Page n+2:
　　43672        128                774         1        2,039.99
　　43673        129                754         1          874.79
　　43673        130                715         3           28.84
　　43673        131                729         1          183.94
　　逻辑的顺序和物理的顺序没有必须是相同的需要。上面的数据显示我们的数据跨了很多页n，n+1，n+2，n+3，也可能是n，n+9，n-5，n+2。逻辑和物理顺序之间的偏差被叫做“外部碎片”。相应的，一页的空闲空间所占的百分比被叫做“内部碎片”。在后面的级别中，我们会介绍更多这方面的细节。
　　同样也没有必要要求每一页都包含相同的行数。通常，在包含索引的表中进行正常的插入和删除活动，会导致表的每一页都包含几乎相同的行数。精确的说，每一页包含的数据量，按照bytes计算，是几乎相同的。如果一行或者索引的入口包含了可变长度的列，每一页的行数会发生变化，尽管每一页的数据量bytes保持不变。
　　每一行的大小=所有列的大小+行的头部信息。行的头部信息的大小依赖于很多因素，总结起来有下面几条：

• 每行6bytes字节的状态信息和长度信息。
  
• 每个固定长度的列占用1bit位的信息，向最近的字节数进行舍入。
  
• 如果包含可变长度的列，首先会占用4字节，然后每个可变长度的列都会额外占用2字节。
  
• 每行都有2字节的额外的偏移指针信息，位于页的尾部。
  

　　因为SalesOrderDetail表有可变长度的列，这些列的大小事先不能确定。但是平均每行使用95字节。因为每页8K字节，SalesOrderDetail表的每页大约75行数据，在上面的列子中每页只有10行数据，在后面，我们会介绍如何使用SQL Server的管理工具设置这些数值。
　　因此，尽管我们经常说SQL Server读取了多少行，其实是误导。SQL Server读取的不是行，读取的最小单位是页。SQL Server使用索引快读的访问行是一种误导的说法。正确的说法是，索引使得SQL Server快读的访问页，而不是行。一旦SQL Server往内存中加载一页或者更多的页，它会检查这些页，并且定位所需要的行。
　　分区
　　SQL Server在页之上还有一些逻辑的组，它把连续的8页叫做一个分区单元。正常来说，一个分区，和页一样，是一个所有权的单元。如果一个分区中的一页为表TableA或者索引IndexB所有，所有的8页也都为相同的所有者。一些非常小的表或者是索引除外，他们不能充满整个分区。在这种情况下，在同一个分区会出现多于一个的表或者索引。但是对大多数对象来说，分区还是一个所有权的单元。
　　因此，SQL Server不会通过表扫描来查询表中的所有数据，而是去读取表所有的页或者是分区。它知道会产生8K的IO，甚至是64K，或者更多，有可能是并行的。如果需要读取每一行的话，会产生很吓人的表扫描。
　　读取页和分区，不仅仅意味着，表扫描比我们预期的工作要少，也意味着，会从非聚集索引中受益。假设下面的查询，针对SalesOrderDetail表，请求大约覆盖了4%的数据。
Query:SELECT *　　FROM Sales.SalesOrderDetail
　　WHERE ProductID = 712
Clustered Index: SalesOrderID / SalesOrderDetailIDAverage Number of Rows per Page:75Nonclustered Index:ProductIDPercentage of rows being requested:4%　　在每25行之后，就会有一行被选中，因为入口会根据包含ProductID的非聚集索引分组，用ProductID的非聚集索引定位行，看起来是个好主意。再好好想想。
　　由于聚集索引，表按照SalesOrderID/SalesOrderDetailID的顺序排列，而不是ProductID的顺序。因此，如果每页平均75行，查询请求25行之后的一行，每页可能包含3行所需的数据，几乎每一页至少被请求一次。换句话说，为了满足请求，每一页都会被读取。这就是一个表扫描，一次读取一个分区，甚至更多，平均每次读取都会包含24个请求。
　　使用SQL Server的新手都会说：“如何选择非聚集索引来使用？”目前为止，你有一个答案：“不要每页选择一行。”后面的内容将会使你的决定变得更准确，关于那些索引是好的，哪些不是好的，这样的选择。
　　结论
　　SQL Server读取的不是行，读取的单位是一页或者更多页。页，是最小的IO单元，每页的大小是8K。一个分区包含8个连续的页。通常情况，一个分区，和他的页，只包含一个对象（堆表或者索引表）的行和入口。因为大IO单元带来的效率，一个包含可用非聚集索引的查询更可选。
　　在第五级中，我们看一下你如何做可以增加非聚集索引给查询带来的好处，如何建立索引，使得你的索引可以覆盖多个查询。换句话说，下一级别会在你的索引中增加一些列。
　　代码下载
　　Pages.SQL
　　Level 4 - Pages.sql