MySQL执行计划总结

时间：2021-08-04 12:47:41

1. 背景

在工作过程中，有时候会对慢查询进行调优。对于MySQL的SQL语句调优，MySQL本身提供了强大的explain关键字用于查询分析执行计划。

本文对explain执行计划进行分析与整理,文中的内容在未特别注明情况下，以MySQL5.7版本为例。

2. 简介

语法：从语法角度explain和describe/desc是相同的，只是一般更常用desc看表结构，explain来看查询计划。

一个标准的explain出来的结果包含这些字段

MySQL执行计划总结

  • id 表示SELECT的标识符。一般来说值越大代表执行优先级越高，如果相同，则上面的结果比下面的结果优先级高。

  • select_type 查询类型

  • table 表示表名，未必是真实存在的表，可能是衍生出来的表。

  • type 表示连接类型

  • possible_keys 表示MySQL可能用于查找行的索引，如果为NULL，通常需要考虑优化查询语句/表索引

  • key 与possible_keys不同，key输出的是查询中实际会使用到的索引。key中标注的索引没有出现在上面的possible_keys也是有可能的，通常是因为有辅助索引的字段覆盖了查询字段，这样的话MySQL会使用索引覆盖，效率会更高。

  • key_len 表示使用索引的字节长度，如果上述key输出的是NULL, key_len也会输出NULL。可以根据key_len的值来推算多重索引实际使用了几个前缀索引列。注意，对于可以为NULL的列，存储长度会大1。

  • ref 表示与索引一起进行查询的列/常数。

  • rows 表示MySQL在查询时必须检查的行数，但是对于InnoDB表，这个值是预估的，未必精确。

  • filtered 表示在执行查询时根据条件筛选行数占比，这也是一个估计值。

  • extra 表示执行计划的一些扩展信息。

3. explain字段解析

下面是以实例来逐一介绍explain中重要字段常见输出类型的含义与来源。

注:下文中的示例在未特别注明情况下是以MySQL5.7为例，并且优化器的开关选项为

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on

3.1 select_type

select_type是用来表示select语句的类型

它可能的值如下表所示

MySQL执行计划总结

注意：下面出现的一些SQL是为了本文编写的示例，而不是在实际生产中常见的SQL。

SIMPLE

表示简单查询，也即不包括连接查询/子查询。

mysql> create table t(a int);

Query OK, 0 rows affected (0.23 sec)

mysql> explain select a from t\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: NULL

1 row in set, 1 warning (0.00 sec)

PRIMARY

表示最外层的查询。

mysql> create table t(a int);

Query OK, 0 rows affected (0.23 sec)

mysql> explain select a from t where a = (select max(a) from t)\G

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: t

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: Using where

*************************** 2. row ***************************

           id: 2

  select_type: SUBQUERY

        table: t

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: NULL

2 rows in set, 1 warning (0.00 sec)

UNION

用于表示union查询中第二条及之后的查询

mysql> create table t(a int);

Query OK, 0 rows affected (0.23 sec)

mysql> explain select * from t union all select * from t union all select * from t\G

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: t

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: NULL

*************************** 2. row ***************************

           id: 2

  select_type: UNION

        table: t

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: NULL

*************************** 3. row ***************************

           id: 3

  select_type: UNION

        table: t

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: NULL

3 rows in set, 1 warning (0.00 sec)

DEPENDENT UNION

与UNION不同，这种类型表示依赖于外层查询。

mysql> create table t(a int);

Query OK, 0 rows affected (0.23 sec)

mysql> explain select * from t where a in (select * from t union all select * from t)\G

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: t

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: Using where

*************************** 2. row ***************************

           id: 2

  select_type: DEPENDENT SUBQUERY

        table: t

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: Using where

*************************** 3. row ***************************

           id: 3

  select_type: DEPENDENT UNION

        table: t

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: Using where

3 rows in set, 1 warning (0.01 sec)

这里有必要解释一下为什么可以通过上面的语句构造出DEPENDENT UNION。这看上去并不依赖外部的UNION子查询怎么会成了DEPENDENT UNION呢？因为MySQL会把这里的in转写为exist，可以通过查看警告信息来看看MySQL优化器对语句进行转写后的样子。

mysql> show warnings\G

*************************** 1. row ***************************

  Level: Note

   Code: 1003

