一文看懂如何分析MySQL Explain（1/3）
一文看懂如何分析MySQL Explain（1/3）
程序员小强
发布于 2019-06-11 19:03:11
发布于 2019-06-11 19:03:11
预计阅读时间：30分钟
在网上经常看到一些写SQL优化经历的文章，看完文章发现懂的不用看，不懂的看不懂，大家都是都在讲调优经历，但是忽略了如何看懂执行计划，如何调优。本文不讲调优经历，只讲如何看懂执行计划及常用的调优原则，从而可以有针对性的提升我们查询语句的性能。
如何查看执行计划
执行以下SQL语句：
EXPLAIN EXTENDED
SELECT * FROM t_student
WHERE classes_id IN ( SELECT id FROM t_classes );
SHOW WARNINGS;是不是感觉很奇怪？怎么跟其他人说的不一样？可能大家印象中都是加上EXPLAIN即可，类似于这种：EXPLAIN SELECT * FROM t_student WHERE classes_id IN (SELECT id FROM t_classes) ，上面例子中多出来的EXTENDED和SHOW WARNINGS是什么鬼？请继续往下看。
加上EXTENDED关键字执行计划会多出filtered列；加上EXTENDED，最后加上SHOW WARNINGS命令可以查看MySQL引擎改写之后的SQL，以上查询SQL会被改写为：
SELECT `test`.`t_student`.`id` AS `id`,
`test`.`t_student`.`classes_id` AS `classes_id`,
`test`.`t_student`.`student_name` AS `student_name`,
`test`.`t_student`.`age` AS `age`,
`test`.`t_student`.`id_card` AS `id_card`,
`test`.`t_student`.`address` AS `address`,
`test`.`t_student`.`no` AS `no`,
`test`.`t_student`.`status` AS `status`
FROM `test`.`t_classes`
JOIN `test`.`t_student`
WHERE ( `test`.`t_classes`.`id` = `test`.`t_student`.`classes_id` )以下是上面SQL的执行计划：
EXPLAIN EXTENDED select * from t_student WHERE classes_id in (select id from t_classes); show WARNINGS;
+----+-------------+-----------+--------+---------------+---------+---------+---------------------------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+--------+---------------+---------+---------+---------------------------+------+----------+-------------+
| 1 | PRIMARY | t_student | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | |
| 1 | PRIMARY | t_classes | eq_ref | PRIMARY | PRIMARY | 4 | test.t_student.classes_id | 1 | 100.00 | Using index |
+----+-------------+-----------+--------+---------------+---------+---------+---------------------------+------+----------+-------------+建表SQL
CREATE TABLE `t_classes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`classes_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '班级名称',
`grade` int(11) NOT NULL COMMENT '年级',
`student_num` int(11) NOT NULL COMMENT '学生数',
`head_teacher_id` int(11) NOT NULL COMMENT '班主任',
`status` tinyint(4) NOT NULL COMMENT '状态',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
PRIMARY KEY (`id`),
KEY `idx_grade` (`grade`) COMMENT '年级索引',
KEY `idx_name_grade` (`grade`,`classes_name`) USING BTREE,
KEY `idx_classes_name` (`classes_name`) USING BTREE COMMENT '班级名索引',
KEY `idx_name_grade_student_num_teacher` (`grade`,`classes_name`,`student_num`,`head_teacher_id`),
KEY `idx_create_time` (`create_time`),
KEY `idx_student_num` (`student_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `t_score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL COMMENT '学生id',
`score` int(255) NOT NULL COMMENT '分数',
`subject_id` int(11) NOT NULL COMMENT '学科id',
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_user` (`student_id`),
KEY `idx_score` (`score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `t_student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
`classes_id` int(11) DEFAULT NULL COMMENT '班级',
`student_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '学生姓名',
`age` int(11) NOT NULL COMMENT '年龄',
`id_card` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '身份证号',
`address` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '地址',
`no` int(11) NOT NULL COMMENT '学号',
`status` tinyint(4) NOT NULL COMMENT '状态',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_id_card` (`id_card`),
KEY `idx_classes_id` (`classes_id`),
KEY `idx_classes_name_card_no` (`classes_id`,`student_name`,`id_card`,`no`),
KEY `idx_student_name` (`student_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `t_student_copy1` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
`classes_id` int(11) NOT NULL COMMENT '班级',
`student_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '学生姓名',
`age` int(11) NOT NULL COMMENT '年龄',
`id_card` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '身份证号',
`address` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '地址',
`no` int(11) NOT NULL COMMENT '学号',
`status` tinyint(4) NOT NULL COMMENT '状态',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_id_card` (`id_card`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `t_subject` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学科id',
`subject_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '学科名',
`classes_id` int(11) NOT NULL COMMENT '班级id',
`teacher_id` int(11) DEFAULT NULL COMMENT '教师id',
PRIMARY KEY (`id`),
KEY `idx_teacher_id` (`teacher_id`),
KEY `idx_classes_id` (`classes_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `t_teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`teacher_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '教师姓名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(2550) COLLATE utf8_unicode_ci NOT NULL,
`birthday` date NOT NULL,
`sex` tinyint(255) NOT NULL,
`address` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;执行计划字段分析
1、id列
id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。MySQL将 select 查询分为简单查询(SIMPLE)和复杂查询(PRIMARY)。复杂查询分为三类：简单子查询、派生表（from语句中的子查询）、union 查询。id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。如下：
EXPLAIN EXTENDED SELECT *,(select classes_name from t_classes) classes_name FROM t_student WHERE classes_id = 1;
+----+-------------+-----------+-------+---------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+-------+---------------+------------------+---------+------+------+----------+-------------+
| 1 | PRIMARY | t_student | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 2 | SUBQUERY | t_classes | index | NULL | idx_classes_name | 767 | NULL | 20 | 100.00 | Using index |
+----+-------------+-----------+-------+---------------+------------------+---------+------+------+----------+-------------+EXPLAIN EXTENDED SELECT * FROM t_student WHERE id = 1 UNION ALL SELECT * FROM t_student WHERE id = 2 ;
+------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 2 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | |
+------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+2、select_type列
常见的值有：simple、primary、union、
dependent union、union result、
subquery、dependent subquery、derived
① simple：表示不需要union操作或者不包含子查询的简单select查询或连接查询，如：
EXPLAIN EXTENDED select * from t_classes;
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t_classes | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------+EXPLAIN EXTENDED select * from t_student inner join t_classes on t_student.classes_id = t_classes.id left join t_subject on t_subject.classes_id = t_classes.id; SHOW WARNINGS;
+----+-------------+-----------+--------+---------------+---------+---------+---------------------------+------+----------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+--------+---------------+---------+---------+---------------------------+------+----------+-------------------------------------------------+
| 1 | SIMPLE | t_student | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | |
| 1 | SIMPLE | t_classes | eq_ref | PRIMARY | PRIMARY | 4 | test.t_student.classes_id | 1 | 100.00 | |
| 1 | SIMPLE | t_subject | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (flat, BNL join) |
+----+-------------+-----------+--------+---------------+---------+---------+---------------------------+------+----------+-------------------------------------------------+
改写之后的SQL
SELECT `test`.`t_student`.`id` AS `id`,
`test`.`t_student`.`classes_id` AS `classes_id`,
`test`.`t_student`.`student_name` AS `student_name`,
`test`.`t_student`.`age` AS `age`,
`test`.`t_student`.`id_card` AS `id_card`,
`test`.`t_student`.`address` AS `address`,
`test`.`t_student`.`no` AS `no`,
`test`.`t_student`.`status` AS `status`,
`test`.`t_classes`.`id` AS `id`,
`test`.`t_classes`.`classes_name` AS `classes_name`,
`test`.`t_classes`.`grade` AS `grade`,
`test`.`t_classes`.`student_num` AS `student_num`,
`test`.`t_classes`.`head_teacher_id` AS `head_teacher_id`,
`test`.`t_classes`.`status` AS `status`,
`test`.`t_classes`.`create_time` AS `create_time`,
`test`.`t_classes`.`update_time` AS `update_time`,
`test`.`t_subject`.`id` AS `id`,
`test`.`t_subject`.`subject_name` AS `subject_name`,
`test`.`t_subject`.`classes_id` AS `classes_id`
FROM `test`.`t_student`
JOIN `test`.`t_classes`
LEFT JOIN `test`.`t_subject` ON ( ( `test`.`t_subject`.`classes_id` = `test`.`t_student`.`classes_id` ) )
WHERE ( `test`.`t_classes`.`id` = `test`.`t_student`.`classes_id` )② primary：一个需要union操作或含有子查询的select，位于最外层的单位查询的select_type即为primary。
EXPLAIN EXTENDED SELECT *,(select classes_name from t_classes where id = t_student.classes_id) from t_student; SHOW WARNINGS;
+----+--------------------+-----------+--------+---------------+---------+---------+---------------------------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-----------+--------+---------------+---------+---------+---------------------------+------+----------+-------+
| 1 | PRIMARY | t_student | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | |
| 2 | DEPENDENT SUBQUERY | t_classes | eq_ref | PRIMARY | PRIMARY | 4 | test.t_student.classes_id | 1 | 100.00 | |
+----+--------------------+-----------+--------+---------------+---------+---------+---------------------------+------+----------+-------+
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'test.t_student.classes_id' of SELECT #2 was resolved in SELECT #1 |
| Note | 1003 | select `test`.`t_student`.`id` AS `id`,`test`.`t_student`.`classes_id` AS `classes_id`,`test`.`t_student`.`student_name` AS `student_name`,`test`.`t_student`.`age` AS `age`,`test`.`t_student`.`id_card` AS `id_card`,`test`.`t_student`.`address` AS `address`,`test`.`t_student`.`no` AS `no`,`test`.`t_student`.`status` AS `status`,<expr_cache><`test`.`t_student`.`classes_id`>((select `test`.`t_classes`.`classes_name` from `test`.`t_classes` where (`test`.`t_classes`.`id` = `test`.`t_student`.`classes_id`))) AS `(select classes_name from t_classes where id = t_student.classes_id)` from `test`.`t_student` |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+EXPLAIN EXTENDED select * from (select * from t_classes where id = 1 union all select * from t_classes where id = 2) a;show WARNINGS;
+-------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | |
| 2 | DERIVED | t_classes | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | |
| 3 | UNION | t_classes | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | |
+-------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
4 rows in set, 1 warning (0.00 sec)
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `a`.`id` AS `id`,`a`.`classes_name` AS `classes_name`,`a`.`grade` AS `grade`,`a`.`student_num` AS `student_num`,`a`.`head_teacher_id` AS `head_teacher_id`,`a`.`status` AS `status`,`a`.`create_time` AS `create_time`,`a`.`update_time` AS `update_time` from (select 1 AS `id`,'1701班' AS `classes_name`,2 AS `grade`,55 AS `student_num`,1 AS `head_teacher_id`,1 AS `status`,'2017-08-15 00:00:00' AS `create_time`,'0000-00-00 00:00:00' AS `update_time` from `test`.`t_classes` where 1 union all select 2 AS `id`,'1702班' AS `classes_name`,2 AS `grade`,56 AS `student_num`,1 AS `head_teacher_id`,1 AS `status`,'2017-08-15 00:00:00' AS `create_time`,'2019-05-23 15:47:36' AS `update_time` from `test`.`t_classes` where 1) `a` |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+③ union：union连接的两个select查询，见②
④ union result：包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null，见②
⑤ dependent subquery：表示这个subquery的查询要受到外部表查询的影响，见②
⑥ dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
EXPLAIN EXTENDED select * from t_student where classes_id in ( select id from t_classes where id = 1 union all select id from t_classes where id = 2 );SHOW WARNINGS;
+-------+--------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-------+--------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | t_student | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t_classes | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
| 3 | DEPENDENT UNION | t_classes | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | |
+-------+--------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
4 rows in set, 1 warning (0.00 sec)
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t_student`.`id` AS `id`,`test`.`t_student`.`classes_id` AS `classes_id`,`test`.`t_student`.`student_name` AS `student_name`,`test`.`t_student`.`age` AS `age`,`test`.`t_student`.`id_card` AS `id_card`,`test`.`t_student`.`address` AS `address`,`test`.`t_student`.`no` AS `no`,`test`.`t_student`.`status` AS `status` from `test`.`t_student` where <expr_cache><`test`.`t_student`.`classes_id`>(<in_optimizer>(`test`.`t_student`.`classes_id`,<exists>(select 1 from `test`.`t_classes` where (<cache>(`test`.`t_student`.`classes_id`) = 1) union all select 2 from `test`.`t_classes` where (<cache>(`test`.`t_student`.`classes_id`) = 2)))) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+⑦subquery：除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery
⑧ derived ：from字句中出现的子查询，也叫做派生表。
注：除了这些常见的还有很多，比如MATERIALIZED，文中不再一一列举，MySQL执行计划太复杂，不同的写法执行计划也不尽相同，建议大家分析执行计划时候对照MySQL改写之后的SQL分析，因为MySQL执行的是改写之后的SQL，很可能和原SQL完全不同，对照原SQL分析执行计划会看晕
3、table列
不论我们的查询语句有多复杂，里边儿包含了多少个表，到最后也是需要对每个表进行单表访问的，EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。如：
EXPLAIN EXTENDED select * from (select id,student_name,classes_id from t_student group by classes_id) a where a.classes_id in ( select id from t_classes where id in (select classes_id from t_student) union all select id from t_classes where id = 2 );SHOW WARNINGS;
+-------+--------------------+-------------+--------+---------------+--------------+----------+-------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-------+--------------------+-------------+--------+---------------+--------------+----------+-------+------+----------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | t_classes | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using index |
| 3 | DEPENDENT SUBQUERY | <subquery4> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | |
| 4 | MATERIALIZED | t_student | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | |
| 5 | DEPENDENT UNION | t_classes | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
| NULL | UNION RESULT | <union3,5> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | |
| 2 | DERIVED | t_student | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using temporary; Using filesort |
+-------+--------------------+-------------+--------+---------------+--------------+----------+-------+------+----------+---------------------------------+
7 rows in set, 1 warning (0.00 sec)
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `a`.`id` AS `id`,`a`.`student_name` AS `student_name`,`a`.`classes_id` AS `classes_id` from (select `test`.`t_student`.`id` AS `id`,`test`.`t_student`.`student_name` AS `student_name`,`test`.`t_student`.`classes_id` AS `classes_id` from `test`.`t_student` group by `test`.`t_student`.`classes_id`) `a` where <expr_cache><`a`.`classes_id`>(<in_optimizer>(`a`.`classes_id`,<exists>(select `test`.`t_classes`.`id` from `test`.`t_classes` semi join (`test`.`t_student`) where (1 and (<cache>(`a`.`classes_id`) = `test`.`t_classes`.`id`)) union all select 2 from `test`.`t_classes` where (<cache>(`a`.`classes_id`) = 2)))) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+table列包含以下四种值：
- NULL：比如select一些与数据库表无关的内容，如select now()
- <unionM,N>：表示由UNION操作产生的临时表，M和N表示产生临时表的源表
- <derivedM>：表示是由id为M的表派生而来的临时表
- <subqueryM>：表示是由id为M的子查询物化而来的临时表
4、type列
type一共有12种方式，包括
① system/const，如果是根据主键查询或根据唯一索引查询，查询起来非常迅速，这个匹配行中的其他列中的值可以被优化器在当前查询中当做常量来处理。例如根据主键或者唯一索引进行的查询。如：
EXPLAIN EXTENDED select * from t_student where id = 1;SHOW WARNINGS;
+-------+-------------+-----------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-------+-------------+-----------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_student | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | |
+-------+-------------+-----------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select 1 AS `id`,1 AS `classes_id`,'张三' AS `student_name`,10 AS `age`,'1300000000' AS `id_card`,'中国' AS `address`,1 AS `no`,1 AS `status` from `test`.`t_student` where 1 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+注：有文章说system是const的一种特殊情况，表本身只有一行数据的话type是system，也有文章说这种类型只出现在MyISAM/Memory存储引擎,InnoDB并不存在这种连接类型，而且笔者没试出来system的类型，这里不再做赘述
② eq_ref：在连接查询时，如果被驱动表是通过主键或者唯一索引列等值匹配的方式进行访问的（如果该主键或者唯一索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq_ref。如下：
EXPLAIN EXTENDED select * from t_student inner join t_classes on t_student.classes_id = t_classes.id;SHOW WARNINGS;
+-------+-------------+-----------+--------+---------------+---------+---------+---------------------------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-------+-------------+-----------+--------+---------------+---------+---------+---------------------------+------+----------+-------+
| 1 | SIMPLE | t_student | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | |
| 1 | SIMPLE | t_classes | eq_ref | PRIMARY | PRIMARY | 4 | test.t_student.classes_id | 1 | 100.00 | |
+-------+-------------+-----------+--------+---------------+---------+---------+---------------------------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t_student`.`id` AS `id`,`test`.`t_student`.`classes_id` AS `classes_id`,`test`.`t_student`.`student_name` AS `student_name`,`test`.`t_student`.`age` AS `age`,`test`.`t_student`.`id_card` AS `id_card`,`test`.`t_student`.`address` AS `address`,`test`.`t_student`.`no` AS `no`,`test`.`t_student`.`status` AS `status`,`test`.`t_classes`.`id` AS `id`,`test`.`t_classes`.`classes_name` AS `classes_name`,`test`.`t_classes`.`grade` AS `grade`,`test`.`t_classes`.`student_num` AS `student_num`,`test`.`t_classes`.`head_teacher_id` AS `head_teacher_id`,`test`.`t_classes`.`status` AS `status`,`test`.`t_classes`.`create_time` AS `create_time`,`test`.`t_classes`.`update_time` AS `update_time` from `test`.`t_student` join `test`.`t_classes` where (`test`.`t_classes`.`id` = `test`.`t_student`.`classes_id`) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+③ ref：当通过普通索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref。比如：
EXPLAIN EXTENDED select * from t_subject where teacher_id = 1;SHOW WARNINGS;
+-------+-------------+-----------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-------+-------------+-----------+------+----------------+----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_subject | ref | idx_teacher_id | idx_teacher_id | 5 | const | 1 | 100.00 | |
+-------+-------------+-----------+------+----------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t_subject`.`id` AS `id`,`test`.`t_subject`.`subject_name` AS `subject_name`,`test`.`t_subject`.`classes_id` AS `classes_id`,`test`.`t_subject`.`teacher_id` AS `teacher_id` from `test`.`t_subject` where (`test`.`t_subject`.`teacher_id` = 1) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+④ fulltext：全文索引，暂时不讲，忽略
⑤ ref_or_null：当对普通索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null，比如说：
EXPLAIN EXTENDED select * from t_subject where teacher_id = 1 or teacher_id is null ;SHOW WARNINGS;
+--------+-------------+-----------+-------------+----------------+----------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+--------+-------------+-----------+-------------+----------------+----------------+---------+-------+------+----------+------------------------------------+
| 1 | SIMPLE | t_subject | ref_or_null | idx_teacher_id | idx_teacher_id | 5 | const | 46 | 100.00 | Using index condition; Using where |
+--------+-------------+-----------+-------------+----------------+----------------+---------+-------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note v| 1003 | select `test`.`t_subject`.`id` AS `id`,`test`.`t_subject`.`subject_name` AS `subject_name`,`test`.`t_subject`.`classes_id` AS `classes_id`,`test`.`t_subject`.`teacher_id` AS `teacher_id` from `test`.`t_subject` where ((`test`.`t_subject`.`teacher_id` = 1) or isnull(`test`.`t_subject`.`teacher_id`)) |
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+注：跟数据量有关，如果表数据量太小type可能退化成ALL，数据量稍微大点会type会转为ref，或者为null的数据太多也可能退化成ALL，如果为null的数据量比较小可能会是ref_or_null
⑥ index_merge：一般情况下对于某个表的查询只能使用到一个索引，但单表访问方法时在某些场景下可以使用Intersection、Union、Sort-Union这三种索引合并的方式来执行查询。
⑦ unique_subquery：unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery。
⑧index_subquery：index_subquery与unique_subquery类似，只不过访问子查询中的表时使用的是普通索引
接下文“一文看懂如何分析MySQL Explain（2/3）”
End
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2019-06-06，如有侵权请联系 cloudcommunity@tencent.com 删除
本文分享自 MoziInnovations 微信公众号，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体分享计划 ，欢迎热爱写作的你一起参与！
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。