一文看懂如何分析MySQL Explain(1/3)

如下点击上方小强的进阶之路”,选择“置顶或者星标”

你关注的就是我关心的!


预计阅读时间: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) ,上面例子中多出来的EXTENDEDSHOW 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相同则从上往下执行,idNULL最后执行。如下:

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列

          常见的值有:simpleprimaryunion

          dependent unionunion result

          subquerydependent subqueryderived


         ① 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`          |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+


       ③ unionunion连接的两个select查询,见

       ④ union result:包含union的结果集,在unionunion all语句中,因为它不需要参与查询,所以id字段为null,见

       ⑤ dependent subquery:表示这个subquery的查询要受到外部表查询的影响,见

     ⑥ dependent union:与union一样,出现在unionunion 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

       ⑧ derivedfrom字句中出现的子查询,也叫做派生表

    注:除了这些常见的还有很多,比如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操作产生的临时表,MN表示产生临时表的源表

  • <derivedM>:表示是由idM的表派生而来的临时表

  • <subqueryM>:表示是由idM子查询物化而来的临时表


       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一般情况下对于某个表的查询只能使用到一个索引,但单表访问方法时在某些场景下可以使用IntersectionUnionSort-Union这三种索引合并的方式来执行查询。

         ⑦ unique_subqueryunique_subquery是针对在一些包含IN子查询的查询语句中,如果查询优化器决定将IN子查询转换为EXISTS子查询而且子查询可以使用到主键进行等值匹配的话,那么该子查询执行计划的type列的值就是unique_subquery

    index_subqueryindex_subqueryunique_subquery类似,只不过访问子查询中的表时使用的是普通索引


接下文“一文看懂如何分析MySQL Explain(2/3)”

 

End

【关注】和【转发】是对小强最大的支持!!!


????????????戳二维码关注小强哦~????????????

好看你就点点我



评论