SQL Server常用运算符合集

本篇将介绍在我们查看的查询计划时的分析技巧，以及几种我们常用的运算符优化技巧，同样侧重基础知识的掌握。通过本篇可以了解我们平常所写的T-SQL语句，在SQL Server数据库系统中是如何分解执行的，数据结果如何通过各个运算符组织形成的。

技术准备

基于SQL Server2008R2版本，利用微软的一个更简洁的案例库（Northwind）进行解析。

一、数据连接

数据连接是我们在写T-SQL语句的时候最常用的，通过两个表之间关联获取想要的数据。

SQL Server默认支持三种物理连接运算符：嵌套循环连接、合并连接以及哈希连接。三种连接各有用途，各有特点，不同的场景会数据库会为我们选择最优的连接方式。

a、嵌套循环连接（nested loops join)

嵌套循环连接是最简单也是最基础的连接方式。两张表通过关键字进行关联，然后通过双层循环依次进行两张表的行进行关联，然后通过关键字进行筛选。

可以参照下图进行理解分析

其实嵌套扫描是很简单的获取数据的方式，简单点就是两层循环过滤出结果值。

我们可以通过如下代码加深理解

for each row R1 in the outer table
for each row R2 int the inner table
if R1 join with R2
return (R1,R2)
举个列子
SELECT o.OrderID
FROM Customers C JOIN Orders O
ON C.CustomerID=O.CustomerID
WHERE C.City=N'London'

以上这个图标就是嵌套循环连接的图标了。而且解释的很明确。

这种方法的消耗就是外表和内表的乘积，其实就是我们所称呼的笛卡尔积。所以消耗的大小是随着两张表的数据量增大而增加的，尤其是内部表，因为它是多次重复扫描的，所以我们在实践中的采取的措施就是减少每个外表或者内表的行数来减少消耗。

对于这种算法还有一种提高性能的方式，因为两张表是通过关键字进行关联的，所以在查询的时候对于底层的数据获取速度直接关乎着此算法的性能，这里优化的方式尽量使用两个表关键字为索引查询，提高查询速度。

还有一点就是在嵌套循环连接中，在两张表关联的时候，对外表都是有筛选条件的，比如上面例子中【WHERE C.City=N’London’】就是对外表（Customers）的筛选，并且这里的City列在该表中存在索引，所以该语句的两个子查询都为索引查找（Index Seek）。

但是，有些情况我们的查询条件不是索引所覆盖的，这时候，在嵌套循环连接下的子运算符就变成了索引扫描（Index scan）或者RID查找。

举个例子

SELECT E1.EmployeeID,COUNT(*)
FROM Employees E1 JOIN Employees E2
ON E1.HireDate<E2.HireDate
GROUP BY E1.EmployeeID

以上代码是从职工表中获取出每位职工入职前的人员数。我们看一下该查询的执行计划

这里很显然两个表的关联通过的是HireDate列进行，而此列又不为索引项所覆盖，所以两张表的获取只能通过全表的聚集索引扫描进行，如果这两张表数据量特别大的话，无疑又是一个非常耗性能的查询。

通过文本可以看出，该T-SQL的查询结果的获取是通过在嵌套循环运算符中，对两个表经过全表扫描之后形成的笛卡儿积进行过滤筛选的。这种方式其实不是一个最优的方式，因为我们获取的结果其实是可以先通过两个表过滤之后，再通过嵌套循环运算符获取结果，这样的话性能会好很多。

我们尝试改一下这个语句

SELECT E1.EmployeeID,ECNT.CNT
FROM Employees E1 CROSS APPLY
(
SELECT COUNT(*) CNT
FROM Employees E2
WHERE E1.HireDate<E2.HireDate
)ECNT

通过上述代码查询的结果项，和上面的是一样的，只是我们根据外部表的结果对内部表进行了过滤，这样执行的时候就不需要获取全部数据项了。

我们查看下文本执行计划

我们比较一下，前后两条语句的执行消耗，对比一下执行效率

执行时间从1秒179毫秒减少至93毫秒。效果明显。

对比CPU消耗、内存、编译时间等总体消耗都有所降低，参考上图。

所以对嵌套循环连接连接的优化方式就是集中在这几点：对两张表数据量的减少、连接关键字上建立索引、谓词查询条件上覆盖索引最好能减少符合谓词条件的记录数。

b、合并连接(merge join)

