执行计划是什么?
使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是 如何处理你的SQL语句的,分析你的查询语句或是表结构的性能瓶颈。
官网介绍:https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
前提介绍:文中所有案例 mysql 版本为 5.7.23
image.png
select 查询的序列号,包含一组数字, 表示查询中执行 select 子句或操作表的顺序
use oemp;
#测试表1
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`other_column` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
#测试表2
CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`other_column` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
#测试表3
CREATE TABLE `t3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`other_column` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
#id 相同
explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id
and t3.other_column = '';
#id 不同
explain select t2.* from t2 where id = (select id from t1 where id =
(select t3.id from t3 where t3.other_column = ''));
#id 相同和不同同时存在
explain select t2.* from (select t3.id from t3 where t3.other_column = '') s1,t2
where s1.id = t2.id;
包含三种情况:id 相同,id 不同,id 相同和 id 不同同时存在。
id 相同,执行结果从上而下
image.png
id不同如果是自查询,id 的序号会递增,id 值越大,优先级越高,越先被执行
image.png
id 如果相同,可以认为是一组的,从上往下执行; 在所有组中,id 值越大,优先级越高,越先被执行; 衍生 = DERIVED
derived_merge 是 Mysql5.7 引入的,会试图将 Derived Table (派生表,from 后的自查询) 视图引用,公用表达式(Common table expressions) 与外层查询进行合并。MySQL 5.7 不在兼容的实现方式,可以通过调整 optimizer_switch 来加以规避
set optimizer_switch='derived_merge=off';
说白了,如果设置为 on 那么就不会出现 derived_merge 行 结果如下:
包括范围:simple. primary,subquery, derived, union, union result 查询类型主要是用于区别普通查询,联合查询,子查询等复杂的查询
类型:all , index , range, ref, eq_ref, const, system ,null type 显示的是关联类型或者访问类型,是较为重要的一个指标,结果从好到坏依次是: system > count > eq_ref > range > index > all
sytem > const > eq_ref > ref > fulltext > ref_or_null > index_merge >> unique_subquery > index_subquery > range > index > ALL
备注:一般来说,得以保证查询至少达到 rang 级别, 最好能达到 ref。
explain select min(id) from t1;
mysql 能够在优化阶段分析查询语句,在执行阶段不用再访问表或者索引。例如:在索引列中选取最小值,单独查询索引来完成,不需要在执行时访问表
表只有一行记录(等于系统表),这是 const 类型的特列, 平时不会出现,这个也可以忽略不计
explain select * from (select * from t1 where id =1) d1;
表示通过索引一次就找到了, const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据,所以很快如将主键置于where 列表中, MySQL 就能将该查询转换为一个常量。
explain select * from t1, t2 where t1.id = t2.id;
唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描. 查询案例:
# tb_emp ddl
CREATE TABLE `tb_emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`dept_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
) ;
#员工表添加年龄列
alter table tb_emp add column `age` int(11) default null after `name`;
#添加复合索引
create index idx_emp_name_age on tb_emp(`name`, `age`);
explain select * from tb_emp where `name` = 'z3';
非唯一性索引扫描, 返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独的行,然而,它可能会找到多个符合个条件的行,所以它应该属于查找和扫描的混合体
explain select * from t1 where id between 1 and 3;
explain select * from t1 where id in (1, 2, 3);
只检索给定范围内的行,使用一个索引来选择行。key 列显示使用了哪个索引 一般就是你在 where 语句中出现了 between、<、>、in 等的查询 这种范围扫描索引比全表扫描要好,因为它只需要开始于索引的某个点,而结束于另一个点,不用全表扫描 案例结果:
explain select id from t1;
Full Index Scan , index 和 ALL 的区别 ,index 类型只遍历索引树, 这通常比 ALL 快, 因为索引文件通常比数据文件小。(也就是说虽然 all 和 index 都是读全表,但是index 是从索引中读取的, 而 all 是从硬盘中读取的 ) 查询结果:
explain select * from t1;
Full Table Scan 将遍历全表找到匹配的行
备注:一般来说,得以保证查询至少达到 rang 级别, 最好能达到 ref。
显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用.
实际使用的索引,如果为NULL,则没有使用索引 查询中若使用了覆盖索引,则该索引仅出现在KEY列表中
explain select col1, col2 from t1;
create index idx_col1_col2 on t1(col1, col2);
explain select col1, col2 from t1;
案例一(加索引之前)
案例二(加索引之后)
desc t1;
explain select * from t1 where col1 = 'ab';
explain select * from t1 where col1 = 'ab' and col2 = 'bc';
表示索引中使用的字节数,可通过该列计算查询中的使用的索引的长度,在不损失精确性的情况下,长度越短越好 key_len 显示的只为索引字段的最大可能长度,** 并非实际使用长度**。即 key_len 是根据表定义计算而得,不是通过表内检索出的。 查询结果:
总结:条件越多,付出的代价越大,key_len 的长度也就越大,建议在一定条件的情况下,key_len 越短,效率越高。
索引最大长度是 768 字节,当字符串过长时,mysql 会做一个类似左前缀索引的处理,将前半段的字符串提取出来做索引。
根据表统计信息及索引选用情况, 大致估算出找到所需的记录所需读取的行数
包含不适合其他列中显示但十分重要的额外信息 id, select_type, table, type , possible_keys, key, key_len, ref, rows, Extra
文件排序
ALTER TABLE `t1`
ADD COLUMN `col1` VARCHAR(30) NULL DEFAULT NULL AFTER `other_column`;
ALTER TABLE `t1`
ADD COLUMN `col2` VARCHAR(30) NULL DEFAULT NULL AFTER `col1`;
explain select col2 from t1 where col1 in ('ab', 'ac', 'as') group by col2 \G;
explain select col2 from t1 where col1 in ('ab', 'ac', 'as')
group by col1, col2, col3 \G;
使用了临时表保存中间结果, MySQL 在对查询结果排序时使用临时表。 常见于排序 order by 和分组查询 group by 。例子:
explain select col2 from t1 where col1=100;
explain select col1, col2 from t1;
表示相应的 select 操作使用了覆盖索引 (Covering Index), 避免了访问表的数据行,效率不错~ 如果同时出现 using where , 表示索引被用来执行索引键值的查找;如果没有同时出现 using where , 表明索引引用来读取数据而非执行查找动作。例子:
覆盖索引 (Covering Index)
explain select * from tb_emp where `name` > 'z3';
查询的列不完全被覆盖索引覆盖,where 条件中是一个前导的范围
表明使用了 where 过滤
使用了链接缓存
explain select * from t1 where 1=2;
where 子句的值总是 false , 不能用来获取任何元组
在没有 GROUPBY 子句的情况下,基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUT(*) 操作不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
优化 distinct 操作 在找到第一匹配的元祖后立即停止找相同值的动作。
例子描述:
explain select d1.name, (select id from t3) d2 from
(select id, name from t1 where other_column = '') d1
union
(select name, id from t2);
查询结果:
案例解析:
参考资料