Home>

First, the sub query

1, where type subquery(Take inner query results as comparison conditions for outer query)

#Without order by to check the latest products
select goods_id, goods_name from goods where goods_id=(select max (goods_id) from goods);
#Remove the latest products under each section (goods_id unique)
select cat_id, goods_id, goods_name from goods where goods_id in (select max (goods_id) from goods group by cat_id);

2, from type subquery(The inner query results are provided for the outer query again)

#Use sub-queries to find out the average scores of students with two or more hanging subjects

Idea:

#Find out which students have more than two subjects
select name, count (*) as gk from stu where score<60 having gk>= 2;
#The above query results,We just need the name,So take the name again
select name from (select name, count (*) as gk from stu having gk>= 2) as t;
#Find out these classmates,Then calculate their average score
select name, avg (score) from stu where name in (select name from (select name, count (*) as gk from stu having gk>= 2) as t) group by name;

3, exists type query(Take the outer query results to the inner layer,See if the inner query is valid)

#Query which sections have products,Column table category, product table goods
select cat_id, cat_name from category where exists (select * from goods where goods.cat_id=category.cat_id);

OptimizationFrom the form of a sentence,Subqueries are divided into special format subqueries and non-special format subqueries.Special format subqueries include subqueries of type in, all, any, some, exists, etc.For some types of subqueries,MySQL supports optimization,Some do n’t support it,Details are as follows.

Example 1:mysql does not support the optimization of exists subqueries:

correlated subqueries of type exists,The query execution plan is as follows:

mysql>explain extended select * from t1 where exists (select 1 from t2 where t1.a1=t2.a2 and t2.a2>10);
+ ---- + -------------------- + ------- + ------ + ------ +- ------------ +
| id | select_type | table | type | key |
+ ---- + -------------------- + ------- + ------ + ------ +- ------------ +
| 1 | primary | t1 | all | null | using where |
| 2 | dependent subquery | t2 | all | null | using where |
+ ---- + -------------------- + ------- + ------ + ------ +- ------------ +
2 rows in set, 2 warnings (0.00 sec)

The statement processed by the query optimizer is:

