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

标签: sql server 性能 | 发表时间:2012-08-29 12:43 | 作者:DotNet菜园
出处:http://www.cnblogs.com/

      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 ongoSET STATISTICS TIME ongoselect  * from T_EPZ_INOUT_ENTRY_DETAIL where entry_apply_id='31227000034000090169'goSET STATISTICS IO offgoSET STATISTICS TIME offgo 

 

 

 

 

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 ongoSET STATISTICS TIME ongoselect  * from T_EPZ_INOUT_ENTRY_DETAIL where entry_apply_id='31227000034000090169'goSET STATISTICS IO offgoSET STATISTICS TIME offgo

 

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 ongoSET STATISTICS TIME ongoselect  * from T_EPZ_INOUT_ENTRY_DETAIL where entry_apply_id='31227000034000090169'goSET STATISTICS IO offgoSET STATISTICS TIME offgo

 

 

 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就可以处理查询了。

 

 

本文链接

相关 [sql server 性能] 推荐:

SQL Server--索引

- - CSDN博客推荐文章
         1,概念:  数据库索引是对数据表中一个或多个列的值进行排序的结构,就像一本书的目录一样,索引提供了在行中快速查询特定行的能力..             2.1优点:  1,大大加快搜索数据的速度,这是引入索引的主要原因..                             2,创建唯一性索引,保证数据库表中每一行数据的唯一性..

SQL Server 面试

- - SQL - 编程语言 - ITeye博客
在SQL语言中,一个SELECT…FROM…WHERE语句称为一个查询块,将一个查询块嵌套在另一个查询块的WHERE子句中的查询称为子查询. 子查询分为嵌套子查询和相关子查询两种. 嵌套子查询的求解方法是由里向外处理,即每个子查询在其上一级查询处理之前求解,子查询的结果作为其父查询的查询条件. 子查询只执行一次,且可以单独执行;.

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

- - 博客园_首页
      SQL Server在堆表中查询数据时,是不知道到底有多少数据行符合你所指定的查找条件,它将根据指定的查询条件把数据表的全部数据都查找一遍. 如果有可采用的索引,SQL Server只需要在索引层级查找每个索引分页的数据,再抓出所需要的少量数据分页即可. 访问数据表内数以万计的数据分页与只访问少数索引的分页两者间的差异,让索引变成效能调校的最佳工具.

SQL Server 查询性能优化——创建索引原则(一)

- - 博客园_首页
索引是提高查询性能的一个重要工具,索引就是把查询语句所需要的少量数据添加到索引分页中,这样访问数据时只要访问少数索引的分页就可以. 但是索引对于提高查询性能也不是万能的,也不是建立越多的索引就越好. 索引建少了,用WHERE子句找数据效率低,不利于查找数据. 索引建多了,不利于新增、修改和删除等操作,因为做这些操作时,SQL SERVER除了要更新数据表本身,还要连带地立即更新所有的相关索引,而且过多的索引也会浪费硬盘空间.

SQL Server 高性能写入的一些总结

- - 博客园_知识库
  在开发过程中,我们不时会遇到系统性能瓶颈问题,而引起这一问题原因可以很多,有可能是代码不够高效、有可能是硬件或网络问题,也有可能是数据库设计的问题.   本篇博文将针对一些常用的数据库性能调休方法进行介绍,而且,为了编写高效的SQL代码,我们需要掌握一些基本代码优化的技巧,所以,我们将从一些基本优化技巧进行介绍.

如何找出你性能最差的SQL Server查询

- - 程序师
我经常会被反复问到这样的问题:”我有一个性能很差的SQL Server. 因此在今天的文章里会给你一些让你很容易找到问题答案的信息向导. SQL Server的一个优点是它本身能回答几乎所有你的问题,因为SQL Server在各个DMV和DMF里存储了很多故障排除信息. 另一方面这也是个缺点,因为你必须知道各个DMV/DMF,还有如何把它们解释和关联在一起.

SQL Server优化50法

- - CSDN博客推荐文章
虽然查询速度慢的原因很多,但是如果通过一定的优化,也可以使查询问题得到一定程度的解决.   查询速度慢的原因很多,常见如下几种:没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷).   I/O吞吐量小,形成了瓶颈效应.   没有创建计算列导致查询不优化.   内存不足网络速度慢查询出的数据量过大(可以采用多次查询,其他的方法降低数据量).

SQL Server 中的事务

- - CSDN博客推荐文章
       事务要有非常明确的开始和结束点,SQL Server 中的每一条数据操作语句,例如SELECT、INSERT、UPDATE和DELETE都是隐式事务的一部分. 即使只有一条语句,系统也会把这条语句当做一个事务,要么执行所有的语句,要么什么都不执行.         事务开始之后,事务所有的操作都会写到事务日志中,写到日志中的事务,一般有两种:一是针对数据的操作,例如插入、修改和删除,这些操作的对象是大量的数据;另一种是针对任务的操作,例如创建索引.

SQL Server优化50法

- - CSDN博客数据库推荐文章
  虽然查询速度慢的原因很多,但是如果通过一定的优化,也可以使查询问题得到一定程度的解决.   查询速度慢的原因很多,常见如下几种:. 没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷). I/O吞吐量小,形成了瓶颈效应. 查询出的数据量过大(可以采用多次查询,其他的方法降低数据量).

SQL Server 查询步骤 - pursuer.chen

- - 博客园_首页
标签:SQL SERVER/MSSQL SERVER/数据库/DBA/查询步骤.       查询步骤是很基础也挺重要的一部分,但是我还是在周围发现有些人虽然会语法,但是对于其中的步骤不是很清楚,这里就来分解一下其中的步骤,在技术内幕系列里面都会有讲到.  TOP于ORDER BY的关系. INSERT INTO Customers VALUES(1,'深圳'),(2,'广州'),(3,'武汉'),(4,'上海'),(5,'北京').