第六章——根据执行计划优化性能（1）——理解哈希、合并、嵌套循环连接策略

前言：

本系列文章包括：

1、 理解Hash、Merge、Nested Loop关联策略。

2、 在执行计划中发现并解决表/索引扫描。

3、 介绍并在执行计划中发现键查找并解决它们。

 

对于性能优化，需要集中处理以下的问题：

1、 为你的环境创建性能基线。

2、 监控现在的性能并发现瓶颈。

3、 解决瓶颈以便得到更好的性能。

 

一个预估执行计划是描述查询将会如何执行的一个蓝图，而一个实际执行计划就是一个查询执行时实际发生的镜像。通过对比两个执行计划，可以发现查询是否真的按照预估执行计划来执行。

在执行计划中，有一些非常重要的操作符需要清楚：

1、           Join策略：SQLServer有3种策略——哈希、合并、嵌套循环。每种策略都有其优缺点，本章将讲述这部分。

2、           扫描和查找是SQLServer用于读取数据的两种方式，这两种方式在性能优化中是核心概念。将会在下一篇中讲述。

3、           键查找有时候会成为主要的性能问题。因为存储引起必须从非聚集索引中跳到聚集索引，一边找到非聚集索引中的非键值列的值。这样的行为通常很耗时间。

 

理解哈希、合并、嵌套循环连接策略

SQLServer提供了3中JOIN的策略，它们没有绝对的好和坏之分。

 

1、 哈希（Hash Join）：SQLServer选择哈希关联作为物理操作符，一边对于大容量数据，且未排序或者没有索引时进行查询。两个进程关联起来进行哈希关联，它们为【建立】和【探测】，在【建立】进程中，会从建立输入（即join的左表中，但是可能这个左表会在优化过程中交换位置，使得不一定就是实际上的左表。）读取所有行，然后在内存中创建一个符合关联条件的哈希表。在【探测】进程中，会从探测表（输入的右表）中读取所有的行，并根据关联条件，与之前创建的内存哈希表匹配。

2、 合并（Merge Join）：如果关联表中已经排序，SQLServer会选择合并关联。合并关联要求关联条件中最少有一个是已经被排序了的。如果数据量不大的时候，这比哈希关联更加有效，它并不是重负荷关联的方式。

3、 嵌套循环（Nested Loop）：在最少两个结果集中，使用嵌套循环会比较有效，这两个结果集中，作为外部表的集合要小，而内部循环结果集具有有效的索引。这种方式不适用于大结果集。

准备工作：

下面将创建两个表，然后看看各种关联方式的执行计划：

USE AdventureWorks
GO
IF OBJECT_ID('SalesOrdHeaderDemo') IS NOT NULL 
    BEGIN
        DROP TABLE SalesOrdHeaderDemo
    END
GO

IF OBJECT_ID('SalesOrdDetailDemo') IS NOT NULL 
    BEGIN
        DROP TABLE SalesOrdDetailDemo
    END
GO

SELECT  *
INTO    SalesOrdHeaderDemo
FROM    Sales.SalesOrderHeader
GO

SELECT  *
INTO    SalesOrdDetailDemo
FROM    Sales.SalesOrderDetail
GO

步骤：

1、 执行一下查询，并开启执行计划（Ctrl+M）：

SELECT  sh.*
FROM    SalesOrdDetailDemo AS sd
        INNER JOIN SalesOrdHeaderDemo AS sh ON sh.salesorderID = sd.salesorderid
GO

2、 然后从执行计划截图中可以看到使用了哈希连接：

3、 现在先创建唯一的聚集索引在两个表中：

CREATE UNIQUE CLUSTERED INDEX idx_salesorderheaderdemo_SalesOrderID ON salesordheaderdemo(SalesOrderID)

GO

CREATE UNIQUE CLUSTERED INDEX idx_SalesDetail_SalesOrderID ON SalesOrdDetailDemo(SalesOrderID,SalesOrderDetailID)

GO

4、 再次执行步骤1的语句：

5、 截图是第二次执行的执行计划，可以发现变成了合并连接，并且表扫描变成了聚集索引扫描：

6、 现在来看看嵌套循环关联，在上面的查询中添加where条件来限定查询的结果集：

SELECT  sh.*
FROM    SalesOrdDetailDemo AS sd
        INNER JOIN SalesOrdHeaderDemo AS sh ON sh.salesorderID = sd.salesorderid
WHERE   sh.salesorderid = 43659
GO

7、 从执行结果中看到现在关联变成了嵌套循环： 

 

分析：

        前面已经提到，哈希关联工作在大数据量且关联字段没有排序的关联中。所以在步骤1中，由于没有索引或者预先排序，数据的关联会使用哈希关联。

        在步骤3中，创建了一个唯一的聚集索引，所以表已经通过聚集索引排序了，此时优化器会选择合并关联。

        在步骤6中，由于使用了where条件限制数据集的大小，同时由于已经排序，所以使用了嵌套循环关联。

        每一种关联方法都有其优缺点，视乎如何优化而已。有时候哈希关联有其非常重要的作用，但是如果可以，强烈建议每个表都应该有一个唯一的聚集索引，一边使用合并关联，如果不可以，千万别尝试使用OPTION提示符来把关联改成合并或者嵌套循环，这可能会降低性能。而嵌套循环仅在小结果集的时候运行的最好。