/*
注意先把优化器关了
*/
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
*/
EXPLAIN
SELECT *
FROM contents;
/*
PRIMARY
UNION
UNION RESULT
*/
EXPLAIN
SELECT * FROM contents
UNION
SELECT * FROM contents;
/*
PRIMARY
DEPENDENT SUBQUERY
DEPENDENT UNION
UNION RESULT
*/
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
*/
EXPLAIN
SELECT c.*, (SELECT post_id FROM contents WHERE post_id = 2)
FROM contents c;
/*
PRIMARY
DEPENDENT SUBQUERY
*/
EXPLAIN
SELECT c.*
FROM contents c
WHERE c.post_id IN (SELECT p.id FROM posts p);
/*
DERIVED 出现在 FROM 子句中的子查询
*/
EXPLAIN
SELECT c.*
FROM (SELECT * FROM contents) c;