Explain执行计划

执行计划Explain简介

Explain+SQL语句可以模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。

执行计划Explain中包含的信息

列属性	Json名称	意义
id	select_id	select标识符
select_type	无	select类型
table	table_name	输出行的表名称
partitions	partitions	匹配的分区
type	access_type	连接类型
possible_keys	[ossibale_keys	可供选择的索引
key	key	实际选择的索引
key_len	key_length	实际用到索引的长度
ref	ref	与索引比较的列
rows	rows	估计要检查的行数
filtered	filtered	按条件过滤的行百分比
Extra	无	附加信息

• id

代表sql中查询语句的序列号，序列号越大则执行的优先级越高，序号一样谁在前谁先执行。id为null则最后执行。

• select_type

SELECT的类型，可以是下表11种中显示的任何一种。

select_type类型	JSON 名称	意义
SIMPLE	无	简单SELECT（不使用 UNION或子查询）
PRIMARY	无	最外层SELECT
UNION	无	UNION中的第二个或以后SELECT的语句
DEPENDENT UNION	dependent (true)	UNION中的第二个或后面SELECT的语句 ，取决于外部查询
UNION RESULT	union_result	UNION的结果。
SUBQUERY	无	diyige selectf的子查询语句 跟在select 关键字后面的select查询语句
DEPENDENT SUBQUERY	dependent (true)	第一个SELECT的子查询中，依赖于（取决于）外部查询
DERIVED	无	派生表
MATERIALIZED	materialized_from_subquery	物化子查询
UNCACHEABLE SUBQUERY	cacheable (false)	一个子查询，其结果无法缓存，必须为外部查询的每一行重新计算
UNCACHEABLE UNION	cacheable (false)	UNION 属于不可缓存子查询 的第二个或以后的选择（请参阅 参考资料UNCACHEABLE SUBQUERY）其结果无法缓存

其中最常用的

SIMPLE 简单的SELECT查询，不包含子查询和UNION
PRIMARY 复杂擦护心中的最外层查询，表示主要的查询
SUBQUERY SELECT或WHERE列表中包含了子查询
DERIVED FROM列表中包含的子查询，即衍生
UNION UNION关键字之后的查询
UNION RESULT 从UNION后的表获取结果集

• table

输出结果集的表,显示这一步所访问数据库中表名称，有时不是真实的表名字，可能是表的别名

• partitions

匹配的分区

• type

表的连接类型，有的地方叫“访问类型”，即对表访问方式，MySQL在表中找到所需行的方式。

    常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）。

    ALL：全表扫描， 即MySQL将遍历全表以找到匹配的行。

    index: 索引全扫描，MySQL遍历整个索引来查询匹配行。
    它的主要优点是避免了排序，最大的缺点是要承担按索引次序读取整个表的开销。
    这通常意味着若是按随即次序访问行，开销将非常大。 
    如果在Extra列中看到了“Using index”，说明mysql正在使用覆盖索引，它只扫描索引的数据，而不是按索引次序的每一行。
    它比按索引次序全表扫描的开销要少很多。
    
    range: 范围扫描就是一个有限制的索引扫描，它开始于索引里的某一点，返回匹配这个值域的行。
    这比全索引扫描好一些，因为它用不着遍历全部索引。显而易见的范围扫描是带有between或在where子句中带有 > 的查询。
    索引范围扫描，常用于、>=、between等操作，注意这种情况下比较的字段是需要加索引的，如果没有索引，则 MySQL会进行全表扫描。

    ref: 这是一种索引访问，也有的叫索引查找。它返回所有匹配某个单值的行。
     然后，它可能会找到多个符合条件的行，因此，它是查找和扫描的混合体。
     此类索引访问只有当使用非唯一索引或唯一索引的非唯一前缀时才发生。
     把它叫做ref是因为索引要跟某个参考值相比较，这个参考值或是一个常数，或是来自多表查询前一个表里的结果值。
     ref_or_null是ref的一个表种，他表示mysql必须在初次查找的结果里进行第二次查找以找出NULL的条目。

    eq_ref: 唯一索引扫描，区别在于使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配。

    const、system: 单表中最多有一条匹配行，查询起来非常迅速，所以这个匹配行的其他列的值可以被优化器在当前查询中当作常量来处理。

    NULL: MySQL不用访问表或者索引，直接就能够得到结果。

• possible_keys

表示查询时，可能使用的索引，指出MySQL能使用哪个索引在表中找到记录，如果该列是NULL，则没有相关的索引。

• key

表示实际使用的索引，key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中。

• key_len

索引字段的长度，表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的），不损失精确性的情况下，长度越短越好。

• ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

• rows

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。

• filtered

 按表条件过滤的行百分比，存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例(百分比)。

• Extra
  执行情况的描述和说明，包含不适合在其他列中显示但是对执行计划非常重要的额外信息，最主要的有以下三种：

Using Index	表示索引覆盖，不会回表查询
Using Where	表示进行了回表查询
Using Index Condition	表示进行了ICP优化
Using Flesort	表示Mysql虚格外排序操作，不能通过索引顺序达到排序效果

using filesort：说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会额外消耗额外的位置
using temporary：建立临时表来保存中间结果，查询完成之后把临时表删除，这通常发生在执行多表连接、子查询或排序等操作时，可能会增加内存和磁盘的使用，并对性能产生影响。一般多见于order by 和 group by语句。
using index：这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找。表示查询使用了索引覆盖，即只通过索引就能满足查询的需求，无需访问实际的数据行。这通常是一个良好的情况，可以提高查询性能。
using where：使用where进行条件过滤。表示在获取结果集后，再应用 WHERE 条件来进一步过滤结果。这可能发生在无法使用索引覆盖的情况下，可能会增加查询的开销
using join buffer:表示是否使用连接缓冲。表示查询使用了连接缓冲区来处理连接操作。这通常发生在执行连接操作时，可以提高查询性能。
impossible where：where语句的结果总是false

如果查询语句中使用了聚合函数（如SUM，AVG，COUNT等），可以使用EXPLAIN EXTENDED语句来获取更详细的信息。执行该语句后，可以使用SHOW WARNINGS语句来查看查询优化器产生的警告信息。

索引优化的一般步骤：
1、先用慢查询日志定位具体需要优化的sql；
2、使用explain执行计划查看索引使用情况；
3、重点关注：
key（查看有没有使用索引）
key_len（查看索引使用是否充分）
type（查看索引类型）
Extra（查看附加信息：排序、临时表、where条件为false等）
一般情况下根据这4列就能找到索引问题。
4、根据上1步找出的索引问题优化sql；
5、再回到第2步。