（1.6）MySQL执行计划

关键词：mysql执行计划

1、用法

【1.1】explain select * from tab_name........

【1.2】desc select * from tab_name.....（高版本可用）

【1.3】举例查看

id：SELECT标识符。这是SELECT的查询序列号。

select_type：表示SELECT语句的类型。它可以是以下几种取值：

    SIMPLE：表示简单杳询，其中不包括连接查询和子查询；

    PRIMARY：表示主查询，或者最外层的查询语句；

    UNION：表示连接查询的第2个或后面的查询语句；

    DEPENDENT UNION：连接查询中的第2个或后面的SELECT语句，取决于外面的查询；

    UNION RESULT：连接查询的结果；

    SUBQUERY：子查询中的第一个SELECT语句；

    DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询；

    DERIVED：导出表的SELECT (FROM语句的子查询）。

 
table：表示查询的表。

type：表示表的连接类型。下面按照从最佳类型到最差类型的顺序给出各种连接类型：

    (1) system

        该表仅有一行的系统表。这是const连接类型的一个特例。

    (2) const

        数据表最多只有一个匹配行，它将在查询开始时被读取，并在余下的査询优化中作为常量对待。const表查询速度很快，因为它们只读取一次。const用于使用常数值比较PRIMARY KEY或UNIQUE索引的所有部分的场合。

        在下面查询中，tb1_name可用const表：

        SELECT  *  from tb1_name WHERE primary_key=1;

        SELECT * from tb1_name WHERE primary_key_part1=1 AND primary_key_part2=2

    (3) eq_ref

        对于每个来自前面的表的行组合，从该表中读取一行。当一个索引的所有部分都在查询中使用，并且索引是UNIQUE或者PRIMARY KEY时，即可使用这种类型。

        eq_ref可以用于使用“=”操作符比较带索引的列。比较值可以为常量或者一个在该表前面所读取的表的列的表达式。

        在下面例子中，MySQL可以使用eq_ref来处理ref_tables：

        SELECT * FROM ref_table,other_table WHERE ref_table.key_cloumn = other_table.cloumn;

        SELECT * FROM ref_table, other_tbale WHERE ref_table.key_cloumn_part1 = other_table.cloumn AND ref_table.key_cloumn_part2 = 1;


    （4）ref

        对于来自前面的表的任意组合，将从该表中读取所有匹配的行。这种类型用于索引既不是UNIQUE也不是PRIMARY KEY的情况，或者查询中使用了索引列在左子集，既索引中左边的部分列组合。ref可以用于使用=或者<=>操作符的带索引的列。

        以下的几个例子中，mysql将使用 ref 来处理ref_table：   

        select * from ref_table where key_column=expr; 

        select * from ref_table,other_table where ref_table.key_column=other_table.column; 

        select * from ref_table,other_table where ref_table.key_column_part1=other_table.column and ref_table.key_column_part2=1;


    （5）ref_or_null

        这种连接类型类似ref，不同的是mysql会在检索的时候额外的搜索包含null值的记录。在解决子查询中经常使用该链接类型的优化。

        在以下的例子中，mysql使用ref_or_null 类型来处理 ref_table：

        select * from ref_table where key_column=expr or key_column is null;

    （6）index_merge

        该链接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。

    （7）unique_subquery

        该类型替换了下面形式的IN子查询的ref：

        value in (select primary_key from single_table where some_expr)

    （8）index_subquery

        这种连接类型类似 unique_subquery。可以替换IN子查询，不过它用于在子查询中没有唯一索引的情况下，

        例如以下形式：

        value in (select key_column from single_table where some_expr)

    （9）range

        只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。ken_len包含所使用索引的最长关键元素。当使用 =, <>, >,>=, <, <=, is null, <=>, between, 或 in操作符，用常量比较关键字列时，类型为range。

        下面介绍几种检索制定行的情况：

        select * from tbl_name where key_column = 10; 

        select * from tbl_name where key_column between 10 and 20; 

        select * from tbl_name where key_column in (10,20,30); 

        select * from tbl_name where key_part1= 10 and key_part2 in (10,20,30);

    （10）index

         连接类型跟ALL一样，不同的是它只扫描索引树。它通常会比ALL快点，因为索引文件通常比数据文件小。

    （11）ALL

        对于前面的表的任意行组合，进行完整的表扫描。如果第一个表没有被标识为const的话就不大好了，在其他情况下通常是非常糟糕的。正常地，可以通过增加索引使得能从表中更快的取得记录以避免ALL。

　　（12）possible_keys

        possible_keys字段是指MySQL在搜索表记录时可能使用哪个索引。如果这个字段的值是NULL，就表示没有索引被用到。这种情况下，就可以检查WHERE子句中哪些字段哪些字段适合增加索引以提高查询的性能。创建一下索引，然后再用explain 检查一下。

key

       key字段显示了MySQL实际上要用的索引。当没有任何索引被用到的时候，这个字段的值就是NULL。想要让MySQL强行使用或者忽略在 possible_keys字段中的索引列表，可以在查询语句中使用关键字force index, use index或 ignore index。参考SELECT语法。

key_len

        key_len 字段显示了mysql使用索引的长度。当key 字段的值为NULL时，索引的长度就是NULL。注意，key_len的值可以告诉你在联合索引中MySQL会真正使用了哪些索引。

ref

        表示使用哪个列或常数与索引一起来查询记录。

rows

        显示MySQL在表中进行查询时必须检查的行数。 

Extra

        本字段显示了查询中mysql的附加信息。以下是这个字段的几个不同值的解释

distinct

        MySQL当找到当前记录的匹配联合结果的第一条记录之后，就不再搜索其他记录了。 


not exists

        MySQL在查询时做一个LEFT JOIN优化时，当它在当前表中找到了和前一条记录符合LEFT JOIN条件后，就不再搜索更多的记录了。下面是一个这种类型的查询例子：

        select * from t1 left join t2 on t1.id=t2.id where t2.id is null;

        假使 t2.id 定义为 not null。这种情况下，MySQL将会扫描表 t1并且用 t1.id 的值在 t2 中查找记录。当在 t2中找到一条匹配的记录时，这就意味着 t2.id 肯定不会都是null，就不会再在 t2 中查找相同id值的其他记录了。也可以这么说，对于 t1 中的每个记录，mysql只需要在t2 中做一次查找，而不管在 t2 中实际有多少匹配的记录。


        range checked for each record (index map: #)

        mysql没找到合适的可用的索引。取代的办法是，对于前一个表的每一个行连接，它会做一个检验以决定该使用哪个索引（如果有的话），并且使用这个索引来从表里取得记录。这个过程不会很快，但总比没有任何索引时做表连接来得快。

        
using filesort

        MySQL需要额外的做一遍从已排好的顺序取得记录。排序程序根据连接的类型遍历所有的记录，并且将所有符合where条件的记录的要排序的键和指向记录的指针存储起来。这些键已经排完序了，对应的记录也会按照排好的顺序取出来。详情请看"7.2.9how mysql optimizes order by"。

using index

        字段的信息直接从索引树中的信息取得，而不再去扫描实际的记录。这种策略用于查询时的字段是一个独立索引的一部分。

using temporary

        mysql需要创建临时表存储结果以完成查询。这种情况通常发生在查询时包含了group by和order by子句，它以不同的方式列出了各个字段。

using where

        where子句将用来限制哪些记录匹配了下一个表或者发送给客户端。除非你特别地想要取得或者检查表种的所有记录，否则的话当查询的extra字段值不是using where并且表连接类型是all或index时可能表示有问题。

        如果你想要让查询尽可能的快，那么就应该注意extra字段的值为using filesort和using temporary的情况

2、释义

【2.1】id：顺序序列

【2.2】select_type：查询类型

　　（1）simple：简单表，不使用表连接、子查询　　（2）primary：（主查询，即外层的查询）

　　（3）union（union中的第二个或者后面的查询语句）　　（4）subquery（子查询中的第一个select）

【2.3】table：操作表名称（有别名会显示别名）

【2.4】type：在表中找到锁需要的检索方式（或者叫访问方式）常见如下

　　从（1）-（7）性能越来越好

　　（1）all：全表扫　　（2）index：索引全表扫　　（3）range：索引范围扫描

　　（4）ref：等值查询，可能有多条记录（使用非唯一索引扫描或唯一索引的前缀扫描）　　

　　（5）eq_ref：等值查询，结果只有一条记录（唯一索引扫描）

　　（6）const/system：单表中最多有一个匹配行，查询结果可以被当做常量处理。例如根据主键或唯一索引进行的查询。

　　（7）null：不用访问表或者索引，比如select 1 from dual;

执行计划，简单的来说，是SQL在数据库中执行时的表现情况,通常用于SQL性能分析,优化等场景。在MySQL中使用 explain 关键字来查看。如下所示:

explain select * from table where table.id = 1

运行上面的sql语句后你会看到，下面的表头信息：

table | type | possible_keys | key | key_len | ref | rows | Extra

解释下表头各列的意义。

table

显示这一行的数据是关于哪张表的

type

这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

不同连接类型的解释（按照效率高低的顺序排序）：

system：表只有一行：system表。这是const连接类型的特殊情况。
const ：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待。
eq_ref：在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用。
ref：这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。
range：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况。
index：这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）。
ALL：这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。

possible_keys

显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key

实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len

使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

rows

MYSQL认为必须检查的用来返回请求数据的行数

Extra

关于MYSQL如何解析查询的额外信息。举例返回值如：Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

以下为extra列返回的一些值和其意义：

Distinct ：一旦mysql找到了与行相联合匹配的行，就不再搜索了。
Not exists ：mysql优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
Range checked for each Record（index map:#）：没有找到理想的索引，因此对从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。
Using filesort：看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
Using index ：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。
Using temporary ：看到这个的时候，查询需要优化了。这里，mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。
Where used ：使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题。

posted @ 2019-04-01 15:34 郭大侠1 阅读(246) 评论(0) 编辑收藏举报

刷新页面返回顶部