关于运维:MySQLMySQL执行计划

应用 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

查问波及到的表。

• 通常就是用户操作的用户表
• <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;

const 扫描的条件为：

1. 命中主键（primary key）或者惟一（unique）索引
2. 被连贯的局部是一个常量（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），后表只有一行被扫描。

再细化一点：

1. join 查问
2. 命中主键（primary key）或者非空惟一（unique not null）索引
3. 等值连贯；

如上例，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 语句呈现了三次：

1. 扫描类型为 eq_ref，此时 id 为主键
2. 扫描类型为 ref，此时 id 为非惟一一般索引
3. 扫描类型为 ALL，全表扫描，此时 id 上无索引

总结

1. explain 后果中的 type 字段，示意（狭义）连贯类型，它形容了找到所需数据应用的扫描形式；
2. 常见的扫描类型有：system>const>eq_ref>ref>range>index>ALL，其扫描速度由快到慢；
3. 各类扫描类型的要点是：
5. system 最快：不进行磁盘 IO
6. const：PK 或者 unique 上的等值查问
7. eq_ref：PK 或者 unique 上的 join 查问，等值匹配，对于前表的每一行，后表只有一行命中
8. ref：非惟一索引，等值匹配，可能有多行命中
9. range：索引上的范畴扫描，例如：between、in、>
10. index：索引上的选集扫描，例如：InnoDB 的 count
11. ALL 最慢：全表扫描
12. 建设正确的索引，十分重要；
13. 应用 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，关联字段均未建设索引，就会呈现这种状况。常见的优化计划是，在关联字段上增加索引，防止每次嵌套循环计算。

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