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

  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 ?
    【mysql】mysql explain 分析结果 id列有点疑惑

  4. 为什么 in 的子句是SIMPLE简单查询

回答

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)

以上是 【mysql】mysql explain 分析结果 id列有点疑惑 的全部内容， 来源链接： utcz.com/a/74891.html

回到顶部