MySQL子查询的优化

本文基于MySQL5.7.19测试

创建四张表，pt1、pt2表加上主键

mysql> create table t1 (a1 int, b1 int);
mysql> create table t2 (a2 int, b2 int); 
mysql> create table pt1 (a1 int, b1 int, primary key (a1));
mysql> create table pt2 (a2 int, b2 int, primary key (a2));

向表中分别插入10000条数据

mysql> delimiter //
mysql> create procedure prod_dt()
    -> begin
    -> declare i int;
    -> set i=0;
    -> while i<10000 do
    -> insert into t1(a1,b1) values(i,i+1);
    -> insert into t2(a2,b2) values(i+1,i+2);
    -> insert into pt1(a1,b1) values(i,i+1);
    -> insert into pt2(a2,b2) values(i+1,i+2);
    -> set i=i+1;
    -> end while;
    -> end;
    -> //
Query OK, 0 rows affected (0.05 sec)

mysql> delimiter ;
mysql> call prod_dt() ;

　　

MySQL支持对简单SELECT查询中的子查询优化，包括：
1 简单SELECT查询中的子查询。
2 带有DISTINCT、ORDERBY、LIMIT操作的简单SELECT查询中的子查询。

# 没有主键，优化器进行了优化，子查询物化后和表t1进行连接。执行计划中没有子查询
mysql> explain extended select * from t1 where t1.a1<100 and a1 in (select a2 from t2 where t2.a2 >10);
+----+--------------+-------------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type  | table       | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                              |
+----+--------------+-------------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  NULL |   100.00 | Using where                                        |
|  1 | SIMPLE       | t1          | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10362 |     3.33 | Using where; Using join buffer (Block Nested Loop) |
|  2 | MATERIALIZED | t2          | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10157 |    33.33 | Using where                                        |
+----+--------------+-------------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
3 rows in set, 2 warnings (0.00 sec)


#有主键，优化器进行了优化。执行计划中没有子查询
mysql> explain extended select * from pt1 where pt1.a1<100 and a1 in (select a2 from pt2 where pt2.a2 >10);
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | pt1   | NULL       | range  | PRIMARY       | PRIMARY | 4       | NULL        |   89 |   100.00 | Using where |
|  1 | SIMPLE      | pt2   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | abce.pt1.a1 |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

mysql> 

　

MySQL不支持对如下情况的子查询进行优化：
-带有UNION操作。
-带有GROUPBY、HAVING、聚集函数。
-使用ORDERBY中带有LIMIT。
-内表、外表的个数超过MySQL支持的最大表的连接数。

#有聚合函数，没有进行子查询优化
mysql> explain extended select * from t1 where t1.a1>(select min(t2.a2) from t2);
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | PRIMARY     | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10362 |    33.33 | Using where |
|  2 | SUBQUERY    | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10157 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
2 rows in set, 2 warnings (0.01 sec)

mysql> explain extended select * from pt1 where pt1.a1>(select min(pt2.a2) from pt2);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
|  1 | PRIMARY     | pt1   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 5000 |   100.00 | Using where                  |
|  2 | SUBQUERY    | NULL  | NULL       | NULL  | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
2 rows in set, 2 warnings (0.00 sec)

mysql> 

MySQL支持哪些子查询的优化技术？

1 子查询合并技术 --> 不支持

#t2表上执行了2次子查询。如果支持子查询合并技术,则t2表上只执行一次子查询
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 | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | PRIMARY     | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10362 |    33.33 | Using where |
|  3 | SUBQUERY    | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10157 |     3.33 | Using where |
|  2 | SUBQUERY    | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10157 |     3.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
3 rows in set, 2 warnings (0.00 sec)

mysql> 

#pt2表上执行了2次子查询。如果支持子查询合并技术,则pt2表上只执行一次子查询
mysql> explain extended select * from pt1 where a1<4 and (exists (select a2 from pt2 where pt2.a2<5 and pt2.b2=1) or exists (select a2 from pt2 where pt2.a2<5 and pt2.b2=2) );
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | pt1   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    4 |   100.00 | Using where |
|  3 | SUBQUERY    | pt2   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    4 |    10.00 | Using where |
|  2 | SUBQUERY    | pt2   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    4 |    10.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
3 rows in set, 2 warnings (0.00 sec)

mysql> 

#人为的合并查询条件为“(t2.b2=1 OR t2.b2=2)”t2表上的子查询，只执行一次
mysql> explain extended select * from t1 where a1<10 and exists (select a2 from t2 where t2.a2<5 and (t2.b2=1 or t2.b2=2));
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | PRIMARY     | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10362 |    33.33 | Using where |
|  2 | SUBQUERY    | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10157 |     6.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
2 rows in set, 2 warnings (0.01 sec)

mysql> explain extended select * from pt1 where a1<10 and exists (select a2 from pt2 where pt2.a2<5 and (pt2.b2=1 or pt2.b2=2));
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | pt1   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   10 |   100.00 | Using where |
|  2 | SUBQUERY    | pt2   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    4 |    19.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

mysql> 

2 子查询展开(子查询反嵌套)技术 --> 支持得不够好

mysql> explain extended select * from t1, (select * from t2 where t2.a2 >10) v_t2 where t1.a1<10 and v_t2.a2<20;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10157 |    11.11 | Using where                                        |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10362 |    33.33 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

mysql> explain extended select * from pt1, (select * from pt2 where pt2.a2 >10) v_t2 where pt1.a1<10 and v_t2.a2<20;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | pt2   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    9 |   100.00 | Using where                                        |
|  1 | SIMPLE      | pt1   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   10 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

mysql> 

#IN子查询的例子,可以看出子查询被物化
mysql> explain extended select * from t1 where t1.a1<100 and a1 in (select a2 from t2 where t2.a2 >10);
+----+--------------+-------------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type  | table       | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                              |
+----+--------------+-------------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  NULL |   100.00 | Using where                                        |
|  1 | SIMPLE       | t1          | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10362 |     3.33 | Using where; Using join buffer (Block Nested Loop) |
|  2 | MATERIALIZED | t2          | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10157 |    33.33 | Using where                                        |
+----+--------------+-------------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
3 rows in set, 2 warnings (0.00 sec)

mysql>

#从查询执行计划看，子查询不存在，SQL语句被转换为内连接操作，这表明MySQL只有在针对主键列进行类似的子查询时，才把子查询上拉为内连接。所以，MySQL还是支持子查询展开技术的。
mysql> explain extended select * from pt1 where pt1.a1<100 and a1 in (select a2 from pt2 where pt2.a2 >10);
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | pt1   | NULL       | range  | PRIMARY       | PRIMARY | 4       | NULL        |   89 |   100.00 | Using where |
|  1 | SIMPLE      | pt2   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | abce.pt1.a1 |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

mysql>

3 聚集子查询消除技术 --> 不支持
#MySQL认为，聚集子查询，只需要执行一次，得到结果后，即可把结果缓冲到内存中供后续连接或过滤等操作使用，没有必要消除掉子查询。
#另外，如果聚集子查询在索引列上执行，则会更快得到查询结果，更加能加速查询速度。

mysql> explain extended select * from t1 where t1.a1>(select min(t2.a2) from t2);
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | PRIMARY     | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10362 |    33.33 | Using where |
|  2 | SUBQUERY    | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10157 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

mysql> explain extended select * from pt1 where pt1.a1>(select min(pt2.a2) from pt2);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
|  1 | PRIMARY     | pt1   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 5000 |   100.00 | Using where                  |
|  2 | SUBQUERY    | NULL  | NULL       | NULL  | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
2 rows in set, 2 warnings (0.00 sec)

mysql>