MySQL - EXPLAIN（优化sql）

EXPLAIN ：mysql 5.6后的版本都支持查看 '增删改查 '的 执行计划（执行的顺序，索引类型......）

先了解下 MYSQL sql语句的执行流程

SELECT * from bx_order  where orderid >'12' GROUP BY categoryid HAVING count(1) > 3 ORDER BY categoryid LIMIT 10,100

加载 bx_order 表 → where 条件判断 → group by 字段 → 聚合函数count(1) → having条件判断 → order by排序 → limit 分页

EXPLAIN语法(MySQL5.7版本)

EXPLAIN Output Columns

Column	JSON Name	Meaning	备注
id	select_id	The SELECT identifier	id越大 越早执行 id相同 顺序自上而下执行
select_type	None	The SELECT type	SIMPLE:简单SELECT(不使用UNION或子查询) PRIMARY:最外面的SELECT UNION:UNION中的第二个或后面的SELECT语句 DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询 UNION RESULT:UNION 的结果 SUBQUERY:子查询中的第一个SELECT DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询 DERIVED:导出表的SELECT(FROM子句的子查询) ......(省略了 非重要的)
table	table_name	The table for the output row	输出的行所引用的表
partitions	partitions	The matching partitions	查询将匹配记录的分区。该值适用NULL于非分区表
type	access_type	The join type	从好到差依次为：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL   1、system：The table has only one row (= system table)表中只有一行数据或者是空表。 const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描。 2、eq_ref：出现在连表语句中，驱动表的连接字段是逐渐或唯一性索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref。 3、ref：ref没有eq_ref严格，没有要求连接表的顺序，也不要求包含主键或唯一性索引，常见与普通索引的连表中。 4、fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。 5、ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。 6、index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所有索引，性能可能大部分时间都不如range。 7、unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值。形如：value IN (SELECT primary_key FROM single_table WHERE some_expr) index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。形如：value IN (SELECT key_column FROM single_table WHERE some_expr) 8、range：索引范围扫描，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。 9、index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。 10、all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。
possible_keys	possible_keys	The possible indexes to choose	查询可能使用到的索引都会在这里列出来
key	key	The index actually chosen	查询真正使用到的索引
key_len	key_length	The length of the chosen key	索引长度,没有索引则为Null
ref	ref	The columns compared to the index	显示将哪些列或常量与列中指定的索引进行比较
rows	rows	Estimate of rows to be examined	影响的行数
filtered	filtered	Percentage of rows filtered by table condition	该filtered列指示将按表条件过滤的表行的估计百分比。最大值为100，这意味着不会对行进行过滤。值从100开始减少表示过滤量增加。
Extra	None	Additional information	额外的信息（重要）

Extra 这个是包含了一些十分重要的额外信息，也是非常重要的一个字段。

他可能包含以下一个或多个值：

1、using filesort，这个说明mysql无法利用索引进行排序，那他只能用排序算法重新进行排序了，这会额外消耗资源。出现这个的话那么说明这条SQL需要优化了，需要正确使用索引或者建立合适的索引。

下面是一个让MySQL使用正确索引的例子，同样的结果，不一样的过程。

但是具体那条SQL更快呢，也不一定，根据具体情况而定，比如数据规模之类的。还有就是开发当中一般是不允许用select * 的，要什么字段就拿什么字段，不然会造成性能浪费。

2、using temporary，建立了临时表来保存中间结果，查询完成之后又要把临时表删除。出现这样的情况说明这条SQL语句请一定要优化，如果这样的SQL一多的话非常影响系统的性能。

3、using index，前面也说过，这个表示当前的查询是覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where表明索引被用来执行索引键值的查找，如果没有using where，表面索引被用来读取数据，而不是进行查找。

4、using where，使用了where进行条件过滤。

5、using join buffer、表示使用了连接缓存。

6、impossible where，where语句的值总是false。

7、Distinct，一旦MySQL找到了与行相联合匹配的行，就不再搜索了。

重点

type：访问类型，查看SQL到底是以何种类型访问数据的。

key：使用的索引，MySQL用了哪个索引，有时候MySQL用的索引不是最好的，需要force index()。

rows：最大扫描的列数。

extra：重要的额外信息，特别注意损耗性能的两个情况，using filesort和using temporary。