SQL Server 调优系列玩转篇二（如何利用汇聚联合提示（Hint）引导语句运行）

前言

上一篇我们分析了查询Hint的用法，作为调优系列的最后一个玩转模块的第一篇。有兴趣的可以点击查看：SQL Server调优系列玩转篇（如何利用查询提示（Hint）引导语句运行）

本篇继续玩转模块的内容，同样，还是希望扎实掌握前面一系列的内容，才进入本模块的内容分析。

闲言少叙，进入本篇的内容。

技术准备

数据库版本为SQL Server2012，利用微软的以前的案例库（Northwind）进行分析，部分内容也会应用微软的另一个案例库AdventureWorks。

相信了解SQL Server的朋友，对这两个库都不会太陌生。

误区纠正

在开始本篇文章主题内容之前，先纠正一些关于新手对于数据库调优的误区。也希望在日常应用解决问题的时候，切记道听途说，人云亦云，毛爷爷说过的：实践是检验真理的唯一标准。

两个误区：

1、当在查询计划中发现了表扫描（Table Scan），就仿佛找到了病根一样，就想搞掉它，因为很多过来人都说过这种方式是性能很烂，而搞掉它的方式就是上索引，而且认为有了索引的就会快很多。

2、SQL Server语句优化就是创建索引，而创建索引就更简单了，找找查询语句，看看Where条件后...有几个筛选条件，创建几个非聚集索引就可以。

 

来看第一个问题：关于表扫描（Table Scan）是否真像传说的性能那么差劲！ 

首先，我们知道比较查询语句性能的优越性，无非就几个关键指标：运行IO、运行时间、消耗：CPU、Memory、编译时间等。

来看，以下语句，完全相同的表结构、表数据，不同的是一张表是堆表，一张是加了聚集索引的表，我们来开启两个回话进行测试比较。

堆表查询：

SET STATISTICS IO ON
--新建个测试表
SELECT * INTO NewOrders FROM Orders
--先清空缓存数据
DBCC DROPCLEANBUFFERS
GO
SELECT * FROM NewOrders
SET STATISTICS IO OFF

存在聚集索引的测试表查询：

SET STATISTICS IO ON
--新建个测试表
SELECT * INTO NewCLOrders FROM Orders
--添加聚集索引
CREATE CLUSTERED INDEX CL_OrderID ON NewCLOrders (OrderID desc)
GO
--先清空缓存数据
DBCC DROPCLEANBUFFERS
GO
SELECT * FROM NewCLOrders
SET STATISTICS IO OFF

这样对比的原因是：很多人认为数据库优化的方式就是加上索引，并且认为查找（Seek）就比扫描好（Scan）。

这样可以肯定的堆表采用的为表扫描（Table Scan），而后者则就是通过聚集索引扫描（Index Scan）

 

先来看IO的两者对比：

先来看看堆表的IO信息

堆表的表现：逻辑读取20次，预读2次，这里预读次数的多少其实是影响性能的重要指标，因为它是直接从磁盘中读取，所以性能最差，当然SQL Server此处采用并行处理，而且第一次读取数之后就缓存到内存中，防止再次的磁盘交互。

再来看聚集索引表的IO信息

采用聚集索引的表，逻辑IO为23次，预读飙升至22次。

相比而言：

相同的查询语句，堆表的查询逻辑读取次数为20次、预读2次，没有物理读...，而用聚集索引的表逻辑读为23次、预读22次！还有统计信息的不准确导致的物理读取1次！....所以相比堆表的SCAN是不是性能好很多。

 

当然，要再深入点分析，其实这两者不同的原因很简单：采用了聚集索引的表因为其存储的结构（B-Tree）的方式，所以逻辑IO肯定要多3，因为从索引根节点至叶子节点，也就是需要经过三个索引页，才能获取到数据页。

而预读的差距这么大的原因也是同样的原因：从堆表中获取数据是一段连续的数据页（确切的说是一次连续读取64个数据页<512KB>），而这时所有加上索引的表做不到的！通过索引只能依次读取一个数据页（8KB）,这也是索引的局限性。

关于查询计划的逻辑读、预读、物理读等IO详细逻辑信息，可以参照我前面的文章，分析的很详细：SQL Server调优系列进阶篇（查询语句运行几个指标值监测）

 

接着我们再对比下执行时间，相信这个也是更为关注的：

