MySQL|MySQL执行计划

MySQL|MySQL执行计划

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

explain执行计划包含的信息

1.jpg

每列的内容

含义

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

查询涉及到的表。

通常就是用户操作的用户表 <unionM,N>:由 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;

2.jpg

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;

3.jpg

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;

4 .jpg

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

select * from user where id=1;

5.jpg

当 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;

6.png

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

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

index

explain select count(*) from user;

7.jpg

如上例,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;

8.jpg

如果 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;

9.jpg

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

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

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

Using temporary

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

10.jpg

(备注:一开始执行时报错 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;

11.png

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';

13.jpg

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

Select tables optimized away

explain select max(id) from user;

14.jpg

比如上面的语句查询 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,关联字段均未建立索引,就会出现这种情况。常见的优化方案是,在关联字段上添加索引,避免每次嵌套循环计算。

更多技术信息可查看云掣官网https://www.dtstack.com/dtsmart/