MySQL--Semi-join(半连接)优化策略

Semi-join(半连接)
半连接主要场景：检查一个结果集（外表）的记录是否在另外一个结果集（字表）中存在匹配记录，半连接仅关注”子表是否存在匹配记录”，而并不考虑”子表存在多少条匹配记录”，半连接的返回结果集仅使用外表的数据集，查询语句中IN或EXISTS语句常使用半连接来处理。

MySQL支持5中Semi-join策略:

1、DuplicateWeedout
2、FirstMatch
3、LooseScan
4、Materializelookup
5、MaterializeScan

 

=========================================

DuplicateWeedout

DuplicateWeedout: 使用临时表对semi-join产生的结果集去重。

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

 

=========================================

FirstMatch
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.

 

=========================================

LooseScan
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.

 

=========================================

Materializelookup
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.

 

=========================================

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

 

=========================================

在MySQL中优化器开关optimizer_switch中，以下参数影响Semi-join的选择：

semijoin={on|off}
materialization={on|off}
loosescan={on|off}
subquery_materialization_cost_based={on|off}

 

=========================================

总结

在SemiJoin中5中优化策略中，影响策略的最关键的因素:

1、inner-table和outer-table上的数据量。

2、inner-table和outer-table上是否有能快速定位数据的索引。