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

ykwll

SQL Server在堆表中查询数据时，是不知道到底有多少数据行符合你所指定的查找条件，它将根据指定的查询条件把数据表的全部数据都查找一遍。如果有可采用的索引，SQL Server只需要在索引层级查找每个索引分页的数据，再抓出所需要的少量数据分页即可。访问数据表内数以万计的数据分页与只访问少数索引的分页两者间的差异，让索引变成效能调校的最佳工具。
堆表的结果示意图：
　　
　　
堆表内的数据页和行没有任何特定的顺序，也不链接在一起。数据页之间唯一的逻辑连接是记录在 IAM 页内的信息。

假设订单明细表中有100万条数据，需要查询某个订单的明细数据，如下：
select  * from T_EPZ_INOUT_ENTRY_DETAIL where entry_apply_id='31227000034000090169'

如果在堆表中进行查询，SQL Server通过扫描 IAM 页对堆表进行全表扫描，对entry_apply_id比较100万次，如果以entry_apply_id字段建立索引，则因为索引键值数据都必定以B-Tree有顺序的摆放，所以可采用二分查找找数据。也就是2的N次方大于记录数，就可以找到该条数据。而2的20次方大于100万，因此最多找寻20次就可以找到该条记录。20次与100万次的比较，你可以轻松感受出性能的差异。

下面我们举个实例来做说明：

一、表空间的高度碎片化
1.此表的碎分布信息，从下图中可以看出此表的有非常多的内部碎片与外部碎片。
a) 此表的平均页密度只有24%，也就是说平均一页只有1/4空间才有数据，其他的3/4空间都是空着，有着很多的内部碎片。
b) 此表的扫描密度只有13%，也就是说理论上的区的数量与实现上区的数量之比为1：7.5，也就是说存在非常多的外部碎片，也就是说每个区的利用率相当低，一个区的数据全部加起来，才一个数据页。
如下图。


　　
对字段的说明：(例二、例三中的图中字段说明是一样的。)
Pages：如果在DBCC SHOWCONTIG 语句中指定了index_id，则将遍历指定索引的叶级上的页链，索引为叶子层使用的分页数目。如果只指定 table_id，或者 index_id 为 0，则将扫描指定表的数据页。
AvgeragePageDensity：平均页密度（为百分比）。该值考虑行大小，所以它是页的填满程度的更准确表示。百分比越大越好。
ScanDensity：扫描密度（为百分比）。这是“BestCount”与“ActualCount”的比率。如果所有内容都是连续的，则该值为 100；如果该值小于 100，则存在一些碎片。

　　

2. SQL查询语句与查询执行计划成本




--查询语句：
SET STATISTICS IO on
go
SET STATISTICS TIME on
go
select  * from T_EPZ_INOUT_ENTRY_DETAIL where entry_apply_id='31227000034000090169'
go
SET STATISTICS IO off
go
SET STATISTICS TIME off
go


　　

　　

3.查询所需要的时间与I/O
SQL Server 执行时间:
   CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。
SQL Server 分析和编译时间:
   CPU 时间 = 16 毫秒，耗费时间 = 76 毫秒。
SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。
（所影响的行数为 13 行）
表 'T_EPZ_INOUT_ENTRY_DETAIL'。扫描计数 1，逻辑读 4825 次，物理读 6 次，预读 19672 次。
SQL Server 执行时间:
   CPU 时间 = 47 毫秒，耗费时间 = 10544 毫秒。
SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。
SQL Server 执行时间:
   CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。
SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

二、表低度碎片化
1. 此表的碎分布信息，从下图中可以看出此表的有非常多的内部碎片与外部碎片。
a) 此表的平均页密度只有97%，也就是说数据差不多把一个数据页都塞满了，没有多余的空间，没有内部碎片。
b) 此表的扫描密度只有98%，也就是说理论上的区的数量与实现上区的数量之比为1：1，也就是说基本上没有外部碎片，也就是说每个区的利用率相当高。
如下图。

　　


备注：对于上图中的一些字段说明，见（一）。




2.SQL查询语句与查询执行计划成本




--查询语句：
SET STATISTICS IO on
go
SET STATISTICS TIME on
go
select  * from T_EPZ_INOUT_ENTRY_DETAIL where entry_apply_id='31227000034000090169'
go
SET STATISTICS IO off
go
SET STATISTICS TIME off
go

　　
3.查询所需要的时间与I/O

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。
SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒，耗费时间 = 92 毫秒。
SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。
（所影响的行数为 13 行）
表 'T_EPZ_INOUT_ENTRY_DETAIL'。扫描计数 1，逻辑读 1205 次，物理读 0 次，预读 1209 次。
SQL Server 执行时间:
   CPU 时间 = 16 毫秒，耗费时间 = 390 毫秒。
SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。
SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。

说明：逻辑读取的数值十分接近数据库中数据页数字，预读的次数也十分接近数据页的数字，物理读取值为0，即所需要查询的数据全部在预读的数据中间。

三、表添加主键，我们看一下有索引的查询

1. 此表的碎分布信息，从下图中可以看出此表的有非常多的内部碎片与外部碎片。

a) 此表的平均页密度只有97%，也就是说数据差不多把一个数据页都塞满了，没有多余的空间，没有内部碎片。

b) 此表的扫描密度只有98%，也就是说理论上的区的数量与实现上区的数量之比为1：1，也就是说基本上没有外部碎片，也就是说每个区的利用率相当高。
如下图：


　　
备注：对于上图中的一些字段说明，见（一）。

2.SQL查询语句与查询执行计划成本





--查询语句：
SET STATISTICS IO on
go
SET STATISTICS TIME on
go
select  * from T_EPZ_INOUT_ENTRY_DETAIL where entry_apply_id='31227000034000090169'
go
SET STATISTICS IO off
go
SET STATISTICS TIME off
go

　　
　　
3.查询所需要的时间与I/O

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。
SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒，耗费时间 = 98 毫秒。
SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。
（所影响的行数为 13 行）
表 'T_EPZ_INOUT_ENTRY_DETAIL'。扫描计数 1，逻辑读 3 次，物理读 2 次，预读 0 次。
SQL Server 执行时间:
   CPU 时间 = 0 毫秒，耗费时间 = 30 毫秒。
SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。
SQL Server 执行时间:
   CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。
SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。


比较以上三者的各种关键值，就可以看出性能的提升程度。

	物理操作	逻辑操作	I/O成本	CPU成本	成本	子树成本	逻辑读	物理读	预读
例一：堆表高度碎片化	Table Scan 逻辑运算符和物理运算符检索 Argument 列内指定表中的所有行	同左	3.61	0.0342	3.645123	3.65	4825	6	19672
例二：堆表低度碎片化	同上	同左	0.464	0.0171	0.963642	0.963	1205	0	1209
例三：表（带主键）	Clustered Index Seek 逻辑运算符和物理运算符利用索引的查找能力从聚集索引中检索行	同左	0.0032	0.000086	0.003289	0.00328	3	2	0
例一/例二/例三			1128/145/1	397/198/1	1108/292/1	1112/293/1	1608/401/1	3/0/1	16/1/0

对表中列的说明：
物理操作：使用的物理运算符，例如 Hash Join 或 Nested Loops。
逻辑操作：与物理运算符匹配的逻辑运算符，如 Join 运算符。
I/O 成本：用于操作的所有 I/O 活动的预计成本。该值应尽可能低。
CPU 成本：用于操作的所有 CPU 活动的预计成本。
成本：查询优化器执行此操作的成本，包括此操作的成本占查询总成本的百分比。由于查询引擎选择最高效的操作执行查询或执行语句，因此该值应尽可能低。
子树成本：查询优化器执行此操作及同一子树内位于此操作之前的所有操作的总成本。

在本文的三个例子中，预读值最高的为19672，最低的为0，物理读的值最高为6，最低为0，而逻辑读的值最高为4825，最低为3。
那么我在服务器上 执行查询时的过程是怎么样的呢？以例一为例。
首先，SQL Server会开始检查完成查询所需要的数据是否在数据缓冲区中，它会很快地发现这些数据不在数据缓冲区中， 并启动预读机制将它所需要的数据页读取到数据缓冲区中，但是由于数据页碎片严重情况，需要多次切区，大大提升了I/O的消耗，如例一中读取19672次，所以当碎片非常严重时，I/O读取非常频繁，多读取了4倍的数据页。
其次，如例一，当SQL  Server检查是否所需要的全部数据都已经在数据缓冲区时，会发现已经有4819个数据页在数据缓冲区中，还有六个数据页不在，它就会立即再次读取磁盘，所以有了6次的物理读，在将所需要的页读到数据缓冲区。一旦所有的数据都在数据缓冲区后，SQL Server就可以处理查询了。