MySQL执行计划的深入分析

前言

在之前的面试过程中,问到执行计划,有很多童鞋不知道是什么?甚至将执行计划与执行时间认为是同一个概念。今天我们就一起来了解一下执行计划到底是什么?有什么用途?

执行计划是什么?

执行计划，简单的来说，是SQL在数据库中执行时的表现情况,通常用于SQL性能分析,优化等场景。

一. 执行计划能告诉我们什么？

  • SQL如何使用索引
  • 联接查询的执行顺序
  • 查询扫描的数据函数

二. 执行计划中的内容

SQL执行计划的输出可能为多行，每一行代表对一个数据库对象的操作

1. ID列

  • ID列中的如果数据为一组数字，表示执行SELECT语句的顺序；如果为NULL，则说明这一行数据是由另外两个SQL语句进行 UNION操作后产生的结果集
  • ID值相同时，说明SQL执行顺序是按照显示的从上至下执行的
  • ID值不同时，ID值越大代表优先级越高，则越先被执行

演示

可以看到上面的执行计划返回了3行结果，id列的值可以看作是SQL中所具有的SELECT操作的序号

由于上述SQL中只有一个SELECT，所以id全为1，因此，我们就要按照由上至下读取执行计划

按照我们的SQL语句，我们会认为执行顺序是a,b,c,但是通过上图可以发现，Mysql并不是完成按照SQL中所写的顺序来进行表的关联操作的

执行对表的执行顺序为a,c,b，这是由于MySQL优化器会根据表中的索引的统计信息来调整表关联的实际顺序

2. SELECT_TYPE列

值 含义
SIMPLE 不包含子查询或是UNION操作的查询
PRIMARY 查询中如果包含任何子查询，那么最外层的查询则被标记为PRIMARY
SUBQUERY SELECT 列表中的子查询
DEPENDENT SUBQUERY 依赖外部结果的子查询
UNION Union操作的第二个或是之后的查询的值为union
DEPENDENT UNION 当UNION作为子查询时，第二或是第二个后的查询的select_type值
UNION RESULT UNION产生的结果集
DERIVED 出现在FROM子句中的子查询

3. TABLE列

包含以下几种结果：

输出去数据行所在表的名称，如果表取了别名，则显示的是别名
<union M,N>： 由ID为M,N查询union产生的结果集
<derived N>/<subquery N> ：由ID为N的查询产生的结果

4. PARTITIONS列:

查询匹配的记录来自哪一个分区
对于分区表，显示查询的分区ID
对于非分区表，显示为NULL

5. TYPE列

按性能从高至低排列如下：

值 含义
system 这是const联接类型的一个特例，当查询的表只有一行时使用
const 表中有且只有一个匹配的行时使用，如对主键或是唯一索引的查询，这是效率最高的联接方式
eq_ref 唯一索引或主键索引查询，对应每个索引键，表中只有一条记录与之匹配
ref 非唯一索引查找，返回匹配某个单独值的所有行
ref_or_null 类似于ref类型的查询，但是附加了对NULL值列的查询
index_merge 该联接类型表示使用了索引合并优化方法
range 索引范围扫描，常见于between、>、<这样的查询条件
index FULL index Scan 全索引扫描，同ALL的区别是，遍历的是索引树
ALL FULL TABLE Scan 全表扫描，这是效率最差的联接方式

6. Extra列

包含MySQL如何执行查询的附加信息

值 含义
Distinct 优化distinct操作，在找到第一个匹配的元素后即停止查找
Not exists 使用not exists来优化查询
Using filesort 使用额外操作进行排序，通常会出现在order by或group by查询中
Using index 使用了覆盖索引进行查询
Using temporary MySQL需要使用临时表来处理查询，常见于排序，子查询，和分组查询
Using where 需要在MySQL服务器层使用WHERE条件来过滤数据
select tables optimized away 直接通过索引来获得数据，不用访问表，这种情况通常效率是最高的

7. POSSIBLE_KEYS列

指出MySQL能使用哪些索引来优化查询

查询列所涉及到的列上的索引都会被列出，但不一定会被使用

8. KEY列

查询优化器优化查询实际所使用的索引

如果表中没有可用的索引，则显示为NULL

如果查询使用了覆盖索引，则该索引仅出现在Key列中

9. KEY_LEN列

显示MySQL索引所使用的字节数，在联合索引中如果有3列，假如3列字段总长度为100个字节，Key_len显示的可能会小于100字节，比如30字节，这就说明在查询过程中没有使用到联合索引的所有列，只是利用到了前面的一列或2列

  • 表示索引字段的最大可能长度
  • Key_len的长度由字段定义计算而来，并非数据的实际长度

10. Ref列

表示当前表在利用Key列记录中的索引进行查询时所用到的列或常量

