Subquery子查询指一个查询语句嵌套在另一个查询语句内部的查询。子查询可以作为查询条件的一部分，并提供条件筛选项或计算项。可以说，子查询是一种实用的嵌套查询，可以嵌套多层，查询更加灵活。但需注意，因MySQL是索引组织表特性，当子查询生成新结果集时，绝大部分场景下，本身表索引就会失效。子查询过多会变得过于复杂。
在MYSQL当中，子查询的外部语句可以是SELECT、INSERT、UPDATE、DELETE、SET或DO中的任意一个。语法如下：
SELECT ... FROM outer_tables WHERE expr IN (SELECT ... FROM inner_tables ...)
子查询的主要优点如下:
- 允许结构化查询，以便可以隔离语句的每个部分。
- 子查询是复杂的多表关联和联合查询的替代方案。
- 在比较复杂的连接或联合查询中，子查询更具可读性。
对于SQL语句中出现子查询时，optimize优化器目前提供3种处理方案：
- 半连接Semijoin:通过更效率的方式比较两个表并返回满足特定条件的行。
- 物化Materialization:把子查询结果物化成临时表,然后代入到外查询中进行查找,来加快查询的执行速度
- EXISTS策略：是否存在结果为True，False
subquery_to_derived：子查询转换
从MySQL 8.0.21开始，优化器在很多情况下能够将SELECT、WHERE、JOIN或HAVING子句中的标量Subquery子查询转换为派生表上的左外连接。(根据派生表的可空性，这有时可以进一步简化为内部连接。)这可以为满足以下条件的子查询完成：
- 子查询使用一个或多个聚合函数，但不使用GROUP BY。(实际可以使用)
- 不使用任何不确定性函数，比如RAND()。
- 子查询不是ANY或ALL子查询，可以重写为使用MIN()或MAX()。
- 父查询不设置用户变量，因为重写它可能会影响执行顺序，如果在同一查询中多次访问该变量，可能会导致意想不到的结果。
- 子查询不应该是相关。也就是说，它不应该在外部查询中引用表中的列，或者包含在外部查询中求值的聚合。
下面通过示例，了解下subquery_to_derived。
示例
1）子语句中聚合操作引起的索引失效情况。
表结构如下：
CREATE TABLE `employees` (
`emp_no` int NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) ,
`last_name` varchar(16) ,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`),
KEY `idx_lname` (`last_name`)
) ENGINE=InnoDB
#query1：
mysql> EXPLAIN SELECT emp_no FROM employees WHERE emp_no IN
(SELECT max(emp_no) FROM employees WHERE last_name IN ('Facello','Simmel') );
#query2：
mysql> EXPLAIN SELECT max(emp_no) FROM employees WHERE emp_no IN
(SELECT emp_no FROM employees WHERE last_name IN ('Facello','Simmel') );
两条语句的执行计划如下：
通过EXPLIAN 可以看到query1总扫描29w行，query2总扫描几百行。返回的值加了聚合操作（MAX，MIN，COUNT，SUM）执行效率差距非常大。
查看Warnings信息，发下内不能使用 on 内部临时表进行自动排序，至此导致索引失效。
mysql> SHOW WARNINGS;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `employees`.`employees`.`emp_no` AS `emp_no` from `employees`.`employees` where
(`employees`.`employees`.`emp_no`,`employees`.`employees`.`emp_no` in
( (/* select#2 */ select max(`employees`.`employees`.`emp_no`) from `employees`.`employees` where
(`employees`.`employees`.`last_name` in ('Facello','Simmel')) having true ),
(`employees`.`employees`.`emp_no` in
on
where ((`employees`.`employees`.`emp_no` = ``.`max(emp_no)`)))))
在optimizer_switch参数中subquery_to_derived默认是off,下面更改subquery_to_derived改成on之后，再次查看下执行计划。
mysql>> show variables like '%optimizer_switch%'G;
*************************** 1. row ***************************
Variable_name: optimizer_switch
Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,
subquery_to_derived=off,
prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
mysql> SET SESSION optimizer_switch="subquery_to_derived=ON";
Query OK, 0 rows affected (0.00 sec)
mysql> EXPLAIN SELECT * FROM employees WHERE emp_no IN
(SELECT max(emp_no) FROM employees WHERE last_name IN ('Facello','Simmel') GROUP BY last_name );
query1的执行计划总扫描行数355。先通过idx_lname进行范围查询，之后通过max函数对得到的结构及进行获取。最后匹配employees表。
在官方Release Notes提示：
The subquery_to_derived flag is set to off by default, since it generally does not improve performance, and its intended use for the most part is for testing purposes.
在默认情况下，subquery_to_derived标志设置为off，因为它通常不会提高性能，而且它的主要用途是用于测试。
下面是反向示例：
总结
对于subquery_to_derived子查询转换场景，目前没有很好算法能准确评估。对于上诉示例中的SQL语句。建议改成join 或则 临时表方式。目前MySQL尽量避免复杂语句。
