手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

MySQL执行计划显示与执行过程不符合

[日期:2014-08-29] 来源:Linux社区  作者:1415699306 [字体: ]

一 建表和现象的过程如下

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 * from t1 where id1 =(select id3 from t3 where id3=1)) t;
+-----+------+------+
| id1 | a1 | b1 |
+-----+------+------+
| 1 | 1 | NULL |
+-----+------+------+
1 row in set (0.01 sec)

mysql> explain extended select * from (select * from t1 where id1=(select id3 from t3 where id3=1)) t;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | <derived2> | 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 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select '1' AS `id1`,'1' AS `a1`,NULL AS `b1` from dual |
+-------+------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

二 分析:
首先, 察看执行计划:
1 id值为1的select_type值为PRIMARY, 这是一个'<derived2>'表,表示子查询出现在FROM子句中,且本行的内容是一个类似“壳”一样的并无多大实质意义的‘虚表’,'<derived2>'中的数字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    | <derived2> | 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进行了优化。

--------------------------------------分割线 --------------------------------------

Ubuntu 14.04下安装MySQL http://www.linuxidc.com/Linux/2014-05/102366.htm

《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF http://www.linuxidc.com/Linux/2014-03/98821.htm

Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL http://www.linuxidc.com/Linux/2014-05/102351.htm

Ubuntu 14.04下搭建MySQL主从服务器 http://www.linuxidc.com/Linux/2014-05/101599.htm

Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群 http://www.linuxidc.com/Linux/2013-11/93019.htm

Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb http://www.linuxidc.com/Linux/2013-08/89270.htm

MySQL-5.5.38通用二进制安装 http://www.linuxidc.com/Linux/2014-07/104509.htm

--------------------------------------分割线 --------------------------------------

本文永久更新链接地址http://www.linuxidc.com/Linux/2014-08/105964.htm

linux
相关资讯       MySQL执行计划  MySQL执行过程 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款