optimized mysql_MySQL|MySQL执行计划

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。

explain执行计划包含的信息

每列的内容

列

含义

id

执行计划的id标志

select_type

select的类型

table

输出记录的表

partitions

匹配的分区

type

join的类型

possible_keys

优化器可能选择的索引

key

优化器实际选择的索引

key_len

使用索引的字节长度

ref

进行比较的索引列

rows

优化器预估的记录数量额外的显示选项

filtered

根据条件过滤得到的记录的百分比

extra

额外的显示选项

1、执行计划的 id

select 查询的序列号，标识执行的顺序id 相同，执行顺序由上至下

id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

2、执行计划的 select_type

查询的类型，主要是用于区分普通查询、联合查询、子查询等。SIMPLE：简单的 select 查询，查询中不包含子查询或者 union

PRIMARY：查询中包含子部分，最外层查询则被标记为 primary

UNION：表示 union 中的第二个或后面的 select 语句

DEPENDENT UNION：union 中的第二个或后面的 select 语句，依赖于外面的查询

UNION RESULT：union 的结果

SUBQUERY：子查询中的第一个 select

DEPENDENT SUBQUERY：子查询中的第一个 select，依赖于外面的查询

DERIVED：派生表的 select(from 子句的子查询)

MATERIALIZED：物化子查询

产生中间临时表(实体)

临时表自动创建索引并和其他表进行关联，提高性能

和子查询的区别是，优化器将可以进行 MATERIALIZED 的语句自动改写成 join，并自动创建索引

UNCACHEABLE SUBQUERY：不会被缓存的并且对于外部查询的每行都要重新计算的子查询

UNCACHEABLE UNION：属于不能被缓存的 union 中的第二个或后面的 select 语句

3、执行计划的 table

查询涉及到的表。通常就是用户操作的用户表

：由 ID 等于 M，N 的语句 union 得到的结果表

：派生表，由 ID 等于 N 的语句查询得到的结果表

：由子查询物化产生的表，由 ID 等于 N 的语句查询得到的结果表

4、执行计划的 type

访问类型，SQL 查询优化中一个很重要的指标，结果值从好到坏依次是：system > const > eq_ref > ref > range > index > ALL。system：系统表，少量数据，往往不需要进行磁盘IO

const：常量连接

eq_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描

ref：非主键非唯一索引等值扫描

range：范围扫描

index：索引树扫描

ALL：全表扫描(full table scan)

const:

