MSSQL性能优化T-SQL篇

MSSQL性能优化T-SQL篇引用出处1、赵睿编写的T-SQL性能优化2、老大的培训和原则性指导以及和同事的交流沟通、自身经验等等3、优化原则4、语句优化技术分析5、优化50法6、即席查询问题的提出对于查询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））前缀。下面讨论一下为什么使用前缀可以改进查询性能。例子：即席查询测试SELECTProID,ProName,SupplierID,SupplierFROMERP_Products当用户Lucy执行以上语句时，查询优化器必须决定是检索Lucy.ERP_Products还是检索dbo.ERP_Products。然后，当用户Lily调用同一个TSQL语句或存储过程时，查询优化器必须对查询计划进行重新编译，以决定用户是需要Lily.ERP_Products还是需要dbo.ERP_Products。但是如果把上面的SELECT语句修改如下：SELECTProID,ProName,SupplierID,SupplierFROMdbo.ERP_Products查询优化器将不会遇到任何模糊性，从而避免重新编译，达到提升性能的目的。3、Union&UnionAll首先讲一下UNION的工作原理，当使用UNION语句时，它的功能与在结果集上SELECTDISTINCT类似，也就是说使用UNION时它会首先合并两个结果集，然后执行一个类似于SELECTDISTINCT的操作，以避免重复行的出现。这个过程在两上结果集没有任何重复行的情况下也会进行DISTINCT处理，所以如果我们确认在UNION的两个结果集确实存在重复行，并且要消除重复行的出现时，就可以使用UNION.从另一方面来说，如果我们知道在结果集中并不会存在重复行，或者说出现重复行对我们的应用程序没有什么影响时，我们应该使用UNIONALL语句来替代UNION语句，UNIONALL和UNION相比的优点在于它并不会对两个结果集进行SELECTDISTINCT操作，这样可以节省SQLServer的资源使用。例子：简单测试--UNIONALLSELECT1ASNumber,2ProIDUNIONALLSELECT1ASNumber,2ProID--UNIONSELECT1ASNumber,2ProIDUNIONSELECT1ASNumber,2ProID查询结果如下：下面来看性能方面的对比，分析下面的执行计划，我们可以看到第二个操作非常消耗资源，如果两个查询一块执行时，第二个查询几乎使用了所有的数据库资源（主要消耗在Aggregate中的消除重复行部分）：结论：在对UNION和UNIONALL进行选择时，除非必要，推荐使用UNIONALL4、Union&Jion在比较UNION和UNIONALL的基础上，下面来看UNION合并结果集和使用JION查询得到结果集之间的差别。例子：使用UNION来合并多个结果集的情况：USEupay;GOSELECT*FROMdbo.ERP_ProductSKUsWHEREUnitsInStock1000UNIONALLSELECT*FROMdbo.ERP_ProductSKUsWHEREUnitsInStock2000上面的查询可以被重写为下面的查询，这样一般就会有性能提升：USEupay;GOSELECTDISTINCT*FROMdbo.ERP_ProductSKUsWHEREUnitsInStock1000ORUnitsInStock2000NOTE：如果知道结果集中并不存在重复的数据行，我们也可以使用UNIONALL来提高性能。但是UnionAll性能一般来说比Union要好，但是比Join要差。查看它们的运行结果，可以发现运行结果是相同的，现在来分析一下它们的执行计划和性能差别：可见使用UNION时执行两次CLUSTERED索引的扫描，并且要把结果使用DISTINCT合并起来，而第二个查询只要进行一次CLUSTERED索引进行扫描，然后直接展现出来，从而大大提高了性能。但是如果能够使用JOIN达到目的的话，我们建议不要使用Union和UnionALL而是直接使用JOIN来取我们需要的数据。5、Distinct有开发人员在写查询的时候不管是否需要都习惯性的在查询中写上DISTINCT,这是一个很不好的习惯，特别是当我们的查询中包含很大数据量的时候更会大大消耗有限的数据库资源并降低的应用程序性能。DISTINCT应该在确认结果集中存在重复行，并且这些重复的确数据并不是我们所需要的数据时才能够使用。这是因为DISTINCT在数据库上执行了很多额外的操作从而消耗了很多数据库资源，这样会减少别的查询在执行时所能够使用的资源，增加数据库出现性能问题的几率。例子：USEupay;GOSELECTDISTINCTPerNameFROMdbo.Person20ASTINNERJOINdbo.ERP_POrdersAST2ONT.PerID=T2.SellerID这是一个非常简单的SELECTDISTINCT查询语句的示例，但是当我们执行一个复杂的语句时就应该考虑重新编码以达到性能的要求，如下面的语句，当返回ERP_POrders表中已经下了订单的用户，很多人会像下面这样写其实，我们可以重写这个语句来提升我们查询语句的性能：USEupay;GOSELECTPerNameFROMdbo.Person20ASTWHEREEXISTS(SELECT1FROMdbo.ERP_POrdersWHERESellerID=T.PerID)上面的查询能够提升性能是因为如果某个用户下了多个订单，当查到这个用户的第个订单时，就会停止对这个用户的处理。我们要慎重考虑是不是真的需要DISTINCT6、TOPN如果我们的应用程序要返回上千行乃至上万行数据的时候,我们要考虑是不是真正的需要这么多数据，是不是可以使用TOP操作符来限制返回给客户端的行数或者返回给客户端结果集行的百分比，这样可以减少资源的使用，提高数据库性能并且有效节省带宽下面说一下关于减少网络传输压力的内容。如果我们每次多返回给客户端10行数据，每行多返回200个字节，每天10000次被执行，这就是一笔不小的网络传输成本(10*200*10000Byte)，另外，如果再加上传输图片等，将要占用更多的带宽。例子：USEupay;GOSELECTTOP1PerID,PerNameFROMdbo.Person20WHEREPerNameLIKE'赵%'这时如果有100,000行数据符合WHERE条件，也只返回限制的1行结果集。因为SQLServer在处理时，当结果集的行数达到TOP中指定的数目时，所有的处理都将停止，这样就可以潜在的提高SQLServer的负载，增加性能。此外，TOP操作还可以让我们指定返回给客户端结果集行数的百分比，如:USEupay;GOSELECTTOP1PERCENTPerID,PerNameFROMdbo.Person20WHEREPerNameLIKE'赵%'NOTE：如果一个SELECT语句既包含TOP又包含ORDERBY子句，那么返回的行将会从排序好的结果集中选择，并且只返回已排好序结果集的前n行（或者前百分之N行）。7、InAndExist当我们能够在我们的查询中选择使用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操作符此操作是强列不推荐使用的，因为它不能应用表的索引。我们使用NOTEXISTS代替NOTIN8、InandBetween在查询语句中选择使用IN和BETWEEN时，建议使用BETWEEN,因为它在很多场合下更高效。例子：假设dbo.ERP_ProductSKUs表，在ProID列上有一个非聚集索引，进行如下查询：SELECT*FROMdbo.ERP_ProductSKUsWHERE[ProID]IN(600,601,602,603)SELECT*FROMdbo.ERP_ProductSKUsWHERE[ProID]BETWEEN600AND603查询优化器可以使我们下面使用BETWEEN的语句比使用IN的语句性能更高效:9、Like当在WHERE中使用Like时，尽可能的在Like语句中使用一个或者多个前导字符，比如：使用LIKE'm%'而不是:LIKE'%m'如果在Like中使用一个前导字符，此时查询优化器就会自动使用相应索引（如果有合适索引存在的话）来处理这个查询，但是如果我们Like语句中的前导字符使用通配符，查询优化器不会使用索引，而会对表进行扫描，从而大大降低性能。这点在SQLServer2005上有所提升，在SQLServer2005中，查询优化器可以为类似于LIKE'%m'语句使用索引。NOTE:使用的前导字符越多，查询优化器就越有可能找到合适的索引，从而加快查询。另外，如果应用程序的查询中有很多对CHAR和VARCHAR列进行Like操作时，我们可能要考虑使用SQLServer的全文索引，全文索引对于处理这方面查询会大大提升性能。10、OR如果一个查询语句中包括多个OR子句，为提升性能，一般情况下可以重写为一系列查询并使用UNIONALL来合并结果集。例子：SELECT*FROMdbo.ERP_POrdersWHERERecvRegion='辽宁省'ORR