乐趣区

关于后端:explain各字段的含义

作者：

jiezi

在

MySQL 5.6.3以前只能EXPLAIN SELECT; 5.6.3当前就能够EXPLAIN SELECT,UPDATE,DELETE

有这样一张user表,300多万行记录,表构造及索引信息如下:

对于sql:


SELECT
    * 
FROM
    `user` 
WHERE
    id > 20000 
    AND country > 1 
    AND grade IN ( 1, 4 ) 
    AND city IN ( 1, 500, 1000, 1500, 3000 ) 
ORDER BY
    update_time DESC 
    LIMIT 30;

explain后果如下:

<font color=”#4682B4″> 1.id </font>

SQL查问中的序列号

id列数字越大越先执行，如果说数字一样大，那么就从上往下顺次执行。

<font color=”#4682B4″> 2.select_type </font>

查问的类型, 能够是如下的任何一种类型：

<font color=”#4682B4″>3.table </font>

查问的表名. 并不一定是理论存在的表名.

能够为如下的值：

  • <unionM,N>: 援用id为M和N UNION后的后果。
  • <derivedN>: 援用id为N的后果派生出的表。派生表能够是一个后果集，例如派生自FROM中子查问的后果。
  • <subqueryN>: 援用id为N的子查问后果物化失去的表。即生成一个长期表保留子查问的后果。

<font color=”#4682B4″> 4.partitions</font>

5.7以前，该项是explain partitions显示的选项; 5.7当前成为了默认选项.

该列显示的为分区表命中的分区状况, 非分区表该字段为空（NULL）.

<font color=”#FFD700″> 5.type</font>

最重要的一个指标, 显示查问应用了何种类型

除ALL之外, 其余type都能够用到索引; 除index_merge外, 其余type只可用到一个索引.

由左到右,性能由差到好:

ALL,index，range，index_subquery，unique_subquery，index_merge，ref_or_null，fulltext，ref，eq_ref，const，system

从上到下,性能由差到好:

<font color=”#FF7F50″>(1) ALL</font>

扫描全表,性能最差.

>>>>>>

<font color=”#FF7F50″>(2) index</font>

扫描全副索引树

或称”索引全表扫描”, 即把索引从头到尾扫一遍.

蕴含两种状况：

  1. 查问应用了笼罩索引, 那么只须要扫描索引就能够取得数据. 这个效率要比全表扫描快, 因为索引通常比数据表小, 且还能防止二次查问. 这种状况在extra中显示Using index.
  2. 反之, 如果在索引上进行全表扫描,则extra字段没有Using index.

如对于user表,telephone字段建有索引,如果

<1>.

explain select telephone from user;,则type字段将为index

<2>.

explain select amount_coin from user;,

因为amount_coin字段没有建索引, 故而type字段将为ALL

<3>.

explain select * from user;,

如果蕴含没有建索引的列, type字段也将为ALL

>>>>>>

<font color=”#FF7F50″>(3) range</font>

扫描局部索引

索引范畴扫描, 对索引的扫描开始于某一点, 返回匹配值域的行，常见于 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查问中

>>>>>>

<font color=”#FF7F50″>(4) index_subquery</font>

该联接类型相似于上面的unique_subquery

实用于非惟一索引, 能够返回反复值.

>>>>>>

<font color=”#FF7F50″>(5) unique_subquery</font>

用于where中in模式的子查问.

子查问返回不反复值惟一值, 能够齐全替换子查问, 效率更高.

该类型替换了上面模式的IN子查问的ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

>>>>>>

<font color=”#FF7F50″>(6) index_merge</font>

示意查问应用了两个以上的索引, 最初取交加或者并集, 常见and ，or的条件应用了不同的索引.

官网排序这个在上面的ref_or_null之后, 但实际上因为要读取多个索引,性能可能大部分工夫都不如更上面的range

>>>>>>

<font color=”#FF7F50″>(7) ref_or_null</font>

跟上面的ref类型相似, 只是减少了null值的比拟.

理论用的不多

>>>>>>

<font color=”#FF7F50″>(8) fulltext</font>

应用全文索引时type会是这个类型.

留神,全文索引的优先级很高,若全文索引和一般索引同时存在时, mysql不论代价, 会优先选择应用全文索引

>>>>>>

<font color=”#FF7F50″>(9) ref</font>

应用非惟一索引或非惟一索引前缀进行的查找

对于来自前表的每一行,在以后表的索引中能够匹配到多行.

若连贯只用到索引的最左前缀或索引不是主键或惟一索引时, 应用ref类型(能够了解成可能呈现”一对多”时)

ref可用于应用’=’或'<=>’操作符作比拟的索引列

>>>>>>

<font color=”#FF7F50″>(10) eq_ref</font>

唯一性索引扫描, 对于每个索引键, 表中只有一条记录与之匹配.

eq_ref和const的区别:

eq_ref 呈现于多表join时, 对于来自前表的每一行, 在以后表中只能找到一行.

这是除了上面几种类型之外最好的类型. 当主键或惟一非NULL索引的所有字段都被用作join联接时会应用此类型.

eq_ref 可用于应用’=’操作符作比拟的索引列, 比拟的值能够是常量, 也能够是应用在此表之前读取的表的列的表达式.

>>>>>>

<font color=”#FF7F50″>(11) const</font>

