MySQL子查询优化---目标列中的子查询执行情况分析

问题:

在目标列处的子查询mysql优化分析器会进行什么优化吗？比如我的主sql返回1000条数据，那么目标列处的子查询会被执行1000次吗？

分析:

其实这个问题看似简单，却不容易回答。这里，包括了子查询的知识，包括了优化器的知识，还包括了执行器的知识；尤其是数据库系统中优化器和执行器的具体实现方式。所以我们如下抽丝剥茧，逐一深入讨论问题。

1 子查询出现的位置,可以是: 目标列 / FROM子句中 / WHERE子句中....

    更多详情参见: 《数据库查询优化器的艺术》一书P18，2.2.1节

2 对于“子查询出现在目标列”，《数据库查询优化器的艺术》P18指出，“子查询如果位于目标列，则只能是标量子查询”。这点很重要。

3 举个例子

mysql> create table t(a INT, b INT);
Query OK, 0 rows affected (0.11 sec)

mysql> select (select * from t) from t;  
ERROR 1241 (21000): Operand should contain 1 column(s)

说明：蓝色字体部分，是子查询，但是，MySQL报错信息说：只能包含一个列对象（注意这个表，我们定义了a和b两个列）

改造SQL如下，把子查询中的星号，变为聚集函数count()，此函数的返回之是一个单一的数值

mysql> select (select count(*) from t) from t;
Empty set (0.00 sec)

说明：这样的语句，MySQL就没有报告错误。

接下来，我们再插入一些值。先插入第一行，然后执行在a列上的子查询。
mysql> insert into t values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> select (select a from t) from t;  //同下面的子查询完全相同，只是表中的数据不同
+-------------------+
| (select a from t) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

说明：子查询只有一个行，可以返回正确结果。

插入第2行，然后执行在a列上的与前面相同的子查询。

mysql> insert into t values (2,2);
Query OK, 1 row affected (0.01 sec)

mysql> select (select a from t) from t;  //同上面的子查询完全相同，只是表中的数据不同

ERROR 1242 (21000): Subquery returns more than 1 row

说明：这回，我们看到，MySQL在数据多于一行的时候，报错，提示：子查询返回的值多于了一行
也就是说，目标列中的子查询返回值，不能多于1行；结合前面的示例，即有结论：目标列中的子查询，返回值不能多于1行1列。这其实就是说：目标列中的子查询只能是标量子查询。
 
4 什么是标量子查询：
   SQL允许子查询出现在返回单个值的表达式能够出现的任何地方，只要该子查询只返回包含单个属性的单个元组；这样的子查询称为标量子查询（scalar subquery）。
 
5 执行器对于目标列为子查询的执行：
继续使用上述示例，执行如下命令（此时表里已经有2条元组）：

mysql> select (select a from t as t2) from t as t1;
ERROR 1242 (21000): Subquery returns more than 1 row

这个错误，我们在之前已经看到过，不再多述。现就这个查询，进行分析。先看其查询执行计划。

mysql> explain select (select a from t as t2) from t as t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
|  2 | SUBQUERY    | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

执行计划告诉我们，对于t2而言，目标列中的子查询，没有被优化，其select_type的值是SUBQUERY。

对于这种情况，执行器执行的时候，是用2层循环执行的，基本算法就是嵌套循环连接。大概方式为：

for (each row of t1)
{
    get r1 form t1;
    for (each row of t2)
    {
        get r2 from t2; 

        join r1 and r2;
    }
}

这就是本文开始的问题所在，可能会使得大家会认为：我的主sql返回1000条数据，那么目标列处的子查询会被执行1000次

如果子查询在目标列，MySQL的执行器，就是这么执行的。所以，可能会使得“目标列处的子查询会被执行1000次”。但是，不要忘记另外一个条件，这个条件就是“目标列中的子查询只能是标量子查询”，所以，MySQL的执行器的真实实现方式如下，注意多出的蓝色字体部分：

for (each row of t1)
{
    get r1 form t1;   
    for (each row of t2)
    { 
        get r2 from t2; 
        if ((t2 from a subquery) and (have_got_a_value)) 
        {
            file a error; //读到多于1个行的时候，报错退出
            return;
        }        
        have_got_a_value= true;         
        join r1 and r2;
    }
}

所以，最终的结果是：我的主sql返回1000条数据，那么目标列处的子查询不会被执行1000次

6 一些优化方式---在目标列处的子查询mysql优化分析器会进行什么优化吗？

mysql> select (select count(*) from t) from t;
+--------------------------------+
| (select count(*) from t as t2) |
+--------------------------------+
|                              2 |
|                              2 |
+--------------------------------+
2 rows in set (0.00 sec)

mysql> explain select (select count(*) from t as t2) from t as t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | PRIMARY     | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                         |
|  2 | SUBQUERY    | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)

这个例子，就是一个优化方式。MySQL官方手册对 Select tables optimized away 的解释如下：

The query contained only aggregate functions (MIN(), MAX()) that were all resolved using an index, or COUNT(*) for MyISAM, and no GROUP BY clause. The optimizer determined that only one row should be returned.

其中， 特别需要注意的是：

1）COUNT(*) for MyISAM不正确，应该是对于InnoDB表也可以被优化。

2）这个优化，是在优化器的阶段进行的，得到的COUNT(*) 被缓存，然后在执行器阶段，不用再求值，也就不会有“目标列处的子查询会被执行1000次”的可能了

 

7 更进一步认识问题

是不是我们解释到这里就可以结束了？答案是：不是的。且看下例：

这个示例，子查询已经符合了标量子查询的要求（避开了第5条中所说的问题），t1中有2个元组，每一个元组，就可能致使子查询都被执行一次。

mysql> select (select max(a) from t as t2) from t as t1;
+------------------------------+
| (select max(a) from t as t2) |
+------------------------------+
|                            2 |
|                            2 |
+------------------------------+
问题就这样又来了：究竟这个示例中的子查询，会被执行几次？

待续... 参见: http://blog.163.com/li_hx/blog/static/1839914132015528853902/