mysql explain 分析结果 id列有点疑惑

Question

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 ?
   
4. 为什么 in 的子句是SIMPLE简单查询

Answer 1

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)