MySQL5.6子查询优化总结

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 | | ALL | NULL | NULL | NULL | NULL | 100 | Using where |
| 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 | | ALL | NULL | NULL | NULL | NULL | NULL | NULL |
| 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类型子查询，同样支持物化的优化，但不支持子查询的消除。