【学习】认识优化查询中的Merge Join、Nested Loops和Hash Match

转载 2012年03月21日 11:14:13

1.基本概念:
    Merge Join([排序]合并联接)、Nested Loops(嵌套循环联接)、Hash Match都是物理运算符。

 

    Merge Join常执行Inner Join(内部联接)、Left Outer Join(左外部联接)、Left Semi Join(左半部联接)、Left Anti Semi Join(左反半部联接)、Right Outer Join(右外部联接)、Right Semi Join(右半部联接)、Right Anti Semi Join(右反半部联接)和Union(联合)逻辑操作。

    在 Argument 列中,如果操作执行一对多联接,则 Merge Join 运算符将包含 MERGE:() 谓词;如果操作执行多对多联接,则该运算符将包含 MANY-TO-MANY MERGE:() 谓词。Argument 列还包含一个用于执行操作的列的列表,该列表以逗号分隔。Merge Join 运算符要求在各自的列上对两个输入进行排序,这可以通过在查询计划中插入显式排序操作来实现。如果不需要显式排序(例如,如果数据库内有合适的 B 树索引或可以对多个操作(如合并联接和对汇总分组)使用排序顺序),则合并联接尤其有效。

 

    Nested Loops常执行Inner Join(内部联接)、Left Outer Join(左外部联接)、Left Semi Join(左半部联接)和Left Anti Semi Join(左反半部联接)逻辑操作。
    Nested Loops通常使用索引在内部表中搜索外部表的每一行。根据预计的开销,Microsoft SQL Server决定是否对外部输入进行排序来改变内部输入索引的搜索位置。
    将基于所执行的逻辑操作返回所有满足 Argument 列内的(可选)谓词的行。


    Hash Match运算符通过计算其生成输入中每行的哈希值生成哈希表。HASH:()谓词以及一个用于创建哈希值的列的列表出现在Argument列内。然后,该谓词为每个探测行(如果适用)使用相同的哈希函数计算哈希值并在哈希表内查找匹配项。如果存在残留谓词(由 Argument 列中的 RESIDUAL:() 标识),则还须满足此残留谓词,只有这样行才能被视为是匹配项。行为取决于所执行的逻辑操作:
(1)对于联接,使用第一个(顶端)输入生成哈希表,使用第二个(底端)输入探测哈希表。按联接类型规定的模式输出匹配项(或不匹配项)。如果多个联接使用相同的联接列,这些操作将分组为一个哈希组。
(2)对于非重复或聚合运算符,使用输入生成哈希表(删除重复项并计算聚合表达式)。生成哈希表时,扫描该表并输出所有项。
(3)对于 union 运算符,使用第一个输入生成哈希表(删除重复项)。使用第二个输入(它必须没有重复项)探测哈希表,返回所有没有匹配项的行,然后扫描该哈希表并返回所有项。


