mysql中的explain史上最详细的笔记

最新推荐文章于 2024-07-16 14:15:48 发布

WithScores

最新推荐文章于 2024-07-16 14:15:48 发布

阅读量274

点赞数 2

分类专栏：数据库优化文章标签： java

本文链接：https://blog.csdn.net/privateBX/article/details/106059625

版权

数据库优化专栏收录该内容

1 篇文章 0 订阅

订阅专栏

mysql中的explain

explain作用

# 使用explain关键字可以模拟优化器执行sql 的查询语句，从而知道mysql是怎么处理自己的sql的

* 我们可以从explain中读取以下的信息
		1. 表读取的顺序
		
		2. 数据读取操作的操作类型
		
		3. 那些索引可以使用
		
		4. 那些索引被实际使用
		
		5. 表之间的引用
		
		6. 每张表有多少行被优化器优化

* explain的用法：
		在我们的sql（针对mysql） 的查询语句前面使用explian关键字就可以
		example： explain select * from user where id = 1;

概述

1. id:选择标识符
2. select_type:表示查询的类型。
3. table:输出结果集的表
4. partitions:匹配的分区
5. type:表示表的连接类型
6. possible_keys:表示查询时，可能使用的索引
7. key:表示实际使用的索引
8. key_len:索引字段的长度
9. ref:列与索引的比较
10. rows:扫描出的行数(估算的行数)
11. filtered:按表条件过滤的行百分比
12. Extra:执行情况的描述和说明

关键字

在这里插入图片描述

Id

# 查询的序号，表示查询中执行的select子句或操作表的顺序

* 两种情况
		1. id相同，执行顺序从上往下
		
		2. id不同，id值越大，优先级越高，越先执行

select_type

SIMPLE

# 表示查询中没个select子句的类型

1. SIMPLE
		简单SELECT，不使用UNION或子查询等

在这里插入图片描述

PRIMARY

2. PRIMARY/UNION
		子查询中最外层查询，如果两表中做UNION或者存在子查询 的时候 ，最外层的表操作称为PRIMARY,内层的操作为UNION

在这里插入图片描述

UNION

3. UNION
		UNION操作中，查询中处于内层的select（内层的select和外层的select没有依赖关系）

DEPENDENT UNION

4. DEPENDENT UNION
		UNION操作中，查询中处于内层的SELECT（内层的SELECT语句与外层的SELECT语句有依赖关系）

UNIOIN RESULT

5. UNIOIN RESULT
		UNION操作的结果，id值通常为NULL

在这里插入图片描述

SUBQUERY

6. SUBQUERY 
		子查询的首个查询（如果有多个子查询存在的话）

DEPENDENT SUBQUERY

7. DEPENDENT SUBQUERY
		子查询中的首个select，但依赖于外层的表（如果有多个子查询存在）

在这里插入图片描述

DERIVED

8. DERIVED
		派生表的select，from子句的子查询
		
8. MATERIALIZED
		被物化的子查询

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JErQDHPz-1589190908681)(E:\notes\mysql中的explain.assets\DEPENDENT SUBQUERY-1589178523365.png)]$

UNCACHEABLE SUBQUERY

9. UNCACHEABLE SUBQUERY
		对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）
		
9. UNCACHEABLE UNION
		UNION操作中，内层的不可被物化的子查询（类似于UNCACHEABLE SUBQUERY）

注意

#  DEPENDENT SUBQUERY 
	
	1. 特别的消耗性能
	2. 不会进行子查询，会先进行外部查询，会生成结果集，再在内部进行关联查询
	3. 子查询的执行效率受制于外层查询的记录数
	4. 可以尝试改成join查询

table

# 显示这一步所访问数据库中表名称
		显示这一行数据是关于那张表的，有时不是真实的表的名字，可能是简称，也可能是第几部执行的结果的简称

type

# 对表的访问的方式（连接类型），表示MySQL在查询的过程中使用了那种类型

* 常见的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

		all: Full Table Scan Mysql将遍历全表以找到匹配的行
		index: Full Index Scan, index与All的区别是index类型只遍历索引树
		range： 只检索给定范围的行，使用一个索引来选择行
		ref:	 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
		eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
		const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
		NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

数据表结构

CREATE TABLE `sys_user` (
  `id` int NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `dept_id` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在这里插入图片描述

all

$explain.assets\type-all-1589181310088.png)]$

