MySQL系列文章之四：执行计划

最新推荐文章于 2023-07-21 10:19:40 发布

小阿飞_

最新推荐文章于 2023-07-21 10:19:40 发布

阅读量863

点赞数 7

分类专栏： MySQL 文章标签： mysql java 数据库

本文链接：https://blog.csdn.net/yifei_345678/article/details/125916766

版权

MySQL 专栏收录该内容

4 篇文章 1 订阅

订阅专栏

Hi ~o(*￣▽￣*)ブ大家好呀！ 我是【小阿飞_】😜

本期为大家带来MySQL系列文章之四：执行计划

本期精彩：带大家了解MySQL中的执行计划的同时讲解MySql中sql语句的执行过程，让大家认识到sql语句得以被执行的本质(●ˇ∀ˇ●)

MySQL中SQL语句执行过程

当你在MySQL中写下一行sql语句时，这行语句在程序中会经过下图的处理流程👇

上图的处理步骤大致如下(●'◡'●)

客户端发送一条查询给服务器
服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划
MySQL根据优化器生成的执行计划，再调用存储引擎的API来执行查询
将结果返回给客户端

MySQL优化器及分类

传统关系型数据库里面的优化器分为CBO和RBO两种👇
1、RBO--- Rule_Based Potimizer 基于规则的优化器

RBO所用的判断规则是一组内置的规则，这些规则是硬编码在数据库的编码中的，RBO会根据这些规则去从SQL诸多的路径中来选择一条作为执行计划（比如在RBO里面，有这么一条规则：有索引使用索引。那么所有带有索引的表在任何情况下都会走索引）所以，RBO现在被很多数据库抛弃（oracle默认是CBO,但是仍然保留RBO代码,MySQL只有CBO）
RBO最大问题在于硬编码在数据库里面的一系列固定规则，来决定执行计划。并没有考虑目标SQL中所涉及的对象的实际数量，实际数据的分布情况，这样一旦规则不适用于该SQL，那么很可能选出来的执行计划就不是最优执行计划了

2、CBO---Cost_Based Potimizer 基于成本的优化器

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

查询优化器大体执行流程👇

执行计划

什么是执行计划？

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理你的sql语句的。分析你的查询语句或是表结构的性能瓶颈。
语法: Explain + 你要分析性能的sql语句

执行计划的作用范围

        表的读取顺序
        数据读取操作的操作类型
        哪些索引可以使用

        哪些索引被实际使用
        表之间的引用
        每张表有多少行被优化器查

执行计划包含的信息

1、id ：获取select子句的操作表顺序，有几种情况👇

id 相同的情况下执行顺序是由上到下
id越大优先级越高,如果是子查询，ID 序列号会递增，id值越大，优先级越高，越先执行
id 相同又有不相同的，序列号大的会先执行，然后相同的从上到下执行

2、select_type ：查询的类别，主要用于区别普通查询，联合查询，子查询等的复杂查询

simple: 简单的select 查询，不包含子查询或者 union
primary: 查询中包含任何复杂的子部分，最外层查询则被标记
subquery: 在 select 或者 where 列表中包含了子查询
derived: 在from 列表中包含子查询被标记为 derived Mysql 会递归执行这些子查询，把结果放到临时表里
union: 若在第二个 select 中出现 union之后，则被标记为 union 若union包含在 from 子句的子查询中，外层 select 将被标记为 derived
union result: 从 union 表获取结果的 SELECT

3、 table ：显示这一行的数据是关于那个表的
4、type ：显示的是访问类型

5、补充o(*￣▽￣*)o👇

type是较为重要的一个指标，结果值从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all
一般来说，得保证查询至少达到range级别，最好能达到ref
system: 表中只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个可以忽略不计;
const: 表示通过索引一次就找到了，const用于比较primary key或者unqiue索引，因为只匹配一条数据，所以很快，如将主键置于where条件中，Mysql 就能将该查询转换一个常量;
eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见与主键或唯一索引扫描;
ref: 非唯一索引扫描，返回匹配某个单独值的所有行，常见于使用非唯一索查询，组合索引查询;
range: 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的 where 语句中出现了 between ,<,>,in 等查询这种范围扫描比全表扫描要好，因为它只需要开始与索引的某一点，而结束与另一点，不用扫描全部索引。
ALL：全表扫描;
index：扫描全部索引树;
NULL： MySQL在优化过程中分解语句，执行时甚至不用访问表或索引;

6、possible_keys

指出MySQL能使用哪个索引在该表中找到行。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引，简而言之：可能使用的key（索引）

7、 key：实际上使用的索引，如果没用索引，则为NULL，查询中若使用了覆盖索引，则该索引和查询的select 字段重叠
8、key_len：显示MySQL决定使用的键长度。表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。如果键是NULL，长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。
9、ref：显示哪个字段或常数与key一起被使用

10、Extra：包含不合适在其他列中显示但十分重要的额外信息👇

Using index：此值表示mysql将使用覆盖索引，以避免访问表
Using where mysql：将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当(并且如果)它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”,有时“Using where”的出现就是一个暗示：查询可受益于不同的索引
Using temporary：mysql 对查询结果排序时会使用临时表
Using filesort：mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成

11、rows：这个数表示mysql要遍历多少数据才能找到，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，在innodb上可能是不准确的

查询缓存

MySQL查询缓存保存查询返回的完整结构。当查询命中该缓存时，MySQL会立刻返回结果，跳过了解析、优化和执行阶段。查询缓存系统会跟踪查询中涉及的每个表，如果这些表发生了变化，那么和这个表相关的所有缓存数据都将失效
MySQL将缓存存放在一个引用表中，通过一个哈希值引用，这个哈希值包括了以下因素，即查询本身、当前要查询的数据库、客户端协议的版本等一些其他可能影响返回结果的信息。当判断缓存是否命中时，MySQL不会进行解析查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。所以，任何字符上的不同，例如空格、注解等都会导致缓存的不命中。
当查询语句中有一些不确定的数据时，则不会被缓存。例如包含函数NOW()或CURRENT_DATE()的查询不会缓存。包含任何用户自定义函数，存储函数，用户变量，临时表，mysql数据库中的系统表或者包含任何列级别权限的表，都不会被缓存。有一点需要注意，MySQL并不是会因为查询中包含一个不确定的函数而不检查查询缓存，因为检查查询缓存之前，MySQL不会解析查询语句，所以也无法知道语句中是否有不确定的函数。事实则是，如果查询语句中包含任何的不确定的函数，那么其查询结果不会被缓存，因为查询缓存中也无法找到对应的缓存结果