从查询执行计划看，子查询存在，MySQL没有进一步做子查询的优化工作。
示例三，MySQL支持对IN类型的子查询的优化，按也有不支持的情况存在：
IN非相关子查询，查询执行计划如下：
| mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 IN (SELECT a2 FROM t2 WHERE t2.a2>10); |
| +----+--------------+-------------+------+------+----------------------------------------------------+ | id | select_type | table | type | key | Extra | +----+--------------+-------------+------+------+----------------------------------------------------+ | 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) |
被查询优化器处理后的语句为：
| /* 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)) |
从查询执行计划看，表t2被物化后，与表t1执行了半连接（semi join）。尽管有“subquery2”这样的内容看起来是子查询，但是表t2已经被上拉到表t1层执行了半连接，所以MySQL支持IN子查询优化为半连接操作。
另外一个IN非相关子查询，查询执行计划如下：
| mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 IN (SELECT a2 FROM t2 WHERE t2.a2=10); |
| +----+--------------+-------------+------+------+----------------------------------------------------+ | id | select_type | table | type | key | Extra | +----+--------------+-------------+------+------+----------------------------------------------------+ | 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) |
被查询优化器处理后的语句为：
| /* 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)) |
从查询执行计划看，子查询不存在，表t1和t2直接做了块嵌套循环半连接（Block Nested Loop），把子查询上拉到父查询中用嵌套循环半连接完成IN操作。另外，由于子查询上拉，使得增加连接条件“a1=a2”，而原先的条件“a2=10”可以利用常量传递优化技术，使得“a1=a2=10”，所以查询执行计划中，两个索引扫描的条件分别为：a1 = 10、a2 = 10。
