松鼠乐园 松鼠乐园
  • 注册
  • 登录
  • 首页
  • 快捷入口
    • Vue
    • Tensorflow
    • Springboot
    • 语言类
      • CSS
      • ES5
      • ES6
      • Go
      • Java
      • Javascript
    • 工具类
      • Git
      • 工具推荐
    • 服务器&运维
      • Centos
      • Docker
      • Linux
      • Mac
      • MySQL
      • Nginx
      • Redis
      • Windows
    • 资源类
      • 论文
      • 书籍推荐
      • 后端资源
      • 前端资源
      • html网页模板
      • 代码
    • 性能优化
    • 测试
  • 重大新闻
  • 人工智能
  • 开源项目
  • Vue2.0从零开始
  • 广场
首页 › MySQL › mysql explain的用法和解释,必学!

mysql explain的用法和解释,必学!

迦娜王
1年前MySQL
272 0 0

mysql explain用法

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

使用方法,在select语句前加上explain就可以了,如:

explain select * from statuses_status where id=11;

创建测试表:

CREATE TABLE people(  id int auto_increment primary key,  zipcode char(32) not null default '',  address varchar(128) not null default '',  lastname char(64) not null default '',  firstname char(64) not null default '',  birthdate char(10) not null default '' ); CREATE TABLE people_car(  people_id int,  plate_number varchar(16) not null default '',  engine_number varchar(16) not null default '',  lasttime timestamp ); 

插入测试数据:

insert into people
(zipcode,address,lastname,firstname,birthdate)
values
('230031','anhui','zhan','jindong','1989-09-15'),
('100000','beijing','zhang','san','1987-03-11'),
('200000','shanghai','wang','wu','1988-08-25')
insert into people_car
(people_id,plate_number,engine_number,lasttime)
values
(1,'A121311','12121313','2013-11-23 :21:12:21'),
(2,'B121311','1S121313','2011-11-23 :21:12:21'),
(3,'C121311','1211SAS1','2012-11-23 :21:12:21')

创建索引用来测试

alter table people add index(zipcode,firstname,lastname);

explain介绍

mysql explain用法介绍和结果的含义。都有哪些好处呢?

先从一个最简单的查询开始:

Query-1
explain select zipcode,firstname,lastname from people;
mysql explain用法介绍和结果的含义。都有哪些好处呢?

explain输出结果共有id,select_type,table,type,possible_keys,key,key_len,ref,rows和Extra几列。每一列分别代表什么意思呢,请看下面的解释。

select_type 表示查询中每个select语句的类型(简单 OR复杂),可以有下面几种

a.SIMPLE:最简单的SELECT查询,没有使用UNION或子查询,见Query-1。

b.PRIMARY:在嵌套的查询中是最外层的SELECT语句,在UNION查询中是最前面的SELECT语句。见Query-2和Query-3。

查询中若包含任何复杂的子部分,最外层查询则被标记为:PRIMARY

Query-2
explain select zipcode from (select * from people a) b;
mysql explain用法介绍和结果的含义。都有哪些好处呢?
Query-3
explain select * from people where zipcode = 100000 union select * from people where zipcode = 200000;
mysql explain用法介绍和结果的含义。都有哪些好处呢?

c.UNION:UNION中第二个以及后面的SELECT语句。见Query-3。

d.DERIVED:派生表SELECT语句中FROM子句中的SELECT语句。见Query-2。

e.UNION RESULT:一个UNION查询的结果。见Query-3。

f.DEPENDENT UNION:顾名思义,首先需要满足UNION的条件,及UNION中第二个以及后面的SELECT语句,同时该语句依赖外部的查询。

Query-4
explain select * from people where id in (select id from people where zipcode = 100000 union select id from people where zipcode = 200000 );
mysql explain用法介绍和结果的含义。都有哪些好处呢?

g.SUBQUERY:子查询中第一个SELECT语句。

Query-6
explain select * from people where id = (select id from people where zipcode = 100000);
mysql explain用法介绍和结果的含义。都有哪些好处呢?

table :显示的这一行信息是关于哪一张表的。有时候并不是真正的表名。

