1
  1. 首先, 在MySQL5.6 和 5.7 均作了尝试, 结果都类似
  2. 表结构很简单, 表中无数据

    mysql> show create table t1\G;
    *************************** 1. row ***************************
           Table: t1
    Create Table: CREATE TABLE `t1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    
    mysql> show create table t2\G;
    *************************** 1. row ***************************
           Table: t2
    Create Table: CREATE TABLE `t2` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    
    mysql> show create table t3\G;
    *************************** 1. row ***************************
           Table: t3
    Create Table: CREATE TABLE `t3` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    
    mysql>
  3. 问题: 为什么 in= 的EXPLAIN的结果是这种区别? in 为啥不是子查询, id都是1,而且是先执行t1, 再t2,t3 ?
    clipboard.png
  4. 为什么 in 的子句是SIMPLE简单查询
wahaha 233
2019-04-12 提问
1 个回答
0

已采纳
mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                     |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `sam`.`t1`.`id` AS `id`,`sam`.`t1`.`name` AS `name` from `sam`.`t3` join `sam`.`t2` join `sam`.`t1` where ((`sam`.`t2`.`id` = `sam`.`t1`.`id`) and (`sam`.`t3`.`id` = `sam`.`t1`.`id`) and (`sam`.`t3`.`name` = 's')) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

撰写答案

推广链接