const: 单表中最多有一个匹配行, 例如依据主键或惟一索引查问.
(如 select * from user where id=100),
查问起来十分迅速

>>>>>>

<font color=”#FF7F50″>(12) system</font>

system是const类型的特例,只会呈现在Myisam或Memory存储引擎, 当查问的表只有一行或空表的状况下,type字段将是system.

如果是Innodb引擎表, type列在这种状况通常都是ALL或者index.

这种类型能够能够忽略不计

>>>>>>

<font color=”#FF7F50″>(13) NULL</font>

不必拜访表或者索引,间接就能失去后果, 如 explain select NOW()

<font color=”#4682B4″> 6.prossible_keys</font>

可能应用到的索引

<font color=”#4682B4″> 7.key </font>

真正应用到的索引

select_type为index_merge时, 这里可能呈现两个以上的索引;

其余的select_type这里只会呈现一个.

<font color=”#4682B4″> 8.key_len</font>

查问用到的索引长度（字节数）

如果是单列索引, 那就整个索引长度算进去;

如果是多列索引(即联结索引),那么查问不肯定都能应用到所有的列,用多少算多少.

能够注意下这个列的值, 算一下多列索引总长度, 就可知有没有应用到所有的列.

**另:
key_len只计算where条件用到的索引长度, 而排序和分组就算用到了索引,也不会计算到key_len中.**

<font color=”#4682B4″> 9.ref</font>

如果应用常数等值查问, 这里会显示const;

如果是连贯查问, 被驱动表的执行打算这里会显示驱动表的关联字段,

如果是条件应用了表达式或者函数,或者条件列产生了外部隐式转换, 这里可能显示为func

<font color=”#FFD700″> 10.rows</font>

十分重要的一个字段

mysql估算的 须要扫描的行数（不是准确值）

通过这个值,能够十分直观地显示 SQL 的效率好坏.

原则上 rows 越小越好.

当存在limit时,会对rows字段产生影响. 倡议在explain时先去除limit

<font color=”#4682B4″> 11.filtered</font>

这个字段示意存储引擎返回的数据在server层过滤后, 剩下多少满足查问的记录数量的比例;

留神是百分比,不是具体记录数.

<font color=”#FFD700″> 12.Extra</font>

十分重要的一个字段

explain 中的很多额定信息会在 Extra 字段显示, 常见的有以下几种内容:

  • distinct：在select局部应用了distinc关键字
  • <font color=”#3CB371″>Using filesort：</font> 当 Extra 中有 Using filesort 时, 示意 MySQL 需额定的排序操作(优先内存,内存空间不够则会在磁盘排序), 不能通过索引程序达到排序成果. 个别存在 Using filesort, 都倡议通过优化去掉, 因为这样的查问对机器的资源耗费很大.
  • <font color=”#00FF7F”>Using index:</font> “笼罩索引扫描”, 示意查问在索引树中就可查找所需数据, 不必扫描表数据文件, 往往阐明性能不错. 即不须要进行filesort
  • Using temporary: 查问有应用长期表, 个别呈现于排序, 分组和多表 join 的状况, 查问效率不高, 倡议通过优化去掉.

另外还有

  • using where：在查找应用索引的状况下，须要回表去查问所需的数据
  • using index condition：查找应用了索引，然而须要回表查问数据
  • using index & using where：查找应用了索引，然而须要的数据都在索引列中能找到，所以不须要回表查问数据

using index 好于 using where 好于 using index condition, 不须要回表查问数据，效率最快

在带有order by子句的sql中,要尽可能使extra字段不要呈现Using filesort,而是Using index

举例如何去掉 Using filesort:

explain后果每个字段的含意阐明

mysql 索引type介绍

MySQL优化：定位慢查问的两种办法以及应用explain剖析SQL

limit 会对explain的type产生微小影响

对于order by的优化

具备LIMIT和不具备LIMIT的ORDER BY可能是不同的

file_sort优化器会事后调配固定数量的sort_buffer_size字节。

MySQL 有时会优化具备LIMIT row_count子句而没有HAVING子句的查问：

如果您只抉择LIMIT的几行，则在某些状况下，MySQL 通常会抉择应用全 table 扫描，而 MySQL 通常会应用索引。

如果将LIMIT row_count和ORDER BY联合应用，MySQL 会在找到排序后果的前 row_count 行后立刻进行排序，而不是对整个后果进行排序。如果通过应用索引进行排序，这将十分快。如果必须执行文件排序，则在找到第一个 row_count 之前，将抉择与查问匹配的所有行，但不带有LIMIT子句，并对其中的大多数或全副进行排序。找到初始行后，MySQL 不会对后果集的其余部分进行排序。

如果没有为ORDER BY应用索引，然而也存在LIMIT子句，则优化器可能可能防止应用合并文件，并应用内存中filesort操作对内存中的行进行排序。

参考:

优化 Sequences

Orderby 排序优化

了解mysql的长期表和文件排序

【IT老齐338】MySQL Extra常见信息解析

【大厂文章速读】字节跳动-慢SQL剖析与优化 （extra呈现这几项，肯定要加倍留神）

本文由mdnice多平台公布

评论

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

这个站点使用 Akismet 来减少垃圾评论。了解你的评论数据如何被处理。

更多文章