上面提到的嵌套循环连接方式存在着诸多的问题，尤其不适合两张表都是大表的情况下，因为它会产生N多次的全表扫描，很显然这种方式会严重的消耗资源。

鉴于上述原因，在数据库里又提供了另外一种连接方式：合并连接。记住这里没有说SQL Server所提供的，是因为此连接算法是市面所有的RDBMS所共同使用的一种连接算法。

合并连接是依次读取两张表的一行进行对比。如果两个行是相同的，则输出一个连接后的行并继续下一行的读取。如果行是不相同的，则舍弃两个输入中较少的那个并继续读取，一直到两个表中某一个表的行扫描结束，则执行完毕，所以该算法执行只会产生每张表一次扫描，并且不需要整张表扫描完就可以停止。

该算法要求按照两张表进行依次扫描对比，但是有两个前提条件：1、必须预先将两张表的对应列进行排序；2、对两张表进行合并连接的条件必须存在等值连接。

我们可以通过以下代码进行理解

get first row R1 from input1
get first row R2 from input2
while not at the end of either input
begin
if R1 joins with R2
begin
output(R1,R2)
get next row R2 from input2
end
else if R1<R2
get next row R1 from input1
else
get next row R2 from input2
end

合并连接运算符总的消耗是和输入表中的行数成正比的，而且对表最多读取一次，这个和嵌套循环连接不一样。因此，合并连接对于大表的连接操作是一个比较好的选择项。

对于合并连接可以从如下几点提高性能：

两张表间的连接值内容列类型，如果两张表中的关联列都为唯一列，也就说都不存在重复值，这种关联性能是最好的，或者有一张表存在唯一列也可以，这种方式关联为一对多关联方式，这种方式也是我们最常用的，比我们经常使用的主从表关联查询；如果两张表中的关联列存在重复值，这样在两表进行关联的时候还需要借助第三张表来暂存重复的值，这第三张表叫做”worktable “是存放在Tempdb或者内存中，而这样性能就会有所影响。所以鉴于此，我们常做的优化方式有：关联连尽量采用聚集索引（唯一性）

我们知道采用该种算法的前提是，两张表都经过排序，所以我们在应用的时候，最好优先使用排序后的表关联。如果没有排序，也要选择的关联项为索引覆盖项，因为大表的排序是一个很耗资源的过程，我们选择索引覆盖列进行排序性能要远远好于普通列的排序。

我们来举个例子

SELECT O.CustomerID,C.CustomerID,C.ContactName
FROM Orders O JOIN Customers C
ON O.CustomerID=C.CustomerID

我们知道这段T-SQL语句中关联项用的是CustomerID，而此列为主键聚集索引，都是唯一的并且经过排序的，所以这里面没有显示的排序操作。

而且凡是采用合并连接的所有输出结果项，都是已经经过排序的。

我们找一个稍复杂的情况，没有提前排序的利用合并查询的T-SQL

SELECT O.OrderID,C.CustomerID,C.ContactName
FROM Orders O JOIN Customers C
ON O.CustomerID=C.CustomerID AND O.ShipCity<>C.City
ORDER BY C.CustomerID

上述代码返回那些客户的发货订单不在客户本地的。

上面的查询计划可以看出，排序的消耗总是巨大的，其实我们上面的语句按照逻辑应该是在合并连接获取数据后，才采用显示的按照CustomerID进行排序。

但是因为合并连接运算符之前本身就需要排序，所以此处SQL Server采取了优先排序的策略，把排序操作提前到了合并连接之前进行，并且在合并连接之后，就不需要在做额外的排序了。

这其实这里我们要求对查询结果排序，正好也利用了合并连接的特点。

c、哈希连接(hash join)

我们分析了上面的两种连接算法，两种算法各有特点，也各有自己的应用场景：嵌套循环连接适合于相对小的数据集连接，合并连接则应对与中型的数据集，但是又有它自己的缺点，比如要求必须有等值连接，并且需要预先排序等。

那对于大型的数据集合的连接数据库是怎么应对的呢？那就是哈希连接算法的应用场景了。

哈希连接对于大型数据集合的并行操作上都比其它方式要好很多，尤其适用于OLAP数据仓库的应用场景中。

哈希连接很多地方和合并连接类似，比如都需要至少一个等值连接，同样支持所有的外连接操作。但不同于合并连接的是，哈希连接不需要预先对输入数据集合排序，我们知道对于大表的排序操作是一个很大的消耗，所以去除排序操作，哈希操作性能无疑会提升很多。