Query-7
explain select * from (select * from (select * from people a) b ) c;
mysql explain用法介绍和结果的含义。都有哪些好处呢?

可以看到如果指定了别名就显示的别名。

N就是id值,指该id值对应的那一步操作的结果。 还有这种类型,出现在UNION语句中,见Query-4。

注意:MySQL对待这些表和普通表一样,但是这些“临时表”是没有任何索引的。

mysql explain用法介绍和结果的含义。都有哪些好处呢?

type

type列很重要,是用来说明表与表之间是如何进行关联操作的,有没有使用索引。MySQL中“关联”一词比一般意义上的要宽泛,MySQL认为任何一次查询都是一次“关联”,并不仅仅是一个查询需要两张表才叫关联,所以也可以理解MySQL是如何访问表的。主要有下面几种类别。

const

当确定最多只会有一行匹配的时候,MySQL优化器会在查询前读取它而且只读取一次,因此非常快。const只会用在将常量和主键或唯一索引进行比较时,而且是比较所有的索引字段。people表在id上有一个主键索引,在(zipcode,firstname,lastname)有一个二级索引。因此Query-8的type是const而Query-9并不是:

Query-8
explain select * from people where id=1;
mysql explain用法介绍和结果的含义。都有哪些好处呢?
Query-9
explain select * from people where zipcode = 100000;
mysql explain用法介绍和结果的含义。都有哪些好处呢?

注意下面的Query-10也不能使用const table,虽然也是主键,也只会返回一条结果。

Query-10
explain select * from people where id >2;
mysql explain用法介绍和结果的含义。都有哪些好处呢?

system

这是const连接类型的一种特例,表仅有一行满足条件。

Query-11
explain select * from (select * from people where id = 1 )b;
mysql explain用法介绍和结果的含义。都有哪些好处呢?
已经是一个const table并且只有一条记录。

eq_ref

唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。 常见于主键或唯一索引扫描。eq_ref类型是除了const外最好的连接类型。

创建员工表Employee和经理表Manager

create table Employee
(
 ID int auto_increment,
 Ename varchar(32),
 Age int,
 Salary float,
 MID int,
 Primary key (ID)
);
create table Manager
(
 MID int,
 Name varchar(32),
 Primary key(MID)
);
Query-12
explain select * from Employee A,Manager B where A.MID=B.MID;
mysql explain用法介绍和结果的含义。都有哪些好处呢?

MID对于表Manager是唯一的,主键索引,来与employee连接,故type为eq_ref。

ref

非唯一性索引扫描,返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找。

这个类型跟eq_ref不同的是,它用在关联操作只使用了索引的最左前缀,或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。
Query-13
explain select * from people where zipcode='100000';
mysql explain用法介绍和结果的含义。都有哪些好处呢?

zipcode、firstname和lastname组成索引,这里只使用了name,即只使用了唯一性索引的一部分,故为ref。

fulltext

链接是使用全文索引进行的。一般我们用到的索引都是B树,这里就不举例说明了。

ref_or_null

该类型和ref类似。但是MySQL会做一个额外的搜索包含NULL列的操作。在解决子查询中经常使用该联接类型的优化。

range

只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range。 索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行, 常见于between、<、>,IN等的查询。
Query-14
explain select * from people where id=1 or id=2;
mysql explain用法介绍和结果的含义。都有哪些好处呢?
Query-15
explain select * from people where id>1;
mysql explain用法介绍和结果的含义。都有哪些好处呢?
Query-16
explain select * from people where id in (1,2);
mysql explain用法介绍和结果的含义。都有哪些好处呢?

index

该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。这个类型通常的作用是告诉我们查询是否使用索引进行排序操作。

按索引扫描表,虽然还是全表扫描,但优点是索引是有序的。index与ALL区别为index类型只遍历索引树。

Query-17
explain select * from people order by id;
mysql explain用法介绍和结果的含义。都有哪些好处呢?

ALL

最慢的一种方式,即全表扫描。

mysql explain用法介绍和结果的含义。都有哪些好处呢?

总结

explain的type列从最差到最好依次是:

ALL:全表扫描。

index:索引扫描。

