使用MySQL的Explain执行计划的方法(SQL性能调优)

目录
  • 前言
  • 1. explain的使用
  • 2. explain字段详解
    • id列
    • select_type列
    • table列
    • partitions列
    • type列
      • system
      • const
      • eq_ref
      • ref
      • ref_or_null
      • index_merge
      • range
      • index
      • ALL
    • possible_keys列
    • key列
    • key_len列
    • ref列
    • rows列
    • filtered列
    • Extra列
      • Using where
      • Using index
      • Using filesort
      • Using temporary
      • Using join buffer
      • Using index condition
  • 知识点总结

前言

上篇文章讲了MySQL架构体系，了解到MySQL Server端的优化器可以生成Explain执行计划，而执行计划可以帮助我们分析SQL语句性能瓶颈，优化SQL查询逻辑，今天就一块学习Explain执行计划的具体用法。

1. explain的使用

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈。 在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，并不会执行这条SQL。

就比如下面这个：

输出这么多列都是干嘛用的？

其实大都是SQL语句的性能统计指标，先简单总结一下每一列的大致作用，下面详细讲一下：

2. explain字段详解

下面就详细讲一下每一列的具体作用。

id列

id表示查询语句的序号，自动分配，顺序递增，值越大，执行优先级越高。

id相同时，优先级由上而下：

select_type列

select_type表示查询类型，常见的有SIMPLE简单查询、PRIMARY主查询、SUBQUERY子查询、UNION联合查询、UNION RESULT联合临时表结果等。

table列

table表示SQL语句查询的表名、表别名、临时表名。

partitions列

partitions表示SQL查询匹配到的分区，没有分区的话显示NULL。

type列

type表示表连接类型或者数据访问类型，就是表之间通过什么方式建立连接的，或者通过什么方式访问到数据的。

具体有以下值，性能由好到差依次是：

system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

system

当表中只有一行记录，也就是系统表，是 const 类型的特列。

const

表示使用主键或者唯一性索引进行等值查询，最多返回一条记录。性能较好，推荐使用。

eq_ref

表示表连接使用到了主键或者唯一性索引，下面的SQL就用到了user表主键id。

ref

表示使用非唯一性索引进行等值查询。

ref_or_null

表示使用非唯一性索引进行等值查询，并且包含了null值的行。

index_merge

表示用到索引合并的优化逻辑，即用到的多个索引。

range

表示用到了索引范围查询。

index

表示使用索引进行全表扫描。

ALL

表示全表扫描，性能最差。

possible_keys列

表示可能用到的索引列，实际查询并不一定能用到。

key列

表示实际查询用到索引列。

key_len列

表示索引所占的字节数。

每种类型所占的字节数如下：

类型 占用空间
char(n) n个字节
varchar(n) 2个字节存储变长字符串，如果是utf-8，则长度 3n + 2
tinyint 1个字节
smallint 2个字节
int 4个字节
bigint 8个字节
date 3个字节
timestamp 4个字节
datetime 8个字节
字段允许为NULL 额外增加1个字节

ref列

表示where语句或者表连接中与索引比较的参数，常见的有const（常量）、func（函数）、字段名。

如果没用到索引，则显示为NULL：

rows列

表示执行SQL语句所扫描的行数。

filtered列

表示按条件过滤的表行的百分比。

用来估算与其他表连接时扫描的行数，row x filtered = 252004 x 10% = 25万行

Extra列

表示一些额外的扩展信息，不适合在其他列展示，却又十分重要。

Using where

表示使用了where条件搜索，但没有使用索引。

Using index

表示用到了覆盖索引，即在索引上就查到了所需数据，无需二次回表查询，性能较好。

Using filesort

表示使用了外部排序，即排序字段没有用到索引。

Using temporary

表示用到了临时表，下面的示例中就是用到临时表来存储查询结果。

Using join buffer

表示在进行表关联的时候，没有用到索引，使用了连接缓存区存储临时结果。

下面的示例中user_id在两张表中都没有建索引。

Using index condition

表示用到索引下推的优化特性。

知识点总结

本文详细介绍了Explain使用方式，以及每种参数所代表的含义。无论是工作还是面试，使用Explain优化SQL查询，都是必备的技能，一定要牢记。

SQL查询优化方式图表：

