1.1 explain分析语句

通过对查浦酒句的分析，可以了解查询语句的执行情况，找出查询语向执行的瓶颈，从而优化查询语句。MySQL中提供了EXPLAIN语句，用来分析查询语句。本节介绍使用EXPLAIN语句分析查询语句的方法。

EXPLAIN语句的基本语法如下:

EXPLAIN [EXTENDED] SELECT select_ options

使用EXTENED关键字，EXPLAIN语句将产生附加信息。select options 是SELECT语句的查询选项，包括FROM WHERE子句等。

执行该语句，可以分析EXPLAIN后面的SELECT语句的执行情况，并且能够分析出所查 询的表的一些特征。

使用EXPLAIN语句来分析1个查询语句，执行如下语句:

mysql> mysql> explain select * from article where status = 0;
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | article | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10856301 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

下面我们对查询结果进行解释：

字段

描述

id

select识别符。这是select的查询序列号，id的值越大优先级别越高，越先被执行，如果id相同，执行顺序右上至下

select_type

select_type表示select语句的类型。

table

type表示这一行的数据是关于哪张表的。

partitions

分区

type

type表示的是表的连接类型。

possible_keys

possible_keys列支出mysql能使用哪个索引在该表中找到行。如果这列是null，则没有相关的索引。在这种情况下，可以通过检查where子句查看它是否引用某些列或者适合索引的列来提高查询性能。如果是这样，可以创建适合的索引来提高查询的性能

key

key表示查询实际应用到的索引，如果没有选择索引，该列的值是null。想要强制使mysql使用或者忽视possible_type列中的索引，在查询中使用force index，use index 或者 ignore index。

key_len

key_len表示mysql选择的索引字段按字节计算的长度，如果键是null，则长度为null，注意通过key_len值可以确定mysql将实际使用一个多列索引中的几个字段

ref

ref表示使用哪个列或常数与索引一起来查询记录

rows

rows显示mysql表中进行查询时必须检查的行数

filtered

 

Extra

extra显示mysql在处理查询时的详细信息。

对于以上字段信息不同值的解释：

  1. select_type
(1) SIMPLE

SIMPLE表示简单查询，其中不包括连接查询和子查询。

(2) PRIMARY与SUBQUERY

PRIMARY表示主查询或者最外层的查询语句。SUBQUERY : 子查询
例如：

mysql> explain select * from article where id = (select id from article where content  = 'LrQ2Ievq9P');

+----+-------------+---------+------------+-------+---------------+---------+---------+-------+----------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows     | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+----------+----------+-------------+
|  1 | PRIMARY     | article | NULL       | const | PRIMARY       | PRIMARY | 4       | const |        1 |   100.00 | NULL        |
|  2 | SUBQUERY    | article | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | 10856301 |    10.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+----------+----------+-------------+
2 rows in set, 1 warning (16.28 sec)

(3) DERIVED： 衍生查询-在select出一批自定义列的数据，概念上相当于一张表，但是该表只在语句执行过程出现和

(4) UNION 与 UNION RESULT
UNION ：联合查询，union 后面的那张表就会表示成它
UNION RESULT:  联合结果

例如：
mysql> explain select * from article where id = 6565544 union all select * from article where id = 8484848 ;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | article | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
|  2 | UNION       | article | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
  1. type
对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

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

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

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

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
  1. Extar
Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，就不一个一个介绍了，在这只介绍常见的一些额外信息说明 .

① Using filesort: 如果根据索引列进行排序（order by 索引列）是可以用到索引的，SQL查询引擎会先根据索引列进行排序，然后获取对应记录的主键id执行回表操作，如果排序字段用不到索引则只能在内存中或磁盘中进行排序操作，MySQL把这种在内存或者磁盘上进行排序的方式统称为文件排序（英文名：filesort），如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort

② Using temporary: 许多查询的执行过程中，MySQL会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含distinct、group by、union等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示Using temporary提示.

③ USING index: 表示相应的select操作中使用了覆盖索引（Covering Index）,避免回表操作，效率不错！
如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查找动作。

④ Using where: 使用了where过滤

⑤ using join buffer: 在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度

⑥ impossible where: where子句的值总是false，不能用来获取任何元组

⑦ select tables optimized away: 在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

⑧ distinct: 优化distinct，在找到第一匹配的元组后即停止找同样值的工作

⑨ Using index condition：查找使用了索引，但是需要回表查询数据