- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
CFSDN坚持开源创造价值,我们致力于搭建一个资源共享平台,让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章详解mysql中explain的type由作者收集整理,如果你对这篇文章有兴趣,记得点赞哟.
导语:
很多情况下,有很多人用各种select语句查询到了他们想要的数据后,往往便以为工作圆满结束了。 这些事情往往发生在一些学生亦或刚入职场但之前又没有很好数据库基础的小白身上,但所谓闻道有先后,只要我们小白好好学习,天天向上,还是很靠谱的.
当一个sql查询语句被写出来之后,其实你的工作只完成了一小半,接下来更重要的工作是评估你自己写的sql的质量与效率。mysql为我们提供了很有用的辅助武器explain,它向我们展示了mysql接收到一条sql语句的执行计划。根据explain返回的结果我们便可以知道我们的sql写的怎么样,是否会造成查询瓶颈,同时根据结果不断的修改调整查询语句,从而完成sql优化的过程.
虽然 explain返回的结果项很多,这里我们只关注三种,分别是type,key,rows。其中key表明的是这次查找中所用到的索引,rows是指这次查找数据所扫描的行数(这里可以先这样理解,但实际上是内循环的次数)。而type则是本文要详细记录的连接类型,前两项重要而且简单,无需多说.
type -- 连接类型 。
type意味着类型,这里的type官方全称是“join type”,意思是“连接类型”,这样很容易给人一种错觉觉得必须需要俩个表以上才有连接类型。事实上这里的连接类型并非字面那样的狭隘,它更确切的说是一种数据库引擎查找表的一种方式,在《高性能mysql》一书中作者更是觉得称呼它为访问类型更贴切一些.
mysql5.7中type的类型达到了14种之多,这里只记录和理解最重要且经常遇见的六种类型,它们分别是all,index,range,ref,eq_ref,const。从左到右,它们的效率依次是增强的。撇开sql的具体应用环境以及其他因素,你应当尽量优化你的sql语句,使它的type尽量靠右,但实际运用中还是要综合考虑各个方面的.
接下来,为了演示和重现这几种连接类型,我新建了一个数据测试表,以方面更好的理解这五种类型.
1
2
3
4
5
6
7
8
|
| employee |
CREATE
TABLE
`employee` (
`rec_id`
int
(11)
NOT
NULL
AUTO_INCREMENT,
`
no
`
varchar
(10)
NOT
NULL
,
`
name
`
varchar
(20)
NOT
NULL
,
`position`
varchar
(20)
NOT
NULL
,
`age`
varchar
(2)
NOT
NULL
,
PRIMARY
KEY
(`rec_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6
DEFAULT
CHARSET=utf8 |
|
all 。
这便是所谓的“全表扫描”,如果是展示一个数据表中的全部数据项,倒是觉得也没什么,如果是在一个查找数据项的sql中出现了all类型,那通常意味着你的sql语句处于一种最原生的状态,有很大的优化空间。 为什么这么说呢?因为all是一种非常暴力和原始的查找方法,非常的耗时而且低效。用all去查找数据就好比这样的一个情形:S学校有俩万人,我告诉你你给我找到小明,然后你怎么做呢!你当然是把全校俩万人挨个找一遍,即使你很幸运第一个人便找到了小明,但是你仍然不能停下,因为你无法确认是否有另外一个小明存在,直到你把俩万人找完为止。所以,基本所有情况,我们都要避免这样类型的查找,除非你不得不这样做。 以employee表为例,下面一种情形便是all类型的查找:
1
2
3
4
5
6
|
mysql> explain
select
*
from
employee
where
`
no
` =
'20150001'
;
+
----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employee |
ALL
|
NULL
|
NULL
|
NULL
|
NULL
| 5 | Using
where
|
+
----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|
这是因为no列既不是主键也不是索引,因此只能采用全表扫描来查找目标no.
index 。
这种连接类型只是另外一种形式的全表扫描,只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据,和all相比,他们都是取得了全表的数据,而且index要先读索引而且要回表随机取数据,因此index不可能会比all快(取同一个表数据),但为什么官方的手册将它的效率说的比all好,唯一可能的原因在于,按照索引扫描全表的数据是有序的。这样一来,结果不同,也就没法比效率的问题了。 如果一定要比效率,只需要获取这个表的数据并且排序便可以看出来谁比谁效率高了:
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> explain
select
*
from
employee
order
by
`
no
` ;
+
----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | employee |
ALL
|
NULL
|
NULL
|
NULL
|
NULL
| 5 | Using filesort |
+
----+-------------+----------+------+---------------+------+---------+------+------+----------------+
mysql> explain
select
*
from
employee
order
by
rec_id ;
+
----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
| 1 | SIMPLE | employee |
index
|
NULL
|
PRIMARY
| 4 |
NULL
| 5 |
NULL
|
+
----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
|
上面可以看出,根据no列排序的连接类型是all型的,但是注意extra列是用到了排序(Using filesort),而根据rec_id列排序的连接类型是index,而且得到的结果自然是有序的,不许额外的排序。可能正是因为这个缘故,index的效率比all高,但注意这需要相同的条件才成立(既需要排序).
如果连接类型为type,而且extra列中的值为‘Using index',那么称这种情况为 索引覆盖; 索引覆盖意味着什么呢?想象这样一种场景,如果说一本新华字典是一张表,当然前面的索引部分(假设按照部首的索引)是这张表的索引,那么索引覆盖就相当于根据部首索引获取第一个字到最后一个字(新华字典的所有字)。我们获得了字典中所有的字,然而我们并没有查一次表,因为我们想要的都早索引中,即索引覆盖.
1
2
3
4
5
6
|
mysql> explain
select
rec_id
from
employee ;
+
----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | employee |
index
|
NULL
|
PRIMARY
| 4 |
NULL
| 5 | Using
index
|
+
----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|
上例获取的rec_id刚好为索引列,因此无需回表取数据.
range 。
range指的是有范围的索引扫描,相对于index的全索引扫描,它有范围限制,因此要优于index。关于range比较容易理解,需要记住的是出现了range,则一定是基于索引的。同时除了显而易见的between,and以及'>','<'外,in和or也是索引范围扫描.
ref 。
出现该连接类型的条件是: 查找条件列使用了索引而且不为主键和unique。其实,意思就是虽然使用了索引,但该索引列的值并不唯一,有重复。这样即使使用索引快速查找到了第一条数据,仍然不能停止,要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表,因为索引是有序的,即便有重复值,也是在一个非常小的范围内扫描。下面为了演示这种情形,给employee表中的name列添加一个普通的key(值允许重复) 。
1
|
alter
table
employee
add
key
I_EMPLOYEE_NAME(`
name
`);
|
接下来,在employee表中根据name查找数据的时候,mysql优化器便选择了ref的连接类型.
1
2
3
4
5
6
|
mysql> explain
select
*
from
employee
where
`
name
` =
'张三'
;
+
----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | employee | ref | I_EMPLOYEE_NAM | I_EMPLOYEE_NAM | 62 | const | 1 | Using
index
condition |
+
----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+
|
ref_eq 。
ref_eq 与 ref相比牛的地方是,它知道这种类型的查找结果集只有一个?什么情况下结果集只有一个呢!那便是使用了主键或者唯一性索引进行查找的情况,比如根据学号查找某一学校的一名同学,在没有查找前我们就知道结果一定只有一个,所以当我们首次查找到这个学号,便立即停止了查询。这种连接类型每次都进行着精确查询,无需过多的扫描,因此查找效率更高,当然列的唯一性是需要根据实际情况决定的。 在单个表中,曾尝试了很多方法想出现ref_eq的连接类型,然而很多时候出现的都是const,因此不得不随手连接了一张表得到了想要的连接类型,该表的建表代买为。(博主比较懒,连接了两个没有关系的表,o(╯□╰)o) 。
1
2
3
4
5
6
7
|
CREATE
TABLE
`score` (
`rec_id`
INT
(11)
NOT
NULL
AUTO_INCREMENT,
`stu_id`
INT
(11)
NOT
NULL
,
`mark`
INT
(11)
NOT
NULL
DEFAULT
'0'
,
PRIMARY
KEY
(`rec_id`),
UNIQUE
KEY
`UK_SCORE_STU_ID` (`stu_id`)
) ENGINE=INNODB AUTO_INCREMENT=6
DEFAULT
CHARSET=utf8
|
employee表中有五条数据,score表中有对应的五条数据,其中employee的rec_id 和score的stu_id 是一一对应的.
1
2
3
4
5
6
7
|
mysql> explain
select
ep.
name
,sc.mark
from
employee ep,score sc
where
ep.rec_id = sc.stu_id;
+
----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+
| 1 | SIMPLE | sc |
ALL
| UK_SCORE_STU_ID |
NULL
|
NULL
|
NULL
| 5 |
NULL
|
| 1 | SIMPLE | ep | eq_ref |
PRIMARY
|
PRIMARY
| 4 | my_db.sc.stu_id | 1 |
NULL
|
+
----+-------------+-------+--------+-----------------+---------+---------+-----------------+------+-------+
|
上面就可以看到score表是全表扫描的类型,rows=5代表外层表循环了五次(因为有五条数据),但是employee表的rows怎么是1,怎么可能?刚开始也是很疑惑,这与mysql的查询原理息息相关,rows实际反映的是查询的内循环数,针对外层的每一条数据匹配,employee的确一枪就可以命中,因此rows为1.
const 。
通常情况下,如果将一个主键放置到where后面作为条件查询,mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化,这个取决于优化器.
总结 。
explain 就像一面镜子,有事没事写完sql记得explain一下。同时,在写文章也发现,有很多东西和细节,想要明白清楚,也是没有那么简单的,需要对操作系统以及数据库的底层查询和运行原理要有一个清楚的理解。同时type的几种类型几乎都是基于索引之上的,因此需要对索引有个深入的了解,而且explain的结果可以指导我们什么时候加索引,什么时候不加索引,从而让我们更好的使用索引.
以上就是详解mysql中explain的type的详细内容,更多关于mysql中explain的type的资料请关注我其它相关文章! 。
原文链接:https://blog.csdn.net/dennis211/article/details/78170079 。
最后此篇关于详解mysql中explain的type的文章就讲到这里了,如果你想了解更多关于详解mysql中explain的type的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。
全称“Java Virtual Machine statistics monitoring tool”(statistics 统计;monitoring 监控;tool 工具) 用于监控虚拟机的各种运
主要是讲下Mongodb的索引的查看、创建、删除、类型说明,还有就是Explain执行计划的解释说明。 可以转载,但请注明出处。  
1>单线程或者单进程 相当于短链接,当accept之后,就开始数据的接收和数据的发送,不接受新的连接,即一个server,一个client 不存在并发。 2>循环服务器和并发服务器
详解 linux中的关机和重启命令 一 shutdown命令 shutdown [选项] 时间 选项: ?
首先,将json串转为一个JObject对象: ? 1
matplotlib官网 matplotlib库默认英文字体 添加黑体(‘SimHei')为绘图字体 代码: plt.rcParams['font.sans-serif']=['SimHei'
在并发编程中,synchronized关键字是常出现的角色。之前我们都称呼synchronized关键字为重量锁,但是在jdk1.6中对synchronized进行了优化,引入了偏向锁、轻量锁。本篇
一般我们的项目中会使用1到2个数据库连接配置,同程艺龙的数据库连接配置被收拢到统一的配置中心,由DBA统一配置和维护,业务方通过某个字符串配置拿到的是Connection对象。  
实例如下: ? 1
1. MemoryCahe NetCore中的缓存和System.Runtime.Caching很相似,但是在功能上做了增强,缓存的key支持object类型;提供了泛型支持;可以读缓存和单个缓存
argument是javascript中函数的一个特殊参数,例如下文,利用argument访问函数参数,判断函数是否执行 复制代码 代码如下: <script
一不小心装了一个Redis服务,开了一个全网的默认端口,一开始以为这台服务器没有公网ip,结果发现之后悔之莫及啊 某天发现cpu load高的出奇,发现一个minerd进程 占了大量cpu,googl
今天写这个是为了 提醒自己 编程过程 不仅要有逻辑 思想 还有要规范 代码 这样可读性 1、PHP 编程规范与编码习惯最主要的有以下几点: 1 文件说明 2 funct
摘要:虚拟机安装时一般都采用最小化安装,默认没有lspci工具。一台测试虚拟网卡性能的虚拟机,需要lspci工具来查看网卡的类型。本文描述了在一个虚拟机中安装lspci工具的具体步骤。 由于要测试
1、修改用户进程可打开文件数限制 在Linux平台上,无论编写客户端程序还是服务端程序,在进行高并发TCP连接处理时,最高的并发数量都要受到系统对用户单一进程同时可打开文件数量的限制(这是因为系统
目录 算术运算符 基本四则运算符 增量赋值运算符 自增/自减运算符 关系运算符 逻
如下所示: ? 1
MapperScannerConfigurer之sqlSessionFactory注入方式讲解 首先,Mybatis中的有一段配置非常方便,省去我们去写DaoImpl(Dao层实现类)的时间,这个
Linux的网络虚拟化是LXC项目中的一个子项目,LXC包括文件系统虚拟化,进程空间虚拟化,用户虚拟化,网络虚拟化,等等,这里使用LXC的网络虚拟化来模拟多个网络环境。 本文从基本的网络设备讲
? 1
我是一名优秀的程序员,十分优秀!