SQLServer查询性能优化.docx

SQLServer查询性能优化.docx

《SQLServer查询性能优化.docx》由会员分享，可在线阅读，更多相关《SQLServer查询性能优化.docx（26页珍藏版）》请在冰豆网上搜索。

SQLServer查询性能优化

SQLServer查询性能优化

查询性能调整已经是数据库应用程序的重要组成部分.使用正确的查询性能调整方法,常常可以大大节省时间和金钱,提高用户体验.

一、SQL查询性能调整

1.性能调优的整程

性能调整是一个迭代过程:

识别主要的瓶颈,试图解决它们,测试修改后的效果,并返回到开始步骤,知道性能可以接受.在解决瓶颈的过程中,应该遵循每次只做一次改动的原则.因为修改会影响到系统的其它部分,所以必须重新评估每次修改对系统总体性能的影响.

图:

性能调整过程

2.影响SQLServer性能方面

在已经优化了硬件,操作系统和SQLServer设置的情况下,影响SQLServer中的性能的方面按照粗略的顺序（最糟糕的首先出现）排序如下:

（1）低质量的索引

（2）不精确的统计

（3）过多的阻塞和死锁

（4）不基于数据集的操作

（5）低质量的查询设计

（6）低质量的数据库设计

（7）过多的碎片

（8）不可重用的执行计划

（9）低质量的执行计划

（10）频繁重编译计划

（11）游标的错误使用

（12）错误配置数据库日志

（13）过多使用或错误配置tempdb

二、SQL查询性能分析

1.SQLProfiler工具

SQLProfilter是一个可用于以下目标的GUI工具:

Ø图形化监视SQLServer查询

Ø在后台收集查询信息

Ø分析性能

Ø诊断像死锁这样的问题

Ø调试Transact-SQL语句

Ø模拟重放SQLServer活动

打开步骤:

开始菜单->SQLServer2008R2->性能工具,选择SQLServerProfiler.如下图:

打开后,创建一个跟踪,如下图

运行后,如下图:

2.识别开销最大的查询

识别开销最大的查询时重点关注以下几个点:

CPU,存和I/O.

CPU表示用于执行查询的CPU时间.

Reads表示一个查询操作的逻辑页面（大小为8K）的数量.从而指出查询产生的存压力.同时一定程度上指出了磁盘的压力.因为存页面必须在查询操作的过程中北备份,在第一次数据访问的时候被写入,并且在存瓶颈时被写到磁盘上.查询的逻辑读数量越大,造成磁盘压力的可能性就越大.而且,过多的逻辑页面也增加了CPU用于管理这些页面的负载.

I/O表示对磁盘的读写.对于一个成本效益较高的执行计划来说,最小化I/O的数量不是一个必要的条件,但是经常可以发现,开销最低的计划有很少的I/O.

3.识别运行缓慢的查询

为了发现执行缓慢的查询,在Duration列上分组跟踪输出.

4.查询执行计划

执行计划是显示查询优化器执行查询所所以用的处理策略（包含多个中间步骤）.

如何查看一个SQL语句的执行计划.在SQLServer查询工具的工具栏中点击”显示估计的执行计划”,或者使用快捷键”Ctrl+L”.

可以在”查询选项”中勾选统计分析选项,帮助分析执行计划.

查询执行计划的阅读顺序是从右到左,从上到下.每个步骤代表获得查询最终输出所执行的一个操作.由执行计划表示的查询执行的一些特征如下:

●如果查询由多个查询的批组成,每个查询的执行计划按照执行的顺序显示.批中的每个执行计划将有一个相对的估算开销,整个批的总开销为100%.

比如:

Select*fromac_user;select*fromMaterialCalculation;

●执行计划中的每个图标代表一个操作符.它们每个都有一个相对的估算开销,执行计划中的所有节点的总开销是100%

●执行计划中的一个起始操作符通常表示一个数据库对象（表或索引）的数据检索机制.

●数据检索通常是一个表操作或一个索引操作.

●索引上的数据检索的命名惯例是[表名].[索引名]

●数据从右到左在两个操作符之间流动,有一个连接箭头表示.连接箭头的宽度是传输行数的图形表示.

●同一列的两个操作符之间的连接机制将是嵌套的循环连接,hash匹配连接或是合并连接.

●将光标放置在执行计划中的一个节点之上,显示一个具有一些细节的弹出窗口.

●在属性窗口中有完整的一组关于操作符的细节,可以右键单击操作符并选择属性来显示.

