首页 > 技术交流 > 面试前必须知道的MySQL命令--explain

面试前必须知道的MySQL命令--explain

头像
程序员大彬 #秋招#
编辑于 2021-08-15 12:12:28 APP内打开
赞 1 | 收藏 26 | 回复0 | 浏览2472

本文已经收录到github仓库,仓库用于分享Java相关知识总结,包括Java基础、MySQL、Springboot、mybatis、Redis、rabbitMQ等等,欢迎大家提pr和star!

github地址:https://github.com/Tyson0314/Java-learning

gitlab地址:https://gitee.com/tysondai/Java-learning

简介

本文主要讲述如何通过 explain 命令获取 select 语句的执行计划,通过 explain 可以知道 select 语句以下信息:

  • 表的加载顺序
  • sql 的查询类型
  • 可能用到哪些索引,实际上用到哪些索引
  • 读取的行数
  • ...

explain 执行计划包含字段信息如下:分别是 idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra 12个字段。

通过explain extended + show warnings可以在原本explain的基础上额外提供一些查询优化的信息,得到优化以后的可能的查询语句(不一定是最终优化的结果)。

先搭建测试环境:

图片说明

id

表示查询中执行select子句或者操作表的顺序,id的值越大,代表优先级越高,越先执行

图片说明

三个表依次嵌套,发现最里层的子查询 id最大,最先执行。

explain-id

select_type

表示 select 查询的类型,主要是用于区分各种复杂的查询,例如:普通查询联合查询子查询等。

  1. SIMPLE:表示最简单的 select 查询语句,在查询中不包含子查询或者交并差集等操作。
  2. PRIMARY:查询中最外层的SELECT(存在子查询的外层的表操作为PRIMARY)。
  3. SUBQUERY:子查询中首个SELECT。
  4. DERIVED:被驱动的SELECT子查询(子查询位于FROM子句)。
  5. UNION:在SELECT之后使用了UNION。

table

查询的表名,并不一定是真实存在的表,有别名显示别名,也可能为临时表。当from子句中有子查询时,table列是 <derivenN>的格式,表示当前查询依赖 id为N的查询,会先执行 id为N的查询。

explain-table

partitions

查询时匹配到的分区信息,对于非分区表值为NULL,当查询的是分区表时,partitions显示分区表命中的分区情况。

explain-partitions

type

查询使用了何种类型,它在 SQL优化中是一个非常重要的指标。

system

当表仅有一行记录时(系统表),数据量很少,往往不需要进行磁盘IO,速度非常快。比如,Mysql系统表proxies_priv在Mysql服务启动时候已经加载在内存中,对这个表进行查询不需要进行磁盘 IO。

explain-system

const

单表操作的时候,查询使用了主键或者唯一索引。

explain-const

eq_ref

多表关联查询的时候,主键和唯一索引作为关联条件。如下图的sql,对于user表(外循环)的每一行,user_role表(内循环)只有一行满足join条件,只要查找到这行记录,就会跳出内循环,继续外循环的下一轮查询。

explain-eq_ref

ref

查找条件列使用了索引而且不为主键和唯一索引。虽然使用了索引,但该索引列的值并不唯一,这样即使使用索引查找到了第一条数据,仍然不能停止,要在目标值附近进行小范围扫描。但它的好处是不需要扫全表,因为索引是有序的,即便有重复值,也是在一个非常小的范围内做扫描。

explain-ref.png

ref_or_null

类似 ref,会额外搜索包含NULL值的行。

index_merge

使用了索引合并优化方法,查询使用了两个以上的索引。新建comment表,id为主键,value_id为非唯一索引,执行explain select content from comment where value_id = 1181000 and id > 1000;,执行结果显示查询同时使用了id和value_id索引,type列的值为index_merge。

type-index_merge

range

有范围的索引扫描,相对于index的全索引扫描,它有范围限制,因此要优于index。像between、and、'>'、'<'、in和or都是范围索引扫描。

explain-range.png

index

