语句MSSQL性能优化T-SQL篇

mssql 时间:2021-01-21 阅读:()

MSSQL性能优化T-SQL篇

引用出处

1 、赵睿编写的T-SQL性能优化

2、老大的培训和原则性指导以及和同事的交流沟通、自身经验等等

3、 http://www.cnblogs.com/ziyiFly/archive/2008/12/24/.html SQL优化原则

4、 http://www.cnblogs.com/wxj1020/archive/2008/04/27/.html SQL语句优化技术分析

5、 http://kb.cnblogs.com/page//SQL Server优化50法

6、 http://www.cnblogs.com/lyhabc/archive/2013/06/16/.html即席查询

问题的提出

对于查询SQL语句复杂视图的的编写等体会不出SQL语句各种写法的性能优劣但是如果将应用系统提交实际应用后随着数据库中数据的增加系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍可见对于一个系统不是简单地能实现其功能就可而是要写出高质量的SQL语句提高系统的可用性。

在多数情况下MSSQL使用索引来更快地遍历表优化器主要根据定义的索引来提高性能。但是如果在SQL语句的where子句中写的SQL代码不合理就会造成优化器删去索引而使用全表扫描一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引这有助于写出高性能的SQL语句。

T-SQL优化推荐

1、不要访问多于你需要的数据

这个听起来是多余的但是确实是非常必要的做起来也不大容易。其中包括不要返回给客户端不需要的列和行。比如在SELECT语句中不要使用SELECT* 否则会经常返回给客气端多于它们所需要的数据这样可以有效减轻网络传输压力减少不必要的I/O以及减少内存耗费并能够使查询优化器最优化我们的查询执行计划从而减少潜在的性能问题。

2、所有者.对象名ObjectOwnerName.ObjectName

建议在所有对表、视图和存储过程和函数引用时加上它们的所有者架构 schema 前缀。下面讨论一下为什么使用前缀可以改进查询性能。

例子即席查询测试

SELECT ProID,ProName,Suppl ierID,Suppl ier

FROM ERP_Products

当用户Lucy执行以上语句时查询优化器必须决定是检索Lucy ERP_Products还是检索dboERP_Products。然后当用户Li ly调用同一个TSQL语句或存储过程时查询优化器必须对查询计划进行重新编译以决定用户是需要Li ly ERP_Products还是需要dbo ERP_Products。但是如果把上面的SELECT语句修改如下

SELECT ProID,ProName,Suppl ierID,Suppl ier

FROM dbo ERP_Products

查询优化器将不会遇到任何模糊性从而避免重新编译达到提升性能的目的。

3、 Union&UnionAl l

首先讲一下UNION的工作原理当使用UNION语句时它的功能与在结果集上SELECTDISTINCT类似也就是说使用UNION时它会首先合并两个结果集然后执行一个类似于SELECT DISTINCT的操作以避免重复行的出现。这个过程在两上结果集没有任何重复行的情况下也会进行DISTINCT处理所以如果我们确认在UNION的两个结果集确实存在重复行并且要消除重复行的出现时就可以使用UNION从另一方面来说如果我们知道在结果集中并不会存在重复行或者说出现重复行对我们的应用程序没有什么影响时我们应该使用UNIONALL语句来替代UNION语句UNIONALL和UNION相比的优点在于它并不会对两个结果集进行SELECT DISTINCT操作这样可以节省SQLServer的资源使用。

例子简单测试

--UNIONALL

SELECT 1 AS Number,2 ProID

UNIONALL

SELECT 1 AS Number,2 ProID

--UNION

SELECT 1 AS Number,2 ProID

UNION

SELECT 1 AS Number,2 ProID

查询结果如下

下面来看性能方面的对比分析下面的执行计划我们可以看到第二个操作非常消耗资源如果两个查询一块执行时第二个查询几乎使用了所有的数据库资源主要消耗在Agg regate中的消除重复行部分 

结论在对UNION和UNIONALL进行选择时除非必要推荐使用UNIONALL

4、 Union&Jion

在比较UNION和UNIONALL的基础上下面来看UNION合并结果集和使用JION查询得到结果集之间的差别。

例子使用UNION来合并多个结果集的情况

USE upay;

SELECT * FROM dbo.ERP_ProductSKUs WHERE UnitsInStock < 1000

UNION ALL

SELECT * FROM dbo.ERP_ProductSKUs WHERE UnitsInStock > 2000

上面的查询可以被重写为下面的查询这样一般就会有性能提升

USE upay;

SELECT DISTINCT * FROM dbo.ERP_ProductSKUs

WHERE UnitsInStock < 1000 OR UnitsInStock > 2000

