【译】SQL Server索引进阶第四篇：页和区

2653885

SQL Server索引进阶第四篇：页和区

　　索引设计是数据库设计中比较重要的一个环节，对数据库的性能其中至关重要的作用，但是索引的设计却又不是那么容易的事情，性能也不是那么轻易就获取到的，很多的技术人员因为不恰当的创建索引，最后使得其效果适得其反，可以说“成也索引，败也索引”。
　　本系列文章来自Stairway to SQL Server Indexes
　　
　　在上一篇文章中，我们比较了完全相同的查询语句分别在存在聚集索引和非聚集索引的环境下耗费的成本.我们以“逻辑读”作为衡量成本的主要标准。现在就让我们来解释逻辑读作为主要衡量标准的原因以及SQL Server在读取时究竟读取什么。
　　当你提交一个向SQL Server的请求查询时，SQL Server知道扫描整表一定可以满足这个需求。但如果使用索引进行数据查找的成本要低于整表扫描时，SQL Server会选择索引。也许你就开始有疑问了，那SQL Server根据什么因素判断成本呢？主要是磁盘IO，查询所请求的磁盘IO是查询消耗的主要部分，这也许是因为IO占用了最宝贵的两种资源：时间和内存。
　　扫描整表时SQL Server所引起的I/O很多人都理解的不太正确，因为实际上SQL Server并不会读取行，而是读取页。页和行作为不同粒度的单位对于读取来说是完全不同的。
　　本篇文章是系列文章中最短的，因为本文主要讲述SQL Server进行IO请求。理解SQL Server的IO是对于理解很多现象的必要基础，比如为什么某些索引使得查询跑得更快而另一些查询不会，或是为什么某些修改数据的查询比其它查询执行的更快，亦或是为什么某些维护计划需要更长的时间。总之，对于SQL Server IO的理解是理解本系列文章的后续文章的关键所在
　　
  页
  　　你创建了一个数据库时，你指定了数据库文件所存储的位置。对于SQL Server来说，每一个文件都是一长串的bytes.SQL Server逻辑上（仅仅是逻辑上，不是物理上）把这个文件分成N多8K大小的块。这个块就是所谓的页。数据库文件第一个8K的块就是页#0,下一个8K的块就是页#1，依此类推。页是IO读取的最小单元，SQL Server每一个IO读写都涉及一个页，当然了，对于多个物理上连续的页，SQL Server也可以用一个IO全部完成。
　　页不仅仅是IO读取的最小单位，也是所有权的最小单位。如果一页中包含了表A的一行数据，这页就只能存储表A的行数据了。或是一页中包含了索引B的条目，那这页也仅仅只能存储索引B的条目了。每页中除去存储数据之外，还存储一些页头信息以及行偏移以便SQL Server知道具体每一行在页中的存储位置。
　　在上一篇中，我们多次讲述了条目在索引中连续，无论是在聚集索引还是非聚集索引中，下面，我们更详细的说一下所谓的连续。在SalesOrderDetail表中的部分数据可以让你对页有一个更直观的认识。
　　
　　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页，n+9页，n-5页，n+2页。逻辑顺序和物理顺序的差异被称为外部碎片。同样的，页面内部没有被使用的空间被称为内部碎片，在本系列接下来的文章中我们将会对内部碎片和外部碎片产生的影响进行更详细的讲解。
　　每一页并不需要含有完全一样的行数，通常情况下，在有聚集索引的表上进行插入或删除操作的结果是每一个页都含有近似的行或条目数。如果不按页中含有的行数，而按页中含有的字节数来看的话，因为有可能包含变长数据，虽然每页包含的字节数类似，但每页所含的行数可能有差异很大。
　　每行的字节数包含所有列的字节数相加外加行开销，有很多因素决定行开销的大小，这些因素可以总结如下:
  

   

•     每行6字节用于存储状态信息和长度信息   
  
•     每列占一位,向上取整到字节   
  
•     如果包含变长数据，第一个变长列4个字节，之后每个变长列额外占两个字节   
  
•     除此之外，页尾还包含每个行2个字节的偏移指针
  

  　　
　　因为SalesOrderDetail含有变长列，所以每行所占的大小并不能提前预测,但是还是可以算出平均每行所占的字节为95字节。因为每页的大小是8k,因此每页大约可以容纳75行，这要比我们上面的例子多出很多，在接下来的文章中我们将会详细讨论通过SQL Server management studio来查看每页所含的行数。
　　所以，虽然我们通常说SQL Server是”读取行”,但如果深究起来这种说法是错误的，其所读取的最小单位是页。我之前的也说过SQL Server通过给定的索引键值可以快速的找到行,但其实深究的话正确的说法是SQL Server通过给定的索引键值找到页。在SQL Server将找到的页存入内存之后，在从内存中找到指定的行。
　　
  区
  　　SQL Server基于页之上做了另外一种逻辑分组，它将8个物理上连续的页分为一个区。正常情况下，和页一样，区也是所有权的最小单位，如果区中的一页属于表A或者表B，那么区中其它页也属于表A或者表B，但对于特别小的表或者索引就不适用了，对于很小的表和索引，一个区中的页可以属于两个以上的表。但对于大多数区来说，区都是所有权的基本单位。
　　因此，对于表扫描来说，SQL Server并不是扫描所有的行，而是属于表的所有的页和区。SQL Server对于IO做的请求是8K或者64K字节的请求，甚至可能是并行读取表。这使得表扫描并不像想象的那么吓人，因为扫描是页为单位而不是每行都要做一个IO请求。
　　以页和区作为单位不仅仅意味着减轻了表扫描的成本，还意味着，要从非聚集索引获益，查询请求的过滤条件要更具有选择性。加下下面的对于SalesOrderDetail表的请求，获取这个表4%左右的数据。
  

查询	SELECT *             FROM Sales.SalesOrderDetail             WHERE ProductID = 712
聚集索引	SalesOrderID / SalesOrderDetailID
每页平均行数	75
非聚集索引	ProductID
请求行所占比例	4%

  　　
　　因为平均每25行中只选择一行，并且where后的条件是根据ProductID来的，而且还存在以ProductID作为KEY的非聚集索引,索引使用非聚集索引来定位每行的信息看上去是个不错的主意，是这样吗？请在想一想。
　　由于表中以SalesOrderID/SalesOrderDetailID作为聚集索引，因为平均每页存在75行数据，而查询从每25行中取一行，也就是平均每页只能取三行。换句话说，几乎表中的每一页都要读到内存中才能满足这个查询，因此还不如直接进行聚集索引扫描来的快，因为扫描以区为单位，因此每次IO请求可以将24行数据载入内存（3行每页*8页每区）。
　　SQL Server新手通常会问”非聚集索引在什么样的选择率下才会被使用”,在本篇文章中你可以知道，比平均每页只能获取查询请求的一行要多就行。本系列文章接下来的章节中将会包含更多细节。
　　
  总结
  　　SQL Server读取的单位是页而不是行。页是IO最小的单位，也就是8K，8个连续的页被称为区。通常情况下，页和区都只能属于一个对象,因为IO读取的特性，一个查询必须要有很高的选择率才能够从非聚集索引获益。
　　在第五篇文章中，我们来看如何提高使用非聚集索引来减少查询成本的概率。一种解决方法是非聚集索引完全覆盖所请求的查询。换句话说，下一讲我们讲解使用Include列。