8.3 explain分析执行计划

null

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序

查询SQL语句的执行计划：

 explain  select * from tb_item where id = 1;

 explain  select * from tb_item where title = '阿尔卡特 (OT-979) 冰川白 联通3G手机3';

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为( system ---> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge ---> index_subquery -----> range -----> index ------> all )
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
extra	执行情况的说明和描述

环境准备

 CREATE TABLE `t_role` (
  `id` varchar(32) NOT NULL,
  `role_name` varchar(255) DEFAULT NULL,
  `role_code` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
CREATE TABLE `t_user` (
  `id` varchar(32) NOT NULL,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
CREATE TABLE `user_role` (
  `id` int(11) NOT NULL auto_increment ,
  `user_id` varchar(32) DEFAULT NULL,
  `role_id` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ur_user_id` (`user_id`),
  KEY `fk_ur_role_id` (`role_id`),
  CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
 
insert into `t_user` (`id`, `username`, `password`, `name`) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超级管理员');
insert into `t_user` (`id`, `username`, `password`, `name`) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系统管理员');
insert into `t_user` (`id`, `username`, `password`, `name`) values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
insert into `t_user` (`id`, `username`, `password`, `name`) values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','学生1');
insert into `t_user` (`id`, `username`, `password`, `name`) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','学生2');
insert into `t_user` (`id`, `username`, `password`, `name`) values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老师1');
 
 
 
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','学生','student','学生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老师','teacher','老师');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教学管理员','teachmanager','教学管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理员','admin','管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超级管理员','super','超级管理员');
 
 
INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;

id

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id 情况有三种：

1） id 相同表示加载表的顺序是从上到下。

 explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;

2） id 不同id值越大，优先级越高，越先被执行。

 EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))

3） id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

 EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id ;
# 其中id为2的 deriverd 衍生出来的虚表

select_type

表示 SELECT 的类型，常见的取值，如下表所示：从上往下效率越来越低

select_type	含义
SIMPLE	`简单的select查询，查询中不包含子查询或者UNION`
PRIMARY	`查询中若包含任何复杂的子查询，最外层查询标记为primary该标识`
SUBQUERY	`在SELECT 或 WHERE 列表中包含了子查询`
DERIVED	在from 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中
UNION	若第二个 SELECT语句出现在 union 之后，则被标记为UNION ；若UNION包含在 from子句的子查询中，外层SELECT将被标记为：deriver
UNION RESULT	从union表获取结果的SELECT，两个UNION合并的结果集在最后

 -- union 和 union result 示例
EXPLAIN select * from employee e LEFT JOIN department d on e.dep_id = d.id
UNION 
select * from employee e RIGHT JOIN department D ON e.dep_id = d.id

table

展示这一行的数据是关于哪一张表的

**partitions **

 如果查询是基于分区表的话, 会显示查询访问的分区（分库分表涉及）

type

type 显示的是访问类型，是较为重要的一个指标，可取值为：

type	含义
NULL	MySQL不访问任何表，索引，直接返回结果
system	表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
const	`表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常量。const于将 "主键" 或 "唯一" 索引的所有部分与常量值进行比较`
eq_ref	`类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描`
ref	`非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）`
range	只检索给定范围的行，使用一个索引来选择行。 where 之后出现 between 、 <、>、in 等操作。
index	`index 与 ALL的区别为 index 类型只是遍历了索引树，通常比ALL 快， ALL 是遍历数据文件。`index是从索引中读取，all是从硬盘当中读取。
all	`将遍历全表以找到匹配的行`

结果值从最好到最坏以此是：

 NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
 
结果值:(最好到最差) 
	 system   >   const    >   eq_ref   >   ref   >   range   >    index   >   ALL

一般来说，我们需要保证查询至少达到 range 级别，最好达到ref 。

possible_keys

 显示可能应用在这张表中的索引,一个或者多个
查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
可能自己创建了4个索引,在执行的时候,可能根据内部的自动判断,只使用了3个

 --  可能不会用到索引，实际用到索引
explain select dep_id from employee

 -- 可能会使用索引，实际没用到索引
EXPLAIN select * from employee e,department d where e.dep_id = d.id

key

 key ： 实际使用的索引，如果为NULL，则没有使用索引。查询中若使用了覆盖索引 ,则该索引仅出现在key列表
 
possible_keys 与 key 关系,理论应该用到哪些索引  实际用到了哪些索引

key_len

 key_len : 表示索引中使用的字节数，该值为 索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

 -- 表示索引中使用的字节数,可通过该列计算查询中使用的索引长度 .汉字占字节数多，数值型占的较少
explain select * from employee where dep_id=1 and name='鲁班' and age=10

ref

 索引是否被引入到, 到底引用到了哪几个索引

 Explain select * from employee e,department d where e.dep_id = d.id and e.cus_id = 1

 Explain select e.dep_id from employee e,department d,customer c where e.dep_id = d.id and e.cus_id = c.id and e.name='鲁班'

rows

扫描行的数量。根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，每张表有多少行被优化器查询过。

filtered

满足查询的记录数量的比例，注意是百分比，不是具体记录数，值越大越好，filtered列的值依赖统计信息，并不十分准确

extra

其他的额外的执行计划信息，如下列表中需要优化三个属性：

extra	含义
using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为"文件排序"，效率低，需要优化
using temporary	使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表，分组没有利用到索引。常见于 order by 和 group by，效率低，需要优化
impossible where	where 子句的值总是false 不能用来获取任何元组，说明条件永远不成立。需要优化
using index	表示相应的select操作使用了覆盖索引，避免访问表的数据行， `效率不错。`

注意：

语句中出现了Using Filesort 和 Using Temporary 说明没有使用到索引，出现 impossible where说明条件永远不成立。

 -- Using filesort (需要优化)  filesort 文件排序
explain select * from employee where dep_id =1 ORDER BY cus_id

 -- Using temporary (需要优化)  使用了 临时表 保存中间结果
explain select name from employee where dep_id in (1,2,3) GROUP BY cus_id

 -- impossible where (需要优化) 不可能存在的
-- 一个name属性既是这个又叫那个
explain select name from tb_user where name='张三' and name='李四';

不需要优化属性值如下：

extra	含义
using index	表示相应的 select 操作使用了覆盖索引，避免访问表的数据行，效率不错。如果同时出现using where 表明索引被用来执行索引键值的查找如果没有同时出现using where 表明索引用来读取数据而非执行查找动作
using where	表明使用了where过滤
using join buffer	使用了连接缓存

上一篇8.2 show processlist 定位低效率执行SQL

下一篇8.4 show profile分析SQL

posted @ 2022-03-27 16:12 Lz_蚂蚱阅读(46) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

Lz_蚂蚱