解释执行计划

环境搭建

版本
5.6.44 MySQL Community Server (GPL)
建表语句

学生信息

CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `classes_id` int(11) DEFAULT NULL,
  `sex` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_idx` (`name`(191)) USING BTREE,
  KEY `classid_idx` (`classes_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

班级信息

CREATE TABLE `classes` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_idx` (`name`(191)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

使用方法

explain update student set sex = 0 where classes_id = (select id from classes where name like '初一');

输出结果

+----+-------------+---------+-------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table   | type  | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+---------+-------+---------------+-------------+---------+-------+------+-------------+
|  1 | PRIMARY     | student | range | classid_idx   | classid_idx | 5       | const |    2 | Using where |
|  2 | SUBQUERY    | classes | range | name_idx      | name_idx    | 767     | NULL  |    1 | Using where |
+----+-------------+---------+-------+---------------+-------------+---------+-------+------+-------------+
	

说明

1. id

   输出行数代表执行了几个语句。ID越大，优先级越高。

2. select_type
   PRIMARY–主查询
   SUBQUERY–子查询
   常见值

   SIMPLE(简单查询) 		
   

   explain select * from student order by id;
   

   DEPENDENT SUBQUERY(select中出现子查询)
   

   explain select name,(select name from classes where id = classes_id)	 from 	student;
   

    UNION+UNION ALL(union 语句)
   

   explain select * from student union select * from student;
   

   DERIVED(一个查询作为from的主体)
   

   explain select * from (select * from student where id >0) as t;
   

3. type（按查询效率排序）
   system(表中只有单行数据，或者空表)
   const(主键或者唯一索引等值查询)
   eq_ref（子查询中对于关联条件，获取到唯一记录，或者说，是通过子表的唯 一索引或者主键进行的关联）

   ref(非唯一关联或者符合组合索引的最左前缀原则)

   mysql> explain select name,(select name from classes where id = classes_id) from student where classes_id=1;
   +----+--------------------+---------+--------+---------------+-------------+---------+------------------------+------+-------+
   | id | select_type        | table   | type   | possible_keys | key         | key_len | ref                    | rows | Extra |
   +----+--------------------+---------+--------+---------------+-------------+---------+------------------------+------+-------+
   |  1 | PRIMARY            | student | ref    | classid_idx   | classid_idx | 5       | const                  |    2 | NULL  |
   |  2 | DEPENDENT SUBQUERY | classes | eq_ref | PRIMARY       | PRIMARY     | 4       | bpm.student.classes_id |    1 | NULL  |
   +----+--------------------+---------+--------+---------------+-------------+---------+------------------------+------+-------+
   	+----+--------------------+---------+--------+---------------+---------+---------+------------------------+------+-------+
   

   range(范围查询:>, <, between ,in, like ‘a%’)
   index(索引覆盖，没有回表获取其他字段的情况)
   all(全表扫描)

4. Extra(其他信息)
   null(最好的情况)
   Distinct+Using tempory(生成了临时表)
   using filesort(server层排序)
   using index(索引覆盖)
   using where (server层过滤)
   using index condition(innodb层过滤)

一些特殊情况
当过滤条件不能过滤掉大部分数据的时候，有可能出现possible key有值，但是type是all，不走索引的情况。
如果确定需要使用索引，可以指定强制使用
explain select * from t1 force index (name_idx) where name in(‘a’)