怎么分析优化sql语句，MYSQL执行计划怎么用

MYSQL 执行计划

Explain 顾名思义：解释

什么是explain

Explain在mysql中被称为执行计划，在你要查询的语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，模拟MySQL优化器来执行SQL语句，执行查询时，会返回执行计划的信息，并不执行这条SQL。(注意，如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中)。
Explain可以用来分析SQL语句和表结构的性能瓶颈。通过执行计划返回的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引预测被使用、哪些索引实际被使用、使用的索引长度，每个数据表有多少行记录被查询等信息。

explain 拓展

explain extended
在explain的基础上提供一些额外的查询信息，在explian extended执行以后，通过show warnings命令可以得到优化后的查询语句，通过优化后的查询语句可以看出优化器做了哪些工作，还可以通过某些数据估算表连接的行数。
explain partitions
用于分析使用了分区的表，会显示出可能用到的分区。

在要执行的sql语句之前添加explain关键字，通过执行器执行之后会返回如下结果集：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
查询id	查询类型	查询表	扫描访问类型	可能命中的索引	实际名中的索引	使用的索引长度	与key一起使用的字段或常数	估算扫描行数	额外的重要信息

该结果集一共包含10个字段，每个字段的含义在下面会一一讲解

以下表为例子

explain select * from(select 字段 from a left join b on 条件 left join (select 字段 from user_addr)cc on 条件 where 条件 union select 字段 from user_info left join(select 字段 from user_extra_info)ee on 条件 left join (select 字段 from user_record)ff on 条件 left join (select 字段 from user_option)gg on条件 where 条件) where 条件 order by 字段 limit 0,10

注意：该表中数据不一定准确，只是用来模拟结果集

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	(NULL)	(NULL)	(NULL)	(NULL)	13445	Using filesort
2	DERIVED	a	ALL	(NULL)	(NULL)	(NULL)	(NULL)	925	Using where
2	DERIVED	b	ALL	(NULL)	(NULL)	(NULL)	(NULL)	11	Using where; Using join buffer (Block Nested Loop)
2	DERIVED	<derived3>	ref	<auto_key0>	<auto_key0>	7	user_id	10	(NULL)
3	DERIVED	user_addr	index	userAddrIdx	userAddrIdx	7	user_id	111	Using index
4	UNION	user_info	EQ_INDEX	userInfoNameIdx	userInfoNameIdx	8	const	39	Using where;Using index
4	UNION	<derived5>	ref	<auto_key0>	<auto_key0>	7	user_id	10	(NULL)
4	UNION	<derived6>	ref	<auto_key0>	<auto_key0>	7	user_id	10	(NULL)
4	UNION	<derived7>	ref	<auto_key0>	<auto_key0>	7	user_id	10	(NULL)
7	DERIVED	user_extra_info	EQ_REF	userEI_UID_IDX	userEI_UID_IDX	7	user_id	199	Using where;Using index
6	DERIVED	user_record	INDEX_MERGE	urUserIdIdx	urUserIdIdx	7	user_id	5916	Using where;Using index
5	DERIVED	user_option	RANGE	uoUserIdIdx	uoUserIdIdx	7	user_id	348	Using where;Using index; Using temporary; Using filesort
(NULL)	UNION RESULT	<union2,4>	ALL	(NULL)	(NULL)	(NULL)	(NULL)	(NULL)	Using temporary

1.id

执行计划结果集中的查询id，该id代表查询的先后顺序。

其规则是：值越大的越先执行，值相等的按照顺序从上往下执行，值为NULL的最后执行

从上表的id列和table列组合可以看出，
MySQL先执行的是步骤7,6,5，然后形成了3个临时表，derived7,derived6,derived5，
在这三个表形成之后走了步骤4，从上往下执行，将这3个表联合起来。
后面接着执行步骤3，形成临时表derived3，
在步骤2中执行a，b，derived3这三个表，
接下来将步骤2和步骤4形成的表进行联合，形成一个结果集存放到临时表derived2中。
最后执行步骤1，操作derived2表，即外层select查询结果

2. selec_type(查询类型)

1. SIMPLE：查询中不包含子查询或者UNION
2. 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
3. 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
4. 在FROM列表中包含的子查询被标记为：DERIVED（衍生）
5. 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在 FROM子句的子查询中，外层SELECT将被标记为：DERIVED
6. 从UNION表获取结果的SELECT被标记为：UNION RESULT

3. table

查询的数据表，可以是物理表，也可以是MySQL在查询过程中衍生出的临时表

4. type

