深入理解mysql-第十一章 mysql查询优化-Explain 详解（中）

我就是我 2023-01-24 02:02 33阅读 0赞

# 一、执行计划-type属性 #

执行计划的一条记录就代表着`MySQL`对某个表的执行查询时的访问方法，其中的`type`列就表明了这个访问这个单表的方法具体是什么，比方说下边这个查询：

#  #

可以看到`type`列的值是`ref`，表明`MySQL`即将使用`ref`访问方法来执行对`s1`表的查询。我们针对使用`InnoDB`存储引擎的表进行单表访问的方法如下：

# 二、执行计划-possible\_keys和key属性 #

在`EXPLAIN`语句输出的执行计划中，`possible_keys`列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，`key`列表示实际用到的索引有哪些，比方说下边这个查询：

上述执行计划的`possible_keys`列的值是`idx_key1,idx_key3`，表示该查询可能使用到`idx_key1,idx_key3`两个索引，然后`key`列的值是`idx_key3`，表示经过查询优化器计算使用不同索引的成本后，最后决定使用`idx_key3`来执行查询比较划算。所以possible\_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

注意：有时候，使用**`index`**访问方法来查询某个表时，`possible_keys`列是空的，而`key`列展示的是实际使用到的索引。

# 三、执行计划-key\_len属性 #

`key_len`列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度。它是由这三个部分构成的：

*  对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，比如某个索引列的类型是`VARCHAR(100)`，使用的字符集是`utf8（每个字符三个字节）`，那么该列实际占用的最大存储空间就是`100 × 3 = 300`个字节。
 *  如果该索引列可以存储`NULL`值，则`key_len`比不可以存储`NULL`值时多1个字节。
 *  对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。

比如下边这个查询：

由于`id`列的类型是`INT`，并且不可以存储`NULL`值，所以在使用该列的索引时`key_len`大小就是`4`。当索引列可以存储`NULL`值时，`key_len`列就变成了`5`，比使用`id`列的索引时多了`1`。

对于可变长度的索引列来说，比如`key1`列的类型是`VARCHAR(100)`，所以该列实际最多占用的存储空间就是`300`字节，又因为该列允许存储`NULL`值，所以`key_len`需要加`1`，又因为该列是可变长度列，所以`key_len`需要加`2`，所以最后`ken_len`的值就是`303`。

`key_len`列主要是为了让我们区分某个使用联合索引的查询具体用了几个索引列，比如idx\_key\_part 这个联合索引如果只用到了联合索引前两个索引列，长度显示就是606。

# 四、执行计划-ref属性 #

当使用**索引列等值匹配**的条件去执行查询时，也就是在**访问方法(type)**是`const`、`ref`、`ref_or_null`其中之一时，`ref`列展示的就是与索引列作等值匹配的值得类型，比如只是一个常数或者是某个列。const代表常数，某一列名（数据库名称.s1.id）代表匹配的是一列，func代表匹配的是函数。

可以看到`ref`列的值是`const`，表明在使用`idx_key1`索引执行查询时，与`key1`列作等值匹配的对象是一个常数。

可以看到对被驱动表`s2`的访问方法是`eq_ref`，而对应的`ref`列的值是`mytest.s1.id`，这说明在对被驱动表进行访问时会用到`PRIMARY`索引，也就是聚簇索引与一个列进行等值匹配的条件，于`s2`表的`id`作等值匹配的对象就是`mytest.s1.id`列（注意这里把数据库名也写出来了）。

# 五、执行计划-rows属性 #

如果查询优化器决定使用**全表扫描**的方式对某个表执行查询时，执行计划的`rows`列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的`rows`列就代表预计扫描的索引记录行数。比如下边这个查询：

我们看到执行计划的`rows`列的值是`266`，这意味着查询优化器在经过分析使用`idx_key1`进行查询的成本之后，觉得满足`key1 > 'z'`这个条件的记录只有`266`条。

# 六、执行计划-filtered属性 #

之前在分析连接查询的成本时，知道了`MySQL`在计算驱动表扇出时采用的一个策略：

*  如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要估计出满足搜索条件的记录到底有多少条。
 *  如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

比方说下边这个查询：

从执行计划的`key`列中可以看出来，该查询使用`idx_key1`索引来执行查询，从`rows`列可以看出满足`key1 > 'z'`的记录有`266`条。执行计划的`filtered`列就代表查询优化器预测在这`266`条记录中，有多少条记录满足其余的搜索条件，也就是`common_field = 'a'`这个条件的百分比。此处`filtered`列的值是`10.00`，说明查询优化器预测在`266`条记录中有`10.00%`的记录满足`common_field = 'a'`这个条件。对于单表查询来说，这个`filtered`列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的`filtered`值。比如以上SQL内连接S2表，那么S1作为驱动表，这个10%就很有意义，就代表了要对s2表进行多少次查询。

从执行计划中可以看出来，查询优化器打算把`s1`当作驱动表，`s2`当作被驱动表。我们可以看到驱动表`s1`表的执行计划的`rows`列为`9688`， `filtered`列为`10.00`，这意味着驱动表`s1`的扇出值就是`9688 × 10.00% = 968.8`，这说明还要对被驱动表执行大约`968`次查询。

# 七、执行计划-Extra属性 #

`Extra`列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解`MySQL`到底将如何执行给定的查询语句。`MySQL`提供的额外信息有好几十个，我们举例一部分说明。

*  No tables used，当查询语句的没有`FROM`子句时将会提示该额外信息。
 *  Impossible WHERE，查询语句的`WHERE`子句永远为`FALSE`时将会提示该额外信息。
 *  Using index，当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下不需要进行回表操作，在`Extra`列将会提示该额外信息。
 *  Using index condition，有些搜索条件中虽然出现了索引列，但却不能使用到索引，比如左模糊查询。
 *  Using where，当我们使用全表扫描来执行对某个表的查询，并且该语句的`WHERE`子句中有针对该表的搜索条件。
 *  Using join buffer (Block Nested Loop)，在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，`MySQL`一般会为其分配一块名叫`join buffer`的内存块来加快查询速度。
 *  Using temporary，在许多查询的执行过程中，`MySQL`可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含`DISTINCT`、`GROUP BY`、`UNION`等子句的查询过程中，如果不能有效利用索引来完成查询，`MySQL`很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的`Extra`列将会显示`Using temporary`提示。