到此这篇关于使用MySQL的Explain执行计划的方法(SQL性能调优)的文章就介绍到这了,更多相关MySQL Explain内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL如何基于Explain关键字优化索引功能

    explain显示了MySQL如何使用索引来处理select语句以及连接表.可以帮助选择更好的索引和写出更优化的查询语句.简单讲,它的作用就是分析查询性能. explain关键字的使用方法很简单,就是把它放在select查询语句的前面. mysql查看是否使用索引,简单的看type类型就可以.如果它是all,那说明这条查询语句遍历了所有的行,并没有使用到索引. 比如:explain select * from company_info where cname like '%小%' explain

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

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

  • MySQL EXPLAIN输出列的详细解释

    1. 简介 EXPLAIN语句提供有关 MySQL 如何执行语句的信息. EXPLAIN与SELECT.DELETE.INSERT.REPLACE和UPDATE语句一起使用. mysql> EXPLAIN SELECT * FROM employees WHERE emp_no = 10001; +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+----

  • MySQL索引优化Explain详解

    在日常工作中,我们会有时会开慢查询去记录一些执行时间比较久的SQL语句,找出这些SQL语句并不意味着完事了,些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划,查看该SQL语句有没有使用上了索引,有没有做全表扫描,这都可以通过explain命令来查看.所以我们深入了解MySQL的基于开销的优化器,还可以获得很多可能被优化器考虑到的访问策略的细节,以及当运行SQL语句时哪种策略预计会被优化器采用. -- 实际SQL,查找用户名为Jefabc的员工 select * from

  • Mysql调优Explain工具详解及实战演练(推荐)

    Mysql调优Explain工具详解及实战演练(推荐)

    Mysql调优Explain工具详解以及实战演练 Explain工具介绍Explain分析示例explain 两个变种explain中的列 索引最佳实战索引使用总结: Mysql安装文档参考 Explain工具介绍 使用EXPLAIN关键字可以模拟优化器执行SQL语句,分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字,MySQL 会在查询上设置一个标记,执行查询会返回执行计划的信息,而不是 执行这条SQL 注意:如果 from 中包含子查询,仍会执行该子

  • 详解mysql中explain的type

    导语: 很多情况下,有很多人用各种select语句查询到了他们想要的数据后,往往便以为工作圆满结束了. 这些事情往往发生在一些学生亦或刚入职场但之前又没有很好数据库基础的小白身上,但所谓闻道有先后,只要我们小白好好学习,天天向上,还是很靠谱的. 当一个sql查询语句被写出来之后,其实你的工作只完成了一小半,接下来更重要的工作是评估你自己写的sql的质量与效率.mysql为我们提供了很有用的辅助武器explain,它向我们展示了mysql接收到一条sql语句的执行计划.根据explain返回的结果

  • mysql explain中key_len的含义以及计算方法

    目录 前言 计算key_len注意事项: 示例 总结 前言 在Mysql中执行explain的结果中有一列为key_len,那么key_len的含义是什么呢? key_len:表示索引使用的字节数,根据这个值可以判断索引的使用情况,特别是在组合索引的时候,判断该索引有多少部分被使用到非常重要. 计算key_len注意事项: 索引字段的附加信息: 可以分为变长和定长数据类型讨论,当索引字段为定长数据类型时,如char,int,datetime,需要有是否为空的标记,这个标记占用1个字节(对于not

  • MySQL通过show status查看、explain分析优化数据库性能

    MySQL通过show status查看、explain分析优化数据库性能

    1.概述 在应用系统开发过程中,由于初期数据量小,开发人员写SQL语句时更重视功能上的实现,但是当应用系统正式上线后,随着生产数据量的急剧增长,很多SQL语句开始逐渐显露出性能问题,对生产环境的影响也越来越大,此时这些有问题的SQL语句就成为整个系统性能的瓶颈,因此我们必须要对它们进行优化,该章节将详细介绍在MySQL中优化SQL语句的方法. 2.通过show status命令了解各种SQL的执行频率 MySQL客户端连接成功后,通过show [session|global]status命令可以

  • MYSQL 性能分析器 EXPLAIN 用法实例分析

    本文实例讲述了MYSQL 性能分析器 EXPLAIN 用法.分享给大家供大家参考,具体如下: 使用方法: EXPLAIN SELECT * FROM user; 环境和数据准备 -- 查看 MySQL 版本 SELECT VERSION(); -- MySQL 提供什么存储引擎 SHOW ENGINES; -- 查看默认存储引擎 SHOW VARIABLES LIKE '%storage_engine%'; 输出结果: id:输出的是整数,用来标识整个 SQL 的执行顺序.id 如果相同,从上往

  • 使用MySQL的Explain执行计划的方法(SQL性能调优)

    使用MySQL的Explain执行计划的方法(SQL性能调优)

    目录 前言 1. explain的使用 2. explain字段详解 id列 select_type列 table列 partitions列 type列 system const eq_ref ref ref_or_null index_merge range index ALL possible_keys列 key列 key_len列 ref列 rows列 filtered列 Extra列 Using where Using index Using filesort Using tempora

  • MySQL EXPLAIN执行计划解析

    MySQL EXPLAIN执行计划解析

    目录 前言 1 调用EXPLAIN 2 EXPLAIN中的列 2.1 id 2.2 select_type 2.3 table 2.4 type 2.5 possible_keys 2.6 key 2.7 key_len 2.8 ref 2.9 rows 2.10 Extra 前言 调用EXPLAIN可以获取关于查询执行计划的信息,以及如何解释输出.EXPLAIN命令是查看查询优化器如何决定执行查询的主要方法,但该动能也有局限性,它的选择并不总是最优的,展示的也并不一定是真相. 1 调用EXPL

  • MongoDB性能篇之创建索引，组合索引，唯一索引，删除索引和explain执行计划

    一.索引 MongoDB 提供了多样性的索引支持,索引信息被保存在system.indexes 中,且默认总是为_id创建索引,它的索引使用基本和MySQL 等关系型数据库一样.其实可以这样说说,索引是凌驾于数据存储系统之上的另一层系统,所以各种结构迥异的存储都有相同或相似的索引实现及使用接口并不足为 奇. 1.基础索引 在字段age 上创建索引,1(升序);-1(降序): db.users.ensureIndex({age:1}) _id 是创建表的时候自动创建的索引,此索引是不能够删除的.当

  • 10个MySQL性能调优的方法

    MYSQL 应该是最流行了 WEB 后端数据库.WEB 开发语言最近发展很快,PHP, Ruby, Python, Java 各有特点,虽然 NOSQL 最近越來越多的被提到,但是相信大部分架构师还是会选择 MYSQL 来做数据存储. MYSQL 如此方便和稳定,以至于我们在开发 WEB 程序的时候很少想到它.即使想到优化也是程序级别的,比如,不要写过于消耗资源的 SQL 语句.但是除此之外,在整个系统上仍然有很多可以优化的地方. 1. 选择合适的存储引擎: InnoDB 除非你的数据表使用来做

  • mysql sql语句性能调优简单实例

    mysql sql语句性能调优简单实例

     mysql sql语句性能调优简单实例 在做服务器开发时,有时候对并发量有一定的要求,有时候影响速度的是某个sql语句,比如某个存储过程.现在假设服务器代码执行过程中,某个sql执行比较缓慢,那如何进行优化呢? 假如现在服务器代码执行如下sql存储过程特别缓慢: call sp_wplogin_register(1, 1, 1, '830000', '222222'); 可以按如下方法来进行调试: 1. 打开mysql profiling: 2. 然后执行需要调优的sql,我们这里执行两条sq

  • 关于MySQL性能调优你必须了解的15个重要变量(小结)

    前言: MYSQL 应该是最流行了 WEB 后端数据库.虽然 NOSQL 最近越来越多的被提到,但是相信大部分架构师还是会选择 MYSQL 来做数据存储.本文作者总结梳理MySQL性能调优的15个重要变量,又不足需要补充的还望大佬指出. 1.DEFAULT_STORAGE_ENGINE 如果你已经在用MySQL 5.6或者5.7,并且你的数据表都是InnoDB,那么表示你已经设置好了.如果没有,确保把你的表转换为InnoDB并且设置default_storage_engine为InnoDB. 为

  • ORACLE数据库查看执行计划的方法

    ORACLE数据库查看执行计划的方法

    一.什么是执行计划(explain plan) 执行计划:一条查询语句在ORACLE中的执行过程或访问路径的描述. 二.如何查看执行计划 1: 在PL/SQL下按F5查看执行计划.第三方工具toad等. 很多人以为PL/SQL的执行计划只能看到基数.优化器.耗费等基本信息,其实这个可以在PL/SQL工具里面设置的.可以看到很多其它信息,如下所示 2: 在SQL*PLUS(PL/SQL的命令窗口和SQL窗口均可)下执行下面步骤 复制代码 代码如下: SQL>EXPLAIN PLAN FOR SEL

  • MYSQL explain 执行计划

    使用方法,在select语句前加上explain就可以了: 如:explain select * from test1 EXPLAIN列的解释: table:显示这一行的数据是关于哪张表的 type:这是重要的列,显示连接使用了何种类型.从最好到最差的连接类型为const.eq_reg.ref.range.indexhe和ALL possible_keys:显示可能应用在这张表中的索引.如果为空,没有可能的索引.可以为相关的域从WHERE语句中选择一个合适的语句 key: 实际使用的索引.如果为

  • MySQL的常见存储引擎介绍与参数设置调优

    MySQL常用存储引擎之MyISAM 特性: 1.并发性与锁级别 2.表损坏修复 check table tablename repair table tablename 3.MyISAM表支持的索引类型 ①.全文索引 ②.前缀索引 4.MyISAM表支持数据压缩 myisampack 限制: 版本 < MySQL5.0时默认表大小为4G 如存储达标则要修改MAX_Rows和AVG_ROW_LENGTH 版本 > MySQL5.0时默认支持为256TB 适用场景: 1.非事务形应用 2.只读类

随机推荐