❤️mysql之EXPLAIN优化分析

小明的学习圈子2023-11-29数据库MySQL 优化

一、EXPLAIN查看SQL执行计划

img

EXPLAIN语句解析

expain解析出来的信息有10列,分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

  1. id:选择标识符
  2. select_type:表示查询的类型。
  3. table:输出结果集的表
  4. type:表示表的连接类型
  5. possible_keys:表示查询时,可能使用的索引,如果为空,说明没有可用的索引。
  6. key:key列显示MySQL实际决定使用的键(索引),必然包含在possible_keys中,如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
  7. key_len:索引字段的长度,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)不损失精确性的情况下,长度越短越好
  8. ref:列与索引的比较,表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
  9. rows:扫描出的行数(MySQL根据表统计信息及索引选用情况,估算的行数)
  10. Extra:执行情况的描述和说明

1.id

SELECT语句的标识符,一般为数字,表示对应的SELECT语句在原始语句中的位置。没有子查询或联合的整个查询只有一个SELECT语句,因此其id通常为1。在联合或子查询语句中,内层的SELECT语句通常按它们在原始语句中的次序进行编号。但UNION操作通常最后会有一个id为NULL的行,因为UNION的结果通常保存至临时表中,而MySQL需要到此临时表中取得结果。

1.1id 相同执行顺序由上到下,即下面sql表的执行顺序是t3,t1,t2.

img

1.2如果是子查询,id序号会自增,id值越大优先级就越高,越先被执行。

img

1.3id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行

img

2.select_type 数据读取操作的操作类型

  • SIMPLE(简单SELECT,不使用UNION或子查询等)
  • PRIMARY(子查询中最外层查询,查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
  • UNION(若第二个SELECT出现在UNION之后,则被标记为UNION,若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED)
  • UNION RESULT(UNION的结果,union语句中第二个select开始后面所有select)
  • SUBQUERY(在select或者where里包含了子查询)
  • DERIVED(派生表的SELECT, FROM子句的子查询-衍生,mysql会递归这些子查询,把结果放到临时表里)
  • UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存,必须重新评估外链接的第一行)
  • DEPENDENT UNION(UNION中的第二个或后面的SELECT语句,取决于外面的查询)
  • DEPENDENT SUBQUERY(子查询中的第一个SELECT,依赖于外部查询)

3.table 输出行所引用的表

4.type访问类型

由好到差system > const > eq_ref > ref > range > index > ALL

链接类型说明
system表只有一行,MyISAM引擎。
const表中至多有一个匹配的行,该行仅在查询开始时读取一次,因此,该行此字段中的值可以被优化器看作是个常量(constant);当基于PRIMARY KEY或UNIQUE NOT NULL字段查询,且与某常量进行等值比较时其类型就为const,其执行速度非常快;
eq_ref类似于const,表中至多有一个匹配的行,但比较的数值不是某常量,而是来自于其它表;ed_ref出现在PRIMARY KEY或UNIQUE NOT NULL类型的索引完全用于联结操作中进行等值(=)比较时; 这是除了system和const之外最好的访问类型;ref     查询时的索引类型不是PRIMARY KEY或UNIQUE NOT NULL导致匹配到的行可能不惟一,或者仅能用到索引的左前缀而非全部时的访问类型;ref可被用于基于索引的字段进行=或<=>操作;
fulltext用于FULLTEXT索引中用纯文本匹配的方法来检索记录。
ref_or_null与ref类似,但包括NULL
index_merge使用“索引合并优化”的记录访问类型,相应地,其key字段(EXPLAIN的输出结果)中会出现用到的多个索引,key_len字段中会出现被使用索引的最长长度列表;将多个“范围扫描(range scan)”获取到的行进行合并成一个结果集的操作即索引合并(index merge)。
unique_subquery在in子查询中,就是value in (select…)把形如select unique_key_column的子查询替换。PS:所以不一定in子句中使用子查询就是低效的!
index_subquery类似于unique_subquery,但子查询中键值不惟一;
range带有范围限制的索引扫描,而非全索引扫描,它开始于索引里的某一点,返回匹配那个值的范围的行;相应地,其key字段(EXPLAIN的输出结果)中会输出所用到的索引, key_len字段中会包含用到的索引的最长部分的长度;range通常用于将索引与常量进行=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或IN()类的比较操作中;
index索引树扫描。a.当查询是索引覆盖的,即所有数据均可从索引树获取的时候(Extra中有Using Index);b.以索引顺序从索引中查找数据行的全表扫描(无 Using Index);c.如果Extra中Using Index与Using Where同时出现的话,则是利用索引查找键值的意思;d.如单独出现,则是用读索引来代替读行,但不用于查找
all全表扫描(full table scan)

5.Extra

该列包含MySQL解决查询的详细信息,有以下几种情况:

  1. Using where:不用读取表中所有信息,仅通过索引就可以获取所需数据,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示mysql服务器将在存储引擎检索行后再进行过滤
  2. Using temporary:表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询,常见 group by ; order by
  3. Using filesort:当Query中包含 order by 操作,而且无法利用索引完成的排序操作称为“文件排序”
  4. Using joinbuffer:改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。
  5. Impossible where:这个值强调了where语句会导致没有符合条件的行(通过收集统计信息不可能存在结果)。
  6. Select tables optimized away:这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行
  7. No tables used:Query语句中使用from dual 或不含任何from子句

Last Updated 2024/4/6 10:47:15