【MYSQL】EXPLAIN执行计划之SELECT_TYPE列

发表于 分类于 阅读次数：

注意先把优化器关了

1
SET SESSION optimizer_switch ='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,duplicateweedout=off,subquery_materialization_cost_based=off,use_index_extensions=off,condition_fanout_filter=off,derived_merge=off,prefer_ordering_index=off';

子查询执行过程

1.从外层查询中取出一个元组（即一行），将元组相关列的值传给内层查询。

2.执行内层查询，得到子查询操作的值。

3.外查询根据子查询返回的结果或结果集得到满足条件的行。

4.然后外层查询取出下一个元组重复做步骤1-3，直到外层的元组全部处理完毕。

SIMPLE

1
2
3
EXPLAIN
SELECT *
FROM contents;

PRIMARY

UNION

UNION RESULT

1
2
3
4
EXPLAIN
SELECT * FROM contents
UNION
SELECT * FROM contents;

PRIMARY

DEPENDENT SUBQUERY

DEPENDENT UNION

UNION RESULT

1
2
3
4
5
6
7
8
EXPLAIN
SELECT *
FROM contents c
WHERE c.post_id in (
SELECT c1.post_id FROM contents c1
UNION
SELECT c2.post_id FROM contents c2
);

PRIMARY

SUBQUERY

1
2
3
EXPLAIN
SELECT c.*, (SELECT post_id FROM contents WHERE post_id = 2)
FROM contents c;

PRIMARY

DEPENDENT SUBQUERY

1
2
3
4
EXPLAIN
SELECT c.*
FROM contents c
WHERE c.post_id IN (SELECT p.id FROM posts p);

DERIVED 出现在 FROM 子句中的子查询

1
2
3
EXPLAIN
SELECT c.*
FROM (SELECT * FROM contents) c;