数据库Join实现

Join 实现算法

一.Nested Loopsb Join

1.定义

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

　 for each row R1 in the outer table
for each row R2 in the inner table
if R1 joins with R2
return (R1, R2)

2.应用场景

　　 适用于outer table(有的地方叫Master table)的记录集比较少(<10000)而且inner table(有的地方叫Detail table)索引选择性较好的情况下(inner table要有index)。

　　 inner table被outer table驱动，outer table返回的每一行都要在inner table中检索到与之匹配的行。当然也可以用ORDERED 提示来改变CBO默认的驱动表，使用USE_NL(table_name1 table_name2)可是强制CBO 执行嵌套循环连接。

　　 cost = outer access cost + (inner access cost * outer cardinality)

3.常用于执行的连接

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

二.Merge Join

　 1.定义　　

　　 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的记录预先读出来放入内存临时表中，比从原数据页或磁盘读取要快。

　 2.应用场景另

　　用在数据没有索引但是已经排序的情况下。

　　通常情况下hash join的效果都比Sort merge join要好，然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时Sort merge join的性能会优于hash join。可以使用USE_MERGE(table_name1 table_name2)来强制使用Sort merge join。

　　cost = (outer access cost * # of hash partitions) + inner access cost

　3.常用于执行的连接

　　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 树索引或可以对多个操作（如合并联接和对汇总分组）使用排序顺序），则合并联接尤其有效。

三.Hash Join

　1.定义

　 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算法。

　 2.应用场景

　　适用于两个表的数据量差别很大。但需要注意的是：如果HASH表太大，无法一次构造在内存中，则分成若干个partition，写入磁盘的temporary segment，则会多一个I/O的代价，会降低效率，此时需要有较大的temporary segment从而尽量提高I/O的性能。

　　可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接。如果使用散列连HASH_AREA_SIZE 初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区自动管理，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整PGA_AGGREGATE_TARGET 即可。

　　也可以使用HASH_JOIN_ENABLED=FALSE(默认为TRUE)强制不使用hash join。

　　cost = (outer access cost * # of hash partitions) + inner access cost

　3.常用于执行的链接

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

Hive中Join物理实现过程

测试sql ：
select * from a join b where a.id= b.id;

a 0 hash map table
b 1 scan table

alias ：　１

MapJoinOperator

实现思路

• 遍历主表alias的每一条记录
• 去Hash表查看join的key是否存在，如果存在，取出结果集
• 过滤当前记录
• 将上面两个结果集做nest loop join，并转发结果

// MapJoinOperator.java
在cleanUpInputFileChangedOp() 方法中load hash table

generateMapMetaData();
loadHashTable();


// HashMapWrapper.java
// 通过join的key在hash表中查看是否有对应的值，如果有，取出结果List作为MapJoinRowContainer rowContainer，

public void setFromRow(Object row, List<ExprNodeEvaluator> fields,
        List<ObjectInspector> ois) throws HiveException {
      if (currentKey == null) {
        currentKey = new Object[fields.size()];
      }
      for (int keyIndex = 0; keyIndex < fields.size(); ++keyIndex) {
        currentKey[keyIndex] = fields.get(keyIndex).evaluate(row);
      }   
      key = MapJoinKey.readFromRow(output, key, currentKey, ois, !isFirstKey);  // currentKey 为join的相关column列，key 为计算出的对应的standardObject
      isFirstKey = false;
      this.currentValue = mHash.get(key);  // 去hash表查看join的值是否存在
    }

// 计算出当前alias行数据，并进行数据过滤  getFilteredValue()
List<Object> nr = JoinUtil.computeValues(row, joinValues[alias],
        joinValuesObjectInspectors[alias], hasFilter);
    if (hasFilter) {
      short filterTag = JoinUtil.isFiltered(row, joinFilters[alias],
          joinFilterObjectInspectors[alias], filterMaps[alias]);
      nr.add(new ShortWritable(filterTag));
      aliasFilterTags[alias] &= filterTag;
    }


// 所有上面操作结果是将当前join key的表数据全部放到storage[] 数组中

MapJoinRowContainer rowContainer = adaptor.getCurrentRows();  // 通过key join出来的一组value值

storage[pos] = rowContainer.copy(); 
            aliasFilterTags[pos] = rowContainer.getAliasFilter();  // 这个表示join的数据是否需要保留

CommonJoinOperator.checkAndGenObject() 
// 将关联好的结果表数据转发到下一个operator
if (!hasEmpty && !mayHasMoreThanOne) {
        genAllOneUniqueJoinObject();
      } else if (!hasEmpty && !hasLeftSemiJoin) {
        genUniqueJoinObject(0, 0);
      } else {
        genJoinObject();
      }

// 生成join数据。将storage中的记录全部取出，做nest loop join并将数据forward
private void genUniqueJoinObject(int aliasNum, int forwardCachePos)
      throws HiveException {
    AbstractRowContainer.RowIterator<List<Object>> iter = storage[order[aliasNum]].rowIter();
    for (List<Object> row = iter.first(); row != null; row = iter.next()) {
      int sz = joinValues[order[aliasNum]].size();
      int p = forwardCachePos;
      for (int j = 0; j < sz; j++) {
        forwardCache[p++] = row.get(j);
      }
      if (aliasNum == numAliases - 1) {
        internalForward(forwardCache, outputObjInspector);
        countAfterReport = 0;
      } else {
        genUniqueJoinObject(aliasNum + 1, p);
      }
    }
  }

关键变量：

order : 所有的表下标
posBigTab ：需要扫描的表的下标

CommonMergeJoinOperator

整体实现思路

• 从source 0 （Master table）中开始遍历数据，调用processOp()
• processOp()中会初始化 (Detail table)表数据，并放入容器，直到Detail 表中取到nextGroup
• 取Master table 的结果放入容器，直到Master table中取到nextGroup
• 将所有storage容器中的结果进行Join生成结果
• 结束主表循环在close() 方法中取出剩余的数据，生成结果直到结束

Source 消费数据流程

• ReduceRecordSource.pushRecord()
• reader.next() 获取getCurrentKey() ,getCurrentValues()
• 将values 放入groupIterator,之后每次调用这个迭代器来取数据
• 取出的数据传递给当前实例对象的reducer对象进行消费

TezProcessor::run()
TezProcessor::initializeAndRunProcessor(inputs, outputs); 
----- run() 调用流程
rproc.run()
ReduceRecordSource::pushRecord()
groupIterator.next() 循环调用子Operator处理数据
CommonMergeJoinOperator.processOp() 开始处理主表(0)数据.在主表的数据取到nextKeyGroup 时，开始join数据，否则将数据放入container

----- close() 调用流程
在rproc.close() 中调用对应的close方法
ReduceRecoredProcessor.run()
ReduceRecoredProcessor::close()
reducer.close(abort)
Operator.closeOp(abort)
CommonMergeJoinOperator::closeOp()
CommonMergeJoinOperator::joinFinalLeftData()

----- 具体处理代码
在处理新表的时候
如果是新的group：
    this.nextGroupStorage[alias].addRow(value);
    foundNextKeyGroup[tag] = true;
    return

如果是同一个group:
    candidateStorage[tag].addRow(value);

部分方法说明：
joinOneGroup() -> joinObject()  // join一组数据，并返回下一次需要fetch的表下标
candidateStorage[] -> storage[]  -> checkAndGenObject(); // 通过将临时容器中的数据放入正式容器中，开始生成join结果
nextKeyGroup // 标识是否为一组新的数据