INTERSECT · k8s原理与实践

[TOC] # INTERSECT INTERSECT 算子用于对左右孩子算子输出进行交集运算，并进行去重。 OceanBase 数据库支持的 INTERSECT 算子包括 MERGE INTERSECT DISTINCT 和 HASH INTERSECT DISTINCT。 ## MERGE INTERSECT DISTINCT 如下示例中，Q1 对两个查询使用 INTERSECT 联接，c1 有可用排序，0 号算子生成了 MERGE INTERSECT DISTINCT 进行求取交集、去重。由于 c2 无可用排序，所以在 3 号算子上分配了 SORT 算子进行排序。算子执行时从左右子节点读取有序输入，利用有序输入进行 MERGE，实现去重并得到交集结果。 ~~~ obclient>CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT); Query OK, 0 rows affected (0.12 sec) obclient>INSERT INTO t1 VALUES(1,1); Query OK, 1 rows affected (0.12 sec) obclient>INSERT INTO t1 VALUES(2,2); Query OK, 1 rows affected (0.12 sec) Q1: obclient>EXPLAIN SELECT c1 FROM t1 INTERSECT SELECT c2 FROM t1\G; *************************** 1. row *************************** Query Plan: ================================================= |ID|OPERATOR |NAME|EST. ROWS|COST| ------------------------------------------------- |0 |MERGE INTERSECT DISTINCT| |2 |77 | |1 | TABLE SCAN |T1 |2 |37 | |2 | SORT | |2 |39 | |3 | TABLE SCAN |T1 |2 |37 | ================================================= Outputs & filters: ------------------------------------- 0 - output([INTERSECT(T1.C1, T1.C2)]), filter(nil) 1 - output([T1.C1]), filter(nil), access([T1.C1]), partitions(p0) 2 - output([T1.C2]), filter(nil), sort_keys([T1.C2, ASC]) 3 - output([T1.C2]), filter(nil), access([T1.C2]), partitions(p0) ~~~ 上述示例中，执行计划展示中的 outputs & filters 详细列出了所有 INTERSECT 算子的输出信息如下： <div data-card-value="data:%7B%22rows%22%3A3%2C%22cols%22%3A2%2C%22html%22%3A%22%3Ctable%20class%3D%5C%22lake-table%5C%22%20style%3D%5C%22width%3A%20748px%3B%5C%22%3E%3Ccolgroup%3E%3Ccol%20span%3D%5C%221%5C%22%20width%3D%5C%22139%5C%22%20%2F%3E%3Ccol%20span%3D%5C%221%5C%22%20width%3D%5C%22609%5C%22%20%2F%3E%3C%2Fcolgroup%3E%3Ctbody%3E%3Ctr%20style%3D%5C%22height%3A%2033px%3B%5C%22%3E%3Ctd%20style%3D%5C%22vertical-align%3A%20top%3B%5C%22%3E%3Cp%20data-lake-id%3D%5C%22b92b778e0b6d42b6559f0dbdfd18725c%5C%22%3E%3Cspan%20class%3D%5C%22lake-fontsize-11%5C%22%20style%3D%5C%22color%3A%20%23262626%3B%5C%22%3E%3Cstrong%3E%E4%BF%A1%E6%81%AF%E5%90%8D%E7%A7%B0%3C%2Fstrong%3E%3C%2Fspan%3E%3C%2Fp%3E%3C%2Ftd%3E%3Ctd%20rowspan%3D%5C%221%5C%22%20colspan%3D%5C%221%5C%22%20style%3D%5C%22vertical-align%3A%20top%3B%5C%22%3E%3Cspan%20class%3D%5C%22lake-fontsize-11%5C%22%20style%3D%5C%22color%3A%20%23262626%3B%5C%22%3E%3Cstrong%3E%3Cspan%3E%E5%90%AB%E4%B9%89%3C%2Fspan%3E%3C%2Fstrong%3E%3C%2Fspan%3E%3C%2Ftd%3E%3C%2Ftr%3E%3Ctr%20style%3D%5C%22height%3A%2033px%3B%5C%22%3E%3Ctd%20rowspan%3D%5C%221%5C%22%20colspan%3D%5C%221%5C%22%20style%3D%5C%22vertical-align%3A%20top%3B%5C%22%3E%3Cspan%20class%3D%5C%22lake-fontsize-11%5C%22%3Eoutput%3C%2Fspan%3E%3C%2Ftd%3E%3Ctd%20rowspan%3D%5C%221%5C%22%20colspan%3D%5C%221%5C%22%20style%3D%5C%22vertical-align%3A%20top%3B%5C%22%3E%3Cspan%20class%3D%5C%22lake-fontsize-11%5C%22%3E%E8%AF%A5%3Cspan%3E%E7%AE%97%E5%AD%90%3C%2Fspan%3E%3Cspan%3E%E7%9A%84%E8%BE%93%E5%87%BA%E8%A1%A8%E8%BE%BE%E5%BC%8F%EF%BC%8C%3C%2Fspan%3E%3Cspan%3E%3Cspan%3E%E4%BD%BF%E7%94%A8%3C%2Fspan%3E%3Cspan%3Eintersect%3C%2Fspan%3E%3Cspan%3E%E8%BF%9E%E6%8E%A5%E7%9A%84%E4%B8%A4%E5%AD%A9%E5%AD%90%E7%AE%97%E5%AD%90%E5%AF%B9%E5%BA%94%E8%BE%93%E5%87%BA%EF%BC%8C%E5%8D%B3%E8%A1%A8%E7%A4%BA%E4%BA%A4%E9%9B%86%E8%BF%90%E7%AE%97%E8%BE%93%E5%87%BA%E7%BB%93%E6%9E%9C%E4%B8%AD%E7%9A%84%E4%B8%80%E5%88%97%EF%BC%8C%E6%8B%AC%E5%8F%B7%E5%86%85%E9%83%A8%E4%B8%BA%E5%B7%A6%E5%8F%B3%E5%AD%A9%E5%AD%90%E8%8A%82%E7%82%B9%E5%AF%B9%E5%BA%94%E6%AD%A4%E5%88%97%E7%9A%84%E8%BE%93%E5%87%BA%E5%88%97%E3%80%82%3C%2Fspan%3E%3C%2Fspan%3E%3C%2Fspan%3E%3C%2Ftd%3E%3C%2Ftr%3E%3Ctr%20style%3D%5C%22height%3A%2033px%3B%5C%22%3E%3Ctd%20rowspan%3D%5C%221%5C%22%20colspan%3D%5C%221%5C%22%20style%3D%5C%22vertical-align%3A%20top%3B%5C%22%3E%3Cspan%20class%3D%5C%22lake-fontsize-11%5C%22%3Efilter%3C%2Fspan%3E%3C%2Ftd%3E%3Ctd%20rowspan%3D%5C%221%5C%22%20colspan%3D%5C%221%5C%22%20style%3D%5C%22vertical-align%3A%20top%3B%5C%22%3E%3Cspan%20class%3D%5C%22lake-fontsize-11%5C%22%3E%3Cspan%3E%3Cspan%3E%E8%AF%A5%E7%AE%97%E5%AD%90%E4%B8%8A%E7%9A%84%E8%BF%87%E6%BB%A4%E6%9D%A1%E4%BB%B6%E3%80%82%3C%2Fspan%3E%3C%2Fspan%3E%3Cspan%3E%E7%94%B1%E4%BA%8E%C2%A0%3C%2Fspan%3E%3Cspan%3Eintersect%3C%2Fspan%3E%3Cspan%3E%3Cspan%3E%C2%A0%E7%AE%97%E5%AD%90%E6%B2%A1%E6%9C%89%20filter%3C%2Fspan%3E%3C%2Fspan%3E%3Cspan%3E%EF%BC%8C%E6%89%80%E4%BB%A5%E4%B8%BA%20nil%3C%2Fspan%3E%E3%80%82%3C%2Fspan%3E%3C%2Ftd%3E%3C%2Ftr%3E%3C%2Ftbody%3E%3C%2Ftable%3E%22%2C%22margin%22%3Afalse%2C%22hideBorder%22%3Afalse%2C%22id%22%3A%22ZczYL%22%7D" id="div-8ww-y9e-x7w" data-tag="div" class="div"><table data-tag="table" id="table-wru-g7l-tvj" class="table"><colgroup span="1" width="139" data-tag="col" id="col-8o9-opb-dr0" colwidth="1*" colnum="1" colname="col1" style="width:50%" class="col"></colgroup><colgroup span="1" width="609" data-tag="col" id="col-cin-b32-gfz" colwidth="1*" colnum="2" colname="col2" style="width:50%" class="col"></colgroup><thead id="thead-rhu-v8c-u9y" class="thead"><tr id="tr-o01-zem-u8v"><th id="td-8vj-u6h-4hd"><p id="p-pia-ebo-beh"><b>信息名称</b></p></th><th id="td-rd0-8w2-4c2"><p id="p-cnp-wue-har"><b>含义</b></p></th></tr></thead><tbody data-tag="tbody" id="tbody-k74-vgb-j0u" class="tbody"><tr data-tag="tr" id="tr-k4e-a3d-tz5" class="tr"><td data-tag="td" id="td-g0t-mtw-wvi" class="td"><p id="p-04x-c0x-nfk">output</p></td><td data-tag="td" id="td-vuf-3m2-055" class="td"><p id="p-njy-6jy-xtr">该算子的输出表达式。</p><p id="p-xnh-9yq-gii">使用 INTERSECT 联接的两个子算子对应输出，即表示交集运算输出结果中的一列，括号内部为左右子节点对应此列的输出列。</p></td></tr><tr data-tag="tr" id="tr-i31-32i-k07" class="tr"><td data-tag="td" id="td-844-1kv-kpa" class="td"><p id="p-pti-9gg-c1j">filter</p></td><td data-tag="td" id="td-tg2-zlh-try" class="td"><p id="p-z2n-n5m-5pv">该算子上的过滤条件。</p><p id="p-vrd-nhg-kqe">由于示例中 INTERSECT 算子没有设置 filter，所以为 nil。</p></td></tr></tbody></table></div> ## HASH INTERSECT DISTINCT 如下例所示，Q2 对两个查询使用 INTERSECT 进行联接，不可利用排序，0 号算子使用 HASH INTERSECT DISTINCT 进行求取交集、去重。算子执行时先读取一侧子节点输出建立哈希表并去重，再读取另一侧子节点利用哈希表求取交集并去重。 ~~~ Q2: obclient>EXPLAIN SELECT c2 FROM t1 INTERSECT SELECT c2 FROM t1\G; *************************** 1. row *************************** Query Plan: ================================================ |ID|OPERATOR |NAME|EST. ROWS|COST| ------------------------------------------------ |0 |HASH INTERSECT DISTINCT| |2 |77 | |1 | TABLE SCAN |T1 |2 |37 | |2 | TABLE SCAN |T1 |2 |37 | ================================================ Outputs & filters: ------------------------------------- 0 - output([INTERSECT(T1.C2, T1.C2)]), filter(nil) 1 - output([T1.C2]), filter(nil), access([T1.C2]), partitions(p0) 2 - output([T1.C2]), filter(nil), access([T1.C2]), partitions(p0) ~~~ 上述示例的执行计划展示中的 outputs & filters 详细列出了 HASH INTERSECT DISTINCT 算子的输出信息，字段的含义与 MERGE INTERSECT DISTINCT 算子相同。