NOTE如果知道结果集中并不存在重复的数据行我们也可以使用UNIONALL来提高性能。但是UnionAl l性能一般来说比Union要好但是比Join要差。

查看它们的运行结果可以发现运行结果是相同的现在来分析一下它们的执行计划和性能差别

可见使用UNION时执行两次CLUSTERED索引的扫描并且要把结果使用DISTINCT合并起来而第二个查询只要进行一次CLUSTERED索引进行扫描然后直接展现出来从而大大提高了性能。

但是如果能够使用JOIN达到目的的话我们建议不要使用Union和UnionALL而是直接使用JOIN来取我们需要的数据。

5、 Distinct

有开发人员在写查询的时候不管是否需要都习惯性的在查询中写上DISTINCT,这是一个很不好的习惯特别是当我们的查询中包含很大数据量的时候更会大大消耗有限的数据库资源并降低的应用程序性能。

DISTINCT应该在确认结果集中存在重复行并且这些重复的确数据并不是我们所需要的数据时才能够使用。这是因为DISTINCT在数据库上执行了很多额外的操作从而消耗了很多数据库资源这样会减少别的查询在执行时所能够使用的资源增加数据库出现性能问题的几率。例子 

USE upay;

SELECT DISTINCT PerName

FROM dbo.Person20 AS T

INNER JOIN dbo.ERP_POrders AS T2 ON T.PerID = T2.SellerID

这是一个非常简单的SELECT DISTINCT查询语句的示例但是当我们执行一个复杂的语句时就应该考虑重新编码以达到性能的要求如下面的语句当返回ERP_POrders表中已经下了订单的用户很多人会像下面这样写

其实我们可以重写这个语句来提升我们查询语句的性能

USE upay;

SELECT PerName

FROM dbo.Person20 AS T

WHERE EXISTS(SELECT 1 FROM dbo.ERP_POrders WHERE SellerID = T.PerID )

上面的查询能够提升性能是因为如果某个用户下了多个订单当查到这个用户的第个订单时就会停止对这个用户的处理。

我们要慎重考虑是不是真的需要DISTINCT

6、 TOP N

如果我们的应用程序要返回上千行乃至上万行数据的时候,我们要考虑是不是真正的需要这么多数据是不是可以使用TOP操作符来限制返回给客户端的行数或者返回给客户端结果集行的百分比这样可以减少资源的使用提高数据库性能并且有效节省带宽

下面说一下关于减少网络传输压力的内容。如果我们每次多返回给客户端10行数据每行多返回200个字节每天10000次被执行这就是一笔不小的网络传输成本(10*200*10000 Byte) 另外如果再加上传输图片等将要占用更多的带宽。

例子

USE upay;

SELECT TOP 1 PerID, PerName FROM dbo.Person20

WHERE PerName LIKE '赵%'

这时如果有100,000行数据符合WHERE条件也只返回限制的1行结果集。因为SQLServer在处理时当结果集的行数达到TOP中指定的数目时所有的处理都将停止这样就可以潜在的提高SQLServer的负载增加性能。

此外TOP操作还可以让我们指定返回给客户端结果集行数的百分比如:

USE upay;

SELECT TOP 1 PERCENT PerID, PerName FROM dbo.Person20

WHERE PerName LIKE '赵%'

NOTE如果一个SELECT语句既包含TOP又包含ORDER BY子句那么返回的行将会从排序好的结果集中选择并且只返回已排好序结果集的前n行或者前百分之N行 。

7、 In And Exist

当我们能够在我们的查询中选择使用IN和Exist语句时推荐使用EXISTS因为EXISTS一般更加高效(EXIST只遇到附合条件的很第一个结果时此记录就退出处理)。

用IN写出来的SQL的优点是比较容易写及清晰易懂这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的从MSSQL执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别

MSSQL试图将其转换成多个表的连接如果转换不成功则先执行IN里面的子查询再查询外层的表记录如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功但对于含有分组统计等方面的SQL就不能转换了。

在业务密集的SQL当中尽量不采用IN操作符用EXISTS方案代替

7.1、 NOTIN操作符

此操作是强列不推荐使用的因为它不能应用表的索引。

我们使用NOT EXISTS代替NOT IN

8、 In and Between

在查询语句中选择使用IN和BETWEEN时建议使用BETWEEN,因为它在很多场合下更高效。例子假设dbo.ERP_ProductSKUs表在ProID列上有一个非聚集索引进行如下查询SELECT * FROM dbo.ERP_ProductSKUs WHERE [ProID] IN(600,601, 602,603)

SELECT * FROM dbo.ERP_ProductSKUs WHERE [ProID] BETWEEN 600 AND 603

查询优化器可以使我们下面使用BETWEEN的语句比使用IN的语句性能更高效:

9、 Li ke

