什么是 Semi-join
常规联接中，结果可能会出现重复值，而子查询可以获得无重复的结果。比如需要找出有人口大于 2000 万的城市的国家，如果用普通联接，则可能出现重复结果：
select country.* from country join city on country.code=city.country_code and population>20000000;
+---------+----------+
| code | name |
+---------+----------+
| 1 | china |
| 1 | china |
+---------+----------+
2 rows in set (0.00 sec)
而子查询则不会：
select * from country where code in
(select country_code from city where population>20000000);
+------+---------+
| code | name |
+------+---------+
| 1 | china |
+------+---------+
1 row in set (0.00 sec)
在子查询中，优化器可以识别出 in 子句中每组只需要返回一个值，在这种情况下，可以使用半联接 Semi-join 来优化子查询，提升查询效率。
Semi-join 限制
不过并不是所有子查询都是半联接，必须满足以下条件：
- 子查询必须是出现在顶层的 WHERE、ON 子句后面的 IN 或者 =ANY
- 子查询必须是单个 select，不能是 union；
- 子查询不能有 group by 或者 having 子句（可以用 semijoin materialization 策略，其他不可以 ）；
- It must not be implicitly grouped (it must contain no aggregate functions). （不知道啥意思，保持原文）；
- 子查询不能有 order by with limit；
- 父查询中不能有 STRAIGHT_JOIN 指定联接顺序；
- The number of outer and inner tables together must be less than the maximum number of tables permitted in a join.
Semi-join 实现策略
子查询可以是相关子查询，如果子查询满足以上条件，MySQL 会将其转换为 semijoin，并从以下的策略中基于成本进行选择其中一种：
- Duplicate Weedout
- FirstMatch
- LooseScan
- Materialize
对应 optimizer_switch 参数中的：
- semijon=ON，控制 semijoin 是否开启的开关
- firstmatch、loosescan、duplicateweedout、materialization 分别是四种策略的开关，默认都是开启的
通过 explain 输出信息可以判断使用了哪种优化策略：
- extra 中出现 Start temporary、End temporary，表示使用了 Duplicate Weedout 策略
- extra 中出现 FirstMatch(tbl_name) ，表示使用了 FirstMatch 策略
- extra 中出现 LooseScan(m…n)，表示使用了 LooseScan 策略
- select_type 列为 MATERIALIZED，以及 table 列为 ，表示使用了 Materialize 策略
接下来介绍 Semi-join Materialization 优化策略。
Semi-join Materialization
Semi-join Materialization 策略就是把子查询结果物化成临时表，再用于 semijoin 的一种特殊的子查询实现，它实际上也可以分为两种策略：
- Materialization-scan
- Materialization-lookup
以下 SQL 为例：
select * from Country
where Country.code IN (select City.Country
from City
where City.Population > 7*1000*1000)
and Country.continent='Europe'
这是一个不相关子查询，查出欧洲有人口超过 700 万的大城市的国家。Semi-join Materialization 优化策略的做法就是：把人口超过 700 万的大城市所在的国家，即 City.Country 字段值填充到一个临时表中，并且 Country 字段为主键（用来去重），然后与 Country 表进行联接：
这个join可以从两个方向进行：
1.从物化表到国家表
2.从国家表到物化表
第一个方向涉及一个全表扫描(在物化表上的全表扫描)，因此被称为"Materialization-scan"
如果从第二个方向进行，最廉价的方式是使用主键从物化表中lookup出匹配的记录。这种方式被称为"Materialization-lookup"。
Materialization-scan
如果我们寻找人口超过700万的城市，优化器将使用materialize-scan，EXPLAIN输出结果也会显示这一点：
MariaDB [world]> explain select * from Country where Country.code IN (select City.Country from City where City.Population > 7*1000*1000);
+----+--------------+-------------+--------+--------------------+------------+---------+--------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-------------+--------+--------------------+------------+---------+--------------------+------+-----------------------+
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 15 | |
| 1 | PRIMARY | Country | eq_ref | PRIMARY | PRIMARY | 3 | world.City.Country | 1 | |
| 2 | MATERIALIZED | City | range | Population,Country | Population | 4 | NULL | 15 | Using index condition |
+----+--------------+-------------+--------+--------------------+------------+---------+--------------------+------+-----------------------+
3 rows in set (0.01 sec)
从上可以看到：
- 仍然有两个select(id=1和id=2）
- 第二个select(id=2)的select_type是MATERIALIZED。这表示会执行并将结果存储在一个在所有列上带有一个唯一性索引的临时表。这个唯一性索引可以避免有重复的记录
- 第一个select中接收到一个名为subquery2的表，这是从第二个select(id=2)获取的物化的表优化器选择在物化的表上执行全表扫描。这就是Materialization-Scan策略的示例。
至于执行成本，我们将从表City读取15行，将15行写入物化表，然后读取它们(优化器假设不会有任何重复)，然后对表Country执行15次eq_ref访问。总共，我们将进行45次读取和15次写入。
相比之下，如果你在MySQL中运行EXPLAIN，你会得到如下结果:
MySQL [world]> explain select * from Country where Country.code IN (select City.Country from City where City.Population > 7*1000*1000);
+----+--------------------+---------+-------+--------------------+------------+---------+------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------+-------+--------------------+------------+---------+------+------+------------------------------------+
| 1 | PRIMARY | Country | ALL | NULL | NULL | NULL | NULL | 239 | Using where |
| 2 | DEPENDENT SUBQUERY | City | range | Population,Country | Population | 4 | NULL | 15 | Using index condition; Using where |
+----+--------------------+---------+-------+--------------------+------------+---------+------+------+------------------------------------+
读的记录是(239 + 239*15) = 3824。
Materialization-Lookup
让我们稍微修改一下查询，看看哪些国家的城市人口超过1百万(而不是7百万):
MariaDB [world]> explain select * from Country where Country.code IN (select City.Country from City where City.Population > 1*1000*1000) ;
+----+--------------+-------------+--------+--------------------+--------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-------------+--------+--------------------+--------------+---------+------+------+-----------------------+
| 1 | PRIMARY | Country | ALL | PRIMARY | NULL | NULL | NULL | 239 | |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 3 | func | 1 | |
| 2 | MATERIALIZED | City | range | Population,Country | Population | 4 | NULL | 238 | Using index condition |
+----+--------------+-------------+--------+--------------------+--------------+---------+------+------+-----------------------+
3 rows in set (0.00 sec)
explain的输出结果和Materialization-scan类似，除了：
- subquery2表是通过eq_ref访问的
- access使用了索引distinct_key
这意味着优化器计划对物化表执行索引查找。换句话说，我们将使用Materialization-lookup策略。
在MySQL中(或者使用optimizer_switch=‘semi-join=off,materialization=off’)，会得到这样的执行计划:
MySQL [world]> explain select * from Country where Country.code IN (select City.Country from City where City.Population > 1*1000*1000) ;
+----+--------------------+---------+----------------+--------------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------+----------------+--------------------+---------+---------+------+------+-------------+
| 1 | PRIMARY | Country | ALL | NULL | NULL | NULL | NULL | 239 | Using where |
| 2 | DEPENDENT SUBQUERY | City | index_subquery | Population,Country | Country | 3 | func | 18 | Using where |
+----+--------------------+---------+----------------+--------------------+---------+---------+------+------+-------------+
可以看出，这两个执行计划都将对国家表进行全面扫描。对于第二步，MariaDB将填充物化表(238行从表City读取并写入临时表)，然后对表Country中的每个记录执行惟一的键查找，结果是238个惟一的键查找。总的来说，第二步将花费(239+238)= 477读取和238 temp.table的写入。
MySQL的第二步计划是使用City上的索引读取18行。它为表国家接收的每个记录的国家。计算出来的成本为(18*239)= 4302读取。如果有更少的子查询调用，这个计划将比物化的计划更好。顺便说一下，MariaDB也可以选择使用这样的查询计划(请参阅FirstMatch策略)，但是它没有选择。
带有group by的子查询
当子查询带有分组的时候，MariaDB可以使用semi-join物化策略(这种场景下，其他semi-join策略不适用)
这允许高效地执行搜索某个组中最佳/最后一个元素的查询。
举个例子，我们来看看每个大陆上人口最多的城市:
explain
select * from City
where City.Population in (select max(City.Population) from City, Country
where City.Country=Country.Code
group by Continent)
+------+--------------+-------------+------+---------------+------------+---------+----------------------------------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-------------+------+---------------+------------+---------+----------------------------------+------+-----------------+
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 239 | |
| 1 | PRIMARY | City | ref | Population | Population | 4 | <subquery2>.max(City.Population) | 1 | |
| 2 | MATERIALIZED | Country | ALL | PRIMARY | NULL | NULL | NULL | 239 | Using temporary |
| 2 | MATERIALIZED | City | ref | Country | Country | 3 | world.Country.Code | 18 | |
+------+--------------+-------------+------+---------------+------------+---------+----------------------------------+------+-----------------+
4 rows in set (0.00 sec)
城市是：
+------+-------------------+---------+------------+
| ID | Name | Country | Population |
+------+-------------------+---------+------------+
| 1024 | Mumbai (Bombay) | IND | 10500000 |
| 3580 | Moscow | RUS | 8389200 |
| 2454 | Macao | MAC | 437500 |
| 608 | Cairo | EGY | 6789479 |
| 2515 | Ciudad de México | MEX | 8591309 |
| 206 | São Paulo | BRA | 9968485 |
| 130 | Sydney | AUS | 3276207 |
+------+-------------------+---------+------------+
Semi-join materialization总结
1.可以用于非相关的in子查询。子查询可以含有分组、和/或聚合函数
2.在explain输出中，子查询会有type=Materialized；父表子查询中有table=<subqueryN>
3.开启需要将变量optimizer_switch中的materialization=on、semijoin=on
4.Non-semijoin materialization与materialization=on|off标记共享
参考
https://blog.csdn.net/ActionTech/article/details/108710418
https://mariadb.com/kb/en/library/semi-join-materialization-strategy/
https://dev.mysql.com/doc/refman/5.7/en/semijoins.html
扫一扫
797
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