扫描访问的类型（通俗点讲就是用没用索引，用的什么索引），我们在分析sql性能并进行调优的时候，type是重点分析对象。其包含如下类型：
1.ALL：全表扫描，也意味着没有走索引，效率最慢。
2.index：全索引表扫描，其与ALL的差别在于，index只是遍历索引树，速度比ALL快，因为索引文件比较小。
3.RANGE：索引范围扫描，对索引的扫描开始于某一点，返回匹配到的值域的行，常见于between，<,>，in等范围查询条件，其效率高于index，因为不需要对索引进行全扫描，只扫描其中一部分即可。
4. INDEX_MERGE：合并索引扫描，使用多个单列索引进行合并搜索，其搜索范围可以进一步缩小，效率比RANGE高，因为可以更快的缩小查找的范围。
5. REF：非唯一性索引扫描，返回匹配某个值的所有行，常见于非唯一索引，比如普通索引，以及组合索引的非唯一前缀匹配。比如一个索引IDX(a,b,c),查找的时候使用where a=‘xxx’,会使用ref。
6. EQ_REF：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引等值扫描。
注意：对于ref和eq_ref，如果有唯一索引，join全连接查询会使用eq_ref，left join或者right join可能会使用ref。
7. CONST：常量，表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次，比如where条件中的主键或者唯一索引。
8. SYSTEM：是const类型的特例，当查询的表只有一行的时候会使用system。
这八种扫描访问类型效率从高到低依次为：
system > const > eq_ref > ref > index_merge > range > index > all.

5.possible_keys

数据库在执行过程中可能使用到的索引，该列的值展示的是数据表中定义的索引名称或者是数据库内部自动生成的索引名称，或者为NULL，为NULL的时候表示未预测到可用的索引。

6.key

数据库查询过程中真实使用到的索引，该列的值展示的是数据表中定义的索引名称或者是数据库内部自动生成的索引名称，或者为NULL，为NULL的时候表示没有可用的索引，select_type为index_merge时，这里可能出现两个以上的索引，其余的select_type这里只会出现一个。

7.key_len

查询过程中实际使用到的索引字节长度，比如多列索引，只用到最左的一列，那么使用到索引的长度则为该列的长度，故该值不一定等于 key 列索引的长度。

8.ref

当 join type 为 eq_ref 或者 ref 时，谓词的关联信息。可能为 ：null（非 eq_ref\ref join type时）、const（常量）、关联的谓词列名，若是使用的常数等值查询，这里会显示const，若是链接查询，被驱动表的执行计划这里会显示驱动表的关联字段，若是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func。

9.rows

根据表统计信息或者索引选用情况，大致估算出找到所需的记录所需要读取的行数。

10.Extra

执行计划记录的十分重要的额外信息。常用到的有如下几种：

• Using filesort：MySQL 对数据使用一个外部的文件内容进行了排序，而不是按照表内的索引进行排序读取。
  Using filesort 说明，得到所需结果集，需要对所有记录进行文件排序。这类 SQL 语句性能极差，需要进行优化。
  典型的，在一个没有建立索引的列上进行了 order by，就会触发 filesort，常见的优化方案是，在 order by 的列上添加索引，避免每次查询都全量排序。

• Using temporary：使用临时表保存中间结果，也就是说 MySQL 在对查询结果排序时使用了临时表，常见于order by 或 group by。
  Using temporary 说明，需要建立临时表（temporary table）来暂存中间结果。
  这类 SQL 语句性能较低，往往也需要进行优化。
  典型的 group by 和 order by 同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。
  临时表存在两种引擎，一种是 Memory 引擎，一种是 MyISAM 引擎，如果返回的数据在 16M 以内（默认），且没有大字段的情况下，使用 Memory 引擎，否则使用 MyISAM 引擎。

• Using index：表示 SQL 操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高。

• Using index condition：表示 SQL 操作命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。

• Using index condition 说明，确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。
  这类 SQL 语句性能也较高，但不如 Using index。

• Using where：表示 SQL 操作使用了 where 过滤条件。

• Select tables optimized away：基于索引优化 MIN/MAX 操作或者 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化。

• Using join buffer (Block Nested Loop)：表示 SQL 操作使用了关联查询或者子查询，且需要进行嵌套循环计算。
  典型的两个关联表 join，关联字段均未建立索引，就会出现这种情况。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。

注：filtered（使用explain extended）

使用explain extended时会出现这个列，5.7以后的版本默认就有这个字段，不须要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少知足查询的记录数量的比例，注意是百分比，不是具体记录数。