MySQL执行计划 type的秘密

 

目录

执行计划 type 列的访问类型

MySQL 执行计划 Extra列 和 type列区别

示例1、type为 index、all、ref情况

SQL1、type：index，Extra：Using index

SQL2、type：ref，Extra：Using where；Using index

SQL3、type：all，Extra：Using where

SQL4、type：ref，Extra：Using where

示例1总结：Where条件运行方式

示例2、type为 const（常数）

创建表

SQL

执行计划

示例分析

---

执行计划 type 列的访问类型

访问类型从全表扫描到索引扫描（index）、范围扫描（range）、唯一索引查询、常数（const）引用等。

速度是从慢到快，扫描的行数也是从大到小。

MySQL 执行计划 Extra列 和 type列区别

很容易把 Extra 列的 “Using index”  和 type 列的 “index” 搞混淆。其实这两者完全不同，type 列和覆盖索引毫无关系；它只是表示这个查询访问数据的方式，或者说是 MySQL 查找行的方式。MySQL 手册中称之为连接方式（join type）。

示例1、type为 index、all、ref情况

接着上篇文章的示例：https://blog.youkuaiyun.com/ruanhao1203/article/details/98061034#%E5%BA%94%E7%94%A8%E8%A6%86%E7%9B%96%E7%B4%A2%E5%BC%95

例如创建表 fgsy 字段如下

覆盖索引如下

SQL1、type：index，Extra：Using index

如下图，只有select里面选择的字段在覆盖索引内，才可以使用覆盖索引。

SQL2、type：ref，Extra：Using where；Using index

SQL3、type：all，Extra：Using where

下面这种方式就不能运用覆盖索引，因为无法覆盖 所有列。

SQL4、type：ref，Extra：Using where

加上where条件的查询

示例1总结：Where条件运行方式

MySQL使用Where条件的三种方式，从好到坏依次为：

1. 在索引中使用 Where 条件 来过滤不匹配的记录。这是存储引擎层完成的。示例的SQL4 就是这种情况。

2. 使用索引覆盖扫描（在 Extra 列中出现了 Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL 服务器层完成的，但无须再回表查询记录。示例的SQL2 就是这种情况。

3. 从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现 Using Where）。这在 MySQL 服务器层完成，MySQL需要先从数据表都护记录然后过滤。示例的SQL3就是这种情况。

示例SQL1 最高效，因为用了覆盖索引，所以type为index，获取行数据直接从索引上获取的。

示例2、type为 const（常数）

创建表

t_left

数据

t_right

t_right索引

数据

SQL

SELECT a.name, b.`name` FROM t_left a 
INNER JOIN t_right b ON a.id = b.a_id
where a.id = 1

执行计划

示例分析

此SQL语句分两步执行查询，也就是上面执行计划的两行输出。第一步先从 a 表找到需要的行。因为 id字段上有主键索引，所以 MySQL 优化器知道这只会返回一行数据，优化器在生成执行计划的时候，就已经通过索引信息知道将会返回多少行数据。因为优化器已经明确了知道有多少个值需要索引查询，所以这里的行数据访问类型是 const。

在执行计划的第二步，MySQL将第一步返回的id列当作一个已知取值的列来处理。因为 a_id上也有索引，同上理解，也就是 const了。