MySQL执行计划解读

前言

​ MySQL查询进行SQL查询，一般要求尽量使用到索引，否则会导致全表扫描。

问题1：全表扫描会产生什么问题？全表扫描实际上是直接扫描表的主键索引,依次读并发送。详细介绍，查看以下博客。MySQL-全表扫描 以及MySQL-全表扫描成本计算

​ 另外，简单查询是不会加锁的。例如select * from table;

问题2：怎么确定有没有使用到索引？explain关键字

Explain

​ 使用Explain关键字查询SQL执行情况，结果会有以下几个字段。分别是：id、select_type、table、type、possible_keys、key、key_len、rows、Extra。

select_type 查询类型

select_type查询类型	说明
SIMPLE	简单的 select 查询,不使用 union 及子查询
PRIMARY	最外层的 select 查询
SUBQUERY	在select或者where中包含了子查询，子查询中的第一个 select 查询,不依赖于外部查询的结果集
DEPENDENT SUBQUERY	子查询中的第一个 select 查询,依赖于外部 查询的结果集
DERIVED	用于 from 子句里有子查询的情况。 MySQL 会递归执行这些子查询, 把结果放在临时表里。
UNION	UNION 中的第二个或随后的 select 查询,不 依赖于外部查询的结果集
DEPENDENT UNION	UNION 中的第二个或随后的 select 查询,依 赖于外部查询的结果集
UNCACHEABLE UNION	UNION 中的第二个或随后的 select 查询,属 于不可缓存的子查询

简单来说就是：

SIMPLE：简单的 select 查询,不使用 union 及子查询
PRIMARY：也就是最后执行的语句
SUBQUERY：在select或者where中包含了子查询
DERIVED：临时表会增加MYSQL负担，但是有时候不得不用，类比：Java中两个变量交换数值
UNION：两个表的查询结果合并

type 对表的访问方式

• ALL 全表扫描

• index 索引全扫描

• range 索引范围扫描。索引范围扫描的结果可能会返回多条记录，例如：一般条件查询中出现了>、<、in、between等查询

• ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中

• eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询

• const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询

• null MySQL不访问任何表或索引，直接返回结果 虽然上至下，效率越来越高，但是根据cost模型，假设有两个索引idx1(a, b, c),idx2(a, c)，SQL为"select * from t where a = 1 and b in (1, 2) order by c";如果走idx1，那么是type为range，如果走idx2，那么type是ref；当需要扫描的行数，使用idx2大约是idx1的5倍以上时，会用idx1，否则会用idx2

possible_keys：表示查询时，可能使用的索引；

possible_keys有值，而key显示为null
这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

possible_keys无值
表示没有相关的索引，可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能。

key：表示实际使用的索引

key_len：索引字段的长度

ref：列与索引的比较

rows：扫描出的行数（估算的行数）

Extra：执行情况的描述和说明。

​ 这个字段里面常见有：Using where、Using index、Using index condition、Using filesort、Using temporary。

• Using where：表示优化器需要通过索引回表查询数据；

• Using index：表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表；

• Using index condition(5.6新特性)：会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；

• Using filesort：**表示需要进行排序操作，且无法使用索引进行排序。**MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。

• Using temporary：**表示使用了临时表来存储中间结果。**性能特别差，需要重点优化

• Using join buffer：表示使用了连接缓存区来存储连接结果。

• Impossible WHERE：表示查询条件不可能为真，通常由于使用了错误的函数或者运算符导致的。

示例：

SQL: select * from tbale where no = ‘1’ and no_name is null;

此数据为新增数据、索引字段为no。

示例：Using index condition; Using where

代表会先条件过滤索引，找到符合索引条件的数据后，再进行其他条件过滤。另外这次的结果是通过索引回表查询的结果。