/* select #1 */select `test`.`t1`.`id1` as` id1`, `test`.`t1`.`a1` as` a1`,  `test`.`t1`.`b1` as` b1`
from `test`.`t1`
where exists (/* select #2 * /
  select 1
  from `test`.`t2`
  where ((`test`.`t1`.`a1` =` test`.`t2`.`a2`) and (`test`.`t2`.`a2`&10))
)

From the query execution plan,The subquery exists,MySQL did not further optimize the subquery.

Another correlated subquery of exists type,The query execution plan is as follows:

mysql>explain extended select * from t1 where exists (select 1 from t2 where t1.b1=t2.b2 and t1.a1=10);
+ ---- + -------------------- + ------- + ------ + ------ +- ------------ +
| id | select_type | table | type | key |
+ ---- + -------------------- + ------- + ------ + ------ +- ------------ +
| 1 | primary | t1 | all | null | using where |
| 2 | dependent subquery | t2 | all | null | using where |
+ ---- + -------------------- + ------- + ------ + ------ +- ------------ +
2 rows in set, 3 warnings (0.02 sec)

The statement processed by the query optimizer is:

/* select #1 */select `test`.`t1`.`id1` as` id1`, `test`.`t1`.`a1` as` a1`,  `test`.`t1`.`b1` as` b1`
from `test`.`t1`
where exists (/* select #2 * /
  select 1
  from `test`.`t2`
  where ((`test`.`t1`.`b1` =` test`.`t2`.`b2`) and (`test`.`t1`.`a1`=10))
)

From the query execution plan,The subquery exists,MySQL did not further optimize the subquery.

Example two, mysql does not support optimization of subqueries of type not exists:

related subqueries of type not exists,The query execution plan is as follows:

mysql>explain extended select * from t1 where not exists (select 1 from t2 where t1.a1=t2.a2 and t2.a2>10);
+ ---- + -------------------- + ------- + ------ + ------ +- ------------ +
| id | select_type | table | type | key |
+ ---- + -------------------- + ------- + ------ + ------ +- ------------ +
| 1 | primary | t1 | all | null | using where |
| 2 | dependent subquery | t2 | all | null | using where |
+ ---- + -------------------- + ------- + ------ + ------ +- ------------ +
2 rows in set, 2 warnings (0.00 sec)

The statement processed by the query optimizer is:

/* select #1 */select `test`.`t1`.`id1` as` id1`, `test`.`t1`.`a1` as` a1`,  `test`.`t1`.`b1` as` b1`
from `test`.`t1`
where (not (exists (
  /* select #2 */select 1
  from `test`.`t2`
  where ((`test`.`t1`.`a1` =` test`.`t2`.`a2`) and (`test`.`t2`.`a2`&10))))
)

From the query execution plan,The subquery exists,MySQL did not further optimize the subquery.

Another related subquery of type not exists,The query execution plan is as follows:

mysql>explain extended select * from t1 where not exists (select 1 from t2 where t1.b1=t2.b2 and t1.a1=10);
+ ---- + -------------------- + ------- + ------ + ------ +- ------------ +
| id | select_type | table | type | key |
+ ---- + -------------------- + ------- + ------ + ------ +- ------------ +
| 1 | primary | t1 | all | null | using where |
| 2 | dependent subquery | t2 | all | null | using where |
+ ---- + -------------------- + ------- + ------ + ------ +- ------------ +
2 rows in set, 3 warnings (0.00 sec)

The statement processed by the query optimizer is:

/* select #1 */select `test`.`t1`.`id1` as` id1`, `test`.`t1`.`a1` as` a1`,  `test`.`t1`.`b1` as` b1`
from `test`.`t1`
where (not (exists (
  /* select #2 */select 1
  from `test`.`t2`
  where ((`test`.`t1`.`b1` =` test`.`t2`.`b2`) and (`test`.`t1`.`a1`=10))))
)

From the query execution plan,The subquery exists,MySQL did not further optimize the subquery.

Example 3:MySQL supports optimization of subqueries of type in.There are also unsupported situations:

in uncorrelated subquery,The query execution plan is as follows:

mysql>explain extended select * from t1 where t1.a1 in (select a2 from t2 where t2.a2>10);
+ ---- + -------------- + ------------- + ------ + ------ +- -------------------------------------------------- -+
| id | select_type | table | type | key |
+ ---- + -------------- + ------------- + ------ + ------ +- -------------------------------------------------- -+
| 1 | simple |<subquery2>| all | null | null |
| 1 | simple | t1 | all | null | using where;using join buffer (block nested loop) |
| 2 | materialized | t2 | all | null | using where |
+ ---- + -------------- + ------------- + ------ + ------ +- -------------------------------------------------- -+
3 rows in set, 1 warning (0.00 sec)

The statement processed by the query optimizer is:

/* select #1 */select `test`.`t1`.`id1` as` id1`, `test`.`t1`.`a1` as` a1`,  `test`.`t1`.`b1` as` b1`
from `test`.`t1` semi join (` test`.`t2`)
where ((`test`.`t1`.`a1` =`<subquery2>`.`a2`) and (` test`.`t2`.`a2`>10))

From the query execution plan,Table t2 is materialized,A semi join was performed with table t1. Although something like "subquery2" looks like a subquery,But table t2 has been pulled up to table t1 to perform a semi-join,So mysql supports in subquery optimization for semi-join operation.

Another in uncorrelated subquery,The query execution plan is as follows:

mysql>explain extended select * from t1 where t1.a1 in (select a2 from t2 where t2.a2=10);
+ ---- + -------------- + ------------- + ------ + ------ +- -------------------------------------------------- -+
| id | select_type | table | type | key |
+ ---- + -------------- + ------------- + ------ + ------ +- -------------------------------------------------- -+
| 1 | simple |<subquery2>| all | null | using where |
| 1 | simple | t1 | all | null | using where;using join buffer (block nested loop) |
| 2 | materialized | t2 | all | null | using where |
+ ---- + -------------- + ------------- + ------ + ------ +- -------------------------------------------------- -+
3 rows in set, 1 warning (0.02 sec)

The statement processed by the query optimizer is:

/* select #1 */select `test`.`t1`.`id1` as` id1`, `test`.`t1`.`a1` as` a1`,  `test`.`t1`.`b1` as` b1`
from `test`.`t1` semi join (` test`.`t2`)
where ((`<subquery2>` .`a2`=10) and (`test`.`t1`.`a1`=10) and (` test`.`t2`.`a2`=10))

From the query execution plan,The subquery does not exist,Tables t1 and t2 directly make a block nested loop semi-join (block nested loop), pull the subquery to the parent query and use the nested loop semi-join to complete the in operation. In addition, since the subquery is pulled up,So that the connection condition "a1=a2" is increased, and the original condition "a2=10" can use constant pass optimization technologyMakes "a1=a2=10", so in the query execution plan,The conditions for the two index scans are:a1=10 and a2=10.

Another in uncorrelated subquery,The query execution plan is as follows:

mysql>explain extended select * from t1 where t1.a1 in (select a2 from t2 where t1.a1=10);
+ ---- + ------------- + ------- + ------ + --------------- -------------------------------------------------- -+
| id | select_type | table | type | extra |
+ ---- + ------------- + ------- + ------ + --------------- -------------------------------------------------- -+
| 1 | simple | t2 | all | using where;start temporary |
| 1 | simple | t1 | all | using where;end temporary;using join buffer (block nested loop) |
+ ---- + ------------- + ------- + ------ + --------------- -------------------------------------------------- -+
2 rows in set, 2 warnings (0.00 sec)
</pre>
</p>
<p>
The statement processed by the query optimizer is:
</div>
<div>
<pre>
/* select #1 */select `test`.`t1`.`id1` as` id1`, `test`.`t1`.`a1` as` a1`,  `test`.`t1`.`b1` as` b1`
from `test`.`t1` semi join (` test`.`t2`)
where ((`test`.`t2`.`a2`=10) and (` test`.`t1`.`a1`=10))

From the query execution plan,The sub-subquery does not exist,Tables t1 and t2 directly make a block nested loop join (block nested loop), but it is a semi join operation. Pull the subquery to the parent query and complete the in operation with the nested loop semijoin.

Example four, mysql supports optimization of not in type subqueries

not in uncorrelated subquery,The query execution plan is as follows:

mysql>explain extended select * from t1 where t1.a1 not in (select a2 from t2 where t2.a2>10);
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
| id | select_type | table | type | key |
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
| 1 | primary | t1 | all | null | using where |
| 2 | subquery | t2 | all | null | using where |
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
2 rows in set, 1 warning (0.02 sec)

The statement processed by the query optimizer is:

/* select #1 */select `test`.`t1`.`id1` as` id1`, `test`.`t1`.`a1` as` a1`,`test`.`t1`.`b1` as` b1`
from `test`.`t1`
where (not (<in_optimizer>(
  `test`.`t1`.`a1`,` test`.`t1`.`a1` in (
    <materialize>(/* select #2 * /
      select `test`.`t2`.`a2`
      from `test`.`t2`
      where (`test`.`t2`.`a2`&10)
      having 1
    ),    <primary_index_lookup>(
      `test`.`t1`.`a1` in<temporary table>on<auto_key>
      where ((`test`.`t1`.`a1` =` materialized-subquery`.`a2`))
    )
   )
  ))
)

From the query execution plan,Table t2 does a subquery. The subquery is materialized. Therefore, MySQL uses a materialized optimization method for the not in subquery.Elimination of subqueries is not supported.

Another not in uncorrelated subquery,The query execution plan is as follows:

mysql>explain extended select * from t1 where t1.a1 not in (select a2 from t2 where t2.a2=10);
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
| id | select_type | table | type | key |
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
| 1 | primary | t1 | all | null | using where |
| 2 | subquery | t2 | all | null | using where |
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
2 rows in set, 1 warning (0.00 sec)

The statement processed by the query optimizer is:

/* select #1 */select `test`.`t1`.`id1` as` id1`, `test`.`t1`.`a1` as` a1`, `test`.`t1`.`b1 `as` b1`
from `test`.`t1`
where (not (<in_optimizer>(
  `test`.`t1`.`a1`,` test`.`t1`.`a1` in (
    <materialize>(/* select #2 * /
      select `test`.`t2`.`a2`
      from `test`.`t2`
      where (`test`.`t2`.`a2`=10)
      having 1
    ),    <primary_index_lookup>(
      `test`.`t1`.`a1` in<temporary table>on<auto_key>
      where ((`test`.`t1`.`a1` =` materialized-subquery`.`a2`))
    )
  )
  ))
)

From the query execution plan,Table t2 does a subquery. The subquery is materialized. Therefore, MySQL uses a materialized optimization method for the not in subquery.Elimination of subqueries is not supported.

Example five, mysql supports the optimization of all types of subqueries:

For irrelevant all subqueries, the query execution plan is as follows:

mysql>explain extended select * from t1 where t1.a1>all (select a2 from t2 where t2.a2>10);
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
| id | select_type | table | type | key |
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
| 1 | primary | t1 | all | null | using where |
| 2 | subquery | t2 | all | null | using where |
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
2 rows in set, 1 warning (0.00 sec)

The statement processed by the query optimizer is:

/* select #1 */select `test`.`t1`.`id1` as` id1`, `test`.`t1`.`a1` as` a1`, `test`.`t1`.`b1 `as` b1`
from `test`.`t1`
where<not>((`test`.`t1`.`a1`<=<max>(
  /* select #2 * /
  select `test`.`t2`.`a2`
  from `test`.`t2`
  where (`test`.`t2`.`a2`&10)
  )
))

From the query execution plan,A subquery appears, but the subquery is restricted by the "<=" operator,There is a unique index on the queried column a2 in the subquery.So you can use the index to find the maximum value,So mysql supports ">all" subquery optimization,The subquery is executed only once to get the maximum value.

For irrelevant all subqueries, the query execution plan is as follows:

mysql>explain extended select * from t1 where t1.a1=all (select a2 from t2 where t2.a2=10);
+ ---- + -------------------- + ------- + ------ + ------ +- ------------ +
| id | select_type | table | type | key |
+ ---- + -------------------- + ------- + ------ + ------ +- ------------ +
| 1 | primary | t1 | all | null | using where |
| 2 | dependent subquery | t2 | all | null | using where |
+ ---- + -------------------- + ------- + ------ + ------ +- ------------ +
2 rows in set, 1 warning (0.00 sec)

The statement processed by the query optimizer is:

/* select #1 */select `test`.`t1`.`id1` as` id1`, `test`.`t1`.`a1` as` a1`, `test`.`t1`.`b1 `as` b1`
from `test`.`t1`
where<not>(<in_optimizer>(
  `test`.`t1`.`a1`,<exists>(
    /* select #2 */select 1 from `test`.`t2`
    where ((`test`.`t2`.`a2`=10) and
      <if>(outer_field_is_not_null,        ((&Cache>(`test`.`t1`.`a1`)<10) or<cache>(isnull (10))),        true
      )
    )
    having<if>(outer_field_is_not_null,<is_not_null_test>(`test`.`t2`.`a2`), true)
  )
))

From the query execution plan,A subquery appears, but the statement processed by the query optimizer contains "exists", which indicates that mysql optimizes "exists strategy" for "= all" subquery optimizationSo mysql supports "= all" subquery optimization.

For irrelevant all subqueries, the query execution plan is as follows:

mysql>explain extended select * from t1 where t1.a1<all (select a2 from t2 where t2.a2=10);
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
| id | select_type | table | type | key |
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
| 1 | primary | t1 | all | null | using where |
| 2 | subquery | t2 | all | null | using where |
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
2 rows in set, 1 warning (0.00 sec)

The statement processed by the query optimizer is:

/* select #1 */select `test`.`t1`.`id1` as` id1`, `test`.`t1`.`a1` as` a1`, `test`.`t1`.`b1 `as` b1`
from `test`.`t1`
where<not>((`test`.`t1`.`a1`>=<min>
  (/* select #2 * /
    select `test`.`t2`.`a2`
    from `test`.`t2`
    where (`test`.`t2`.`a2`=10)
  )
))

From the query execution plan,A subquery appears, but the subquery is restricted by the ">=<min>" operator,There is a unique index on the queried column a2 in the subquery.So you can use the index to find the maximum value,So mysql supports "

Example six, mysql supports optimization of some types of subqueries:

Subqueries using the ">some" formula are optimized,The query execution plan is as follows:

mysql>explain extended select * from t1 where t1.a1>some (select a2 from t2 where t2.a2>10);
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
| id | select_type | table | type | key |
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
| 1 | primary | t1 | all | null | using where |
| 2 | subquery | t2 | all | null | using where |
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
2 rows in set, 1 warning (0.05 sec)

The statement processed by the query optimizer is:

/* select #1 */select `test`.`t1`.`id1` as` id1`, `test`.`t1`.`a1` as` a1`,   `test`.`t1`.`b1` as` b1`
from `test`.`t1`
where<nop>((`test`.`t1`.`a1`>(
  /* select #2 * /
  select min (`test`.`t2`.`a2`)
  from `test`.`t2`
  where (`test`.`t2`.`a2`&10)
)))

From the query execution plan,A subquery appears, but the subquery is limited by the "min" function,There is a unique index on the queried column a2 in the subquery.So you can use the index to find the maximum value,So mysql supports ">some" subquery optimization,The subquery is executed only once to get the maximum value.

Subqueries using the "= some" formula are optimized,The query execution plan is as follows:

mysql>explain extended select * from t1 where t1.a1=some (select a2 from t2 where t2.a2=10);
+ ---- + ---------- + ------------- + ------ + ------ +- -------------------------------------------------- -+
| id | select_type | table | type | key |
+ ---- + -------------- + ------------- + ------ + ------ +- -------------------------------------------------- -+
| 1 | simple |<subquery2>| all | null | using where |
| 1 | simple | t1 | all | null | using where;using join buffer (block nested loop) |
| 2 | materialized | t2 | all | null | using where |
+ ---- + -------------- + ------------- + ------ + ------ +- -------------------------------------------------- -+
3 rows in set, 1 warning (0.01 sec)

The statement processed by the query optimizer is:

/* select #1 */select `test`.`t1`.`id1` as` id1`, `test`.`t1`.`a1` as` a1`,`test`.`t1`.`b1` as` b1`
from `test`.`t1` semi join (` test`.`t2`)
where ((`<subquery2>` .`a2`=10) and (`test`.`t1`.`a1`=10) and (` test`.`t2`.`a2`=10))

From the query execution plan,No subquery appears,Table t2 is materialized and semi-connected to table t1.

Subqueries that use the "

mysql>explain extended select * from t1 where t1.a1<some (select a2 from t2 where t2.a2=10);
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
| id | select_type | table | type | key |
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
| 1 | primary | t1 | all | null | using where |
| 2 | subquery | t2 | all | null | using where |
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
2 rows in set, 1 warning (0.00 sec)

The statement processed by the query optimizer is:

/* select #1 */select `test`.`t1`.`id1` as` id1`, `test`.`t1`.`a1` as` a1`,  `test`.`t1`.`b1` as` b1`
from `test`.`t1`
where<nop>(
  (
    `test`.`t1`.`a1`<(/* select #2 * /
      select max (`test`.`t2`.`a2`)
      from `test`.`t2`
      where (`test`.`t2`.`a2`=10)
    )
  )
)

From the query execution plan,A subquery appears, but the subquery is limited by the "max" function,There is a unique index on the queried column a2 in the subquery.So you can use the index to find the maximum value,So mysql supports "

Example seven:MySQL supports optimization of any type of subquery:

Subqueries using the ">any" formula are optimized,The query execution plan is as follows:

mysql>explain extended select * from t1 where t1.a1>any (select a2 from t2 where t2.a2>10);
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
| id | select_type | table | type | key |
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
| 1 | primary | t1 | all | null | using where |
| 2 | subquery | t2 | all | null | using where |
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
2 rows in set, 1 warning (0.00 sec)

The statement processed by the query optimizer is:

/* select #1 */select `test`.`t1`.`id1` as` id1`, `test`.`t1`.`a1` as` a1`,  `test`.`t1`.`b1` as` b1`
from `test`.`t1`
where<nop>(
  (
    `test`.`t1`.`a1`>(/* select #2 * /
      select min (`test`.`t2`.`a2`)
      from `test`.`t2`
      where (`test`.`t2`.`a2`&10)
    )
  )
)

From the query execution plan,A subquery appears, but the subquery is limited by the "min" function,There is a unique index on the queried column a2 in the subquery.So you can use the index to find the maximum value,So mysql supports ">any" subquery optimization,The subquery is executed only once to find the minimum value.

Subqueries using the "= any" formula are optimized,The query execution plan is as follows:

mysql>explain extended select * from t1 where t1.a1=any (select a2 from t2 where t2.a2>10);
+ ---- + -------------- + ------------- + ------ + ------ +- -------------------------------------------------- -+
| id | select_type | table | type | key |
+ ---- + -------------- + ------------- + ------ + ------ +- -------------------------------------------------- -+
| 1 | simple |<subquery2>| all | null | null |
| 1 | simple | t1 | all | null | using where;using join buffer (block nested loop) |
| 2 | materialized | t2 | all | null | using where |
+ ---- + -------------- + ------------- + ------ + ------ +- -------------------------------------------------- -+
3 rows in set, 1 warning (0.02 sec)

The statement processed by the query optimizer is:

/* select #1 */select `test`.`t1`.`id1` as` id1`, `test`.`t1`.`a1` as` a1`,  `test`.`t1`.`b1` as` b1`
from `test`.`t1` semi join (` test`.`t2`)
where ((`test`.`t1`.`a1` =`<subquery2>`.`a2`) and (` test`.`t2`.`a2`>10))

From the query execution plan,No subquery appears,Table t2 is materialized and semi-connected to table t1.

Subqueries using the "

mysql>explain extended select * from t1 where t1.a1<any (select a2 from t2 where t2.a2>10);
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
| id | select_type | table | type | key |
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
| 1 | primary | t1 | all | null | using where |
| 2 | subquery | t2 | all | null | using where |
+ ---- + ------------- + ------- + ------ + ------ + -------- ----- +
2 rows in set, 1 warning (0.00 sec)

The statement processed by the query optimizer is:

/* select #1 */select `test`.`t1`.`id1` as` id1`, `test`.`t1`.`a1` as` a1`,  `test`.`t1`.`b1` as` b1`
from `test`.`t1`
where<nop>(
  (
    `test`.`t1`.`a1`<(/* select #2 * /
      select max (`test`.`t2`.`a2`)
      from `test`.`t2`
      where (`test`.`t2`.`a2`&10)
    )
  )
)

From the query execution plan,A subquery appears, but the subquery is limited by the "max" function,There is a unique index on the queried column a2 in the subquery.So you can use the index to find the maximum value,So mysql supports "

  • Previous Learn strict mode of javascript with me
  • Next Summary of problems with datasets in javascript