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

MySQL的explain语句分析

2018-08-20 1437
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： 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操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
幸运券发放
目录
相关文章
互联网课堂
|
2月前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN：指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询，帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标，如 `id`、`select_type`、`table`、`type`、`key` 等，并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例，展示了如何通过创建合适索引和调整查询语句来提升查询性能。
互联网课堂
425 9
绿水长流*z
|
1月前
|
SQL 关系型数据库 MySQL
MySQL事务日志-Undo Log工作原理分析
事务的持久性是交由Redo Log来保证，原子性则是交由Undo Log来保证。如果事务中的SQL执行到一半出现错误，需要把前面已经执行过的SQL撤销以达到原子性的目的，这个过程也叫做"回滚"，所以Undo Log也叫回滚日志。
绿水长流*z
97 7
MySQL事务日志-Undo Log工作原理分析
蓝易云
|
1月前
|
关系型数据库 MySQL 数据库
mysql慢查询每日汇报与分析
通过启用慢查询日志、提取和分析慢查询日志，可以有效识别和优化数据库中的性能瓶颈。结合适当的自动化工具和优化措施，可以显著提高MySQL数据库的性能和稳定性。希望本文的详解和示例能够为数据库管理人员提供有价值的参考，帮助实现高效的数据库管理。
蓝易云
44 11
东阳马生架构
|
20天前
|
缓存 NoSQL 关系型数据库
MySQL原理简介—4.深入分析Buffer Pool
本文介绍了MySQL的Buffer Pool机制，包括其作用、配置方法及内部结构。Buffer Pool是MySQL用于缓存磁盘数据页的关键组件，能显著提升数据库读写性能。默认大小为128MB，可根据服务器配置调整（如32GB内存可设为2GB）。它通过free链表管理空闲缓存页，flush链表记录脏页，并用LRU链表区分冷热数据以优化淘汰策略。此外，还探讨了多Buffer Pool实例、chunk动态调整等优化并发性能的方法，以及如何通过`show engine innodb status`查看Buffer Pool状态。关键词：MySQL内存数据更新机制。
东阳马生架构
38 0
大数据文摘
|
2月前
|
SQL 关系型数据库 MySQL
MySQL 窗口函数详解：分析性查询的强大工具
MySQL 窗口函数从 8.0 版本开始支持，提供了一种灵活的方式处理 SQL 查询中的数据。无需分组即可对行集进行分析，常用于计算排名、累计和、移动平均值等。基本语法包括 `function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])`，常见函数有 `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `SUM()`, `AVG()` 等。窗口框架定义了计算聚合值时应包含的行。适用于复杂数据操作和分析报告。
大数据文摘
159 11
绿水长流*z
|
4月前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现，而持久性则依赖于事务日志中的`Redo Log`。在MySQL中，`Redo Log`确保已提交事务的数据能持久保存，即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改，待事务提交时写入磁盘。此外，`Redo Log`采用简单的物理日志格式和高效的顺序IO，确保快速提交。通过不同的落盘策略，可在性能和安全性之间做出权衡。
绿水长流*z
1863 14
MySQL事务日志-Redo Log工作原理分析
小王老师呀
|
4月前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则，包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限，后者又优先于表级别权限。在权限冲突时，更严格的权限将被优先执行，确保数据库的安全性与资源合理分配。
小王老师呀
83 4
糜终
|
4月前
|
SQL 关系型数据库 MySQL
MySQL 更新1000万条数据和DDL执行时间分析
MySQL 更新1000万条数据和DDL执行时间分析
糜终
346 4
baker_zhuang
|
4月前
|
SQL 自然语言处理 关系型数据库
Vanna使用ollama分析本地MySQL数据库
这篇文章详细介绍了如何使用Vanna结合Ollama框架来分析本地MySQL数据库，实现自然语言查询功能，包括环境搭建和配置流程。
baker_zhuang
695 0
猿java
|
4月前
|
SQL 关系型数据库 MySQL
MySQL EXPLAIN该如何分析？
本文将详细介绍MySQL中`EXPLAIN`关键字的工作原理及结果字段解析，帮助优化查询性能。`EXPLAIN`可显示查询SQL的执行计划，其结果包括`id`、`select_type`、`table`等字段。通过具体示例和优化建议，帮助你理解和应用`EXPLAIN`，提升数据库查询效率。
猿java
283 0

热门文章

最新文章

  • 1
    MySQL底层概述—2.InnoDB磁盘结构
  • 2
    MySQL底层概述—1.InnoDB内存结构
  • 3
    MySQL原理简介—1.SQL的执行流程
  • 4
    SpringBoot 通过集成 Flink CDC 来实时追踪 MySql 数据变动
  • 5
    嵌入式C++、STM32、MySQL、GPS、InfluxDB和MQTT协议数据可视化
  • 6
    如何实现 MySQL 的读写分离？
  • 7
    MySQL原理简介—3.生产环境的部署压测
  • 8
    Docker Compose V2 安装常用数据库MySQL+Mongo
  • 9
    MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
  • 10
    MySQL日志详解——日志分类、二进制日志bin log、回滚日志undo log、重做日志redo log
  • 1
    数据管理服务DMS支持MySQL数据库的无锁结构变更
    13
  • 2
    如何解决 MySQL 数据库服务器 CPU 飙升的情况
    61
  • 3
    join查询可以⽆限叠加吗？MySQL对join查询有什么限制吗？
    43
  • 4
    MySQL日志
    22
  • 5
    图解MySQL【日志】——磁盘 I/O 次数过高时优化的办法
    18
  • 6
    图解MySQL【日志】——两阶段提交
    19
  • 7
    图解MySQL【日志】——Redo Log
    21
  • 8
    MySQL和SQLSugar百万条数据查询分页优化
    36
  • 9
    【YashanDB 知识库】MySQL 迁移至崖山 char 类型数据自动补空格问题
    16
  • 10
    【YashanDB 知识库】如何将 mysql 含有 group by 的 SQL 转换成崖山支持的 SQL
    20

    • 相关课程

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

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    通义万相：视觉生成大模型再进化