1、子查询合并不支持
mysql> explain select * from t1 where exists (select * from t2 where t2.a2>5) and exists (select * from t2 where t2.a2<10);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 9867 | NULL |
| 3 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 100 | Using where |
| 2 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 100 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
3 rows in set (0.00 sec)
依然是两条子查询 没有合并成一条。
对比子查询逻辑优化后的语句:
mysql> explain select * from t1 where exists (select * from t2 where t2.a2>5 and t2.a2<10);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 9867 | NULL |
| 2 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 100 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.01 sec)
两个子查询合并成了一条。
所以在做多个子查询时需注意对子查询的合并。
2、子查询展开
mysql> explain select * from t1, (select * from t2 where t2.a2>10) v_t2 where t1.a1<10 and v_t2.a2<20;
| 1 | PRIMARY |
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 9867 | Using where; Using join buffer (Block Nested Loop) |
| 2 | DERIVED | t2 | ALL | NULL | NULL | NULL | NULL | 100 | Using where |
3 rows in set (0.00 sec)
对比逻优化后,将子查询展开的语句:
mysql> explain select * from t1, t2 where t1.a1<10 and t2.a2<20;
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 100 | Using where |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 9867 | Using where; Using join buffer (Block Nested Loop) |
2 rows in set (0.00 sec)
所以对嵌套的子查询,注意在逻辑上进行展开,将其和外表连接查询。
3、不支持对聚类子查询的消除
4、不支持对exists、not exists类型的子查询优化
5、支持对IN类型的子查询优化,但有些情况下优化不完全。
mysql> explain select b1 from t1 where t1.a1 in (select t2.a2 from t2 where t2.b2=10);
+----+--------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 9867 | Using where; Using join buffer (Block Nested Loop) |
| 2 | MATERIALIZED | t2 | ALL | NULL | NULL | NULL | NULL | 100 | Using where |
+----+--------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------------+
3 rows in set (0.00 sec)
上表说明对IN类型子查询做了一定优化,但是不完全,没有消除子查询。
对比优化后的语句:
mysql> explain select b1 from t1, t2 where t1.a1=t2.a2 and t2.b2=10;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 100 | Using where |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 9867 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)
6、对于NOT IN类型子查询,同样支持物化的优化,但不支持子查询的消除。
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。