index包括select索引列,order by主键两种情况。

  1. order by主键。这种情况会按照索引顺序全表扫描数据,拿到的数据是按照主键排好序的,不需要额外进行排序

    type-index-orderBy

  2. select索引列。type为index,而且extra字段为using index,也称这种情况为索引覆盖。所需要取的数据都在索引列,无需回表查询。

    type-index-selectIndexColumn

all

全表扫描,查询没有用到索引,性能最差。

explain-all

possible_keys

此次查询中可能选用的索引。但这个索引并不定一会是最终查询数据时所被用到的索引

key

此次查询中确切使用到的索引。

rows

估算要找到所需的记录,需要读取的行数。评估SQL 性能的一个比较重要的数据,mysql需要扫描的行数,很直观的显示 SQL 性能的好坏,一般情况下 rows 值越小越好。

filtered

存储引擎返回的数据在经过过滤后,剩下满足条件的记录数量的比例。

extra

表示额外的信息说明。为了方便测试,这里新建两张表。

图片说明

using where

查询的列未被索引覆盖,where筛选条件非索引的前导列。对存储引擎返回的结果进行过滤(Post-filter,后过滤),一般发生在MySQL服务器,而不是存储引擎层。

extra-using-where

using index

查询的列被索引覆盖,并且where筛选条件符合最左前缀原则,通过索引查找就能直接找到符合条件的数据,不需要回表查询数据。

extra-using-index

Using where&Using index

查询的列被索引覆盖,但无法通过索引查找找到符合条件的数据,不过可以通过索引扫描找到符合条件的数据,也不需要回表查询数据。

包括两种情况:

  • where筛选条件不符合最左前缀原则

  • where筛选条件是索引列前导列的一个范围

null

查询的列未被索引覆盖,并且where筛选条件是索引的前导列,也就是用到了索引,但是部分字段未被索引覆盖,必须回表查询这些字段,Extra中为NULL。

extra-null

using index condition

索引下推(index condition pushdown,ICP),先使用where条件过滤索引,过滤完索引后找到所有符合索引条件的数据行,随后用 WHERE 子句中的其他条件去过滤这些数据行。

不使用ICP的情况(set optimizer_switch='index_condition_pushdown=off'),如下图,在步骤4中,没有使用where条件过滤索引:

no-icp

使用ICP的情况(set optimizer_switch='index_condition_pushdown=on'):

icp

下面的例子使用了ICP:
图片说明

icp-demo

关掉ICP之后(set optimizer_switch='index_condition_pushdown=off'),可以看到extra列为using where,不会使用索引下推。

no-icp-demo

索引下推例子

索引下推图解

索引下推优化

using temporary

使用了临时表保存中间结果,常见于 order by 和 group by 中。典型的,当group by和order by同时存在,且作用于不同的字段时,就会建立临时表,以便计算出最终的结果集。

filesort

文件排序。表示无法利用索引完成排序操作,以下情况会导致filesort:

  • order by 的字段不是索引字段
  • select 查询字段不全是索引字段
  • select 查询字段都是索引字段,但是 order by 字段和索引字段的顺序不一致

explain-filesort

using join buffer

Block Nested Loop,需要进行嵌套循环计算。两个关联表join,关联字段均未建立索引,就会出现这种情况。比如内层和外层的type均为ALL,rows均为4,需要循环进行4*4次计算。常见的优化方案是,在关联字段上添加索引,避免每次嵌套循环计算。

本文参考了一些优秀的博客,感兴趣的可以了解下:

最后给大家推荐一个github仓库,上面放了上百本经典的计算机书籍,包括数据库、操作系统、计算机网络、数据结构和算法等,可以star一下,下次找书直接在上面搜索,仓库持续更新中~
github仓库:https://github.com/Tyson0314/java-books
如果github访问不了,可以访问gitee仓库。
gitee仓库:https://gitee.com/tysondai/java-books

0条回帖

回帖
加载中...
话题 回帖

相关热帖

技术交流近期热帖

近期精华帖

热门推荐