当在WHERE中使用Like时尽可能的在Like语句中使用一个或者多个前导字符比如使用

L I KE 'm%'

而不是:

如果在Like中使用一个前导字符此时查询优化器就会自动使用相应索引如果有合适索引存在的话来处理这个查询但是如果我们Li ke语句中的前导字符使用通配符查询优化器不会

使用索引而会对表进行扫描从而大大降低性能。这点在SQL Server2005上有所提升在SQLServer2005中查询优化器可以为类似于LIKE'%m'语句使用索引。

NOTE:使用的前导字符越多查询优化器就越有可能找到合适的索引从而加快查询。

另外如果应用程序的查询中有很多对CHAR和VARCHAR列进行Like操作时我们可能要考虑使用SQLServer的全文索引全文索引对于处理这方面查询会大大提升性能。

10、 OR

如果一个查询语句中包括多个OR子句为提升性能一般情况下可以重写为一系列查询并使用UNIONALL来合并结果集。

例子

SELECT *

FROM dbo.ERP_POrders

WHERE RecvRegion = '辽宁省'

OR RecvCity = '保定市'

OR AreaFrom LIKE '内蒙古赤峰市阿鲁科尔沁旗'

这个语句在WHERE中有三个独立的条件为了使用到索引我们要在这三列上创建组合索引。像这样的语句可能被重写为多个查询并使用UNIONALL来合并结果集从而替代OR操作。如下

SELECT * FROM dbo.ER= '辽宁省'

UNION ALL

SELECT * FROM dbo.ER= '保定市'

UNION ALL

SELECT * FROM dbo.ER'内蒙古赤峰市阿鲁科尔沁旗'

_每一个查询都得出一样的结果集元数据相同 如果在这个表中只有Re cvRegi on列上存在索引此时第一个SELECT就会使用此索引而第二个第三个SELECT语句会进行表的扫描而不是全部进行表的扫描。

这只是一个非常简单的例子只是示范了如何重写查询来提高性能如果这个查询比较复杂那么使用UNIONALL就会大大提高性能因为这样我们可以对每个语句进行调整而如果是全在一个OR子句中这些我们是做不到的。

NOTE:注意在这里我们是使用UNIONALL而不是UNION原因是UNION不光会对结果集进行合并也会对结果集进行排序并移除所有的重复行所以性能会大大降低,当然对于这样的一个查询也比较特殊之所以这个比较能成功是来源元数据在数据库中的密度影响假设RecvRegion,RecvCity,AreaFrom字段的密度很低那么UNION ALL任然没有使用OR的性能高所以建议在使用时慎用UNION ALL。

10.1、 >及<操作符大于或小于操作符

大于或小于操作符一般情况下是不用调整的因为它有索引就会采用索引查找但有的情况下可以对它进行优化如一个表有100万记录一个数值型字段A 30万记录的A=0  30万记录的A=1  39万记录的A=2  1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了因为A>2时MSSQL会先找出为2的记录索引再进行比较而A>=3时MSSQL则直接找到=3的记录索引。

11、 Order by/sorting

ORDER BY要占用很多额外的资源所以除非在真正需要的情况下否则不要在SELECT语句中使用ORDERBY。我们可以考虑这些数据在客户端进行排序是否会更好或者客户端是否需要我们对结果集进行排序。

S O RT一般是伴随着以下T-S Q L语句出现

ORDERBY

GROUP BY

SELECTDISTINCT

UNION

CREATE INDEX 此时也需要对数据进行排序

一般情况下这些命令不可能避免但是另一方面我们可能想办法减少这些语句对资源的使用这包括

尽可能减少被排序的行数,就是只对必要排序的数据行集进行排序。

尽可能减少结果集中列的数目。

尽可能减少所有行的物理宽度

尽可能对I NT的列进行排序而不是Character或者Char列。

当使用S O RT操作时一定要考虑上面的建议并保持下面的原则“除非必要否则不要来对结果集进行排序”。

12、 Exist and count(*)

对数据库进行更新时很多时候要首先判断被更新的记录是否存在或者对删除对象之前判断对象是否存在此时不要使用SELECTCOUNT(*)来标识因为它的性能是非常低的。为了提升性能我们可以使用IF EXISTS来实现相关的操作。 IF EXISTS能提高性能是因为当有一条记录为真是处理就会立即退出但是COUNT(*)会检查表中的每一条记录的情况不管符合条件的数据有一条还是上万条。

例子

USE upay

--COUNT(*) :

IF (SELECT COUNT(*) FROM dbo.Person20) > 0

BEGIN

PRINT '111'

END

--下面是使用EXIST的语句也是性能较好的语句

IF EXISTS (SELECT * FROM DBO.AUTHORS )

BEGIN

PRINT '222'

END