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

标签: sql server 性能优化 | 发表时间:2012-08-29 20: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 查询性能优化——堆表、碎片与索引(一)

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

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

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

SQL Server--索引

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

SQL Server 面试

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

文章: SQL Server大负载的生产环境下的性能优化:初识元数据优化

- - InfoQ cn
相信朋友对SQL Server性能调优相关的知识或多或少都有一些了解. 虽然说现在NOSQL相关的技术非常的火热,但是RMDB(关系型数据库)与NOSQL是并存的,并且适用在各种的项目中. 在一般的企业级开发中,主要还是RMDB占据主导地位. 并且在互联网项目中,也不是摒弃了RMDB,例如MySQL就在很多的互联网应用中发挥着作用.

Oracle SQL性能优化

- - 数据库 - ITeye博客
(1)      选择最有效率的表名顺序(只在基于规则的优化器中有效):. ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表. 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

SQL之性能优化

- - CSDN博客数据库推荐文章
在实际应用中,数据库中的数据会有很多,若要从这些数据表中检索数据,就需要对系统进行优化,提高数据库系统的响应速度,下面就是日常一些查询优化的方法. 索引可以提高数据库查询的速度,提高数据库的访问性能,但同时也会影响数据更新操作(例如插入、修改、删除)的速度. 如果WHERE子句中经常用到的某一列或者某几列创建索引.

Sql性能优化梳理

- - IT瘾-geek
本文主要针对的是关系型数据数据库MySql. 键值类数据库可以参考最简大数据Redis. 先简单梳理下Mysql的基本概念,然后分创建时和查询时这两个阶段的优化展开. 第一层:客户端通过连接服务,将要执行的sql指令传输过来. 第二层:服务器解析并优化sql,生成最终的执行计划并执行. 第三层:存储引擎,负责数据的储存和提取.

SQL Server优化50法

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