AI 助理
文档备案控制台
开发者社区 数据库 文章 正文

MySQL的explain语句分析

2018-08-20 1555
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL DuckDB 分析主实例，基础系列 4核8GB
RDS MySQL DuckDB 分析主实例，集群系列 4核8GB
RDS AI 助手，专业版
简介： MySQL的explain语句分析 一、Explain概述 Mysql所提供的explain关键词是用于调优排患的一个神器，通过它可以分析查询语句的执行情况，DBA可以通过分析语句的执行结果对查询语句甚至表结构进行优化，例如添加索引，修改索引，使用覆盖索引等等。

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：出现了回表情况，如上
文章标签：
云数据库 RDS MySQL 版
索引
关系型数据库
MySQL
数据库管理
关键词：
云数据库 RDS MySQL 版分析
云数据库 RDS MySQL 版explain
云数据库 RDS MySQL 版explain分析
云数据库 RDS MySQL 版语句
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
幸运券发放
目录
相关文章
顾翔
|
6月前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能（下）
使用MYSQL Report分析数据库性能
顾翔
454 158
蒋星熠Jaxonic
|
5月前
|
存储 消息中间件 监控
MySQL 到 ClickHouse 明细分析链路改造：数据校验、补偿与延迟治理
蒋星熠Jaxonic，数据领域技术深耕者。擅长MySQL到ClickHouse链路改造，精通实时同步、数据校验与延迟治理，致力于构建高性能、高一致性的数据架构体系。
蒋星熠Jaxonic
238 2
MySQL 到 ClickHouse 明细分析链路改造：数据校验、补偿与延迟治理
顾翔
|
6月前
|
缓存 监控 关系型数据库
使用MYSQL Report分析数据库性能（中）
使用MYSQL Report分析数据库性能
顾翔
454 156
顾翔
|
6月前
|
缓存 监控 关系型数据库
使用MYSQL Report分析数据库性能（上）
最终建议：当前系统是完美的读密集型负载模型，优化重点应放在减少行读取量和提高数据定位效率。通过索引优化、分区策略和内存缓存，预期可降低30%的CPU负载，同时保持100%的缓冲池命中率。建议每百万次查询后刷新统计信息以持续优化
顾翔
542 161
凉凉心.
|
5月前
|
NoSQL 算法 Redis
【Docker】（3）学习Docker中 镜像与容器数据卷、映射关系！手把手带你安装 MySql主从同步 和 Redis三主三从集群！并且进行主从切换与扩容操作，还有分析 哈希分区 等知识点！
Union文件系统（UnionFS）是一种**分层、轻量级并且高性能的文件系统**，它支持对文件系统的修改作为一次提交来一层层的叠加，同时可以将不同目录挂载到同一个虚拟文件系统下(unite several directories into a single virtual filesystem) Union 文件系统是 Docker 镜像的基础。 镜像可以通过分层来进行继承，基于基础镜像（没有父镜像），可以制作各种具体的应用镜像。
凉凉心.
671 5
蓝易云
|
7月前
|
存储 关系型数据库 MySQL
深入理解MySQL索引类型及其应用场景分析。
通过以上介绍可以看出各类MySQL指标各自拥有明显利弊与最佳实践情墁，在实际业务处理过程中选择正确型号极其重要以确保系统运作流畅而稳健。
蓝易云
230 12
蓝易云
|
8月前
|
存储 SQL 关系型数据库
MySQL的Redo Log与Binlog机制对照分析
通过合理的配置和细致的管理，这两种日志机制相互配合，能够有效地提升MySQL数据库的可靠性和稳定性。
蓝易云
260 10
技术自由圈/原疯狂创客圈
|
8月前
|
SQL 关系型数据库 MySQL
MySQL group by 底层原理详解。group by 执行 慢 原因深度分析。（图解+秒懂+史上最全）
MySQL group by 底层原理详解。group by 执行 慢 原因深度分析。（图解+秒懂+史上最全）
技术自由圈/原疯狂创客圈
973 0
MySQL group by 底层原理详解。group by 执行 慢 原因深度分析。（图解+秒懂+史上最全）
蓝易云
|
11月前
|
SQL 关系型数据库 MySQL
【MySQL】SQL分析的几种方法
以上就是SQL分析的几种方法。需要注意的是，这些方法并不是孤立的，而是相互关联的。在实际的SQL分析中，我们通常需要结合使用这些方法，才能找出最佳的优化策略。同时，SQL分析也需要对数据库管理系统，数据，业务需求有深入的理解，这需要时间和经验的积累。
蓝易云
357 12
数据库知识学习者
|
10月前
|
缓存 JSON 关系型数据库
MySQL 查询优化分析 - 常用分析方法
本文介绍了MySQL查询优化分析的常用方法EXPLAIN、Optimizer Trace、Profiling和常用监控指标。
数据库知识学习者
545 0

热门文章

最新文章

  • 1
    阿里云DTS踩坑经验分享系列｜如何使用DTS进行MySQL->ClickHouse同步
  • 2
    MySQL表连接（内连接、交叉连接、外连接、联合查询）
  • 3
    MySQL 小心使用 replace into
  • 4
    对比MySQL，一文看透HBase的能力及使用场景
  • 5
    mysql存储过程及常用函数
  • 6
    MySQL - 关键字作为列名/表名的处理方式
  • 7
    MySQL的常用操作
  • 8
    rds备份与恢复
  • 9
    MySQL next-key lock 加锁范围总结
  • 10
    mysql5.7数据库用户过期
  • 1
    【紧急救援】MySQL CPU 100%！一套组合拳教你快速定位并解决！
    600
  • 2
    MySQL中日期和时间函数的使用指南
    330
  • 3
    MySQL 与 Redis 如何保证双写一致性？
    581
  • 4
    【赵渝强老师】MySQL的事务隔离级别
    343
  • 5
    【Docker】（3）学习Docker中 镜像与容器数据卷、映射关系！手把手带你安装 MySql主从同步 和 Redis三主三从集群！并且进行主从切换与扩容操作，还有分析 哈希分区 等知识点！
    671
  • 6
    MySQL源码编译安装
    1175
  • 7
    MySQL二进制包安装
    657
  • 8
    MySQL包安装 -- SUSE系列（离线RPM包安装MySQL）
    582
  • 9
    MySQL包安装 -- SUSE系列（SUSE资源库安装MySQL）
    504
  • 10
    MySQL包安装 -- Debian系列（离线DEB包安装MySQL）
    1949

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 阿里云数据库产品家族及特性
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • MySQL 技术大全：开发、优化与运维实战
  • 阿里云数据库运维实战手册
  • 搭建电商项目架构连接MySQL

    • 推荐镜像

    更多
  • mysql
    • 下一篇
    ECS账号安全防护最佳实践