MySQL执行计划介绍

explain工具介绍

    使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈，因为SQL语句再执行过程中会在MySQL中进行一系列的分析，优化等等的操作。

     它返回的不是查询后的数据而是执行计划的信息，使用如下：

explain select * from actor;

返回的结果就是下图

1、id列

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的 顺序增长的。并且id越大执行优先级就越高，id相同就从上往下执行，如果为null那么就是最后执行。

2、select_type

顾名思义，反应的是查询的类型是复杂查询还是简单查询，图上就是一个简单查询：simple；这个字段还有其它的值

1.primary：复杂查询中最外层的select语句

2.subquery：包含在 select 中的子查询（不在 from 子句中）

3.derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为 派生表（MySQL新版本都是将衍生表有一个合并优化，所以一般不显示这个字段）

 set session optimizer_switch='derived_merge=off';#关闭衍生表的合并优化

下面用一个例子解释primary、subquery类型

explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der

id越大越优先执行，也就是说subquery 优先执行，根据上面的介绍也就是在select语句中的子句，而且不在from中那么就是

(select 1 from actor where id = 1)#subquery

由于我自己的MySQL版本有点高，这个合并优化的语句不管用。。。。。。，大致是这么个意思。

4.union：在 union 中的第二个和随后的 select

explain select 1 union all select 1;

结果如下

也就是union all 后面的语句select 1

3、table列

也就是表示当前查询访问的是哪一张表。

4、 type列

这一列比较重要，反应的是当前查询语句的好坏，就是说MySQL决定如何查找表中的行，查找数据行记录的大概 范围

依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL，在优化SQL语句的时候保证到range级别。

NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在 索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表。

const, system：mysql能对查询的某部分进行优化并将其转化成一个常量，用于 primary key 或 unique key 的所有列与常数比较时，所以表最多 有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为 system。

eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合 条件的记录。

ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀（最左前缀），索引要和某个值相比较，可能会找到多个符合条件的行。

range：范围扫描通常出现在 in(), between ,> ,= 等操作中。使用一个索引来检索给定 范围的行。

explain select * from actor where id > 1;

ALL：即全表扫描，意味着mysql需要从头到尾去查找所需要的行。一般这种情况是需要进行SQL优化了。

5、possible_keys列

该列反应的是当前查询语句可能用到的索引。explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况可能是因为表中的数据不多，MySQL在语句优化阶段判断可以不走索引，直接全表扫描

6. key列 这一列显示mysql实际采用哪个索引来优化对该表的访问。 如果没有使用索引，则该列是 NULL。

7、key_len列

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。这个列也是常常用来推断select语句使用到了那些索引字段

比如一个table中的id是int类型，因为int是4字节。所以该字段就使用了id这个字段的索引。

key_len计算规则如下：

字符串 char(n)：3n字节长度 varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2

（如果字段允许为null,则key_len长度还需要+1）

数值类型 tinyint：1字节 smallint：2字节 int：4字节 bigint：8字节

时间类型 date：3字节 timestamp：4字节 datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL 一般来说最好不要有为空的字段。

8. ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常 量），字段名

9. rows列

这一列是mysql估计要读取并检测的行数，这里并不是最终的检索行数，而是一个估计数值

10. Extra列

这一列展示的是额外信息。常见的重要值如下：

Using index：使用覆盖索引

Using where：使用 where 语句来处理结果，查询的列未被索引覆盖

Using index condition：查询的列不完全被索引覆盖，where只是提供了一个范围

Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行 优化的，首先是想到用索引来优化

Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序，这种情况就是效率相当慢的

SQL语句的优化建议

1、查询时尽量使用全值匹配，使用索引作为条件时值要确定

2、最左前缀法则

单列索引要满足前面的字段如：select * from A where like ‘xx%’   这样like就不会全表扫描，如果是联合索引那么查询从索引的最左前列开始并且不跳过索引 中的列。意思就是如果有一张表A有一个联合索引（a,b,c）在使用联合索引查询时where中使用了a和c那么生效的只有a，c是不会生效的。

3、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转 向全表扫描

4、尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），并确定查询的字段。

索引失效的情况还有：

.is null,is not null 也无法使用索引，所以建表的时候不要有可以为空的列

.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

.字符串不加单引号索引失效

少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、 表大小等多个因素整体评估是否使用索引