●操作符细节在顶部显示物理和逻辑操作的类型.物理操作代表存储引擎实际使用的,而逻辑操作室优化器用于建立估算执行计划的结构.

●操作符细节弹出窗口的参数部分在分析中特别有用,因为她显示了优化器所使用的过滤或连接条件.

5.识别执行计划中开销最大的步骤

可以通过以下的方式选择开始步骤:

Ø查看执行计划中开销最大的节点.

Ø如果执行计划来自于一个批语句,可能查找开销最大的语句.

Ø查看节点间连接箭头的宽度.非常宽的连接箭头表示对应节点之间传输大量的行.分析箭头左边节点并理解为什么需要这么多行.还要检查箭头的属性,可能看到的估计的行和实际的行不一样,这可能是由过时的统计造成的.

SELECTmc.StyleDocTreeID,mcd.MaterialCalculationID

FROMMaterialCalculationDetailmcd

LEFTJOINMaterialCalculationmcONmc.ID=mcd.MaterialCalculationID

Ø寻找hash连接操作.对于小的结果集,嵌套的循环连接通常是首选.

Ø寻找书签操作操作.对于结果集的书签操作可能造成大量的逻辑读.

Ø寻找有感叹号表示警告的操作符,这是需要立刻注意的地方.通常解决警告的情况将对性能有所帮助.

Ø寻找执行排序操作的步骤.这表示数据没有以正确的排序进行检索.

6.数据的3中连接类型

Ø嵌套循环连接

Ø合并连接

ØHash连接

3者之间的性能比较（按性能从高到低）:

嵌套循环连接>合并连接>Hash连接

三、索引分析

1.聚集索引与非聚集索引

索引是由具有如下特征的一棵树所组成的:

唯一的,作为遍历起始点的根分页,可能存在的中间索引层次以及底层的叶子分页.所用索引可以找到正确的叶子分页.索引的中间层次是根据表的行数以及索引行的大小而变化的.如果使用一个较长的键来创建索引,一个分页上就只能容纳较少的条目,因而改索引就需要更多的分页（或者说更多层）.对于任何索引,无论是聚集索引还是非聚集索引,叶级别都是按照键的顺序由所有的键值组成的（对于复合索引,就是若干键值的组合）.并已B-树的结构存储.

B-树结构

聚集索引的叶级别不仅包含了索引键,还包含数据页.也就是说,每行的所有字段都在叶级别中.聚集索引中键值的顺序与表中数据的顺序一致.表中数据页是通过一个被称作”页链”的双向链表来维护的（堆中的页不是在一起的）.页链中叶顺序以及数据页上的记录的顺序就是索引键的顺序.由于实际数据页的页链只能按一种方式排序,因此一表只能有一个聚集索引.

单个分区中聚集索引的结构

非聚集索引页级别不包含任何全部的数据.除了键值以外,每个叶级别中的索引行包含了一个书签,告诉SQL可以在哪里找到与索引键相应的数据行.一个书签可能有两种格式.如果表上存在聚集索引,书签就是相应的数据行的聚集索引键.如果是堆表结构（就是没有聚集索引的情况下）,书签就是一个行标示符（rowidentifier,RID）,以”文件号:

叶号:

槽号”的格式来定位实际的行.由于非聚集索引的存在与否并不影响数据分页组织,因此每表上并不像聚集索引那样只能拥有一个非聚集索引.每表能够包含最多999（SQL2005为249）个非聚集索引,但实际用到的比这个数要少得多.

单个分区中非聚集索引的结构

聚集索引和非聚集索引之间的最大区别在于除了索引键以外叶级别还存放了什么.

聚集索引中查找行

非聚集索引中查找行

2.索引的好处和索引的开销

减少磁盘I/O和逻辑读次数的最佳方法之一就是使用索引.索引允许SQLServer在表里查询数据而不需要扫描整个表.

索引给性能带来的好处有一定的代价.有索引的表需要更多的存储和存空间以容纳表的数据页之外的索引页面.数据操作Insert,Update,Delete可能要花费更长的时间.

3.索引设计建议

Ø检查Where子句和连接条件列

当一个查询语句提交后,SQLServer将按一下步骤进行操作:

（1）优化器识别Where子句和连接条件中包含的列

（2）接着优化器检查这些列上的索引

（3）优化器通过从索引上维护的统计确定子句的选择性（也就是返回多少行）评估每个索引的有效性

（4）最终优化器根据前面几个步骤中收集的信息,估计读取所限定的行开销最低的方法.

Ø使用窄索引

