MySQL查询优化器--逻辑查询优化技术（二）--子查询优化（三）

3 子查询优化的限制

MySQL支持对简单SELECT查询中的子查询优化，包括：

q  简单SELECT查询中的子查询；

q  带有DISTINCT、ORDERBY、LIMIT。

 

MySQL支持对简单SELECT查询中的子查询优化，不支持对如下情况优化：

q  带有UNION操作；

q  带有GROUPBY、HAVING、聚集函数；

q  使用ORDERBY中带有LIMIT；

q  内表、外表的个数超过MySQL支持的最大表的连接数。

 

这两种情况的细分，使得PostgreSQL能够更为细腻的处理子查询。PostgreSQL对于子查询，通常的优化方式，是尽量采取“拉平”操作，把子查询合并到父查询中，其目的，是为了在多表连接时能利用多表连接算法进行优化。

4 MySQL支持的子查询优化技术

一 子查询合并，不支持（依旧是用两个子查询完成工作）

mysql> explain EXTENDED SELECT * FROM t1 WHERE a1<4 AND (EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=1) OR EXISTS (SELECT a2 FROM t2 WHERE t2.a2

<5 AND t2.b2=2) );

+----+-------------+-------+-------+---------------+------------------------------------+

| id | select_type | table | type  | possible_keys | Extra                              |

+----+-------------+-------+-------+---------------+------------------------------------+

|  1 | PRIMARY     | t1    | range | t_idx_t1      | Using index condition              |

|  3 | SUBQUERY    | t2    | range | t_idx_t2      | Using index condition; Using where |

|  2 | SUBQUERY    | t2    | range | t_idx_t2      | Using index condition; Using where |

+----+-------------+-------+-------+---------------+------------------------------------+

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where (`test`.`t1`.`a1` < 4)

 

SQL语句的语义等于上一条SQL，但查询计划不同（用一个子查询完成工作）

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a1<4 AND EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND (t2.b2=1 OR t2.b2=2));

+----+-------------+-------+-------+---------------+------------------------------------+

| id | select_type | table | type  | possible_keys | Extra                              |

+----+-------------+-------+-------+---------------+------------------------------------+

|  1 | PRIMARY     | t1    | range | t_idx_t1      | Using index condition              |

|  2 | SUBQUERY    | t2    | range | t_idx_t2      | Using index condition; Using where |

+----+-------------+-------+-------+---------------+------------------------------------+

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1` from `test`.`t1` where (`test`.`t1`.`a1` < 4)

 

二 子查询反嵌套（子查询展开），支持（用嵌套循环连接实现）

mysql> EXPLAIN EXTENDED SELECT * FROM t1, (SELECT * FROM t2 WHERE t2.a2 >5) v_t2 WHERE t1.a1<4 AND v_t2.a2<5;

+----+-------------+------------+-------+-----------------------------------------------

| id | select_type | table      | type  | Extra

+----+-------------+------------+-------+-----------------------------------------------

|  1 | PRIMARY     | t1         | range | Using index condition

|  1 | PRIMARY     | <derived2> | ALL   | Using where; Using join buffer (Block Nested Loop)

|  2 | DERIVED     | t2         | range | Using index condition

+----+-------------+------------+-------+-----------------------------------------------

被查询优化器处理后的语句为：

/* select#1 */

select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`v_t2`.`a2` AS `a2`,`v_t2`.`b2` AS `b2`

from `test`.`t1` join (

  /* select#2 */

  select `test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`

  from `test`.`t2`

  where (`test`.`t2`.`a2` > 5)) `v_t2`

where ((`test`.`t1`.`a1` < 4) and (`v_t2`.`a2` < 5))

 

三 聚集子查询消除，不支持（依旧是作为子查询执行）

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1>(SELECT MIN(t2.a2) FROM t2);

+----+-------------+-------+------+---------------+------------------------------+

| id | select_type | table | type | possible_keys | Extra                        |

+----+-------------+-------+------+---------------+------------------------------+

|  1 | PRIMARY     | t1    | ALL  | t_idx_t1      | Using where                  |

|  2 | SUBQUERY    | NULL  | NULL | NULL          | Select tables optimized away |

+----+-------------+-------+------+---------------+------------------------------+

被查询优化器处理后的语句为：

/* select#1 */

select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where

(

  `test`.`t1`.`a1` > (

    /* select#2 */

    select min(`test`.`t2`.`a2`)

    from `test`.`t2`

  )

)