MySQL的子查詢及相干優化進修教程。本站提示廣大學習愛好者：（MySQL的子查詢及相干優化進修教程）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL的子查詢及相干優化進修教程正文
1、子查詢
1、where型子查詢
（把內層查詢成果看成外層查詢的比擬前提）
#不消order by 來查詢最新的商品 select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);
#掏出每一個欄面前目今最新的產物(goods_id獨一) 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型子查詢
(把內層的查詢成果供外層再次查詢)
#用子查詢查出掛科兩門及以上的同窗的均勻成就
思緒：
#先查出哪些同窗掛科兩門以上 select name,count(*) as gk from stu where score < 60 having gk >=2; #以上查詢成果，我們只需名字便可以了，所以再取一次名字 select name from (select name,count(*) as gk from stu having gk >=2) as t; #找出這些同窗了，那末再盤算他們的均勻分 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型子查詢
（把外層查詢成果拿到內層，看內層的查詢能否成立）
#查詢哪些欄面前目今有商品，欄目表category,商品表goods select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);
2、優化
從句式的情勢看，子查詢分為特別格局子查詢和非特別格局子查詢，特別格局的子查詢中又包含IN、ALL、ANY、SOME、EXISTS等類型的子查詢，關於有的類型的子查詢，MySQL有的支撐優化，有的不支撐，詳細情形以下。
示例一，MySQL不支撐對EXISTS類型的子查詢的優化：
EXISTS類型的相干子查詢，查詢履行籌劃以下：
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 | Extra | +----+--------------------+-------+------+------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | Using where | | 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | Using where | +----+--------------------+-------+------+------+-------------+ 2 rows in set, 2 warnings (0.00 sec)
被查詢優化器處置後的語句為：
/* 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)) )
從查詢履行籌劃看，子查詢存在，MySQL沒有進一步做子查詢的優化任務。
別的的一個EXISTS類型的相干子查詢，查詢履行籌劃以下：
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 | Extra | +----+--------------------+-------+------+------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | Using where | | 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | Using where | +----+--------------------+-------+------+------+-------------+ 2 rows in set, 3 warnings (0.02 sec)
被查詢優化器處置後的語句為：
/* 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)) )
從查詢履行籌劃看，子查詢存在，MySQL沒有進一步做子查詢的優化任務。
示例二，MySQL不支撐對NOT EXISTS類型的子查詢的優化：
NOT EXISTS類型的相干子查詢，查詢履行籌劃以下：
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 | Extra | +----+--------------------+-------+------+------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | Using where | | 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | Using where | +----+--------------------+-------+------+------+-------------+ 2 rows in set, 2 warnings (0.00 sec)
被查詢優化器處置後的語句為：
/* 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)))) )
從查詢履行籌劃看，子查詢存在，MySQL沒有進一步做子查詢的優化任務。
別的的一個NOT EXISTS類型的相干子查詢，查詢履行籌劃以下：
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 | Extra | +----+--------------------+-------+------+------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | Using where | | 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | Using where | +----+--------------------+-------+------+------+-------------+ 2 rows in set, 3 warnings (0.00 sec)
被查詢優化器處置後的語句為：
/* 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)))) )
從查詢履行籌劃看，子查詢存在，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。
別的一個IN非相干子查詢，查詢履行籌劃以下：
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)
/* 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))
從查詢履行籌劃看，子子查詢不存在，表t1和t2直接做了塊嵌套輪回銜接（Block Nested Loop），但屬於半銜接操作（semi join），把子查詢上拉到父查詢頂用嵌套輪回半銜接完成IN操作。
示例四，MySQL支撐對NOT IN類型的子查詢的優化
NOT IN非相干子查詢，查詢履行籌劃以下：
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 | Extra | +----+-------------+-------+------+------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | Using where | | 2 | SUBQUERY | t2 | ALL | NULL | Using where | +----+-------------+-------+------+------+-------------+ 2 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`
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`))
)
)
))
)
從查詢履行籌劃看，表t2做了子查詢（SUBQUERY）。而子查詢被物化（materialize）。所以，MySQL關於NOT IN子查詢采取了物化的優化方法，但不支撐子查詢的清除。
別的一個NOT IN非相干子查詢，查詢履行籌劃以下：
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 | Extra | +----+-------------+-------+------+------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | Using where | | 2 | SUBQUERY | t2 | ALL | NULL | Using where | +----+-------------+-------+------+------+-------------+ 2 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`
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`))
)
)
))
)
從查詢履行籌劃看，表t2做了子查詢（SUBQUERY）。而子查詢被物化（materialize）。所以，MySQL關於NOT IN子查詢采取了物化的優化方法，但不支撐子查詢的清除。
示例五，MySQL支撐對ALL類型的子查詢的優化：
不相干的ALL子查詢，查詢履行籌劃以下：
mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 >ALL (SELECT a2 FROM t2 WHERE t2.a2>10);
+----+-------------+-------+------+------+-------------+ | id | select_type | table | type | key | Extra | +----+-------------+-------+------+------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | Using where | | 2 | SUBQUERY | t2 | ALL | NULL | Using where | +----+-------------+-------+------+------+-------------+ 2 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` where <not>((`test`.`t1`.`a1` <= <max>( /* select#2 */ select `test`.`t2`.`a2` from `test`.`t2` where (`test`.`t2`.`a2` > 10) ) ))
從查詢履行籌劃看，湧現了子查詢（SUBQUERY），然則，子查詢被“<= <max>”操作符限制，而子查詢中的被查詢列a2上存在獨一索引，所以可以應用索引求最值，所以MySQL支撐“>ALL”式的子查詢優化，子查詢只被履行一次便可求得最年夜值。
不相干的ALL子查詢，查詢履行籌劃以下：
mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 =ALL (SELECT a2 FROM t2 WHERE t2.a2=10);
+----+--------------------+-------+------+------+-------------+ | id | select_type | table | type | key | Extra | +----+--------------------+-------+------+------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | Using where | | 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | Using where | +----+--------------------+-------+------+------+-------------+ 2 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`
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)
)
))
從查詢履行籌劃看，湧現了子查詢（SUBQUERY），然則被查詢優化器處置後的語句中包括“exists”，這注解MySQL關於“=ALL”式的子查詢優化用“EXISTS strategy”方法優化，所以MySQL支撐“=ALL”式的子查詢優化。
不相干的ALL子查詢，查詢履行籌劃以下：
mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 <ALL (SELECT a2 FROM t2 WHERE t2.a2=10);
+----+-------------+-------+------+------+-------------+ | id | select_type | table | type | key | Extra | +----+-------------+-------+------+------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | Using where | | 2 | SUBQUERY | t2 | ALL | NULL | Using where | +----+-------------+-------+------+------+-------------+ 2 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`
where <not>((`test`.`t1`.`a1` >= <min>
(/* select#2 */
select `test`.`t2`.`a2`
from `test`.`t2`
where (`test`.`t2`.`a2` = 10)
)
))
從查詢履行籌劃看，湧現了子查詢（SUBQUERY），然則，子查詢被“>= <min>”操作符限制，而子查詢中的被查詢列a2上存在獨一索引，所以可以應用索引求最值，所以MySQL支撐“<ALL”式的子查詢優化，子查詢只被履行一次便可求得最小值。
示例六，MySQL支撐對SOME類型的子查詢的優化：
應用了“>SOME”式子的子查詢被優化，查詢履行籌劃以下：
mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 >SOME (SELECT a2 FROM t2 WHERE t2.a2>10);
+----+-------------+-------+------+------+-------------+ | id | select_type | table | type | key | Extra | +----+-------------+-------+------+------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | Using where | | 2 | SUBQUERY | t2 | ALL | NULL | Using where | +----+-------------+-------+------+------+-------------+ 2 rows in set, 1 warning (0.05 sec)
被查詢優化器處置後的語句為：
/* 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) )))
從查詢履行籌劃看，湧現了子查詢（SUBQUERY），然則，子查詢被“min”函數限制，而子查詢中的被查詢列a2上存在獨一索引，所以可以應用索引求最值，所以MySQL支撐“>SOME”式的子查詢優化，子查詢只被履行一次便可求得最年夜值。
應用了“=SOME”式子的子查詢被優化，查詢履行籌劃以下：
mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 =SOME (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.01 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))
從查詢履行籌劃看，沒有湧現了子查詢，表t2被物化，與表t1停止了半銜接。
應用了“<SOME”式子的子查詢被優化，查詢履行籌劃以下：
mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 <SOME (SELECT a2 FROM t2 WHERE t2.a2=10);
+----+-------------+-------+------+------+-------------+ | id | select_type | table | type | key | Extra | +----+-------------+-------+------+------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | Using where | | 2 | SUBQUERY | t2 | ALL | NULL | Using where | +----+-------------+-------+------+------+-------------+ 2 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`
where <nop>(
(
`test`.`t1`.`a1` < (/* select#2 */
select max(`test`.`t2`.`a2`)
from `test`.`t2`
where (`test`.`t2`.`a2` = 10)
)
)
)
從查詢履行籌劃看，湧現了子查詢（SUBQUERY），然則，子查詢被“max”函數限制，而子查詢中的被查詢列a2上存在獨一索引，所以可以應用索引求最值，所以MySQL支撐“<SOME”式的子查詢優化，子查詢只被履行一次便可求得最年夜值。
示例七，MySQL支撐對ANY類型的子查詢的優化：
應用了“>ANY”式子的子查詢被優化，查詢履行籌劃以下：
mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 >ANY (SELECT a2 FROM t2 WHERE t2.a2>10);
+----+-------------+-------+------+------+-------------+ | id | select_type | table | type | key | Extra | +----+-------------+-------+------+------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | Using where | | 2 | SUBQUERY | t2 | ALL | NULL | Using where | +----+-------------+-------+------+------+-------------+ 2 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`
where <nop>(
(
`test`.`t1`.`a1` > (/* select#2 */
select min(`test`.`t2`.`a2`)
from `test`.`t2`
where (`test`.`t2`.`a2` > 10)
)
)
)
從查詢履行籌劃看，湧現了子查詢（SUBQUERY），然則，子查詢被“min”函數限制，而子查詢中的被查詢列a2上存在獨一索引，所以可以應用索引求最值，所以MySQL支撐“>ANY”式的子查詢優化，子查詢只被履行一次便可求得最小值。
應用了“=ANY”式子的子查詢被優化，查詢履行籌劃以下：
mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 =ANY (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.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 ((`test`.`t1`.`a1` = `<subquery2>`.`a2`) and (`test`.`t2`.`a2` > 10))
從查詢履行籌劃看，沒有湧現了子查詢，表t2被物化，與表t1停止了半銜接。
應用了“<ANY”式子的子查詢被優化，查詢履行籌劃以下：
mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 <ANY (SELECT a2 FROM t2 WHERE t2.a2>10);
+----+-------------+-------+------+------+-------------+ | id | select_type | table | type | key | Extra | +----+-------------+-------+------+------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | Using where | | 2 | SUBQUERY | t2 | ALL | NULL | Using where | +----+-------------+-------+------+------+-------------+ 2 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`
where <nop>(
(
`test`.`t1`.`a1` < (/* select#2 */
select max(`test`.`t2`.`a2`)
from `test`.`t2`
where (`test`.`t2`.`a2` > 10)
)
)
)
從查詢履行籌劃看，湧現了子查詢（SUBQUERY），然則，子查詢被“max”函數限制，而子查詢中的被查詢列a2上存在獨一索引，所以可以應用索引求最值，所以MySQL支撐“<ANY”式的子查詢優化，子查詢只被履行一次便可求得最年夜值。
