- 牛云
-
所谓的semi-join是指semi-join子查询。 当一张表在另一张表找到匹配的记录之后,半连接(semi-jion)返回第一张表中的记录。
与条件连接相反,即使在右节点中找到几条匹配的记录,左节点 的表也只会返回一条记录。另外,右节点的表一条记录也不会返回。半连接通常使用IN 或 EXISTS 作为连接条件。 该子查询具有如下结构:
SELECT ... FROM outer_tables WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...
这种查询的特点是我们只关心outer_table中与semi-join相匹配的记录。
换句话说,最后的结果集是在outer_tables中的,而semi-join的作用只是对outer_tables中的记录进行筛选。这也是我们进行 semi-join优化的基础,即我们只需要从semi-join中获取到最少量的足以对outer_tables记录进行筛选的信息就足够了。
所谓的最少量,体现到优化策略上就是如何去重。
以如下语句为例:
可能返回的结果集如下: China(Beijin), China(Shanghai), France(Paris)...
我们可以看到这里有2个China,分别来至2条城市记录Beijin和Shanghai, 但实际上我们只需要1个China就足够对outer_table
Country进行筛选了。所以我们需要去重。
Mysql支持的semi-join策略主要有5个,它们分别为:
直接转为join
Convert the subquery to a join, or use table pullout and run the query as an inner join between subquery tables and outer tables. Table pullout pulls a table out from the subquery to the outer query.
只选用内部表的第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.
把inner-table数据基于索引进行分组,取每组第一条数据进行匹配。
LooseScan: Scan a subquery table using an index that enables a single value to be chosen from each subquery"s value group.
使用临时表对semi-join产生的结果集去重。
Duplicate Weedout: Run the semijoin as if it was a join and remove duplicate records using a temporary table.
Using index; Start temporary
Using where
Using index; End temporary
Start temporary, End temporary
表示半连接中使用了DuplicateWeedout策略的临时表
将inner-table去重固化成临时表,遍历固化表,然后在outer-table上寻找匹配。
Materialize the subquery into an indexed temporary table that is used to perform a join, where 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. For more information about materialization, see Section 8.2.2.2, “Optimizing Subqueries with Materialization”.
mysql> SELECT @@optimizer_switchG
*************************** 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
下列join查询可能出现重复
However, the result lists each class once for each enrolled student. For the question being asked, this is unnecessary duplication of information.
但是,结果会为每个注册的学生列出一次每个班级。对于正在问的问题,这是不必要的信息重复。
Assuming that class_num is a primary key in the class table, duplicate suppression is possible by using SELECT DISTINCT, but it is inefficient to generate all matching rows first only to eliminate duplicates later.
The same duplicate-free result can be obtained by using a subquery:
假设class_num是class表中的主键,通过使用SELECT DISTINCT可以抑制重复,但是先生成所有匹配的行,然后再消除重复是低效的。
使用子查询可以获得相同的无重复结果:
在这里,优化器可以认识到IN子句要求子查询只从花名册表中返回每个类号的一个实例。在这种情况下,查询可以使用半连接;也就是说,只返回类中与花名册中的行匹配的每一行的一个实例的操作。
包含EXISTS子查询谓词的以下语句相当于包含IN子查询谓词的前一条语句:
有些join会出现重复,我们第一时间想到的是使用DISTINCT去重。但是这里是先join后去重,效率低。
我们完全可以先去重后join。
so,使用in或EXISTS子查询的Semijoin的方式可以优化之
1、使用join distinct
2、使用Semijoin ,d表出现 FirstMatch(a)