SQL Server 查询性能优化——堆表、碎片与索引（二）

 本文是对 SQL Server 查询性能优化——堆表、碎片与索引（一）的一些总结。

 第一：先对 SQL Server 查询性能优化——堆表、碎片与索引（一）中的例一的SET STATISTICS IO之后出现的关键信息如下

表 'T_EPZ_INOUT_ENTRY_DETAIL'。扫描计数 1，逻辑读 4825 次，物理读 6 次，预读 19672 次。

这句解释一下。(有些内容来自网络，有些内部是自己的理解。)

 

SQL SERVER 数据库引擎当遇到一个查询语句时，SQL SERVER数据库引擎会分别生成执行计划(占用CPU和内存资源)，同时存储引擎读取 IAM 以生成必须要读取的磁盘地址排序列表。这使 SQL Server 得以将其 I/O 优化为大型有序读取，根据它们在磁盘上的位置按顺序完成。磁盘中取得需要取的数据(占用I/O资源，这就是预读)，注 意，两个步骤是并行的，SQL SERVER通过这种方式可以让计算和 I/O 重叠进行，从而充分利用 CPU 和磁盘，从而提高性能。

 

扫描计数：查询数据时对涉及到的表被 访问次数或涉及到的索引的扫描次数。在我们的例子中，不管是表扫描（例一与例二）还是索引扫描（例三）其中的表或索引都只被访问了1次，由于查询中不包括连接命令，这一信息并不是十分有用，但如果查询中包含有一个或多个连接，则这一信息是十分有用的。
　　一个循环外部的表的扫描计数值为1，但对于一个循环内的表而言，其值为循环的次数。可以想象得到，对于一个循环内的表而言，其 扫描计数值越小，它所使用的资源越少，查询的性能也就越高。因此在调节一个带连接的查询的性能时，需要关注扫描计数的值，在进行调节 时，注意观察它是增加还是减少了。

逻辑读： 这是SET STATISTICS IO或SET STATISTICS TIME命令提供的最有用的数据。我们知道，SQL Server在对任何数据进行操作前，必须首先从磁盘中读取数据所在的数据页或索引页，并把数据页或索引页存到数据缓冲区高速缓存中。
　　那么逻辑读的意义是什么呢？逻辑读是指SQL Server为得到查询中的结果而必须从数据缓冲区高速缓存读取的页数。在执行查询时，SQL Server不会读取比实际需求多或少的数据， 因此，当在相同的数据集上执行同一个查询，得到的逻辑读的数字总是相同的。
　　为什么说在调节查询性能中知道SQL Server执行查询时的逻辑读值是很重要的呢？因为在每次执行同一查询时，这个数值是不会变化的。因此，在进行查询性能的调节时，这是一个可以用来衡量你的调节措施是否成功的一个很好的标准。
　　在对查询的性能进行调节时，如果逻辑读值下降，就表明查询使用的服务器资源减少，查询的性能有所提高。如果逻辑读值增加，则表示调节措施降低了查询的性能。在其他条件不变的情况下，一个查询使用的逻辑读越少，其效率就越高，查询的速度就越快。

