通过explain分析select语句的执行计划

本文主要给大家介绍通过explain分析select语句的执行计划,文章内容都是笔者用心摘选和编辑的,具有一定的针对性,对大家的参考意义还是比较大的,下面跟笔者一起了解下通过explain分析select语句的执行计划吧。

让客户满意是我们工作的目标,不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户,将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴,公司提供的服务项目有:域名申请雅安服务器托管、营销软件、网站建设、景宁畲族自治网站维护、网站推广。

explain分析执行计划

通过以上步骤定位到有问题的sql语句以后我们可以通过explain来分析一下该select语句的执行计划,包括该语句如何连接和连接的顺序

Explain select * from user where id = 1;

这个命令可以显示select * from user where id = 1这个查询语句的执行计划

  通过explain分析select语句的执行计划

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

 

Explain之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 ;

通过explain分析select语句的执行计划

 

在这里先加载t_role表再加载t_user,最后加载user_role

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

通过explain分析select语句的执行计划

这里t_user最先被加载然后是user_role,最后是t_role

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 ;

通过explain分析select语句的执行计划

 

explain 之 select_type:

  表示select的类型有以下取值:

SIMPLE:简单的查询也就是不包含子查询和union

PRIMARY:若查询中包含子查询那么父查询用该标志

SUBQUERY:表示子查询

DERIVED:表示从from子查询查询出来的结果形成的临时表中查询

UNION:联合查询中的UNION后的select语句

UNIONRESULT:通过UNION联合的语句

explain 之 table:
表示这个查询是针对哪张表的
explain 之 type:
type表示访问类型是很重要的指标可取值为:
NULL:MySQL不访问任何表和索引,直接返回结果
例如: SELECT NOW();
System:表只有一行记录,一般不会出现
Const:表示通过索引查询,且只返回一条记录,此处索引指的是主键索引和唯一索引。
Eq_ref:多表关联查询,并且查询出来的数据只有一条
Ref:根据非唯一性索引查询,查询出来的结果有多条
Range:范围扫描,where之后的between,>,<,=等等
Index:表示遍历整个索引树
All:会遍历全表
查询效率由高到低依次是: system > const > eq_ref > ref > range > index > ALL
一般来说我们需要让它达到range或更高
explain 之 key
possible_keys:可能应用的索引,一个或多个
key:实际使用的索引,如果为空则没有使用索引
key_len:索引中使用的字节数,为索引字段最大可能的长度,长度越短越好
explain 之 rows
扫描行的数量
explain 之 extra
表示其它额外执行的信息
using filesort:表示对没有建立索引的字段进行排序。
using temporary:使用临时表保存中间结果,常用语group by语句
using index:表示select操作使用了索引

看完以上关于通过explain分析select语句的执行计划,很多读者朋友肯定多少有一定的了解,如需获取更多的行业知识信息 ,可以持续关注我们的行业资讯栏目的。


当前文章:通过explain分析select语句的执行计划
新闻来源:http://myzitong.com/article/jspjcc.html