mysql explain的用法

mysql explain的用法

mysql explain的用法

MySQL的EXPLAIN命令显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 

一、通过expalin可以得到

 

1、表的读取顺序

2、表的读取操作的操作类型

3、哪些索引可以使用

4、哪些索引被实际使用

5、表之间的引用

6、每张表有多少行被优化器查询

 

 

二、如何调用expalin命令

 

1、只需要在SELECT前面加上EXPLAIN即可，例如

 

explain select surname,first_name form a,b where a.id=b.id 

2、在语句结尾(;之前)加上\G能够更清晰的查看.

3、需要说的是EXPLAIN只对SELECT查询作解释,INSERT,UPDATE,DELETE不会

 

 

 

三、explain显示的列

 

 

1、id

 

语句的执行顺序标识，如果在语句中没有子查询或联合,说明只有一个SELECT,于是这个列显示为1,否则内层的SELECT会顺序编号.

 

 

2、select_type

 

显示了对应的查询是简单还是复杂SELECT，主要有以下几种查询类型

 

 

1)、simple 简单类型

 

语句中没有子查询或union

 

2)、primary

 

最外层的select ，不是主键

 

3)、union 

 

union是在select 语句中第二个select语句后面所有的select，第一个select 为primary 

 

4)、dependent subquery

 

子查询中内层中第一个select语句

 

5)、dependent  union

 

子查询中union且为union中第二个select开始的后面所有select，依赖于外部的结果集。

 

6)、SUBQUERY

 

子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集。

 

7)、devived 

 

派生表的查询语句

 

8)、uncacheable subquery 

 

结果集无法缓存的子查询

 

9)、union result 

 

union中合并的结果

 

 

 

3、table 

 

1)、显示对应行正在访问哪个表

 

2)、当FROM子句中有子查询或UNION时,table列是<derivedN>,其中N是id列对应的值

 

 

 

4、type 

 

这列很重要,显示了连接使用了哪种类别,有无使用索引。主要包括以下几种类型

 

1)、all

全表扫描，效果是最不理想的。

2)、const

const是在where条件以常量作为查询条件，最多只会有一条记录匹配，由于是常量，实际上只须要读一次。

3)、eq_ref

最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问。一般会出现在连接查询的语句中。

4)、fulltext

进行全文索引检索。

5)、index

全索引扫描。MySQL在扫描表时按索引次序进行而不是行。
 

6)、index_merge

查询中同时使用两个（或更多）索引，然后对索引结果进行合并（merge），再读取表数据。

7)、index_subquery

子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引。

8)、rang

索引范围扫描。一个有限制的索引扫描,它开始于索引里的某一点,返回匹配这个值域的行(显而易见的范围扫描.即带有BETWEEN或在WHERE子句中带有>的查询,当MySQL使用索引去查找一系列值的时候,如IN()和OR列表，也为显示的范围扫描)
 

9)、ref

也叫索引查找,他返回所有匹配某单个值的行,它可能会找到多个符合条件行。

10)、ref_or_null

与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。

11)、system

系统表，表中只有一行数据；

12)、unique_subquery

子查询中的返回结果字段组合是主键或唯一约束。

 

5、possible_keys

这一列显示了查询可以使用哪些索引,是基于查询访问的列和使用的比较操作符来判断的.如果没有任何索引可以使用，就会显示成null

 

6、key

显示了MySQL决定采用哪个索引来优化对该表的访问

 

7、key_len

1)、key_len列显示mysql决定使用的键长度，如果键是null，则长度为null。

2)、显示MySQL在索引里使用的字节数.举个例子就是在查询中使用到了主键,而主键的数据类型为INT,则为4,SMALLINT则为2

3)、使用的索引长度，一般越短越好。

 

8、Ref

显示了之前的表在key列记录的索引中查询值所用到的列或常量。

 

9、rows

显示的是MySQL为了找到所需的值而要读取的行数.

 

10、extra

在此显示的是在其他列不适合显示的额外信息，主要可能会是以下内容：

1)、Distinct

查找distinct 值，当mysql找到了第一条匹配的结果时，将停止该值的查询，转为后面其他值查询。

2)、Full scan on NULL key

子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用。

3)、Using index

MySQL将使用覆盖索引,以避免访问表(就是仅仅使用了索引中信息而没有读取表中)
 

4)、Using where

意味着MySQL服务器将在存储引擎检索行后在进行过滤(将会通过WHERE条件来筛选存储引擎返回的记录)
 

5)、Using temporary

意味着MySQL在对查询结果排序时会用到一个临时表.
 

6)、Using filesort

看到这个的时候，查询就需要优化了。意味着MySQL会对结果使用一个外部索引排序,而不是按索引次序从表里读出来.