乐趣区

关于后端: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 多平台公布

退出移动版