mysql explain 优化

EXPLAIN 每列含义

Column	Meaning	优化时如何参考
id	The SELECT identifier	查询序号
select_type	The SELECT type	查询类型
table	The table for the output row	查询用到的表
partitions	The matching partitions	匹配的分区
type	The join type	join 类型
possible_keys	The possible indexes to choose	此次查询中可能选用的索引
key	The index actually chosen	此次查询中确切使用到的索引.
key_len	The length of the chosen key	表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.参考资料二中有计算方式
ref	The columns compared to the index	哪个字段或常数与 key 一起被使用
rows	Estimate of rows to be examined	显示此查询一共扫描了多少行. 这个是一个估计值，越小越好
filtered	Percentage of rows filtered by table condition	表示此查询条件所过滤的数据的百分比
Extra	Additional information	额外的信息，Using filesort，Using temporary建议优化

查询类型详解

select_type Value	Meaning
SIMPLE	Simple SELECT (not using UNION or subqueries)
PRIMARY	Outermost SELECT
UNION	Second or later SELECT statement in a UNION
DEPENDENT UNION	Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT	Result of a UNION.
SUBQUERY	First SELECT in subquery
DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query
DERIVED	Derived table
DEPENDENT DERIVED	Derived table dependent on another table
MATERIALIZED	Materialized subquery
UNCACHEABLE SUBQUERY	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

join 类型详解,性能从最好到最差

Join Type Value	Meaning
system	表中只有一条数据. 这个类型是特殊的 const 类型
const	针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.
eq_ref	此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高
ref	此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询.
fulltext	全文索引
ref_or_null	This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. This join type optimization is used most often in resolving subqueries.
index_merge	This join type indicates that the Index Merge optimization is used. In this case, the key column in the output row contains a list of indexes used, and key_len contains a list of the longest key parts for the indexes used.
unique_subquery	unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency.
index_subquery	This join type is similar to unique_subquery.
range	表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个.
index	表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index.
ALL	表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.

优化时尽可能消除下面四种情况：

1.全表扫描
2.全索引扫描
3.临时表
4.fileSort

参考资料：

一、mysql官方文档 EXPLAIN Output Format
二、MySQL 性能优化神器 Explain 使用分析