range:索引范围扫描。

ref :非唯一性索引扫描。

eq_ref :唯一性索引扫描。

const,system:将查询转换为一个常量。

null:MySQL在优化过程中分解语句,执行时甚至不用访问表或索引

possible_keys

possible_keys列指出MySQL能使用哪个索引在该表中找到行。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用。

key

key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

key_len列显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。使用的索引的长度。在不损失精确性的情况下,长度越短越好 。

ref

ref列显示使用哪个列或常数与key一起从表中选择行。

表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值。

rows

rows列显示MySQL认为它执行查询时必须检查的行数。注意这是一个预估值。

mysql explain用法介绍和结果的含义。都有哪些好处呢?

Extra

Extra是EXPLAIN输出中另外一个很重要的列,该列显示MySQL在查询过程中的一些详细信息,包含的信息很多,只选择几个重点的介绍下。

Using filesort

MySQL有两种方式可以生成有序的结果,通过排序操作或者使用索引,当Extra中出现了Using filesort 说明MySQL使用了后者,但注意虽然叫filesort但并不是说明就是用了文件来进行排序,只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快,所以一般这时也要考虑优化查询了。

Using temporary

说明使用了临时表,一般看到它说明查询需要优化了,就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。

Not exists

MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行, 就不再搜索了。

Using index

说明查询是覆盖了索引的,这是好事情。MySQL直接从索引中过滤不需要的记录并返回命中的结果。这是MySQL服务层完成的,但无需再回表查询记录。

Using index condition

这是MySQL 5.6出来的新特性,叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的,但是现在可以了,这样减少了不必要的IO操作,但是只能用在二级索引上。

Using where

使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。

注意:Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。

explain mysql
0
10题多选MYSQL面试题
上一篇
MySQL优化技巧
下一篇
评论 (0)

请登录以参与评论。

现在登录
聚合文章
在Gitee收获近 5k Star,更新后的Vue版RuoYi有哪些新变化?
2月前
vue3.x reactive、effect、computed、watch依赖关系及实现原理
2月前
Vue 3 新特性:在 Composition API 中使用 CSS Modules
2月前
新手必看的前端项目去中心化和模块化思想
2月前
标签
AI AI项目 css docker Drone Elaticsearch es5 es6 Geometry Go gru java Javascript jenkins lstm mysql mysql优化 mysql地理位置索引 mysql索引 mysql规范 mysql设计 mysql配置文件 mysql面试题 mysql高可用 nginx Redis redis性能 rnn SpringBoot Tensorflow tensorflow2.0 UI设计 vue vue3.0 vue原理 whistle ZooKeeper 开源项目 抓包工具 日志输出 机器学习 深度学习 神经网络 论文 面试题
相关文章
开源的SQL查询优化工具–EverSQL
MySQL Geometry扩展在地理位置计算中的效率优势
mysql纵表转横表
分析一个叶大师的my.cnf自动生成工具
松鼠乐园

资源整合,创造价值

小伙伴
墨魇博客 无同创意
目录
重大新闻 Centos CSS Docker ES5 ES6 Go Java Javascript Linux Mac MySQL Nginx Redis Springboot Tensorflow Vue Vue2.x从零开始 Windows 书籍推荐 人工智能 前端资源 后端资源 壁纸 开源项目 测试 论文
Copyright © 2018-2021 松鼠乐园. Designed by nicetheme. 浙ICP备15039601号-4
  • 重大新闻
  • Centos
  • CSS
  • Docker
  • ES5
  • ES6
  • Go
  • Java
  • Javascript
  • Linux
  • Mac
  • MySQL
  • Nginx
  • Redis
  • Springboot
  • Tensorflow
  • Vue
  • Vue2.x从零开始
  • Windows
  • 书籍推荐
  • 人工智能
  • 前端资源
  • 后端资源
  • 壁纸
  • 开源项目
  • 测试
  • 论文
热门搜索
  • jetson nano
  • vue
  • java
  • mysql
  • 人工智能
  • 人脸识别
迦娜王
坚持才有希望
1224 文章
33 评论
231 喜欢
  • 0
  • 0
  • Top