Explain总结
Explain简介
EXPLAIN模拟优化器执行SQL语句，在5.6以及以后的版本中，除了select，其他比如insert，update和delete均可以使用explain查看执行计划。从而知道mysql是如何处理sql语句，分析查询语句或者表结构的性能瓶颈。
EXPLAIN 语句并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。
Explain作用
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
Explain用法
explain+SQL语句即可
简单看一条查询语句的执行计划：
mysql> explain SELECT * FROM dept_emp WHERE emp_no IN (SELECT emp_no FROM dept_emp GROUP BY emp_no HAVING COUNT(emp_no)>1);
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
| 1 | PRIMARY | dept_emp | NULL | ALL | NULL | NULL | NULL | NULL | 331143 | 100.00 | Using where |
| 2 | SUBQUERY | dept_emp | NULL | index | PRIMARY,dept_no | PRIMARY | 16 | NULL | 331143 | 100.00 | Using index |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+Explain执行计划结果分析
| 列名 | 含义 | 详细解释 |
|---|---|---|
| id | SELECT 查询的序列标识符 | 查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序。 两种情况:id相同，执行顺序从上往下；id不同，id值越大，优先级越高，越先执行。如果行引用其他行的并集结果，则该值可以为 NULL。 |
| select_type | SELECT 关键字对应的查询类型 | 查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询，常见的值有： - SIMPLE：简单查询，不包含 UNION 或者子查询。 - PRIMARY：查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY。 - SUBQUERY：子查询中的第一个 SELECT。 - UNION：在 UNION 语句中，UNION 之后出现的 SELECT。 - DERIVED：在 FROM 中出现的子查询将被标记为 DERIVED。 - UNION RESULT：UNION 查询的结果。 |
| table | 用到的表名 | 查询用到的表名，每行都有对应的表名，表名除了正常的表之外，也可能是以下列出的值： - unionM,N: 本行引用了 id 为 M 和 N 的行的 UNION 结果； - derivedN : 本行引用了 id 为 N 的表所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询。 - subqueryN : 本行引用了 id 为 N 的表所产生的的物化子查询结果。 |
| partitions | 匹配的分区，对于未分区的表，值为 NULL | 查询所匹配记录所在的分区，对于未分区的表，值为 NULL。 |
| type（重要） | 表的访问方法 | 查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 常见的几种类型具体含义如下： - system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。 - const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。 - eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。 - ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。 - index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。 - range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描。 - index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。 - ALL：全表扫描。 注意:一般保证查询至少达到range级别，最好能达到ref。 |
| possible_keys | 可能用到的索引 | possible_keys 列表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ，则表示没有可能用到的索引；这种情况下，需要检查 WHERE 语句中所使用的的列，看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。 |
| key（重要） | 实际用到的索引 | key 列表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。查询中如果使用覆盖索引，则该索引和查询的select字段重叠。 |
| key_len | 所选索引的长度 | 表示索引中使用的字节数，该列计算查询中使用的索引的长度。在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。 |
| ref | 当使用索引等值查询时，与索引作比较的列或常量 | 表示在查询索引时，哪些列或者常量被用来与索引的值进行比较。 |
| rows | 预计要读取的行数 | rows 列表示根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好。 |
| filtered | 按表条件过滤后，留存的记录数的百分比 | 表示估算的经过查询条件筛选出的列数的百分比。例如 rows 是 1000，filtered 是 50（50%），则实际筛选出的列数为 1000 * 50% = 500。 |
| Extra（重要） | 附加信息 | 这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下： - Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。效率很低，尽量避免出现。 - Using temporary：MySQL 需要创建临时表来存储查询的中间结果，常见于 ORDER BY 和 GROUP BY。效率低，避免出现。 - Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。 - Using index condition：表示查询优化器选择使用了索引条件下推这个特性。 - Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。 - Using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。 - impossible where:where子句的值总是false，不能用来获取任何元组 - select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。 - distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。 当 Extra 列包含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，需要尽可能避免。 |