Message: /* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t` where <in_optimizer>(`test`.`t`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t` where (<cache>(`test`.`t`.`a`) = `test`.`t`.`a`) union all /* select#3 */ select 1 from `test`.`t` where (<cache>(`test`.`t`.`a`) = `test`.`t`.`a`)))

1 row in set (0.00 sec)

可以看到原来的in被转写为exist，内层的union语句需要外层扫描到的a字段。

UNION RESULT

表示UNION的结果

mysql> create table t(a int);

Query OK, 0 rows affected (0.23 sec)

mysql> explain select * from t union select * from s\G

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: t

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: NULL

*************************** 2. row ***************************

           id: 2

  select_type: UNION

        table: s

   partitions: NULL

         type: index

possible_keys: NULL

          key: PRIMARY

      key_len: 4

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: Using index

*************************** 3. row ***************************

           id: NULL

  select_type: UNION RESULT

        table: <union1,2>

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: NULL

     filtered: NULL

        Extra: Using temporary

3 rows in set, 1 warning (0.00 sec)

SUBQUERY

子查询中的第一个select语句，并且不依赖于外部查询。

mysql> create table t(a int);

Query OK, 0 rows affected (0.23 sec)

mysql> explain select a from t where a = (select a from t)\G

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: t

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: Using where

*************************** 2. row ***************************

           id: 2

  select_type: SUBQUERY

        table: t

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: NULL

2 rows in set, 1 warning (0.00 sec)

DEPENDENT SUBQUERY

依赖子查询，与SUBQUERY的区别就在于这种类型是依赖于外层查询。因此我们可以很容易地构造出DEPENDENT SUBQUERY。

mysql> create table t(a int);

Query OK, 0 rows affected (0.21 sec)

mysql> create table s(b int);

Query OK, 0 rows affected (0.23 sec)

mysql> explain select a from t where a = (select * from s where b = a)\G

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: t

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: Using where

*************************** 2. row ***************************

           id: 2

  select_type: DEPENDENT SUBQUERY

        table: s

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: Using where

2 rows in set, 2 warnings (0.00 sec)

DERIVED

表示一个派生表（可以简单理解为非物理表。派生表意味着在查询过程中会在内存或磁盘上创建临时表，临时表是不具有索引的，因此临时表在与其他表关联时性能会比较差。在MySQL较低版本(5.5以下)中，比较容易构造出一个派生表查询，如下所示。但是在MySQL5.6之后版本，根据optimizer_switch中参数derived_merge是否为开启，from子句中的子查询可以与外部查询综合起来优化。因此下面的简单示例在MySQL5.7是无效的（除非关闭derived_merge优化选项)。

mysql> create table t(a int);

Query OK, 0 rows affected (0.21 sec)

mysql> explain select * from (select * from t) q\G

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: <derived2>

         type: system

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 0

        Extra: const row not found

*************************** 2. row ***************************

           id: 2

  select_type: DERIVED

        table: t

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

        Extra:

2 rows in set (0.04 sec)

MATERIALIZED

这是MySQL5.6开始引入的一种新的select_type，主要是优化from/in子句中的子查询。关于这个东西的中文说法常见有“物化“或者“具体化”两种翻译。

关于MATERIALIZATION，在官方doc上有更详细的说明

mysql> create table t(a int);

Query OK, 0 rows affected (0.22 sec)

mysql> create table s(b int);

Query OK, 0 rows affected (0.22 sec)

mysql> insert into t select null;

