SELECT count(driver_id) FROM driver where exists (SELECT uid FROM (SELECT uid from user) as b where b.uid = driver.driver_id);
执行效果如图：
可以发现优化减少了6s多的执行时间，下面是SQL的执行计划：
同样的还是相关子查询，但是减少了内部遍历查询的操作。所以可以通过预查询来减少遍历操作，而提高效率。
其实在实际编程中，很多开发人员选择不使用连接表查询，而是自己先把数据从一张表中取出，再到另一张表中执行WHEREIN操作，这原理和上面SQL语句实现的是一样的。
MySQL5.6对子查询的优化？
SEMI JOIN策略
优化器会识别出需要子查询的IN语句以便从区域表返回每个区域键的一个实例。这就导致了MySQL会以半连接的方式执行SELECT语句，所以全局表中每个区域只会有一个实例与记录相匹配。
半连接和常规连接之间存在两个非常重要的区别：
在半连接中，内表不会导致重复的结果。
此操作不会有内表中的字段添加到结果中去。
因此，半连接的结果常常是来自外表记录的一个子集。从有效性上看，半连接的优化在于有效的消除了来自内表的重复项，MySQL应用了四个不同的半连接执行策略用来去重。
Table Pullout优化
Convert the subquery to a join, or use table pullout and run the query as an inner join between subquery tables and outer tables. Table pullout pulls a table out from the subquery to the outer query.将子查询转变为一个连接，或是利用table pullout并将查询作为子查询表和外表之间的一个内连接来执行。Table pullout会为外部查询从子查询抽取出一个表。
有些时候，一个子查询可以被重写为JOIN，例如：
SELECT OrderID FROM Orders where EmployeeID IN (select EmployeeID from Employees where EmployeeID > 3);
如果知道OrderID是唯一的，即主键或者唯一索引，那么SQL语句会被重写为Join形式。
SELECT OrderID FROM Orders join Employees where Orders.EmployeeID = Employees.EmployeeID and Employees.EmployeeID > 3;
Table pullout的作用就是根据唯一索引将子查询重写为JOIN语句，在MySql 5.5中，上述的SQL语句执行计划：
如果通过EXPLAIN EXTENDED 和 SHOW WARNINGS命令，可以看到如下结果：
select `northwind`.`Orders`.`OrderID` AS `OrderID` from `northwind`.`Orders` where <in_optimizer>(`northwind`.`Orders`.`EmployeeID`,<exists>(<primary_index_lookup>(<cache>(`northwind`.`Orders`.`EmployeeID`) in Employees on PRIMARY where ((`northwind`.`Employees`.`EmployeeID` > 3) and (<cache>(`northwind`.`Orders`.`EmployeeID`) = `northwind`.`Employees`.`EmployeeID`)))))
正是上面说的in为什么慢？
在MySql 5.6中，优化器会对SQL语句重写，得到的执行计划：
在MySql 5.6中，优化器没有将独立子查询重写为相关子查询，通过EXPLAIN EXTENDED 和 SHOW WARNINGS命令，得到优化器的执行方式为：
/* select#1 */ select `northwind`.`orders`.`OrderID` AS `OrderID` from `northwind`.`employees` join `northwind`.`orders` where ((`northwind`.`orders`.`EmployeeID` = `northwind`.`employees`.`EmployeeID`) and (`northwind`.`employees`.`EmployeeID` > 3))
很显然，优化器将上述子查询重写为JOIN语句，这就是Table Pullout优化。
Duplicate Weedout优化
Run the semi-join as if it was a join and remove duplicate records using a temporary table.执行半连接，就如同它是一个连接并利用临时表移除了重复的记录。
上面内部表查出的列是唯一的，因此优化器会将子查询重写为JOIN语句，以提高SQL执行的效率。Duplicate Weedout优化是指外部查询条件是列是唯一的，MySql优化器会先将子查询查出的结果进行去重。比如下面这条SQL语句：
SELECT ContactName FROM Customers where CustomerID in (select CustomerID from Orders where OrderID > 10000 and Customers.Country = Orders.ShipCountry);
因为CustomerID是主键，所以应该对子查询得到的结果进行去重。在MySql 5.6中的执行计划：
Extra选项提示的Start temporary表示创建一张去重的临时表，End temporary表示删除该临时表。而通过EXPLAIN EXTENDED 和 SHOW WARNINGS命令，得到优化器的执行方式为：
/* select#1 */ select `northwind`.`customers`.`ContactName` AS `ContactName` from `northwind`.`customers` semi join (`northwind`.`orders`) where ((`northwind`.`customers`.`CustomerID` = `northwind`.`orders`.`CustomerID`) and (`northwind`.`customers`.`Country` = `northwind`.`orders`.`ShipCountry`) and (`northwind`.`orders`.`OrderID` > 10000))
与Table Pullout优化不同的是，显示的是semi join而不是join，其中原因在于多了一些去重的工作，对于上述的执行计划，其扫描成本约为830+830*1=1660次。
而在MySql 5.5中的执行计划为：
可以看到，在MySql 5.5中还是将语句转化为相关子查询，扫描成本约为93+93*9=930次。
我们可以看到MySql 5.6优化以后比5.5的扫描成本反而大，其实这只是在两张表较小的的情况下的结果，如果表很大，优化的效果会非常明显。
Materialization优化
Materialize the subquery into a temporary table with an index and use the temporary table to perform a join. The index is used to remove duplicates. The index might also be used later for lookups when joining the temporary table with the outer tables; if not, the table is scanned.
上面的子查询是相关子查询，如果子查询是独立子查询，则优化器可以选择将独立子查询产生的结果填充到单独一张物化临时表中，如图：
根据JOIN的顺序，Materialization优化可分为：
Materialization scan：JOIN是将物化临时表和表进行关联。
Materialization lookup：JOIN是将表和物化临时表进行关联。
下面的子查询可以利用Materialization来进行优化：
SELECT OrderID FROM Orders where OrderID in (select OrderID from `Order Details` where UnitPrice < 50 );
SQL语句的执行计划：
可以看到，在进行JOIN时（也就是id为1的步骤），先扫描的表是Orders，然后是subquery2，因此这是Materialization lookup的优化。对于下面的SQL：
select * FROM driver where driver_id in (select uid from user);
SQL语句的执行计划：
先扫描的是subquery2，再是driver表，这就是Materialization scan的优化。
FirstMacth优化
When scanning the inner tables for row combinations and there are multiple instances of a given value group, choose one rather than returning them all. This "shortcuts" scanning and eliminates production of unnecessary rows.为了对记录进行合并而在扫描内表，并且对于给定值群组有多个实例时，选择其一而不是将它们全部返回。这为表扫描提供了一个早期退出机制而且还消除了不必要记录的产生。
半连接的最先匹配(FirstMatch)策略执行子查询的方式与MySQL稍早版本中的IN-TO-EXISTS是非常相似的。对于外表中的每条匹配记录，MySQL都会在内表中进行匹配检查。当发现存在匹配时，它会从外表返回记录。只有在未发现匹配的情况下，引擎才会回退去扫描整个内表。
LooseScan优化
Scan a subquery table using an index that enables a single value to be chosen from each subquery's value group.利用索引来扫描一个子查询表可以从每个子查询的值群组中选出一个单一的值。
SEMI JOIN变量
Each of these strategies except Duplicate Weedout can be enabled or disabled using the optimizer_switch system variable. The semijoin flag controls whether semi-joins are used. If it is set to on, the firstmatch, loosescan, and materialization flags enable finer control over the permitted semi-join strategies. These flags are on by default.除Duplicate Weedout之外的每个策略可以用变量控制开关，semijoin控制semi-joins优化是否开启，如果设置开启，其他的策略也有独立的变量控制。所有的变量在5.6默认是打开的。
mysql> SELECT @@optimizer_switch\G;
*************************** 1. row ***************************
@@optimizer_switch: 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
1 row in set (0.00 sec)
EXPLAIN查看策略
Semi-joined tables show up in the outer select. EXPLAIN EXTENDED plus SHOW WARNINGS shows the rewritten query, which displays the semi-join structure. From this you can get an idea about which tables were pulled out of the semi-join. If a subquery was converted to a semi-join, you will see that the subquery predicate is gone and its tables and WHERE clause were merged into the outer query join list and WHERE clause.
Temporary table use for Duplicate Weedout is indicated by Start temporary and End temporary in the Extra column. Tables that were not pulled out and are in the range of EXPLAIN output rows covered by Start temporary and End temporary will have their rowid in the temporary table.
FirstMatch(tbl_name) in the Extra column(列) indicates join shortcutting.
LooseScan(m..n) in the Extra column indicates use of the LooseScan strategy. m and n are key part numbers.
As of MySQL 5.6.7, temporary table use for materialization is indicated by rows with a select_type value of MATERIALIZED and rows with a table value of .
Before MySQL 5.6.7, temporary table use for materialization is indicated in the Extra column by Materialize if a single table is used, or by Start materialize and End materialize if multiple tables are used. If Scan is present, no temporary table index is used for table reads. Otherwise, an index lookup is used.
上面介绍中FirstMacth优化、LooseScan优化的具体效果没有很好的例子去显示出来。有机会可以交流学习。
有不对的地方希望大家多交流，谢谢。
《MySql技术内幕：SQL编程》
http://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html
http://tech.it168.com/a2013/0506/1479/000001479749.shtml
转载请注明出处。
作者：wuxiwei
出处：http://www.cnblogs.com/wxw16/p/6105624.html