数据准备： CREATE TABLE user( id int(11) NOT NULL, NAME varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user values(1,'shenjian'); insert into user values(2,'zhangsan'); insert into user values(3,'lisi'); 然后执行: explain select * from user where id=1;

const 扫描的条件为：命中主键(primary key)或者唯一(unique)索引

被连接的部分是一个常量(const)值

如上例，id 是 主键索引，连接部分是常量1。

eq_ref

数据准备: CREATE TABLE user( id int(11) NOT NULL, NAME varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user values(1,'shenjian'); insert into user values(2,'zhangsan'); insert into user values(3,'lisi'); CREATE TABLE user_ex ( id int(11) NOT NULL, age int(11) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user_ex values(1,18); insert into user_ex values(2,20); insert into user_ex values(3,30); insert into user_ex values(4,40); insert into user_ex values(5,50); 然后执行: explain select * from user,user_ex where user.id=user_ex.id;

eq_ref 扫描的条件为，对于前表的每一行(row)，后表只有一行被扫描。

再细化一点：join 查询

命中主键(primary key)或者非空唯一(unique not null)索引

等值连接；

如上例，id 是主键，该 join 查询为 eq_ref 扫描。

ref

数据准备: CREATE TABLE user ( id int(11) DEFAULT NULL, name varchar(20) DEFAULT NULL, KEY id (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user values(1,'shenjian'); insert into user values(2,'zhangsan'); insert into user values(3,'lisi'); CREATE TABLE user_ex ( id int(11) DEFAULT NULL, age int(11) DEFAULT NULL, KEY id (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user_ex values(1,18); insert into user_ex values(2,20); insert into user_ex values(3,30); insert into user_ex values(4,40); insert into user_ex values(5,50); 然后执行: explain select * from user,user_ex where user.id=user_ex.id;

如果把上例 eq_ref 案例中的主键索引，改为普通非唯一(non unique)索引。就由 eq_ref 降级为了 ref，此时对于前表的每一行(row)，后表可能有多于一行的数据被扫描。

select * from user where id=1;

当 id 改为普通非唯一索引后，常量的连接查询，也由 const 降级为了 ref，因为也可能有多于一行的数据被扫描。

ref 扫描，可能出现在 join 里，也可能出现在单表普通索引里，每一次匹配可能有多行数据返回，虽然它比 eq_ref 要慢，但它仍然是一个很快的 join 类型。

range

数据准备: CREATE TABLE user ( id int(11) DEFAULT NULL, name varchar(20) DEFAULT NULL, KEY id (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user values(1,'shenjian'),(2,'zhangsan'),(3,'lisi'),(4,'wangwu'),(5,'zhaoliu'); 然后执行: explain select from user where id between 1 and 4; explain select from user where id in(1,2,3); explain select * from user where id > 3;

ange 扫描就比较好理解了，它是索引上的范围查询，它会在索引上扫码特定范围内的值。

像上例中的 between，in，> 都是典型的范围(range)查询。

index

explain select count(*) from user;

如上例，id 是主键，该 count 查询需要通过扫描索引上的全部数据来计数，它仅比全表扫描快一点。

ALL

数据准备: CREATE TABLE user ( id int(11) DEFAULT NULL, name varchar(20) DEFAULT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user values(1,'shenjian'); insert into user values(2,'zhangsan'); insert into user values(3,'lisi'); CREATE TABLE user_ex ( id int(11) DEFAULT NULL, age int(11) DEFAULT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user_ex values(1,18); insert into user_ex values(2,20); insert into user_ex values(3,30); insert into user_ex values(4,40); insert into user_ex values(5,50); 然后执行: explain select * from user,user_ex where user.id=user_ex.id;

如果 id 上不建索引，对于前表的每一行(row)，后表都要被全表扫描。

文章中，这个相同的 join 语句出现了三次：扫描类型为 eq_ref，此时 id 为主键

扫描类型为 ref，此时 id 为非唯一普通索引

扫描类型为 ALL，全表扫描，此时id上无索引

总结explain 结果中的 type 字段，表示(广义)连接类型，它描述了找到所需数据使用的扫描方式；

常见的扫描类型有：system>const>eq_ref>ref>range>index>ALL，其扫描速度由快到慢；

各类扫描类型的要点是：

system 最快：不进行磁盘 IO

const：PK 或者 unique 上的等值查询

eq_ref：PK 或者 unique 上的 join 查询，等值匹配，对于前表的每一行，后表只有一行命中

ref：非唯一索引，等值匹配，可能有多行命中

range：索引上的范围扫描，例如：between、in、>

index：索引上的全集扫描，例如：InnoDB 的 count

ALL 最慢：全表扫描

建立正确的索引，非常重要；

使用 explain 了解并优化执行计划，非常重要；

5、执行计划 possible_keys

查询过程中有可能用到的索引。

6、执行计划 key

实际使用的索引，如果为 NULL ，则没有使用索引。

7、执行计划 rows

根据表统计信息或者索引选用情况，大致估算出找到所需的记录所需要读取的行数。

8、执行计划 filtered

表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

9、执行计划 Extra

十分重要的额外信息。Using filesort：MySQL 对数据使用一个外部的文件内容进行了排序，而不是按照表内的索引进行排序读取。

Using index：表示 SQL 操作中使用了覆盖索引(Covering Index)，避免了访问表的数据行，效率高。

Using index condition：表示 SQL 操作命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。

Using index for group by：优化器只需要使用索引就能处理 group by 或 distinct 语句。

Using join buffer (Block Nested Loop)：表示 SQL 操作使用了关联查询或者子查询，且需要进行嵌套循环计算。

Using MRR：优化器使用 MRR 优化

Using temporary：使用临时表保存中间结果，也就是说 MySQL 在对查询结果排序时使用了临时表，常见于order by 或 group by。

Using where：表示 SQL 操作使用了 where 过滤条件。

Select tables optimized away：基于索引优化 MIN/MAX 操作或者 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化。

数据准备: create table user( id int(11) not null, name varchar(20) default null, sex varchar(5) default null, primary key (id), key name (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 用户表：id 主键索引，name 普通索引(非唯一)，sex 无索引。 四行记录：其中 name 普通索引存在重复记录 lisi。

Using filesort

执行: explain select * from user order by sex;

Extra 为 Using filesort 说明，得到所需结果集，需要对所有记录进行文件排序。

这类 SQL 语句性能极差，需要进行优化。

典型的，在一个没有建立索引的列上进行了 order by，就会触发 filesort，常见的优化方案是，在 order by 的列上添加索引，避免每次查询都全量排序。

Using temporary

执行： explain select * from user group by name order by sex;

(备注：一开始执行时报错 ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.user.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 原因是 ： 错误1055(42000)：选择列表的表达式1不在GROUP BY子句中，并且包含未聚合的列“test.fruits.f_id”，它在功能上不依赖GROUP BY子句中的列；这与SQL_mode=only_full_group_by不兼容) 解决办法： 在mysql中输入 mysql> set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; ) 重新查询就可以了

Extra 为 Using temporary 说明，需要建立临时表(temporary table)来暂存中间结果。

这类 SQL 语句性能较低，往往也需要进行优化。

典型的 group by 和 order by 同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

临时表存在两种引擎，一种是 Memory 引擎，一种是 MyISAM 引擎，如果返回的数据在 16M 以内(默认)，且没有大字段的情况下，使用 Memory 引擎，否则使用 MyISAM 引擎。

Using index

执行: explain select id from user;

Extra 为 Using index 说明，SQL 所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录。

这类 SQL 语句往往性能较好。

Using index condition

执行: explain select id, name, sex from user where name='shenjian';

Extra 为 Using index condition 说明，确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。

这类 SQL 语句性能也较高，但不如 Using index。

Using where

explain select * from user where sex='no';

Extra 为 Using where 说明，查询的结果集使用了 where 过滤条件，比如上面的 SQL 使用了 sex = 'no' 的过滤条件

Select tables optimized away

explain select max(id) from user;

比如上面的语句查询 id 的最大值，因为 id 是主键索引，根据 B+Tree 的结构，天然就是有序存放的，所以不需要等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化。

Using join buffer (Block Nested Loop)

explain select * from user where id in (select id from user where sex='no');

Extra 为 Using join buffer (Block Nested Loop) 说明，需要进行嵌套循环计算。内层和外层的 type 均为 ALL，rows 均为4，需要循环进行4*4次计算。

这类 SQL 语句性能往往也较低，需要进行优化。

典型的两个关联表 join，关联字段均未建立索引，就会出现这种情况。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。