mysql 重复子查询优化,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类型子查询，同样支持物化的优化，但不支持子查询的消除。