MySQL中的semi-join

浅浅的花香味﹌ 2021-06-10 20:39 327阅读 0赞

[http://www.linuxidc.com/Linux/2015-05/117523.htm][http_www.linuxidc.com_Linux_2015-05_117523.htm]

[https://cloud.tencent.com/developer/article/1427702][https_cloud.tencent.com_developer_article_1427702]

1. 背景介绍  
什么是semi-join？  
所谓的semi-join是指semi-join子查询。 当一张表在另一张表找到匹配的记录之后，半连接（semi-jion）返回第一张表中的记录。与条件连接相反，即使在右节点中找到几条匹配的记录，左节点 的表也只会返回一条记录。另外，右节点的表一条记录也不会返回。半连接通常使用IN  或 EXISTS 作为连接条件。 该子查询具有如下结构：

SELECT ... FROM outer_tables WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...

即在where条件的“IN”中的那个子查询。  
这种查询的特点是我们只关心outer\_table中与semi-join相匹配的记录。  
换句话说，最后的结果集是在outer\_tables中的，而semi-join的作用只是对outer\_tables中的记录进行筛选。这也是我们进行 semi-join优化的基础，即我们只需要从semi-join中获取到最少量的足以对outer\_tables记录进行筛选的信息就足够了。  
所谓的最少量，体现到优化策略上就是如何去重。  
以如下语句为例：

select * from Country 
    where 
      Country.Code in 
    (select City.country 
                       from City 
                       where City.Population>1*1000*1000);

当中的semi-join： “

select City.country 
                       from City 
                       where City.Population>1*1000*1000

” 可能返回的结果集如下： China(Beijin), China(Shanghai), France(Paris)...  
我们可以看到这里有2个China，分别来至2条城市记录Beijin和Shanghai, 但实际上我们只需要1个China就足够对outer\_table

Country进行筛选了。所以我们需要去重。  
  
2. Mysql支持的Semi-join策略

Mysql支持的semi-join策略主要有5个，它们分别为：  
1. DuplicateWeedout: 使用临时表对semi-join产生的结果集去重。

Duplicate Weedout: Run the semi-join as if it was a join and remove duplicate records using a temporary table.  
![format_png][]

对应的匹配条件为：  
![format_png 1][]

2. FirstMatch: 只选用内部表的第1条与外表匹配的记录。

FirstMatch: When scanning the inner tables for row combinations and there are multiple instances of a given value group, choose one rather than returning them all. This "shortcuts" scanning and eliminates production of unnecessary rows.  
![format_png 2][]

对应的匹配条件为：  
![format_png 3][]  
  
3. LooseScan: 把inner-table数据基于索引进行分组，取每组第一条数据进行匹配。

LooseScan: Scan a subquery table using an index that enables a single value to be chosen from each subquery's value group.  
![format_png 4][]

对应的匹配条件为：  
![format_png 5][]

4. Materializelookup: 将inner-table去重固化成临时表，遍历outer-table，然后在固化表上去寻找匹配。

Materialize the subquery into a temporary table with an index and use the temporary table to perform a join. The index is used to remove duplicates. The index might also be used later for lookups when joining the temporary table with the outer tables; if not, the table is scanned.  
对应的匹配条件：  
![format_png 6][]

5. MaterializeScan: 将inner-table去重固化成临时表，遍历固化表，然后在outer-table上寻找匹配。  
![format_png 7][]

对应的条件：  
![format_png 8][]

Each of these strategies except Duplicate Weedout can be enabled or disabled using the [`optimizer_switch`][optimizer_switch] system variable. The `semijoin` flag controls whether semi-joins are used. If it is set to `on`, the `firstmatch`, `loosescan`, and `materialization` flags enable finer control over the permitted semi-join strategies. These flags are `on` by default.

The use of semi-join strategies is indicated in [`EXPLAIN`][EXPLAIN] output as follows:

*  Semi-joined tables show up in the outer select. [`EXPLAIN EXTENDED`][EXPLAIN EXTENDED] plus [`SHOW WARNINGS`][SHOW WARNINGS] shows the rewritten query, which displays the semi-join structure. From this you can get an idea about which tables were pulled out of the semi-join. If a subquery was converted to a semi-join, you will see that the subquery predicate is gone and its tables and `WHERE` clause were merged into the outer query join list and `WHERE` clause.
 *  Temporary table use for Duplicate Weedout is indicated by `Start temporary` and `End temporary`in the `Extra` column. Tables that were not pulled out and are in the range of [`EXPLAIN`][EXPLAIN] output rows covered by `Start temporary` and `End temporary` will have their `rowid` in the temporary table.
 *  `FirstMatch(tbl_name)` in the `Extra` column indicates join shortcutting.
 *  `LooseScan(m..n)` in the `Extra` column indicates use of the LooseScan strategy. *`m`* and *`n`* are key part numbers.

*  As of MySQL 5.6.7, temporary table use for materialization is indicated by rows with a `select_type` value of `MATERIALIZED` and rows with a `table` value of `<subqueryN>`.
    
    Before MySQL 5.6.7, temporary table use for materialization is indicated in the `Extra` column by`Materialize` if a single table is used, or by `Start materialize` and `End materialize` if multiple tables are used. If `Scan` is present, no temporary table index is used for table reads. Otherwise, an index lookup is used.

mysql> SELECT @@optimizer\_switch\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
@@optimizer\_switch: index\_merge=on,index\_merge\_union=on,  
                    index\_merge\_sort\_union=on,  
                    index\_merge\_intersection=on,  
                    engine\_condition\_pushdown=on,  
                    index\_condition\_pushdown=on,  
                    mrr=on,mrr\_cost\_based=on,  
                    block\_nested\_loop=on,batched\_key\_access=off,  
                    materialization=on,semijoin=on,loosescan=on,  
                    firstmatch=on,  
                    subquery\_materialization\_cost\_based=on,  
                    use\_index\_extensions=on

[http_www.linuxidc.com_Linux_2015-05_117523.htm]: http://www.linuxidc.com/Linux/2015-05/117523.htm
[https_cloud.tencent.com_developer_article_1427702]: https://cloud.tencent.com/developer/article/1427702
[format_png]: /images/20210527/b7a6a682a6364d5fb145c4b933f9cab7.png
[format_png 1]: /images/20210527/266096b559284eb19fd842ab0555e069.png
[format_png 2]: /images/20210527/94b876bdae0b4b0fbc786a98e87a15a4.png
[format_png 3]: /images/20210527/2d9566e7de354d3c88d05f523e6f5354.png
[format_png 4]: /images/20210527/a2f1b52d55ba4361be659bcd39256840.png
[format_png 5]: /images/20210527/6b22a92f26ec404cbc4c7ce3069823bd.png
[format_png 6]: /images/20210527/e58b988d212a4dbd9d56327185e14867.png
[format_png 7]: /images/20210527/a984b652f5dd4e15863f68699c7fb53f.png
[format_png 8]: /images/20210527/7a7e9313712045498f8ae75a1016f82d.png
[optimizer_switch]: http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_optimizer_switch
[EXPLAIN]: http://dev.mysql.com/doc/refman/5.6/en/explain.html
[EXPLAIN EXTENDED]: http://dev.mysql.com/doc/refman/5.6/en/explain-extended.html
[SHOW WARNINGS]: http://dev.mysql.com/doc/refman/5.6/en/show-warnings.html