mysql 执行计划不对_MySQL执行计划显示与执行过程不符合

一 建表和现象的过程如下

CREATE TABLE t1(id1 INT,a1 INT,b1 INT,PRIMARY KEY(id1));

CREATE TABLE t3(id3 INT UNIQUE,a3 INT,b3 INT);

INSERT INTO t1 VALUES(1,1,NULL);

INSERT INTO t3 VALUES(1,1,NULL);

mysql>select*from(select*fromt1whereid1=(selectid3fromt3whereid3=1))t;

+-----+------+------+

|id1|a1|b1|

+-----+------+------+

|1|1|NULL|

+-----+------+------+

1rowinset(0.01sec)

mysql>explain extendedselect*from(select*fromt1whereid1=(selectid3fromt3whereid3=1))t;

+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+

|id|select_type|table|type|possible_keys|key|key_len|ref|rows|filtered|Extra|

+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+

|1|PRIMARY||system|NULL|NULL|NULL|NULL|1|100.00|NULL|

|2|DERIVED|t1|const|PRIMARY|PRIMARY|4|const|1|100.00|NULL|

|3|SUBQUERY|t3|const|id3|id3|5|const|1|100.00|Usingindex|

+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+

3rowsinset,1warning(0.00sec)

mysql>show warnings;

+-------+------+-----------------------------------------------------------------------+

|Level|Code|Message|

+-------+------+-----------------------------------------------------------------------+

|Note|1003|/* select#1 */select'1'AS`id1`,'1'AS`a1`,NULL AS`b1`fromdual|

+-------+------+-----------------------------------------------------------------------+

1rowinset(0.00sec)

二 分析:

首先, 察看执行计划:

1 id值为1的select_type值为PRIMARY, 这是一个''表,表示子查询出现在FROM子句中，且本行的内容是一个类似“壳”一样的并无多大实质意义的‘虚表’，''中的数字2来自于下一行id为2的结果。而别名为t的子查询，确实出现在最外层的FROM中。

2 id值为2的select_type值为DERIVED，表示这是一个被驱动的表(被驱动的FROM子句中的子查询).

3 id值为3的select_type值为SUBQUERY，表示嵌套中的第二个在t3表上的子查询，没有被优化为其他类型(优化可如子查询被消除后转为内连接)。

其次，察看警告信息：

1 FROM子句后的表变为了‘dual’这一虚表，t1和t2全部消失。为什么？

2 MySQL在优化的过程中，利用等式的性质，推知了：

2.1 id1=id3=1

2.2 查询语句从逻辑推理上，就可以变形为：select * from t1 where id1=1

此时，子查询其实被消除了，但是执行计划中没有体现之一点，这是执行计划错误之处。

2.3 进一步，t1表上id1列是主键，所以根据索引，可以查知*对应的目标列的值：这样，逻辑推理上，就可以变形为：

select '1' AS `id1`,'1' AS `a1`,NULL AS `b1` from t1 where id1=1

2.4 因为目标列的值已经能够在优化的过程中得知，所以FROM子句中的表，可被标识为“常量表”。

3 于是，在显示查询执行计划的后期(explain命令后期)，代码中有个判断：如果表全部是常量表且已经是“被优化了的(optimized_away)”则把FROM子句中的表对象，用“dual”替换。这就是警告中为什么会出现“from dual”的原因。

再次，执行计划和警告信息显示存在不一致，那么，MySQL在执行的时候，是按照谁来执行呢？

这点可以通过跟踪代码进行分析。

执行过程如下：

1 优化阶段：即生成执行计划阶段

MySQL在优化阶段的过程，就如执行计划显示的结果一样，对各个子句进行着执行计划显示的过程，先是因为FROM子句中的子查询执行id为1的过程，然后是对被驱动的id为2的优化，在没有结束id为2的优化的过程中，发现id为2的子句是子查询，就嵌套调用去优化子查询，于是引发了等式化简等过程。

在运用各种技术做各种化简的过程中，一些值或结果已经得知，顺带即完成了一些求解的工作。

这也说明一个问题：有的朋友问id间的序号表明了一个什么样的执行次序？因为嵌套的关系，首先启动的是id值小的，在id值为小的执行过程中，接着又启动了id值为大的查询子句，所以，最先执行完毕的，是id值大者；最先执行的，是id值小者。

2 执行阶段：

已经知道是一个常量求解，且结果在优化过程中得知，直接输出。

这也解释了警告信息中得到的是“from dual”，与执行过程的含义，相符。即：执行阶段，查询计划表明的过程已经结束了。

三 补充说明

mysql>  explain extended select * from (select * from t1 where id1 in (select id3 from t3 where id3=5)) t；

-> ;

+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+

| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |

+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+

|  1 | PRIMARY     | | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL        |

|  2 | DERIVED     | t1         | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |

|  2 | DERIVED     | t3         | const  | id3           | id3     | 5       | const |    1 |   100.00 | Using index |

+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+

3 rows in set, 1 warning (49.53 sec)

mysql> show warnings;

+-------+------+-----------------------------------------------------------------------+

| Level | Code | Message                                                               |

+-------+------+-----------------------------------------------------------------------+

| Note  | 1003 | /* select#1 */ select '5' AS `id1`,'5' AS `a1`,NULL AS `b1` from dual |

+-------+------+-----------------------------------------------------------------------+

1 row in set (0.00 sec)

"标题三"中和"标题一"中的SQL差别在于嵌套的子查询的形式是等号还是IN。

而IN的形式，被MySQL识别优化了标识子查询未“DERIVED ”了，这是MySQL从形式上目前只支持IN形式的子查询优化的优化，对于等号这样的形式，不支持。但是，从等式性质上，最终对"标题一"中的SQL进行了优化。