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`
)
)