性能测试之T-SQL调优

作者：川石学院　日期：2021-06-11 14:22:59　点击数：

　　对T-SQL语句进行调校是DBA调优数据库性能测试的主要任务，因为不同的查询语句，即使查询出来的结果一致，其消耗的时间和系统资源也有所不同，所以如何使查询语句最优化是调优数据库的一个重要手段，本章节主要介绍常用的T-SQL调校方法。

　　一、 NOT IN和NOT EXISTS

　　在以前的做法中，如果子查询需要扫描子表中的所有行时，将NOT IN语句重写为NOT EXISTS语句，这是因为子查询会对聚簇索引执行全扫描，并且NOT IN不应用于索引，但是现在由于优化器的进步，无论使用哪种方法的代码，都可以较高的效率执行查询语句。

性能测试之T-SQL调优(图1)

　　下面是两种查询代码：

　　select * from people
　　where personid not in
　　(select id from boysnames where name = '123'
　　)
　　select * from people
　　where not EXISTS
　　(select id from boysnames where name = '123'
　　)

　　并行运行这两个查询，并显示估计的执行计划，如图11-50所示。　　

性能测试之T-SQL调优(图2)

　　图11-50 估计执行计划

　　从显示的执行计划中可以看到，两个查询的开销都为50%，这说明从效率上来说，两种查询方法所花费的时间是一致的，所以使用NOT EXISTS代替NOT IN，并不能切实的改进查询效率。

　　二、谓词的使用

　　谓词是指允许构造条件处理满足条件的表行，常见的谓常有IN、BETWEEN、LIKE和EXISTS等。在查询过程中对于谓词的使用应该更明确，并且原则上是谓词应该尽早的使用，这样可以较早的将行数减少，以便节约成本，如在杂货店，要找一袋食品，通常有两种方法，一种方法是，找到所有关于食品类的东西，再在这类食品类的东西里面找需要的食品;另外一种方法是直接在所有杂货中找所需要的食品。

　　下面是一个使用HAVING和WHERE进行查询的实例：

　　SET SHOWPLAN_ALL ON
　　select avg(soc.soc),soc.id
　　from soc
　　inner join test
　　on test.id = soc.ID
　　group by soc.id
　　having soc.id like '45%'

　　查询的详细如图11-51所示。　　

性能测试之T-SQL调优(图3)

　　图11-51 查询的详细信息

　　从显示的详细信息中可以看出在第2行信息中使用了筛选器，但在第8行信息中并没有使用筛选器，导致第8行是全表扫描。下面对该语句进行重写，代码如下：

　　SET SHOWPLAN_ALL ON
　　select avg(soc.soc),soc.id
　　from soc
　　inner join test
　　on test.id = soc.ID
　　where soc.id like '45%'
　　group by soc.id

　　查询的详细如图11-52所示。　　

性能测试之T-SQL调优(图4)

　　图11-52 查询的详细信息

　　将谓词写到WHERE子句中，可以发现在第8行信息中，使用了筛选，这样选择出来的信息则不是整张表的内容，而是筛选后的内容，接着再进行连接的时候，表行的内容即减少了很多，进而可以节约查询成本，并且WHERE子句会在HAVING语句之前处理。

　　三、为中间结果使用临时表

　　临时表与永久表相似，但临时表存储在tempdb中，当不再使用时会自动删除。临时表有两种类型：本地和全局。它们在名称、可见性以及可用性上有区别，本地临时表的名称以单个数字符号(#)打头，它们仅对当前的用户连接是可见的，当用户从SQL Server实例断开连接时被删除。全局临时表的名称以两个数字符号(##)打头，创建后对任何用户都是可见的，当所有引用该表的用户从SQL Server断开连接时被删除。

　　例如，如果创建了employees表，则任何在数据库中有使用该表的安全权限的用户都可以使用该表，除非已将其删除。如果数据库会话创建了本地临时表#employees，则仅会话可以使用该表，会话断开连接后就将该表删除。如果创建了##employees全局临时表，则数据库中的任何用户均可使用该表。如果该表在您创建后没有其他用户使用，则当您断开连接时该表删除。如果您创建该表后另一个用户在使用该表，则SQL Server将在您断开连接并且所有其他会话不再使用该表时将其删除。

　　在编写报表过程中，会产生一些中间结果，特别是一个些高度复杂的OLTP(联机业务分析)结果，并且要求多条遍历路径，那么这些中间结果就变的很有意义，这样可以使用临时表来保存这些中间结果，但使用临时表来保存中间结果会产生四部分成本：结构创建、数据初始化读取、将数据写入到结构中、再次将数据读出，这样做其实挺冗余的，但是在如下一些情况下使用临时表可以提高执行效率：

　　正在检索存储过程调用的结果，这样没有别的办法，只能将这些结果保存在一个临时表结构中;

　　可以重复作用，并且复杂的、涉及遍历或聚合的场景，这种情况下，使用临时表可以提高效率，因为只需要对索引结构进行一次遍历，如果对数据进行聚合，则可以消除执行冗余计算过程中的CPU和内存的使用;

　　在一个大的数据库中，连接两个大型表的代价很大，如果结果规模很小，则从每个表中先相互独立地将结果提取到临时表中，然后对这些结果进行连接更为有效;

　　如果涉及到一些报表查询，可以将中间结果保存到临时表中，这样可以尽量的诊断故障或查询代码，进行降低维护的复杂性;

　　下面是创建临时表的实例：