mysql explain 查询优化调试详细说明

EXPLAIN语句提供有关MySQL如何执行语句的信息。 EXPLAIN与SELECT,DELETE,INSERT,REPLACE和UPDATE语句一起使用。

作用

MySQL将显示来自优化器的有关语句执行计划的信息,MySQL解释了它将如何处理该语句,包括如下:

  • 如何连接表
  • 以何种顺序连接表
  • 可能会用到那些索引,那些索引会被真正使用
  • 一个表中有多少行被优化器查询
  • 额外的补充信息...

EXPLAIN输出列

idColumnsJSON NameMeaning
1idselect_id每个select子句的标识id
2select_typeNoneselect语句的类型
3tabletable_name当前表名
4partitionspartitions匹配的分区
5typeaccess_type当前表内访问方式 join type
6possible_keyspossible_keys可能使用到的索引
7keykey经过优化器评估最终使用的索引
8key_lenkey_length使用到的索引长度
9refref引用到的上一个表的列
10rowsrowsrows_examined,要得到最终记录索要扫描经过的记录数
11filteredfiltered按表条件过滤行的百分比
12ExtraNone额外的信息说明

详细说明

id

SELECT识别符。这是SELECT查询序列号。这个不重要,查询序号即为sql语句执行的顺序。

select_type

不同的查询类型如下:

idselect_type valueJSON nameMeaning
1SIMPLENone简单的SELECT语句(不包括UNION操作或子查询操作)
2PRIMARYNonePRIMARY:查询中最外层的SELECT(如两表做UNION或者存在子查询的外层的表操作为PRIMARY,内层的操作为UNION)
3UNIONNoneUNION:UNION操作中,查询中处于内层的SELECT(内层的SELECT语句与外层的SELECT语句没有依赖关系)
4DEPENDENT UNIONdependent(true)DEPENDENT UNION:UNION操作中,查询中处于内层的SELECT(内层的SELECT语句与外层的SELECT语句有依赖关系)
5UNIOIN RESULTunion_resultUNION RESULT:UNION操作的结果,id值通常为NULL
6SUBQUERYNoneSUBQUERY:子查询中首个SELECT(如果有多个子查询存在)
7DEPENDENT SUBQUERYdependent(true)DEPENDENT SUBQUERY:子查询中首个SELECT,但依赖于外层的表(如果有多个子查询存在)
8DERIVEDNoneDERIVED:被驱动的SELECT子查询(子查询位于FROM子句)
9MATERIALIZEDmaterialized_form_subqueryMATERIALIZED:被物化的子查询
10UNCACHEABLE SUBQUERYcacheable(false)UNCACHEABLE SUBQUERY:对于外层的主表,子查询不可被物化,每次都需要计算(耗时操作)
11UNCACHEABLE UNIONcacheable(false)UNCACHEABLE UNION:UNION操作中,内层的不可被物化的子查询(类似于UNCACHEABLE SUBQUERY)

table

当前所引用的表名称

partitions

匹配的分区

type

表的连接类型,或者说访问方式

性能由好到坏排序:

idtype valueMeaning
1system表中只有一行
2const单表中最多有一个匹配行,primary key 或者 unique index的检索
3eq_ref多表连接中被驱动表的连接列上有primary key或者unique index的检索
4ref与eq_ref类似,但不是使用primary key或者unique index,而是普通索引。也可以是单表上non-unique索引检索
5fulltext使用FULLTEXT索引执行连接
6ref_or_null与ref类似,区别在于条件中包含对NULL的查询
7index_merge索引合并优化,利用一个表里的N个索引查询,key_len表示这些索引键的和最长长度。
8unique_subqueryin的后面是一个查询primary keyunique字段的子查询
9index_subqueryin的后面是一个查询普通index字段的子查询
10range单表索引中的范围查询,使用索引查询出单个表中的一些行数据。ref列会变为null
11index等于ALL。它有两种情况:(1)覆盖索引 (2)用索引的顺序做一个全表扫描。
12all全表扫描

possible_keys

表示 MySQL 在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key 字段决定.

如果此列是NULL 则没有相关的索引

您可以通过检查该WHERE 子句以检查它是否引用了某些适合索引的列,从而提高查询性能。如果是这样,请创建一个适当的索引并再次检查查询,要查看表具有哪些索引,请使用。 SHOW INDEX FROM *tbl_name*

key

指示MySQL实际决定使用的键(索引)

key_len

a、一般地,key_len 等于索引列类型字节长度,例如int类型为4-bytes,bigint为8-bytes;
b、如果是字符串类型,还需要同时考虑字符集因素,例如:CHAR(30) UTF8则key_len至少是90-bytes;
c、若该列类型定义时允许NULL,其key_len还需要再加 1-bytes;
d、若该列类型为变长类型,例如 VARCHAR(TEXTBLOB不允许整列创建索引,如果创建部分索引,也被视为动态列类型),其key_len还需要再加 2-bytes;

ref

ref列显示使用哪个列或常数与key一起从表中选择行

rows

表示MySQL认为执行查询必须检查的行数。

对于InnoDB表,此数字是估计值,可能并不总是准确的,这个数越小越好

filtered

指示将被表条件过滤的表行的估计百分比。最大值为100,表示没有行过滤发生。值从100减小表示过滤量增加。 rows显示检查的估计行数,rows× filtered显示将与下表连接的行数。例如,如果 rows为1000且 filtered为50.00(50%),则与下表连接的行数为1000×50%= 500。

Extra

idtype valueMeaning
1const row not found所要查询的表为空
2Distinctmysql正在查询distinct值,因此当它每查到一个distinct值之后就会停止当前组的搜索,去查询下一个值
3Impossible WHEREwhere条件总为false,表里没有满足条件的记录
4Impossible WHERE noticed after reading const tables在优化器评估了const表之后,发现where条件均不满足
5no matching row in const table当前join的表为const表,不能匹配
6Not exists优化器发现内表记录不可能满足where条件
7Select tables optimized away在没有group by子句时,对于MyISAM的select count(*)操作,或者当对于min(),max()的操作可以利用索引优化,优化器发现只会返回一行。
8Using filesort使用filesort来进行order by操作
9Using index覆盖索引
10Using index for group-by对于group by列或者distinct列,可以利用索引检索出数据,而不需要去表里查数据、分组、排序、去重等等
11Using join buffer之前的表连接在nested loop之后放进join buffer,再来和本表进行join。适用于本表的访问type为range,index或all
12Using sort_union,using union,using intersectindex_merge的三种情况
13Using temporary使用了临时表来存储中间结果集,适用于group by,distinct,或order by列为不同表的列。
14Using where在存储引擎层检索出记录后,在server利用where条件进行过滤,并返回给客户端
编程七大原则和23种设计模式汇总整理 
下一篇:编程七大原则和23种设计模式汇总整理
评论

如果我的文章对你有帮助,或许可以打赏一下呀!

支付宝
微信
QQ