11. rows列

  • 表示MySQL通过索引的统计信息，估算出来的所需读取的行数（关联查询时，显示的是每次嵌套查询时所需要的行数）
  • Rows值的大小是个统计抽样结果，并不十分准确

12. Filtered列

  • 表示返回结果的行数占需读取行数的百分比
  • Filtered列的值越大越好（值越大，表明实际读取的行数与所需要返回的行数越接近）
  • Filtered列的值依赖统计信息，所以同样也不是十分准确，只是一个参考值

三. 执行计划的限制

  • 无法展示存储过程，触发器，UDF对查询的影响
  • 无法使用EXPLAIN对存储过程进行分析
  • 早期版本的MySQL只支持对SELECT语句进行分析

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对我们的支持。

赞 (0)

相关推荐

  • 详解MySQL执行原理、逻辑分层、更改数据库处理引擎

    详解MySQL执行原理、逻辑分层、更改数据库处理引擎

    用了那么长时间的MySQL,sql语句相信早已烂熟于心,于是,我就试着去了解它的执行原理,以下是我学习过程中的总结要点. 只要是B/S架构,都是会有客户端与服务端,mysql也不例外. 首先客户端发出一个请求,这个请求就是一个查询请求(Select),而它请求的对象就是服务端,服务端是怎么处理这项查询功能的呢? 逻辑分层 首先可以把服务端想象成一个大的容器,里面有四层结构,当一个请求过来后,将会执行这四层,执行一遍后才会返回给我们想要的结果. 第一层 :连接层 也就是说,我们客户端发送一个Sel

  • mysql执行计划id为空（UNION关键字）详解

    mysql执行计划id为空（UNION关键字）详解

    简介 在工作过程中,有时候会对慢查询进行调优.对于MySQL的SQL语句调优,MySQL本身提供了强大的explain关键字用于查询分析执行计划.本文主要给大家介绍了关于mysql执行计划id为空(UNION关键字)的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的 介绍吧 UNION 操作符用于合并两个或多个 SELECT 语句的结果集.例如,我有两个表,表1记录的是公司男员工的数据,包括年龄.姓名.职位.表2记录的是公司女员工的数据,包括姓名.家庭住址.手机号等字段.这时,

  • MySQL中执行计划explain命令示例详解

    MySQL中执行计划explain命令示例详解

    前言 explain命令是查看查询优化器如何决定执行查询的主要方法. 这个功能有局限性,并不总会说出真相,但它的输出是可以获取的最好信息,值得花时间去了解,因为可以学习到查询是如何执行的. 调用EXPLAIN 在select之前添加explain,mysql会在查询上设置一个标记,当执行查询计划时,这个标记会使其返回关于执行计划中每一步的信息,而不是执行它. 它会返回一行或多行信息,显示出执行计划中的每一部分和执行次序. 这是一个简单的explain效果: 在查询中每个表在输出只有一行,如果查询

  • MySQL中通过EXPLAIN如何分析SQL的执行计划详解

    前言 在MySQL中,我们可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序. 下面分别对EXPLAIN命令结果的每一列进行说明: .select_type:表示SELECT的类型,常见的取值有: 类型 说明 SIMPLE 简单表,不使用表连接或子查询 PRIMARY 主查询,即外层的查询 UNION UNION中的第二个或者后面的查询语句 SUBQUERY 子查询中的第一个 .table:输出结果集的表(表别名) .t

  • MySql中如何使用 explain 查询 SQL 的执行计划

    explain命令是查看查询优化器如何决定执行查询的主要方法. 这个功能有局限性,并不总会说出真相,但它的输出是可以获取的最好信息,值得花时间去了解,因为可以学习到查询是如何执行的. 1.什么是MySQL执行计划 要对执行计划有个比较好的理解,需要先对MySQL的基础结构及查询基本原理有简单的了解. MySQL本身的功能架构分为三个部分,分别是 应用层.逻辑层.物理层,不只是MySQL ,其他大多数数据库产品都是按这种架构来进行划分的. 应用层,主要负责与客户端进行交互,建立链接,记住链接状态,

  • mysql基础架构教程之查询语句执行的流程详解

    mysql基础架构教程之查询语句执行的流程详解

    前言 一直是想知道一条SQL语句是怎么被执行的,它执行的顺序是怎样的,然后查看总结各方资料,就有了下面这一篇文章了. 这篇笔记主要记录mysql的基础架构,一条查询语句是如何执行的. 下面话不多说了,来一起看看详细的介绍吧 比如,在我们从student表中查询一个id=2的信息 select * from student where id=2; 在解释这条语句执行流程之前,我们看看mysql的基础架构. 图来自极客时间的mysql实践,该图是描述的是MySQL的逻辑架构. server层包括连接

  • Mysql查询正在执行的事务以及等待锁的操作方式

    Mysql查询正在执行的事务以及等待锁的操作方式

    使用navicat测试学习: 首先使用set autocommit = 0;(取消自动提交,则当执行语句commit或者rollback执行提交事务或者回滚) 在打开一个执行update 查询 正在执行的事务: SELECT * FROM information_schema.INNODB_TRX 根据这个事务的线程ID(trx_mysql_thread_id): 从上图看出对应的mysql 线程:一个94362 (第二个正在等待锁)另一个是93847(第一个update 正在执行 没有提交事务

  • MySQL执行计划的深入分析

    MySQL执行计划的深入分析

    前言 在之前的面试过程中,问到执行计划,有很多童鞋不知道是什么?甚至将执行计划与执行时间认为是同一个概念.今天我们就一起来了解一下执行计划到底是什么?有什么用途? 执行计划是什么? 执行计划,简单的来说,是SQL在数据库中执行时的表现情况,通常用于SQL性能分析,优化等场景. 一. 执行计划能告诉我们什么? SQL如何使用索引 联接查询的执行顺序 查询扫描的数据函数 二. 执行计划中的内容 SQL执行计划的输出可能为多行,每一行代表对一个数据库对象的操作 1. ID列 ID列中的如果数据为一组数

  • mysql执行计划介绍

    mysql执行计划介绍

    烂sql不仅直接影响sql的响应时间,更影响db的性能,导致其它正常的sql响应时间变长.如何写好sql,学会看执行计划至关重要.下面我简单讲讲mysql的执行计划,只列出了一些常见的情况,希望对大家有所帮助. 测试表结构: 复制代码 代码如下: CREATE TABLE `t1` (  `c1` int(11) NOT NULL DEFAULT '0',  `c2` varchar(128) DEFAULT NULL,  `c3` varchar(64) DEFAULT NULL,  `c4`

  • 详解 MySQL 执行计划

    详解 MySQL 执行计划

    EXPLAIN语句提供有关MySQL如何执行语句的信息.EXPLAIN与SELECT,DELETE,INSERT,REPLACE和UPDATE语句一起使用. EXPLAIN为SELECT语句中使用的每个表返回一行信息.它按照MySQL在处理语句时读取它们的顺序列出了输出中的表. MySQL使用嵌套循环连接方法解析所有连接.这意味着MySQL从第一个表中读取一行,然后在第二个表,第三个表中找到匹配的行,依此类推.处理完所有表后,MySQL输出所选列,并通过表列表回溯,直到找到一个表,其中有更多匹配

  • 带你学习MySQL执行计划

    1.执行计划简介 执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后,具体的执行方式.MySQL 为我们提供了  EXPLAIN 语句,来获取执行计划的相关信息.需要注意的是,EXPLAIN 语句并不会真的去执行相关的语句,而是通过查询优化器对语句进行分析,找出最优的查询方案,并显示对应的信息. 执行计划通常用于 SQL 性能分析.优化等场景.通过 explain 的结果,可以了解到如数据表的查询顺序.数据查询操作的操作类型.哪些索引可以被命中.哪些索引实际会命中.每个数据表

  • 一文带你看懂MySQL执行计划

    一文带你看懂MySQL执行计划

    目录 前言 explain/desc 用法 explain/desc 输出详解 一.id ,select 查询序列号 二.select_type,查询语句类型 三.table,查询涉及的表或衍生表 四.partitions查询涉及到的分区 五.type提供了判断查询是否高效的重要依据依据 六.possible_keys:指示MySQL可以从中选择查找此表中的行的索引. 七.key:MySQL查询实际使用到的索引. 八.key_len:表示索引中使用的字节数(只计算利用索引作为index key的

  • MySQL执行计划详解

    一.MySQL执行计划介绍 在MySQL中,执行计划的实现是基于JOIN和QEP_TAB这两个对象.其中JOIN类表示一个查询语句块的优化和执行,每个select查询语句(即Query_block对象)在处理的时候,都会被当做JOIN对象,其定义在sql/sql_optimizer.h. QEP_TAB是Query Execution Plan Table的缩写,这里的表Table对象主要包含物化表.临时表.派生表.常量表等.JOIN::optimize()是优化执行器的统一入口,在这里会把一个

  • Mysql深入探索之Explain执行计划详析

    前言 如何写出效率高的SQL语句,提到这必然离不开Explain执行计划的分析,至于什么是执行计划,如何写出高效率的SQL,本篇文章将会一一介绍. 执行计划 执行计划是数据库根据 SQL 语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的. 使用explain关键字可以模拟优化器执行 SQL 查询语句,从而知道 MySQL 是如何处理你的 SQL 语句的,分析你的 select 语句或是表结构的性能瓶颈,让我们知道 select 效率低下的原因,从而改进我们的查询.

随机推荐