MySQL Semi Join optimization subquery

Test environment

Mysql> descclass;+------------+-------------+------+-----+---------+-------+|Field|Type| Null | Key | Default |Extra|+------------+-------------+------+-----+---------+-------+|Class_num| int( One)|NO|Pri| NULL    |       ||Class_name| varchar( -)|YES|     | NULL    |       |+------------+-------------+------+-----+---------+-------+2Rowsinch Set(0.00sec) MySQL> descroster;+-------------+---------+------+-----+---------+-------+|Field|Type| Null | Key | Default |Extra|+-------------+---------+------+-----+---------+-------+|Class_num| int( One)|YES|     | NULL    |       ||Student_num| int( One)|YES|     | NULL    |       |+-------------+---------+------+-----+---------+-------+2Rowsinch Set(0.00Sec

The Roster table records the student's number and the corresponding classroom, multiple students may be in the same classroom, so the field Class_num has a duplicate value

Class table is recorded in the classroom and corresponding class name, field Class_num is a unique value

If you want to find out what classes exist for students

Mysql>  SELECTClass.class_num, Class.class_name fromClassINNER JOINRosterWHEREClass.class_num=Roster.class_num; +-----------+------------+|Class_num|Class_name|+-----------+------------+|         2 |Class2    ||         3 |Class3    ||         3 |Class3    |+-----------+------------+3Rowsinch Set(0.00sec)

Duplicate values can be removed through distinct, but this affects performance, so results are obtained through subqueries

Mysql>  SELECTClass_num, Class_name fromClassWHEREClass_numinch(SELECTClass_num fromroster); +-----------+------------+|Class_num|Class_name|+-----------+------------+|         2 |Class2    ||         3 |Class3    |+-----------+------------+2Rowsinch Set(0.00Sec

The optimizer actually overwrites the subquery for a semi-join

Mysql>ExplainSELECTClass_num, Class_name fromClassWHEREClass_numinch(SELECTClass_num fromroster);+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--- ----------------------------------------------------------------+|Id|Select_type| Table  |Partitions|Type|Possible_keys| Key  |Key_len|Ref|Rows|Filtered|Extra|+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--- ----------------------------------------------------------------+|  1 |Simple|Roster| NULL       |  All  | NULL          | NULL | NULL    | NULL |    3 |   100.00 |StartTemporary                                                   ||  1 |Simple|Class| NULL       |  All  | PRIMARY       | NULL | NULL    | NULL |    4 |    25.00 |Usingwhere;End Temporary; UsingJoinBuffer (Block Nested Loop)|+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--- ----------------------------------------------------------------+2Rowsinch Set,1Warning (0.00sec) MySQL>show warnings;+-------+------+---------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- -----------------+|  Level |Code|Message|+-------+------+---------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- -----------------+|Note| 1003 | /*select#1*/ Select' Test '. ' class '. ' Class_num ' as' Class_num ', ' Test '. ' class '. ' Class_name ' as' Class_name ' from' Test '. ' Class ' semiJoin(' Test '. ' roster ')where(' Test '. ' class '. ' Class_num ')=' Test '. ' roster '. ' Class_num ')|+-------+------+---------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- -----------------+1Rowinch Set(0.00Sec

Use of temporary indicates that temporary tables are used to remove duplicate values
<subqueryN> 则表明临时表用于了物化表

select_typeMATERIALIZEDtable<subqueryN>.

If the subquery conforms to the criteria (ref: Http://dev.mysql.com/doc/refman/5.7/en/subquery-optimization.html#semi-joins), MySQL translates it into semi-join and makes cost-based choices from the following strategies

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

• Duplicate Weedout:run the Semi-join as if it was a join and remove Duplicate records using a temporary table.

• Firstmatch:when scanning the inner tables for row combinations and there is multiple instances of a given value group, C Hoose one rather than returning them all. This "shortcuts" scanning and eliminates production of unnecessary rows.

• Loosescan:scan a subquery table using an index, enables a single value to is chosen from each subquery ' s value group.

• 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 is used later for lookups when joining the temporary table with the outer tables; If not, the table is scanned

The contents of the content of the original document are not dared to be translated freely.

The optimizer_switch semi join tag in the system variable controls whether a half connection is available, 5.6 is on by default

MySQL Semi Join optimization subquery