看明白了吧，获取完全相同的数据量，堆表执行的时候耗时157毫秒，并且分析和编译没有占用时间，这个很简单，因为它是堆表，根本不需要根据统计信息进行优化和选择；而加上聚集索引的表就不一样了，需要根据索引的统计信息对T-SQL语句进行优化和编译，而这足足耗费了79毫秒，然后执行的时候还需要更多的预读IO,还有如果优化器没有优化到位的时候，还要造成额外的物理IO，所以它总耗费了298毫秒...

在我的测试表中只有八百多行的数据中就产生了如此的差距值..如果数据量多的话...性能就堪忧了....

关于CPU和内存值我就不截图了......上面我们分析了加了聚集索引了，就产生了查询优化器一系列的过程...而编译就是需要CPU资源的.....

通过上面的结果，我要表达的是：

首先，在我们所看到的查询计划中，不要一看到表扫描，就感觉这个运算符是很慢的，或者是很耗时的。更有甚者看到了就感觉问题出现在这上面，并且为很多人所唾弃为“万恶的表扫描”....

其次，请记住，在SQL Server中你所看到的任何一个运算符，都是在目前你所设定的环境中基本是最好的....更没有那个运算符好与那个运算符烂一说...诸如偏执的认为哈希连接就比嵌套循环要快...索引查找就比索引扫描要好等问题.....我们要做的就是合适的场景运用合适的处理方式，最优的顺应SQL Server性能。

再次，经过上述了问题的分析，也不要陷入另外一个极端的误区：表扫描就要比聚集索引扫描好！后续的文章中我会给你展示聚集索引比表扫描好的用处...在SQL Server的世界中，只有你真正的触摸的本质，才不会迷茫...才会看清一切所谓的教条调优都非绝对！

 

关于第二个问题的误区，其实是很多人的误区，误认为了非聚集索引的强大性，误以为在列中加上了索引就可以充分应用。本篇就不纠正了，可以参照我前面的文章，相信看完了基本也就懂了非聚集索引的利弊项，连接：SQL Server调优系列进阶篇（如何索引调优）

一、GROUP 提示 （Hints）

继续咱们本篇文章的内容，上一篇我们分析了查询的几个重要的Hints,本篇文章我们来看分组提示，分组查询也是我们在写T-SQL语句经常用到的，关于分组的运算符也有两个：Order Group和Hash Group。其实关于排序一直也是数据库中最为头疼的运算。这个运算符也是消耗比较大的，相当的耗内存，如果数据量较大的话，SQL Server处理的方式也是通过哈希算法进行优化。

当然，关于分组查询运算符分解，看以参照基础篇中的文章：SQL Server调优系列基础篇（常用运算符总结）

来看个例子：

SELECT CustomerID,MAX(OrderDate) 
FROM Orders
GROUP BY CustomerID

上面的查询语句，我们想获取出每个订单的最大订单日期。

通过查询计划我们可以推测出肯定在CustomerID列存在索引，这样SQL Serer能直接利用这个进行排序，但是即便如此消耗还是飙升到56%....然后通过再加上一个流聚合计算出最大订单日期。

当然，此方式也是SQL Server认为的一种最优方式，但是如果数据量多的话，此种方式将会造成内存严重的消耗。

所以，我们可以采用GROUP Hint进行提示，将其更改为Hash 分组..代码如下：

SELECT CustomerID,MAX(OrderDate) 
FROM Orders
GROUP BY CustomerID
OPTION(HASH GROUP)

当然，此处可能并不是一个最优的方式，只是为了演示，但是如果基础数据量增大的话，我也相信SQL Server会自动的更改为哈希匹配的方式进行。

 

 二、组合提示 （Hints）

大部分情况下，我们所写的T-SQL语句并不是简单的，有很多的各种嵌套查询进行，如果这种查询语句，我们的提示（Hints）就可能不是单一的。

我们来看如此方式该如何进行指导。先来看个简单的例子：

SELECT O.OrderID
FROM Customers C JOIN Orders O
JOIN Employees E
ON O.EmployeeID=E.EmployeeID
ON C.CustomerID=O.CustomerID
WHERE C.City=N'London' AND E.City=N'London'
OPTION(FORCE ORDER,HASH JOIN)

不仅仅如此，我们还可以手动给查询语句写查询计划。

也就是我们自己写的XML查询计划，让T-SQL语句就按照我们自定义的查询计划去进行，当然，这是大招了，我们留在最后使用。

 

参考文献

• 微软联机丛书逻辑运算符和物理运算符引用
• 参照书籍《SQL.Server.2005.技术内幕》系列