2.执行步骤
    Merge Join第一个步骤是确保两个关联表都是按照关联的字段进行排序。如果关联字段有可用的索引,并且排序一致,则可以直接进行Merge Join操作;否则,SQL Server需要先对关联的表按照关联字段进行一次排序(就是说在Merge Join前的两个输入上,可能都需要执行一个Sort操作,再进行Merge Join)。 
    两个表都按照关联字段排序好之后,Merge Join操作从每个表取一条记录开始匹配,如果符合关联条件,则放入结果集中;否则,将关联字段值较小的记录抛弃,从这条记录对应的表中取下一条记录继续进行匹配,直到整个循环结束。

    在多对多的关联表上执行Merge Join时,通常需要使用临时表进行操作。例如A join B使用Merge Join时,如果对于关联字段的某一组值,在A和B中都存在多条记录A1、A2...An、B1、B2...Bn,则为A中每一条记录A1、A2...An,都必须在B中对所有相等的记录B1、B2...Bn进行一次匹配。这样,指针需要多次从B1移动到Bn,每一次都需要读取相应的B1...Bn记录。将B1...Bn的记录预先读出来放入内存临时表中,比从原数据页或磁盘读取要快。

 

    Nested Loops也称为嵌套迭代,它将一个联接输入用作外部输入表(显示为图形执行计划中的顶端输入),将另一个联接输入用作内部(底端)输入表。外部循环逐行消耗外部输入表。内部循环为每个外部行执行,在内部输入表中搜索匹配行。最简单的情况是,搜索时扫描整个表或索引;这称为单纯嵌套循环联接。如果搜索时使用索引,则称为索引嵌套循环联接。如果将索引生成为查询计划的一部分(并在查询完成后立即将索引破坏),则称为临时索引嵌套循环联接。

 

    Hash Match有两个输入:build input(也叫做outer input)和probe input(也叫做inner input),不仅用于inner/left/right join等,象union/group by等也会使用hash join进行操作,在group by中build input和probe input都是同一个记录集。

    Hash Match操作分两个阶段完成:Build(构造)阶段和Probe(探测)阶段。
    Build(构造)阶段主要构造哈希表(hash table)。在inner/left/right join等操作中,表的关联字段作为hash key;在group by操作中,group by的字段作为hash key;在union或其它一些去除重复记录的操作中,hash key包括所有的select字段。
    Build操作从build input输入中取出每一行记录,将该行记录关联字段的值使用hash函数生成hash值,这个hash值对应到hash table中的hash buckets(哈希表目)。如果一个hash值对应到多个hash buckts,则这些hash buckets使用链表数据结构连接起来。当整个build input的table处理完毕后,build input中的所有记录都被hash table中的hash buckets引用/关联了。
    Probe(探测)阶段,SQL Server从probe input输入中取出每一行记录,同样将该行记录关联字段的值,使用build阶段中相同的hash函数生成hash值,根据这个hash值,从build阶段构造的hash table中搜索对应的hash bucket。hash算法中为了解决冲突,hash bucket可能会链接到其它的hash bucket,probe动作会搜索整个冲突链上的hash bucket,以查找匹配的记录。 
如果build input记录数非常大,构建的hash table无法在内存中容纳时,SQL Server分别将build input和probe input切分成多个分区部分(partition),每个partition都包括一个独立的、成对匹配的build input和probe input,这样就将一个大的hash join切分成多个独立、互相不影响的hash join,每一个分区的hash join都能够在内存中完成。SQL Server将切分后的partition文件保存在磁盘上,每次装载一个分区的build input和probe input到内存中,进行一次hash join。这种hash join叫做Grace Hash join,使用的Grace Hash Join算法。

 

3.性能分析
    Merge Join(合并联接)本身的速度很快,但如果需要排序操作,选择合并联接就会非常费时。然而,如果数据量很大且能够从现有 B 树索引中获得预排序的所需数据,则合并联接通常是最快的可用联接算法。如果是无序的数据,Merge Join首先做的是排序,如果数据量大,排序就会溢出到tempdb, 效率就将低了。

 

    如果外部输入很小(<10000)而内部输入很大且预先创建了索引,则Nested Loops(嵌套循环联接)尤其有效。在许多小事务中(如那些只影响较小的一组行的事务),索引嵌套循环联接远比合并联接和哈希联接优越。但在大查询中,嵌套循环联接通常不是最佳选择。

 

    如果两个表的数据量差别很大,则使用Hash Match。但需要注意的是:如果HASH表太大,无法一次构造在内存中,则分成若干个partition,写入磁盘的temporary segment,则会多一个I/O的代价,会降低效率,此时需要有较大的temporary segment从而尽量提高I/O的性能。Hash join的主要资源消耗在于CPU(在内存中创建临时的HASH表,并进行HASH计算),而Merge join的资源消耗主要在于磁盘I/O(扫描表或索引)。