物理读：在这里我要说的的东西可能初听起来有点自相矛盾，但只要反复思考，就会明白其中的真正含意。
　　物理读指的是，在执行真正的查询操作前，SQL Server必须从磁盘上向数据缓冲区高速缓存中读取它所需要的数据。在SQL Server开始执行查询前，它要作的第一件事就是检查它所需要的数据是否在数据缓冲区高速缓存中，如果在，就从中读取，如果不在，SQL Server必须首先将它需要的数据从磁盘上读到数据缓冲区高速缓存中。
　　我们可以想象得到，SQL Server在执行物理读时比执行逻辑读需要更多的服务器资源。因此，在理想情况下，我们应当尽量避免物理读操作。
　　下面的这一部分听起来让人容易感到糊涂了。在对查询的性能进行调节时，可以忽略物理读而只专注于逻辑读。你一定会纳闷儿，刚才不是还说物理读比逻辑读需要更多的服务器资源吗？
　　情况确实是这样，SQL Server在执行查询时所需要的物理读次数不可能通过性能调节而减少的。减少物理读的次数是DBA的一项重要工作，但它涉及到整个服务器性能的调节，而不仅仅是查询性能的调节。在进行查询性能调节时，我们不能控制数据缓冲区高速缓存的大小或服务器的忙碌程度以及完成查询所需要的数据是在数据缓冲区中还是在磁盘上，唯一我们能够控制的数据是得到查询结果所需要执行的逻辑读的次数。
　　因此，在查询性能的调节中，我们可以心安理得地不理会SET STATISTICS IO命令提供的物理读的值。（减少物理读次数、加快SQL Server运行速度的一种方式是确保服务器的物理内存足够多。）

预读：与 物理读一样，这个值在查询性能调节中也没有什么用处。预读表示SQL Server在执行预读机制时从磁盘上读取的数据页或索引页。为了优化其性能，SQL Server数据引擎首先预测执行查询执行计划所需的数据和索引页，然后在查询实际使用这些页之前将它们读入缓冲区高速缓存。根据SQL Server对数据 需求预测的准确程度，预读的数据页可能有用，也可能没用。

备注：一个缓冲区就是一个 8KB 大小的内存页

 

以上文字大部分来源自网络，本人对其中部分不认同处，或有自己的理解处，做了少量修改。

 

　

 

第二：关于碎片对于性能影响的结论：

SQL Server 中数据存储的基本单位是页，一页包含8KB数据。磁盘 I/O 操作在页级执行。也就是说，SQL Server 读取或写入的基本单位是数据页。连续的8个页面组成一个区（extent）。数据的insert和update操作可以引起页面分割产生碎片。如果分割后的两个页面在同一个区内，这种碎片称为内部碎片，如果分割后的两个页面处于不同的区内，这种碎片称为外部碎片。

一、内部碎片和外部碎片对数据检索性能都有负面影响。

1. 内部碎片的产生使数据稀疏的分布在大量的页面中，这增加了读取页面到内存中所需的磁盘I/O操作，增加了从内存中检索数据的逻辑读取数量。

2. 外部碎片导致磁盘上的索引页面不连续，新的叶子页面和原始叶子页面离得很远，物理顺序与逻辑顺序不同。为了更好的性能，首选顺序I/O，因为这能在一个磁盘I/O读取整个区（8个8KB页面）。非连续的页面需要非顺序或者随机I/O操作来从磁盘读取数据，一个随机I/O只能读取一个页面（8KB）。

 

二、在堆表中，当删除数据链中间的记录行时，会出现空页,随着空页的累积，区的利用率也会下降，从而出现内部碎片与外部碎片。带索引的表也有可能出现外部碎片，如在现有的聚集索引中插入一行，这行正好导致现有的页空间无法满足容纳新的行，从而导致了分页，如果分页后的两个页面正好分布在两个区，就是外部碎片。当有外部碎片存在，会出现以下问题：对表进行处理时，常常出现死锁；利用较大的I／O操作或增加I/O缓冲区的大小也无法改变较慢的I／O速度；行操作的争用。

 

三、带有索引的表会由于插人记录而导致分页，但当删除记录后，页会获得释放．从而形成跨几个区的数据．而要访问该数据就必须遍历几个区，这将导致增加I/O操作，查询记录的时间大大延长，开始时数据库的性能虽然较高，但使用一段时间后就会发生性能下降等问题。实际上，数据在存储空间上排列得越紧密有序，SQL Server访问的速度就越抉，消除碎片有助于提高系统的性能和更有效地利用数据存储空间。(如例二，例二的访问速度就比例一要快）。

 

四、对于扫描部分或全部表的查询，这些表碎片会导致额外的页读取，这将防碍数据的并行扫描。