Mysql Explain学习笔记
1、Mysql版本
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.24-log |
+------------+
1 row in set (0.06 sec)
2、建表
create table student(
stu_id bigint primary key auto_increment,
stu_name varchar(20),
stu_age int(10),
course_id int(10)
)
create table course(
course_id int(10) primary key,
course_name varchar(50)
)
3、插入数据
insert into student(stu_name,stu_age,course_id) values("zhangsan",20,1);
insert into student(stu_name,stu_age,course_id) values("lisi",21,2);
insert into student(stu_name,stu_age,course_id) values("wangwu",22,3);
insert into student(stu_name,stu_age,course_id) values(null,23,3);
insert into course values(1,"数学");
insert into course values(2,"英语");
insert into course values(3,"体育");
4、创建索引
alter table student add key(stu_id,stu_name,stu_age);
5、explain
explain命令是用来查看mysql优化器执行sql语句的情况，方便我们对sql进行优化。
5.1、字段总览
先使用explain命令查询。
mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.02 sec)
其中，字段有| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |。
5.2、id
只有一个查询语句，里面的id就只有一个。
如果有嵌套查询
mysql> explain select * from student,course where student.course_id = course.course_id;
+----+-------------+---------+------------+--------+---------------+---------+---------+--------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+--------+---------------+---------+---------+--------------------------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
| 1 | SIMPLE | course | NULL | eq_ref | PRIMARY | PRIMARY | 4 | my2019.student.course_id | 1 | 100.00 | NULL |
+----+-------------+---------+------------+--------+---------------+---------+---------+--------------------------+------+----------+-------------+
2 rows in set (0.03 sec)
mysql> explain select * from student where course_id = (select course_id from course where course_name = '数学');
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | PRIMARY | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 2 | SUBQUERY | course | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set (0.13 sec)
如果两个id的值相同，则是从上到下执行，不相同则id越大的越先执行。
5.3、select_type
select_type包含simple,primary,derived,union,union result,subquery等。
5.3.1、simple
simple是不包含子查询和其他关键字的简单查询。
mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.33 sec)
5.3.2、primary
在一个存在子查询的sql语句中，最外层的select就是primary。
mysql> explain select * from student where course_id = (select course_id from course where course_name = '数学');
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | PRIMARY | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 2 | SUBQUERY | course | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set (0.13 sec)
5.3.3、derived
一个虚拟的表就是derived，比如在查询中使用到的一张虚拟表。
mysql> explain select * from (select * from student limit 1) b;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 2 | DERIVED | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
2 rows in set (0.14 sec)
其中，用到了select * from student limit 1，是mysql内部的一张虚拟的表。
5.3.4、subquery
子查询。
mysql> explain select * from student where course_id = (select course_id from course where course_name = '数学');
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | PRIMARY | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 2 | SUBQUERY | course | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set (0.13 sec)
其中，select course_id from course where course_name = '数学'是一个子查询语句。
5.3.5、union
使用了union关键字的右边的查询。
mysql> explain select * from student union all select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | PRIMARY | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 2 | UNION | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set (0.35 sec)
5.3.6、union result
使用了union联合的结果。
mysql> explain select * from student union select * from student;
+------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 2 | UNION | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set (0.12 sec)
5.4、table
所用到的表。有两种情况，一是真实的表名，二是虚拟表。
mysql> explain select * from (select * from student limit 1) b;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 2 | DERIVED | student | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
2 rows in set (0.14 sec)
其中，id为2的是查询的student表，并且是一张虚拟表，而id为1的是derived2，也就是derived[id]，id表示字段中的id，意思就是说id为1使用的表是id为2里面生成的。
5.5、type
type的值表示查询的连接类型，有多个参数。
性能从好到坏： null > system/const > eq_ref > ref > ref_or_null >index_merge > range > index > all
5.5.1、null
表示在mysql优化器在优化过程中就已经得到了结果。
mysql> explain select max(stu_id) from student;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set (0.46 sec)
5.5.2、system/const
表示在查询过程中，最多只有一条匹配的记录。
mysql> explain select * from student where stu_id = 1;
+----+-------------+---------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | student | NULL | const | PRIMARY,stu_id | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
1 row in set (0.12 sec)
5.5.3、eq_ref
表示sql里面的索引都被连接使用并且是unique或primary key类型。
mysql> explain select * from student a,course b where a.course_id = b.course_id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 1 | SIMPLE | b | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set (0.51 sec)
注意，这是出现的type是all，这是因为我们的表是InnoDB引擎的，InnoDB引擎在数据量很少的情况下type会出现all，需要将表修改成MyISAM引擎
mysql> explain select * from student a,course b where a.course_id = b.course_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 4 | rlxy93.a.course_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
2 rows in set (0.35 sec)
再次查询便是eq_ref了。
5.5.4、ref
ref和eq_ref不同的是，ref在进行关联操作时，只使用了索引的最左前缀，或者索引不是unique或primary key。
mysql> explain select * from student where stu_name="zhangsan";
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | stu_name | stu_name | 63 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set (0.18 sec)
5.5.5、ref_or_null
ref_or_null和ref类似，只不过结果里面包含null。
mysql> explain select * from student where stu_name="zhaoliu" or stu_name = null;
+----+-------------+---------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | ref_or_null | stu_name | stu_name | 63 | const | 4 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
1 row in set (0.11 sec)
5.5.6、range
表示使用了索引的范围。
mysql> explain select * from student where stu_name in ("wangwu","zhangsan");
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | range | stu_name | stu_name | 63 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set (0.12 sec)
5.5.7、index
表示对索引进行扫描。
mysql> explain select stu_name from student;
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | index | NULL | stu_name | 68 | NULL | 4 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set (0.21 sec)
5.5.8、all
表示对全表进行扫描。
mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.25 sec)
5.6、possible_keys
里面的值表示能够使用到的索引。
mysql> explain select * from student where stu_name in ("wangwu","zhangsan");
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | range | stu_name | stu_name | 63 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set (0.12 sec)
其中，possible_keys的值是stu_name，表示使用到了stu_name这个索引字段。
5.7、key
表示mysql实际使用到了哪个索引。
mysql> explain select * from student where stu_name in ("wangwu","zhangsan");
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | range | stu_name | stu_name | 63 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set (0.12 sec)
其中，mysql使用到了stu_name索引。
5.8、key_len
表示mysql在索引中使用到的字节数，这个数值越小越好。
5.9、ref
表示mysql使用了哪个列或常数来和key一起寻找目标数据。
5.10、row
表示mysql为了找到目标数据而寻找的行数，这个数值越小越好。
5.11、Extra
Extra包含Not exists，range checked for each record，Using index condition，using temporary，using filesort，using where，
5.11.1、Not exists
不包含信息。
5.11.2、range checked for each record
没有找到合适的索引。
5.11.3、Using index condition
使用了覆盖索引。
5.11.4、using temporary
mysql对结果进行排序的时候使用到了一张临时的表。
5.11.5、using filesort
mysql没有按照表的索引顺序进行读取，而是使用了其他字段进行排序。
5.11.6、using where
mysql使用index或all查询到了数据之后，再进行where条件的过滤。
总结
1、首先是type，如果type里面是all，那么必须优化，因为all使用到了全表查询，对性能影响很大。
2、Extra中的using temporary需要一张临时表，对系统资源有占用，所以最好建立一个索引。
3、Extra中的using filesort是无法建立索引来查询，所以最好建立一个索引。
4、Extra中的using where是使用了index或all得到的，得到之后又需要对where进行过滤，所以最好建立一个索引。