4.优化原则
    一般情况下,Hash Join处理代价非常高,是数据库服务器内存和CPU的头号杀手之一,尤其是涉及到分区(数据量太大导致内存不够的情况,或者并发访问很高导致当前处理线程无法获得足够的内存,那么数据量不是特大的情况下也可能需要进行分区),为了尽快的完成所有的分区步骤,将使用大量异步的I/O操作,因此期间单一一个线程就可能导致多个磁盘驱动器出于忙碌状态,这很有可能阻塞其它线程的执行。 因此,
    1. 要避免大数据的Hash Join,尽量将其转化为高效的Merge Join、Nested Loops。可能使用的手段有表结构设计、索引调整设计、SQL优化,以及业务设计优化。例如冗余字段的运用,将统计分析结果用service定期跑到静态表中,适当的冗余表,使用AOP或类似机制同步更新等。
    2. 尽量减少join两个输入端的数据量。这一点比较常犯的毛病是,条件不符合SARG,在子查询内部条件给的不充分(SQL过于复杂情况下SQL Server查询优化器经常犯傻,写在子查询外部的条件不会被用在子查询内部,影响子查询内部的效率或者是跟子查询再join时候的效率)。


原文链接:http://blog.csdn.net/yongsheng0550/article/details/5452200

相关文章推荐

NESTED LOOPS & HASH JOINS & MERGE JOIN

1、NESTED LOOPS(嵌套循环) 2、HASH JOINS(散列链接) 3、

普通表的Join 三种算法(join 一) 嵌套循环Join(Nested Loops Join)、排序合并Join(Sort-Merge Join)和哈希Join(Hash Join)

refer to http://mysun.iteye.com/blog/1748473 通过Map-Reduce实现Join系列之一 本系列的开篇在提到使用Map-Reduce实现Join之前,...
  • G7N3F
  • G7N3F
  • 2016-01-18 16:58
  • 1155

nested loops/hash join

最近看执行计划时,老是看到nested loops/hash join这些词,于是做了个实验,贴出来如果错误请指出。 ps:顺便也谈了下CBO 1.创建表t1,t2 都只有很少的几行数据,...

Sql优化(一) Merge Join vs. Hash Join vs. Nested Loop

本文介绍了Merge Join,Hash Join,Nested Loop这三种数据库Join方式的工作原理,并通过实验进一步说明了其适用范围。

Oracle优化器、优化模式、表的连接方式(Hash Join、Nested Loop、Sort Merge Join)

查询优化器Oracle的查询优化器(QO)分为两种: 1. RBO:Ruled-Based Optimization, 基于规则的优化器; 2. CBO :Cost-Based Op...
  • wwlhz
  • wwlhz
  • 2017-06-30 14:32
  • 147

oracle sql调优学习笔记(三)表的连接方式:NESTED LOOP、HASH JOIN、SORT MERGE JOIN

表连接方式及使用场合 NESTED LOOP     对于被连接的数据子集较小的情况,nested loop连接是个较好的选择。nested loop就是扫描一个表,每读到一条记录,就根据...

SQL优化(一) Merge Join VS. Hash Join VS. Nested Loop

本文介绍了Merge Join,Hash Join,Nested Loop这三种数据库Join方式的工作原理,并通过实验进一步说明了其适用范围。

Nested Loop,Hash Join,Merge Join

原创文章,首发自本人个人博客站点,转载请务必注明出自http://www.jasongj.com Nested Loop,Hash Join,Merge Join介绍 Nested Lo...

多表连接的三种方式详解 HASH JOIN MERGE JOIN NESTED LOOP

在多表联合查询的时候,如果我们查看它的执行计划,就会发现里面有多表之间的连接方式。 之前打算在sqlplus中用执行计划的,但是格式看起来有点乱,就用Toad 做了3个截图。从3张图里我们看到了几点信...

HASH JOIN , MERGE JOIN ,NESTED LOOP

NESTED LOOP: 对于被连接的数据子集较小的情况,嵌套循环连接是个较好的选择。在嵌套循环中,内表被外表驱动,外表返回的每一行都要在内表中检索找到与它匹配的行,因此整个查询返回的结果集不能太大...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)