可以在表中的一个列组合上创建索引.如果一个索引中包含的列在1~2之间,就叫窄索引,大于2列的称为宽索引.

Ø检查列的唯一性

在一个具有很小围的可能值的列（如性别等）上创建索引对性能没有好处,因为查询优化器将不能使用索引有效地减少返回的行.

Ø检查列的数据类型

为了最好的性能,尽量在索引中使用较少的列.还应该避免在索引中使用宽数据类型的列.比如:

Char,Varchar,NChar和NVarchar等.

Ø考虑列的顺序

索引键值在索引的第一列上排序,然后在前一列的每个之中一下一列做子排序.

Ø考虑索引类型（聚集索引或非聚集索引）

聚集索引和非聚集索引都是B-树结构,两者之间的主要区别是聚集索引中叶子页面时表的数据页面,因此数据和指针的顺序相同,这意味着聚集索引就是该表.而非聚集索引叶子页面存储的是执行聚集索引的地址或表记录行的地址.速度没有聚集索引快,但每表只能有一个聚集索引.

4.聚集索引的建议

（1）首先创建聚集索引

因为所有非聚集索引是在其索引行上保存聚集索引键,所以非聚集索引和聚集索引创建的顺序非常重要.如果非聚集索引先于聚集索引创建,那么非聚集索引行定位器将包含指向表中对应RID的指针.稍后创建聚集索引将修改所有非聚集索引,将聚集索引键作为新的行定位器值.这实际上重建了所有非聚集索引.

（2）保持窄索引

为了最佳性能,应使聚集索引的总体长度尽可能的小.整数数据类型的列通常是聚集索引的较好候选者,而字符串数据类型列决不是最佳的选择.

（3）一步重建聚集索引

因为非聚集索引对聚集索引的依赖性,用单独的DropIndex和CreateIndex语句重建聚集索引将导致所有的非聚集索引被建立两次.为了避免这种情况,使用用CreateIndex语句的Drop_Existing子句重建聚集索引.

（4）何时使用一个聚集索引

Ø检索一定围的数据

Ø读取预先排序的数据

（5）何时不使用聚集索引

Ø频繁更新的列

Ø宽的关键字

5.非聚集索引及书签查找

当一个查询请求不是优化器选择的非聚集索引一部分的列时,需要一个查找.这对于一个聚集索引来说是一个关键字查找,对于一个堆表来说是一个RID查找.这也叫书签查找.

（1）何时使用非聚集索引

Ø频繁更新的列

Ø宽关键字

（2）何时不使用非聚集索引

Ø检索大量行的查询（用聚集索引更好）

6.高级索引技术

（1）覆盖索引

覆盖索引是在所有为满足SQL查询不用到达基本表所需的列上建立的非聚集索引.这时我们可以创建一个符合索引来实现.也可以使用Include操作符来实现.

Include最好在一下情况中使用:

Ø你不希望增加索引键的大小,但是仍希望有一个覆盖索引

Ø你打算索引一种不能被索引的数据类型（文本,ntext和图像除外）

Ø你已经超过了一个索引的关键字列的最大数量（最好避免这种情况）

（2）索引交叉

如果一个表有多个索引,那么SQLServer可以使用多个索引来执行一个查询.根据每个索引选择的小的数据子集,然后执行两个子集的一个交叉（即:

只返回满足条件的那些行.）

（3）索引连接

索引连接就是将索引覆盖技术应用到索引交叉.如果没有单个覆盖查询的索引而多个索引一起可以覆盖该查询,SQLServer可以使用索引连接来完成满足查询而不需要转到基本表.

（4）过滤索引

过滤索引是使用过滤器的非聚集索引.这个过滤器基本上是一个Where子句.

四、查询设计分析

一般来说,以下的一些建议能确保最佳性能

（1）在小的结果集上操作

在Select语句的选择列表中使用最小的列集,不要使用输出结果集中不需要的列.例如:

不要使用Select*返回所有列.Select*语句是覆盖索引无效,因为在索引中包含所有列是不现实的.

（2）有效使用索引

Ø避免不可参数化的搜索条件

可参数化的搜索条件一般能是优化器使用Where子句中引用的列上的索引.而不可参数化的搜索条件一般会阻止使用Where子句中引用的列上的索引.

常见的可参数化和不可参数化的搜索条件

类型

搜索条件

可参数化

包含条件=,>,>=,<,<=和between,以及一些Like条件（like‘abc%’）

不可参数化

排除条件<>,!

=,!

>,!

<,NotExists,NotIn和NotLikeIn,Or,以及一些Like条件（like‘%abc’）

