MySQL的explain语句分析

2021-11-30 10:21:06

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);

查询所有的数据，并使用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)

字段含义：
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)

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)

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)

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)

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)

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)

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)

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)

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)

Using where：出现了回表情况，如上

原文地址https://blog.csdn.net/jackFXX/article/details/81837286

码农公寓

MySQL的explain语句分析

一、Explain概述

二、Explain结构

三、字段详解

相关文章