Query OK, 1 row affected (0.05 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t select null;

Query OK, 1 row affected (0.05 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> explain select * from t where a in (select * from s)\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: <subquery2>

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: NULL

     filtered: 100.00

        Extra: NULL

*************************** 2. row ***************************

           id: 1

  select_type: SIMPLE

        table: t

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 2

     filtered: 50.00

        Extra: Using where; Using join buffer (Block Nested Loop)

*************************** 3. row ***************************

           id: 2

  select_type: MATERIALIZED

        table: s

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: NULL

3 rows in set, 1 warning (0.00 sec)

UNCACHEABLE SUBQUERY

顾名思义，便是无法缓存的SUBQUERY。通常来说,SUBQUERY是只会被执行一次的，后续相同的SUBQUERY会使用第一次执行后的缓存结果。但是在某些情况下，SUBQUERY会出现无法缓存，而需要每次重复执行的情况。通常是由于子查询中带有一些用户变量/随机函数(UUID/RAND)等。就拿前文示例中的SUBQUERY稍作修改

mysql> create table t(a int);

Query OK, 0 rows affected (0.23 sec)

set @tmp=1;

Query OK, 0 rows affected (0.00 sec)

mysql> explain select a from t where a = (select a from t where a = @tmp)\G

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: t

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: Using where

*************************** 2. row ***************************

           id: 2

  select_type: UNCACHEABLE SUBQUERY

        table: t

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: Using where

2 rows in set, 1 warning (0.00 sec)

UNCACHEABLE UNION

与上文的UNCACHEABLE SUBQUERY类似，不作冗述。

至此,查询计划的select_type介绍完毕

3.2 table

table列内容比较简单。一般展示的情况大致有以下几种

  • NULL 比如select一些与数据库表无关的内容，如select now()
  • <unionM,N> 表示由UNION操作产生的临时表，其中M和N表示产生临时表的源表
  • <derivedM> 表示是由id为M的表派生而来的临时表
  • <subqueryM> 表示是由id为M的子查询物化而来的临时表

我们根据上文介绍的id, select_type, table列已经足以大致分析复杂查询中的执行顺序。

下面要介绍的是另一个非常重要的字段type

3.3 type

在MySQL官方doc的type小节，type是被描述为join type连接类型的。正如《高性能MySQL》中6.4也提及了，MySQL赋予了join一词比较丰富的含义，而不仅仅是我们通常脑海中浮现的SQL Join。每一次查询都是一个join，所以对于所谓的“join type”连接类型，我们不妨理解为获取数据的方式。

下面先大致看一下type列可能会有哪些不同的取值。

  • system
  • const
  • eq_ref
  • ref
  • fulltext
  • ref_or_null
  • index_merge
  • unique_subquery
  • index_subquery
  • range
  • index
  • all

一共是12种方式，除了全表扫描不使用索引外，其余11种都使用了索引。其实这么说也是不严谨的，对于InnoDB存储引擎，它是索引组织表的，所有的记录都存放在聚集索引中，即使扫表，也可以说是使用了索引。下面逐一进行介绍，并附以示例。

system

这是一种比较特殊的连接类型，官方文档上似乎没特别注明，但实际上，这种类型只出现在MyISAM/Memory存储引擎,InnoDB并不存在这种连接类型。

mysql> create table t (a int primary key) engine myisam;

Query OK, 0 rows affected (0.06 sec)

mysql> insert into t select 1;

Query OK, 1 row affected (0.02 sec)

Records: 1  Duplicates: 0  Warnings: 0

ysql> explain select * from t\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t

   partitions: NULL

         type: system

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: NULL

1 row in set, 1 warning (0.00 sec)

mysql> alter table t engine innodb;

Query OK, 1 row affected (0.34 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> explain select * from t\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t

   partitions: NULL

         type: index

possible_keys: NULL

          key: PRIMARY

      key_len: 4

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: Using index

1 row in set, 1 warning (0.01 sec)

const

const表示在数据表中最多只能找到一条匹配行，它会再查询刚开始的时候被读取，之后会被优化器当作常量。const通常会出现在用常量来比较主键或者唯一索引的所有列的时候。

mysql> create table t (a int, b int, c varchar(15), primary key(a, b));

Query OK, 0 rows affected (0.22 sec)

mysql> insert into t select 1,2,'hello world';

Query OK, 1 row affected (0.04 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> explain select * from t where a = 1 and b =2\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t

   partitions: NULL

         type: const

possible_keys: PRIMARY

          key: PRIMARY

      key_len: 8

          ref: const,const

         rows: 1

     filtered: 100.00

        Extra: NULL

1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G

*************************** 1. row ***************************

  Level: Note

   Code: 1003

Message: /* select#1 */ select '1' AS `a`,'2' AS `b`,'hello world' AS `c` from `test`.`t` where 1

1 row in set (0.00 sec)

此时，如果我们查看一下warning信息，可以发现优化器居然直接将查询给展开了。

eq_ref

eq_ref是一种在多表连接查询中可能会出现的连接方式。它会出现在连表查询中检索第二个或更后面的表时使用的条件为这个表的主键或者唯一非空索引的情况。

mysql> create table t (a int, b int);

Query OK, 0 rows affected (0.22 sec)

mysql> create table s (c int not null, d int not null, unique index(c,d));

Query OK, 0 rows affected (0.23 sec)

mysql> explain select * from t,s where t.a=s.c and t.b=s.d\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: Using where

*************************** 2. row ***************************

           id: 1

  select_type: SIMPLE

        table: s

   partitions: NULL

         type: eq_ref

possible_keys: c

          key: c

      key_len: 8

          ref: test.t.a,test.t.b

         rows: 1

     filtered: 100.00

        Extra: Using index

2 rows in set, 1 warning (0.00 sec)

通过此查询计划的输出，MySQL内部此查询的执行方式为以t表为驱动表，使用 t.a=s.c and t.b=s.d来检索s表，s表的连接方式为eq_ref。因为满足唯一索引的所有列都有被使用，且此唯一索引所有列都非null。

ref

可以说ref是一种退化的eq_ref。ref的出现表征着检索条件不能确定至多一条记录。

mysql> create table t (a int, key(a));

Query OK, 0 rows affected (0.24 sec)

mysql> explain select * from t where a = 3\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t

   partitions: NULL

         type: ref

possible_keys: a

          key: a

      key_len: 5

          ref: const

         rows: 1

     filtered: 100.00

        Extra: Using index

1 row in set, 1 warning (0.00 sec)

以上介绍的四种连接方式(system/const/eq_ref/ref)都属于性能比较高的连接方式，它们的where子句必须是等值比较运算符(=或者<=>)。

fulltext

在MySQL5.5及以下版本只有MyISAM存储引擎支持全文索引。这个连接类型说明的事情很简单，查询使用了全文索引，仅此而已。

ref_or_null

ref_or_null连接类型和上文提及的ref差不多，只是顾名思义，有个or null的选项。

mysql> create table t (a int, key(a));

Query OK, 0 rows affected (0.25 sec)

mysql> explain select * from t where a = 3 or a is null\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t

   partitions: NULL

         type: ref_or_null

possible_keys: a

          key: a

      key_len: 5

          ref: const

         rows: 2

     filtered: 100.00

        Extra: Using where; Using index

1 row in set, 1 warning (0.01 sec)

实际情况，几乎遇不到这种类型，因为通常大部分公司的MySQL规范会强制要求表中的字段为not null。

index_merge

关于索引合并，《高性能MySQL》的6.5.3节只是简单地一笔带过。

所谓索引合并就是对于查询条件比较复杂的情况，MySQL会将条件拆分，使用不同的索引，再将结果进行交、并、去重等操作。

mysql> create table t(a int,b int,key(a),key(b));

Query OK, 0 rows affected (0.03 sec)

mysql> insert into t select 1,2;

Query OK, 1 row affected (0.05 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t select * from t;

Query OK, 1 row affected (0.03 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t select * from t;

Query OK, 2 rows affected (0.03 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t select * from t;

Query OK, 4 rows affected (0.03 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t select * from t;

Query OK, 8 rows affected (0.03 sec)

Records: 8  Duplicates: 0  Warnings: 0

mysql> insert into t select * from t;

Query OK, 16 rows affected (0.04 sec)

Records: 16  Duplicates: 0  Warnings: 0

mysql> insert into t select * from t;

Query OK, 32 rows affected (0.03 sec)

Records: 32  Duplicates: 0  Warnings: 0

mysql> insert into t select * from t;

Query OK, 64 rows affected (0.05 sec)

Records: 64  Duplicates: 0  Warnings: 0

mysql> explain select * from t  where a<1 or b>3\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t

         type: index_merge

possible_keys: a,b

          key: a,b

      key_len: 5,5

          ref: NULL

         rows: 2

        Extra: Using sort_union(a,b); Using where

1 row in set (0.03 sec)

在上例中，在建表后，需要人为地再插入一些数据以避免在小数据量的情况下，MySQL直接扫表方式来完成查询。在实际应用中，即便优化器选项中开启index_merge，也未必会使用到，如果确信索引合并效率比较高的话，可以用index hint来指引MySQL使用index_merge。

索引合并的不足在于：

  1. 它需要读取多个索引，这就增加了磁盘的I/O，效率不如读取单个索引。
  2. 对于复杂的AND/OR，很多时候没办法正确优化，或者即便可以用索引合并，MySQL在执行的时候也未必会用
  3. 索引合并需要对部分结果进行交、并、去重，这本身也是有一定开销的。

unique_subquery

与eq_ref不同的地方在于,当至多只会有一条结果的select出现在where中的in条件时，执行计划的type会显示为unique_subquery。在高版本MySQL(5.6或以上)由于对in子查询有了很多优化，比较难看见这种类型。了解即可。

index_subquery

与unique_subquery不同点在于：unique_subquery保证了in列表中的值不会是重复的。而index_subquery有点类似于ref，它会利用索引对in子句中的查询结果去重。

range

这是一种很常见的连接方式,对于where条件中出现索引列 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN与常数进行比较的运算，都有可能会出现range连接方式。在这种情况下ref列会输出NULL。

mysql> create table t ( a int,b int, key(a));

Query OK, 0 rows affected (0.26 sec)

mysql> explain select * from t where a >1\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t

   partitions: NULL

         type: range

possible_keys: a

          key: a

      key_len: 5

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: Using index condition

1 row in set, 1 warning (0.00 sec)

index

index很容易给人造成误解，认为是一种很高效的查询。实际上index可以认为是用索引树来扫表而已。它和扫表要读的记录数是一样的，只是因为（二级）索引树只含有索引列，比数据文件（对于InnoDB来说是聚集索引）要小很多，所以会比all效率要高一些。

通常在如下两种情况下可能会出现index连接方式

  • 某个索引的列覆盖了查询条件，即可以使用索引覆盖避免访问聚集索引（InnoDB），这时extra列会显示"using index"。
  • 可以利用索引的顺序来遍历记录，这种情况下extra列不会显示"using index"。
mysql> create table t (a int, b int, key(a));

Query OK, 0 rows affected (0.26 sec)

mysql> explain select a from t order by a desc\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t

   partitions: NULL

         type: index

possible_keys: NULL

          key: a

      key_len: 5

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: Using index

1 row in set, 1 warning (0.01 sec)

mysql> explain select * from t order by a desc\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: Using filesort

1 row in set, 1 warning (0.00 sec)

这个例子展示了，当我们需要按a顺序查询t表中的a字段时，MySQL会扫描索引。但是当我们的查询为*时，这时由于即便访问索引也需要再次去数据文件（InnoDB来说就是聚集索引）中读取相应的行，MySQL为了减少这样的I/O索性直接用聚集索引扫表，在内存中排好序输出。

all

这就是让人望而生畏的所谓的“扫表”了。扫表出现的原因通常是由于既有索引不能适应查询SQL，也包括如上面index中提及的为了减少I/O，MySQL可能读聚集索引扫表再进行排序。对于OLTP应用，扫表还是尽量要避免的，尽可能保证type是range及以上为好。这里就不列出扫表的示例了。

3.4 possible_keys

这只是列出MySQL在选择执行计划时的索引候选。对于SQL调优，此列信息作用不是太大。

3.5 key

key列展示的是在执行查询时实际会使用的索引，这个列输出的信息非常重要。它的输出可能是NULL/PRIMARY/或者自定索引名。在非索引合并的情况下，这个列输出的信息只会至多包含一个索引，对于索引合并，则会是索引合并用到的索引。

3.6 key_len

key_len展示了key中对应项在实际使用时用到了几个字节。这同样是一个很重要的信息。例如对于使用多重索引的情况，可以根据key_len来推断查询SQL使用多重索引几个前缀索引列。值得注意的是MySQL对于可为NULL的字段需要额外的一个字节存储，因此key_len在这种情况下会比列类型本身的值多1。

3.7 ref

ref展示的用于与key中显示的索引进行比较的常数/列。

如果是常数的话ref列会显示const。对于这个列比较需要关注的是出现func的情况，这表示与索引进行比较的项是经过某种函数运算的。

3.8 rows

如前文已经提及，rows表示MySQL在查询时必须检查的行数，但是对于InnoDB表，这个值是预估的，未必精确。

3.9 extra

extra是一个非常具有参考价值的列。官方doc也有专门的小节来讲解extra列中的值。

经验有限，在实际工作中也不是所有的都遇到过，下面记录一些遇到过的extra信息，之后随着工作遇到新的继续补充。

Using filesort

这是在SQL调优实战中很容易发现的猎物，出现这种情况，通常是由于查询SQL的order by没有合适的索引可以用。虽然名字是叫filesort然而实际上未必是在文件中排序，可能是内存中也可能是在磁盘中(取决于排序缓冲区的大小）。对于出现Using filesort通常需要考虑优化掉不必要的order by或者添加索引。但是在实际情况中，也可能即使order by的列有对应索引，仍然会出现filesort。举个例子来说对于select * from t where ... order by col。如果where条件中的筛选性比较低，MySQL是有可能为了避免读取二级索引和聚集索引造成的I/O开销，而倾向于只使用聚集索引顺序访问过滤where条件中的记录，然后再进行filesort的。在实际工作中，曾经发现公司项目组的分页框架就有类似的问题，对于分页框架，往往需要查出原SQL可以在数据库表中能筛选出来的总记录数，比较简单的方式就是在原SQL外面套上一个select count(0) from,将原来的SQL包为子查询。这样的话由于原SQL往往带着order by语句，很可能会出现为了求一个total count而出现扫表+排序的情况。解决方式可以是在分页拦截器中修改原SQL，将原来的语句最外层改写为select count(0)形式。这样MySQL容易优化掉不必要的order by。

Using temporary

与filesort一样，这也是非常值得关注的信息。这代表查询中MySQL创建了临时表，仅仅靠explain，通常无法断定临时表是在内存中创建的还是在磁盘中创建的。官方文档上提及常见的情况是group by与order by用了不同的索引。但是实际上并不是说extra里没显示Using temporary就代表执行过程中没有创建临时表。

以下情况都是通常会创建临时表的情况。

  • from语句带了子查询，MySQL把这个叫做派生derived，实际上也是临时表
  • count(distinct col)并且无法使用索引时，会创建临时表。
  • union/union all会用临时表来合并结果。
  • 无法使用索引的排序

Using where

在《高性能MySQL》的6.2.2有提及对于用户编写的带有where语句的SQL，MySQL有三种方式处理，从好到坏依次是。在存储引擎层用索引来过滤where条件中不匹配的记录;在MySQL服务器层用索引覆盖(extra列会出现Using index)返回记录;从数据表中返回数据后，过滤where中不匹配的条件(extra会出现Using where)这也是MySQL服务器层完成的。

Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)

关于连接的这两种方式，可以参考官方文档中的描述以及算法原型

对于多表连接查询，如果被驱动表（下一个待join的表）在连接条件上没有高效的索引(连接类型为all/index/range)的话，通常会使用BNL算法来进行表之间的join。Batched Key Access是MySQL5.6开始出现的一种连接算法。对于出现连接缓冲的extra信息，可以检查下MySQL选择的连接顺序，以及被连接表上的索引情况。一般来说优化器都足以选择最优的连接顺序，如果需要人为指定的话，尽量遵循以下几点

  • 临时表和普通表join 这种情况用临时表作驱动表
  • 临时表和临时表join 用小表作驱动表
  • 普通表和普通表join 看索引和表大小，都有索引或者都没索引，小表作驱动表。其余情况的话尽量保证被驱动表上连接字段有索引。

Impossible WHERE noticed after reading const tables

回顾一下上面提及过的system/const连接方式，对于查询中的where条件如果是可以唯一确定至多一条记录的话，MySQL是可以在查询一开始就读取记录并将结果看作常量。因此如下的情况，MySQL是可以非常迅速推断出结果不存在的。

mysql> create table t (a int primary key, b int);

Query OK, 0 rows affected (0.28 sec)

mysql> insert into t select 1,2;

Query OK, 1 row affected (0.05 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t select 2,3;

Query OK, 1 row affected (0.04 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t select 3,4;

Query OK, 1 row affected (0.05 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> explain select * from t where a=1 and b=3\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: NULL

   partitions: NULL

         type: NULL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: NULL

     filtered: NULL

        Extra: Impossible WHERE noticed after reading const tables

1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G

*************************** 1. row ***************************

  Level: Note

   Code: 1003

Message: /* select#1 */ select '1' AS `a`,'2' AS `b` from `test`.`t` where 0

1 row in set (0.00 sec)

Const row not found

对于SQL调优实战，比较少遇到。出现这种情况，排查一下筛选条件值是否错了，或者表数据是否少了即可。

No tables used

对于select 1 from dual或者其他不带表的查询，extra信息中会显示此列。

相关文章