MySQL的explain语句分析

MySQL的explain语句分析

一、Explain概述

Mysql所提供的explain关键词是用于调优排患的一个神器,通过它可以分析查询语句的执行情况,DBA可以通过分析语句的执行结果对查询语句甚至表结构进行优化,例如添加索引,修改索引,使用覆盖索引等等。

二、Explain结构

创建一张学生表和一张成绩表:

CREATE TABLE `student_info`(
`id` int not null AUTO_INCREMENT comment '学生学号',
`stu_name` VARCHAR(10) NOT NULL comment '学生姓名',
`age` tinyint not null comment '年龄',
`gender` VARCHAR(1) not null comment '性别',
PRIMARY KEY (id),
key idx_stu_name(stu_name)
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

INSERT INTO student_info(stu_name,age,gender)
VALUES ('张三',20,'男'),('李四',18,'男'),('韩梅梅',20,'女'),('李华',101,'女');

CREATE TABLE `score_info`(
`id` int not null AUTO_INCREMENT comment '成绩单号',
`stu_id` int not null comment '学生学号',
`score` tinyint not null comment '分数',
PRIMARY KEY (id),
KEY idx_stu_id(stu_id)
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

INSERT INTO score_info(stu_id,score)
VALUES (1,59),(2,60),(3,80),(4,100);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

查询所有的数据,并使用explain分析执行情况:

mysql> explain select *  from student_info \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student_info
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

字段含义: 
id:Select语句的标识符,代表查询语句的顺序 
select_type:查询的类型(单个查询,还是有连接union) 
table:使用的哪张表 
partitions:使用哪个分区 
type:查询的方式或者访问方式 
possible_keys:可能使用的索引 
key:实际使用的索引 
key_len:索引长度 
ref:使用哪个列与索引一起从表中选择 
rows:结果又多少行 
filtered:数据被过滤之后,剩余结果的百分比 
Extra:额外的执行情况描述 
下面着重分析几个非常重要的字段的作用。

三、字段详解

select_type:表示查询的类型,主要有SIMPLE,PRIMARY,UNION,SUBQUERY,分别表示不使用表连接,主查询,UNION中之后的查询,子查询中的第一个,如下:

mysql> explain select * from student_info where id not in  (select id from student_info)\G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: student_info
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: student_info
   partitions: NULL
         type: unique_subquery
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: func
         rows: 1
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28

type:表示查询的方式或者访问的方式,常见的取值有:

  • system:表中仅仅只有一条匹配行

  • const:针对主键或者唯一索引的扫描,且结果仅仅返回一行数据

    mysql> explain select id from student_info where id =1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • ref:通常出现在多表的连接查询,使用了普通索引,或者使用了前缀扫描规则:
mysql> explain select * from student_info st inner join score_info sc on st.id=sc.stu_id where st.id=1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: st
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: sc
   partitions: NULL
         type: ref
possible_keys: idx_stu_id
          key: idx_stu_id
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • range:在索引范围查询,且使用了<、<=、>、>=、between语句:
mysql> explain select * from student_info where id>1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student_info
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • index:表示扫描整个的索引文件,在索引树中可以找到所需要的数据:
mysql> explain select stu_name  from student_info\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student_info
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_stu_name
      key_len: 32
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • ALL:表示全表扫描,这是相当于没有使用任何的索引列,因此非常耗时,建了索引却不用,那索引的意义何在呢,因此必须要调整:
mysql> explain select * from student_info\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student_info
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

key:查询中所使用到的索引,如果建了索引,且key为空时,相当于未使用索引,需要进行调整 
extra: 一些额外的信息,主要有一下几种类型:

  • Using index:表示使用了覆盖索引扫描即所查询的列上都建立了索引,是非常良好的一种情况:
mysql> explain select id,stu_name from student_info\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student_info
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_stu_name
      key_len: 32
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • Using filesort:表示一些额外的排序工作不能通过索引完成,需要占用cpu资源去完成,应当优化:
mysql> explain select * from student_info order by stu_name\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student_info
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
     //stu_name字段上并未建立排序规则,因此需要mysql自身对结果进行排序,耗费cpu资源
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • Using tempory:表示使用了临时表,多出现于连表查询的情况,需要优化:
mysql> explain select * from student_info st inner join score_info sc  on st.id=sc.stu_id order by st.id\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: st
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: sc
   partitions: NULL
         type: ALL
possible_keys: idx_stu_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • Using where:出现了回表情况,如上
上一篇:《Spark大数据分析:核心概念、技术及实践》一3.10 总结


下一篇:让网站永久拥有HTTPS - 申请免费SSL证书并自动续期