笔记 Explain

最新推荐文章于 2024-06-14 13:54:53 发布

艾尼路.

最新推荐文章于 2024-06-14 13:54:53 发布

阅读量80

点赞数

本文链接：https://blog.csdn.net/weixin_47321566/article/details/110437750

版权

Explain

一、介绍

1.Explain是什么

Explain （执行计划），简单的来说，是SQL在数据库中执行时的表现情况,通常用于SQL性能分析,优化等场景。

2.为什么要用Explain

explain可以显示mysql如何使用索引来处理select语句，以及连接表。

explain可以帮助我们分析select语句

让我们知道查询效率低下的原因，从而改进我们的查询。

这条命令的输出结果能够让我们了解Mysql查询优化器是如何执行sql语句的。

这条命令并没有提供任何调整建议，但他能够提供重要的信息帮你做出优化决策。

所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

3.怎么用

用法非常简单。只需在select查询语句前加explain。

EXPLAIN select * from teacher t JOIN course c ON t.t_id = c.t_id

查询结果

在这里插入图片描述

4.使用Explain可以得到什么

表的读取顺序
表的读取操作的操作类型
哪些索引可以使用
哪些索引实际被使用
表之间的引用
每张表有多少行被优化器查询
额外开销

二、读懂执行计划

expain出来的信息有12列，分别是id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra

概要描述：
id: 查询的序列号表示查询中执行 select 子句或操作表的顺序,id值越大优先级越高,越先被执行。id 相同,执行顺序由上至下。
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型，从最好到最差的连接类型为const、eq_reg、ref、range、index和all
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度，在不损失精确性的情况下，长度越短越好
ref:列与索引的比较
rows:扫描出的行数(估算的行数)， mysql认为必须检查的用来返回请求数据的行数
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

1.id

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

id 相同 —》执行顺序由上至下。
id不同 —》id的序号会递增，id值越大优先级越高,越先被执行。（子查询时）
id相同又不同（两种情况同时存在） —》

id如果相同，可以认为是一组，从上往下顺序执行；

在所有组中，id值越大，优先级越高，越先执行

2.select_type

表示查询的类型。主要是用于区分普通查询、联合查询、子查询等。

（1） SIMPLE(简单SELECT，不使用UNION或子查询等)

（2） PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)

（3） SUBQUERY( 在select 或 where列表中包含了子查询 )

（4） DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

（5） UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)

（6） UNION (UNION中的第二个或后面的SELECT语句)

（7） DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

（8） DERIVED(派生表的SELECT, FROM子句的子查询)

（9） UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

3.table

输出的行所引用的表（显示这一行的数据是关于哪张表的）。有时不是真实的表名字，可能是简称、别名

4.partitions

代表给定表所使用的分区。

5.type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

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

ALL：Full Table Scan（全表扫描）， MySQL将遍历全表以找到匹配的行，效率低，性能最差
index: Full Index Scan（全索引查找），index与ALL区别为index类型只遍历整个索引，比all快的多，因为索引文件比数据文件小得多。取出的列是索引包含的列，就会通过全索引扫描。
range: 索引范围扫描，一般在where子句中使用 < 、>、in、between等关键词。只检索给定范围的行，属于范围查找
ref: 查找非唯一性索引。属于精确查找、数据返回可能是多条
eq_ref: primary key 或 unique key索引的所有部分被连接使用，最多返回一条符合条件的记录。简单的select查询不会出现这种type。
const： mysql能对查询的某部分进行优化并将其转换为常量。用于primary key 或 unique key 的所有列与常数作比较时，所以表中数据只匹配到一行，速度快。
system： const的特例，仅返回一条数据的时候。平时不会出现，这个也可以忽略不计

6.possible-keys

mysql能使用哪个索引在该表中找到行数据

possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

7.key

显示mysqlkey实际使用的索引。 必然包含在possible_keys中

如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）

8.key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

注意，key_len的值可以告诉你在联合索引中 MySQL 会真正使用了哪些索引。

9.ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

连接匹配条件,如果走主键索引的话,该值为: const, 全表扫描的话,为null值

10.rows

mysql认为必须检查的用来返回请求数据的行数

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好

11.filtered

表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

12.Extra

关于mysql如何解析查询的额外信息

（1） Using where：说明SQL使用了where条件过滤数据。

（2） Using index：说明，SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录。

这类SQL语句往往性能较好。

（3） Using filesort：说明，得到所需结果集，需要对所有记录进行文件排序。

这类SQL语句性能极差， 需要进行优化。

典型的，在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。

（4） Using temporary：说明，需要建立临时表(temporary table)来暂存中间结果。

这类SQL语句性能较低，往往也需要进行优化。

典型的，group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

三、优化器

1.优化器的目的

优化器的目的是按照一定原则来得到他认为的目标SQL在当前情形下最有效的执行路径,优化器的目的是为了得到目标SQL的执行计划。按照 MySQL 认为最优的方案去执行。

2.优化器分类

传统关系型数据库里面的优化器分为CBO和RBO两种。

RBO— Rule_Based Potimizer 基于规则的优化器:

RBO :RBO所用的判断规则是一组内置的规则，这些规则是硬编码在数据库的编码中的，RBO会根据这些规则去从SQL诸多的路径中来选择一条作为执行计划（比如在RBO里面，有这么一条规则：有索引使用索引。那么所有带有索引的表在任何情况下都会走索引）所以，RBO现在被很多数据库抛弃（oracle默认是CBO，但是仍然保留RBO代码，MySQL只有CBO）

RBO最大问题在于硬编码在数据库里面的一系列固定规则，来决定执行计划。并没有考虑目标SQL中所涉及的对象的实际数量，实际数据的分布情况，这样一旦规则不适用于该SQL，那么很可能选出来的执行计划就不是最优执行计划了。

CBO—Cost_Based Potimizer 基于开销的优化器:

CBO :CBO在会从目标诸多的执行路径中选择一个成本最小的执行路径来作为执行计划。这里的成本他实际代表了MySQL根据相关统计信息计算出来目标SQL对应的步骤的IO，CPU等消耗。也就是意味着数据库里的成本实际上就是对于执行目标SQL所需要IO,CPU等资源的一个估计值。而成本值是根据索引，表，行的统计信息计算出来的。(计算过程比较复杂)