index

# 这种连接类型只是另外一种的全表扫描，只不过他的扫描顺序是按照索引的顺序。这种索引根据索引然后回表取数据。

		和all相比，他们都是取得了全表的数据，而且index要先读缩影而且要回表随机去数据，因此index不可能会比all快（取同一个表数据）。
		但是为什么官方的手册将它到效率说的比all好，唯一的可能原因在于，按照索引扫描全表的数据是有序的
		如果一定要比效率，只需要获取这个表的数据并且排序便可以看出来谁比谁效率高了：

all

在这里插入图片描述

index

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5W2ndaS4-1589190908685)(E:\notes\mysql中的explain.assets\type-index-compare.png)]$

* 上面可以看到dept_id 排序的连接类型是all，但是注意到Extra的列是用到了排序（Using filesort）,而根据id排序的连接类型是index，而且得到的结果自然是有序的，不允许额外的排序。

		正因为这个原因，index的效率比all高，但是需要相同的条件才成立
		
* 如果连接类型为type，而且extra列中的值为‘Using index’,那么称为这种情况为索引覆盖
		索引覆盖： 想象新华字字典是一张表，当然有前面的索引的部分（假如是按照英文字母来排序的索引），那么索引覆盖就相当于根据字母索引来获取到这个字典中的所有的字。
		我们获取到字典中的所有的字并没有查询表，因为我们想要的数据全都在索引中了，即索引覆盖

在这里插入图片描述

range

# range指的是有范围的索引扫描，相对于index全索引扫描，他有范围限制，因此效率要高于index。

		出现了range，则一定是基于索引的，同时除了显而易见的between，and以及>,<外，or和in也是范围扫描

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0ezsaoc9-1589190908687)(E:\notes\mysql中的explain.assets\type-range.png)]$

ref

# 出现该连接类型的条件是：查询条件列使用了索引而且不为主键和unique。（非唯一性索引扫描）
		意思是使用了索引，但是该索引列的值不唯一，有重复。
		这样即使使用索引快速查找到了第一条数据，仍然不能够停止，要进行目标值辅警的小范围扫描。
		
		它的好处是它并不需要扫描全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

为了演示这种情况，给name这个字段上添加了一个key

CREATE TABLE `sys_user` (
  `id` int NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `dept_id` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在这里插入图片描述

ref_eq

# 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于唯一索引，或者主键扫描

sys_code的表结构

CREATE TABLE `sys_code` (
  `code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

sys_dept表结构

CREATE TABLE `sys_dept` (
  `id` varchar(32) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `code` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SdvXoAh7-1589190908689)(E:\notes\mysql中的explain.assets\type-eq_ref.png)]
在这里插入图片描述

* 其中的sys_dept表中的code是唯一性的，sys_code的code是主键

const

# 通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。

possible_keys

# 指出mysql能使用哪个索引在表中找到记录，查询涉及到的字段上如存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示null）

在这里插入图片描述

key

# key列显示mysql实际决定使用的键（索引），必然包含在possible_keys中

* 如果没有选择索引，键是null，要想强制mysql使用或者忽视possible_keys列中的索引，
		在查询中使用 FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

# 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

    不损失精确性的情况下，长度越短越好

ref

# 显示索引的哪一列被使用了，如果有可能是一个常数，那些列或者常亮被用于查询索引列上的值

rows

# 根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra

Using filesort

* 说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序”

在这里插入图片描述

Using temporary

* 使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。

Using index

* 覆盖索引

		表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。

Using where

* 表明使用where 过滤

Using join buffer

* 使用连接缓存

impossible where

* where子句的值总是false，不能用来获取任何元组

select tables optimized away

* 在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

distinct

* 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

Sql的顺序

select distinct 
        <select_list>
from
    <left_table><join_type>
join <right_table> on <join_condition>
where
    <where_condition>
group by
    <group_by_list>
having
    <having_condition>
order by
    <order_by_condition>
limit <limit number>

执行顺序

1、from <left_table><join_type>
2、on <join_condition>
3、<join_type> join <right_table>
4、where <where_condition>
5、group by <group_by_list>
6、having <having_condition>
7、select
8、distinct <select_list>
9、order by <order_by_condition>
10、limit <limit_number>