Ø避免在Where子句列上使用算术运算符

Where子句中的列上使用运算符可以阻止优化器使用该列上的索引.例如:

Select*fromTableWhereID*2=100

Ø避免在Where子句列上使用函数

Where子句中的列上使用函数也阻止优化器使用该列上的索引.例如Selecta.NamefromTableWhereSUBTRING（a.Name,1,1）=‘F’

Ø尽量使用InnerJoin,少用OuterJoin

比如有如下两表:

●InnerJoin产生的结果是AB的交集

SELECT*FROMTableA INNERJOIN TableBONTableA.name=TableB.name

●Left[Outer]Join产生表A的完全集，而B表中匹配的则有值，没有匹配的则以null值取代.

SELECT*FROMTableA LEFTOUTERJOIN TableBONTableA.name=TableB.name

●Right[Outer]Join产生表B的完全集，而A表中匹配的则有值，没有匹配的则以null值取代.

SELECT*FROMTableARIGHTOUTERJOIN TableBONTableA.name=TableB.name

图标如leftjoin类似。

SQL的查询性能与查询的数据量密切相关.使用OuterJoin意味着必须对左右两个表查询所有行,如果表很大而没有相应的Where语句,那么OuterJoin很容易导致tablescan或indexscan,而InnerJoin返回的是两个关联表的交集,正常情况下返回的数据量要比使用OuterJoin返回的数据量少,而且使用InnerJoin可以避免全表扫描.如果数据量大,还会导致外部输入表和部输入表之间以Hash连接方式进行连接,而Hash连接是3种连接方式（嵌套循环连接,合并连接,Hash连接）中性能最低的一个.

（3）避免资源密集型的查询

Ø避免数据类型转换

Ø使用Exists代替Count（*）来验证数据存在

Ø使用UnionAll代替Union

Ø为聚合和排序操作使用索引

五、常用提高查询速度的SQL语句写法

Ø应尽量避免在 where 子句中使用!

=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

Ø对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

Ø应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

     select id from t where num is null

 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

Ø应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

     select id from t where num=10 or num=20

     可以这样查询：

     select id from t where num=10

     union all

     select id from t where num=20

Ø下面的查询也将导致全表扫描：

（不能前置百分号）

 select id from t where name like ‘%abc%’

Øin 和 not in 也要慎用，否则会导致全表扫描，如：

     select id from t where num in（1,2,3）

     对于连续的数值，能用 between 就不要用 in 了：

     select id from t where num between 1 and 3

select xx,phone FROM send  a JOIN （select '' phone  union select '' …………  UNION  SELECT '' ） b  on  a.Phone=b.phone

替代下面  很多数据隔开的时候

in（'','',''…………）

Ø应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

如：

     select id from t where num/2=100

     应改为:

     select id from t where num=100*2

Ø应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

如：

     select id from t where substring（name,1,3）=’abc’  

应改为:

    select id from t where name like ‘abc%’

Ø不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无确使用索引。

Ø在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致。

Ø很多时候用 exists 代替 in 是一个好的选择：

 select num from a where num in（select num from b）

 用下面的语句替换：

 select num from a where exists（select 1 from b where num=a.num）

Ø并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

Ø索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。

一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

Ø应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。

若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

Ø尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

Ø尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段搜索效率显然要高些。

Ø任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

Ø避免频繁创建和删除临时表，以减少系统表资源的消耗。

Ø在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

Ø如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

Ø尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

Ø使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

Ø与临时表一样，游标并不是不可使用。

对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。

在结果集中包括“合计”的例程通常要比使用游标执行的速度快。

如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

Ø在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。

无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

Ø尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需否合理。

Ø尽量避免大事务操作，提高系统并发能力。

六、操作实例

1.和诚0832.材料送外加工合同待处理查询:

SELECTp.ProviderNo,p.Relation1asRelation,m.FactoryMaterial,mcs.codeasMSCode,p.TelephoneNoasPhone,p.FaxNoasFaxs,vs.WarehouseID,fw.codeasWarehouseCode,p.Buyer,p.ProviderCharacter,c.MaterialCalculationIDasID,vs.UIDasOrderDocUID,c.StyleDocTreeID,c.MaterialCalculationID,c.IndexCount,m.MaterialCategoryID,c.MaterialID,inf.CodeasFactoryCode,CONVERT（VARCHAR（10）,c.SubmitDate,120）asSubmitDate,c.ShipMentDate,vs.DocCodeasOrderCode,vs.CodeasStyleCode,vs.NameasStyle