MySQL 执行计划详解

已于 2024-07-29 20:58:01 修改
阅读量2.2k 收藏 24
点赞数 26
分类专栏： mysql 文章标签： mysql 数据库
于 2024-07-29 20:35:27 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhangHP_123/article/details/140780387
版权

文章目录

一. 概念

有了慢查询后，需要对慢查询语句进行分析。一条查询语句经过MySQL查询优化器后，基于各种成本和规则优化后生成一个所谓的执行计划。

这个执行计划揭露了接下来执行查询的方式。比如多表连接查询的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。

EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，我们需要搞懂EXPLAIN的各个输出项字段的作用，从而可以针对性提升查询语句的性能。

通过EXPLAIN关键字可以模拟优化器执行SQL查询语句，进而知道MySQL如何处理SQL的。分析查询语句或者表结的性能瓶颈，可以通过EXPLAIN解析，如：

  1. 表的读取顺序
  2. 数据读取操作的操作类型
  3. 哪些索引会被使用
  4. 哪些索引实际被使用
  5. 使用索引实际的字节数
  6. 表之间的引用
  7. 每张表有多少行被优化器查询

二. 语法

执行计划的语句：EXPLAIN + SQL语句

EXPLAIN select * from table

除了select，其余的delete,update,insert,replace语句前都可以加EXPLAIN，用来查询语句的执行计划。

三. 详解各字段

先对explain输出各个列的作用大致罗列下：

  1. id：在一个大的查询语句中每个select关键字都对应一个唯一id。
  2. select_type：select *关键字对应的查询的类型。
  3. table：表名。
  4. partitions：匹配的分区信息。
  5. type：针对单表的访问方法。
  6. possible_keys：可能用懂啊的索引。
  7. key：实际用到的索引。
  8. key_len：实际使用到的索引长度。
  9. ref：当使用索引列等值查询时，与索引列进行等值匹配对象的信息。
  10. rows：预估需要扫描的行数。
  11. filtered：某个表经过搜索条件后过滤剩余记录条数的百分比。
  12. Extra：额外的信息。

1. id

id列编号是select的序列号。有几个select就有几个id。

  1. id相同，执行顺序由上到下

    explain select * from a inner join b on a.id = b.id

    在这里插入图片描述

  2. id不同，如果子查询，id的序号递增，id值越大优先级越高，越先执行。

  3. id有null最后执行。

    explain select * from a union select * from a;

    在这里插入图片描述

2. select_type

代表对应行是简单还是复杂的查询。

⭐️类型：

  1. SIMPLE：简单查询，查询不包含子查询和union。

    explain select * from a;

explain select * from a inner join b on a.id = b.id;

  2. PRIMARY：查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARY。
    在这里插入图片描述

  3. SUBQUERY：select、where之后包含了子查询，在select语句中出现的子查询语句，结果不依赖于外部查询（不在from语句中）。

  4. DEPENDENT SUBQUERY：指在select语句中出现的查询语句，结果依赖于外部查询
    在这里插入图片描述

  5. DEPENDENT SUBQUERY：指在select语句中出现的查询语句，结果依赖于外部查询

    在这里插入图片描述

  6. DERIVED：在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放在临时表中。

    派生表是在一个查询中定义的一个临时表，它由一个子查询生成，但不是作为查询的一部分返回，而是作为外部查询的一部分来引用。派生表通常用在FROM子句中，看起来像是一个普通的表名，但实际上是一个子查询的结果集。

    -- 关闭对衍生表合并优化
set session optimizer_switch='derived_merge=off';
-- 查看optimizer_switch参数
show variables like '%optimizer_switch%';
-- 
explain select * from (select * from a) as t; 
-- 还原表合并优化
set session optimizer_switch='derived_merge=on';

    在这里插入图片描述

  7. UNION：若第二个select出现在UNION后，则被标记为UNION

    explain select * from a union select * from a

  8. UNION RESULT：UNION关键字会将数据结果进行去重，会使用一个临时表，临时表的记录会被标记为UNION RESULT

    explain select * from a union select * from a

  9. DEPENDENT UNION ：当一个UNION操作符连接的查询中，至少有一个查询依赖于外部查询的某个值时，这样的查询被称为DEPENDENT UNION

    explain select * from a where id in (select id from a union select id from a)

    在这里插入图片描述

3. table

该行数据属于哪张表。

4. partitions

和分区相关，一般情况下查询语句的执行计划的partitions列的值都是NULL

5. type

执行计划的一条记录代表着MySQL对某个表的执行查询时的访问方法/访问类型.

其中type就表明这个访问方法/访问类型，较为重要的一个指标，结果值从最好到最坏排序：

常用的：

system > const > eq_ref > ref > range > index > all

全type的：

system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all。

一般来说至少达到range级别，最好达到ref。

⭐️类型：

  1. system：系统表，少来给你数据，往往不需要磁盘IO。或者当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory

  2. const：当我们根据主键或者唯一索引列等值匹配时。

    explain select * from a where id = 1

    在这里插入图片描述

  3. eq_ref：进行多表连接查询，被驱动表通过主键或唯一索引进行等着查询。

    explain select * from a join b on a.id = b.id

    在这里插入图片描述

  4. ref：普通二级索引等值查询。

    create index idx_index on a (user_name);

explain select * from a where user_name = ''

    在这里插入图片描述

  5. ref_or_null：命中索引时，查询条件除了等值查询，还包含null值查询。

    create index idx_index on a (user_name);

explain select * from a where user_name = '' or user_name is null;

    在这里插入图片描述

  6. index_merge：查询条件命中多个索引情况下

    create index idx_index on a (user_name);
create index idx_index2 on a (sex);

explain select * from a where user_name = '' or sex = 1;

    在这里插入图片描述

  7. unique_subquery：查询条件包含子查询，并且子查询的列可以进行主键等值匹配。

  8. index_subquery：查询条件包含子查询，并且子查询的列可以通过索引进行等值匹配。

  9. range：命中索引时，查询某一个范围内的结果。

    create index idx_index on a (user_name);

explain select * from a where user_name > '张';

    在这里插入图片描述

  10. index：直接在某个索引树上做条件判断，并且不需要回表。

create index idx_index on a (user_name, sex);

-- 虽然没满足左前缀，但可以遍历idx_index的B+Tree，找到sex等于1记录
-- 查询结果的user_name在联合索引上，不用回表，此时就可以用index
explain select user_name from a where sex = 1;

在这里插入图片描述

  1. all：直接遍历整个聚簇索引。

    explain select * from a

    在这里插入图片描述

6. possible_keys与key

possible_keys代表可能用到的索引。

key代表实际用到的索引。

7. key_len

key_len代表优化器决定使用某个索引执行查询时，该索引记录的最大长度。

计算原理：

对于固定长度类型的索引列来说，它实际占用的存储空间的最大长度为该索引的固定值。对于指定字符集的变长类型的索引列来说

如varchar(10)，使用的字符集是utf8（1个字符占用3个字节），那么该列实际占用的最大存储空间是10 * 3 + 2 + 1 = 33 byte。

如果可以存储null值，key_len比不可存储NULL多1个字节。对于varchar来说是长度可变字段，还会有2个字节的空间来存储该字段变长列的实际长度。

如int，为4个字节。

如datetime，为6个字节。

MySQL执行计划中输出key_len列柱用为了让我们区分某个使用联合索引的查询具体用了几个索引。

8. ref

这一列表明了在key列记录的索引中，表查找值所用到的列或常量

  1. const 或 NULL:
    • const: 当查询使用常量作为索引查找的键值时，ref 列将显示为 const。这通常发生在查询中使用了常量值（如数字或字符串字面量）作为WHERE子句的一部分。
    • NULL: 当查询不使用索引或者使用全表扫描时，ref 列将显示为 NULL。
  2. 表名.列名:
    • 当查询使用某个表的列作为索引查找的键值时，ref 列将显示为 <table_name>.<column_name>。这意味着查询使用了索引查找来定位行。
    • 如果查询涉及到多个表并且使用了某个表的列来查找另一个表中的行，则ref列将显示为前一个表的列名。
  3. func:
    • 当查询使用函数作为索引查找的键值时，ref 列将显示为 func。这通常发生在查询中使用了函数（如LOWER()或CONCAT()）来处理索引列。
  4. 索引名称:
    • 有时ref列也可能包含索引名称，这通常发生在查询使用了特定索引来查找行。

9. rows

如果查询优化器决定使用全表扫描方式对某个表执行查询时，执行计划的row列的代表的预计需要扫描的行数。

如果使用索引查询，rows代表预计扫描索引行数。

10. filtered

表示在JOIN操作中，前一个表的行数据被过滤的比例。

11. Extra

Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句

  • using index：使用覆盖索引会出现。
  • using where：在查找使用索引情况下，需要回表去查询所需数据。
  • using condition：查找使用了索引，但需要回表查询数据。
  • using index & using where：查找使用了索引，但需要的数据都在索引列中找到，所以不需要回表查询数据
mysql执行计划详解
lucky_ly的博客
12-31 2320
1.什么是执行计划 执行计划：就是mysql优化器在众多的执行路径中选择一条成本最低的路径作为执行计划，就是sql执行步骤的集合，执行计划的选择是根据数据的页，索引的页，以及统计信息等等，来推断出的，在某些情况下，mysql可能会选错最佳的执行计划。理解执行计划对于我们排查慢sql有着至关重要的作用。 2.查看mysql执行计划 在sql语句前面加上explain即可 执行计划中有几个比较关键的...
MySQL_执行计划详细说明
dbtg45121的博客
01-16 238
1 简要说明 id 表格查询的顺序编号。 降序查看，id相同的从上到下查查看。 id可以为null ，当table为( union ,m,n )类型的时候，id为null，这个时候，id的顺序为 m跟n的后面。 select_type 查询的方式 下文详细说明。 tabl...
MYSQL执行计划
最新发布
Romtrees的博客
09-27 542
前面文章，我们学习了 MySQL 慢日志相关内容，当我们筛选得到具体的慢 SQL 后，就要想办法去优化啦。优化 SQL 的第一步应该是读懂 SQL 的执行计划。本篇文章，我们一起来学习下 MySQL explain 执行计划相关知识。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。MySQL 为我们提供了 EXPLAIN 语句，来获取执行计划的相关信息。
写SQL要学会使用"执行计划"
chirendan6562的博客
11-26 472
能写SQL是程序员的基本功，而能写出性能优异的SQL是优秀程序员的必备技能。 可那些性能好的SQL语句是怎么写出来的？难道他们了解数据库底层的东西吗？ 其实了解数据库原理是一方面，更快捷的是借助“执行计划”（Explain Plan）来分析SQL语句执行的步骤及过程。不同的数据库...
MySQL执行计划详解
青山师
04-19 3342
执行计划是MySQL查询优化器生成的一组指令，用于执行SQL语句。在执行SQL语句时，MySQL会对SQL进行解析、优化、执行三个步骤。在优化阶段，MySQL查询优化器会生成一个执行计划，该计划被称为查询计划或执行计划。执行计划告诉MySQL执行SQL语句的具体步骤，包括表的连接方式、扫描方式、过滤条件等。执行计划是指MySQL根据用户请求所生成的一份查询计划，它决定了MySQL在执行SQL语句时所采取的操作方式，包括表的连接方式、索引的选择、访问方法等，进而影响着查询语句的执行效率。
mysql——执行计划
qq_51167391的博客
11-14 1852
ALL与index的区别就是ALL是从硬盘中读取，而index是从索引文件中读取」，ALL全表扫描意味着Mysql会从表的头到尾进行扫描，这时候表示通常需要增加索引来进行优化了，或者说是查询中并没有使用索引作为条件进行查询。在数据库的查询中不要使用（！id表示执行select查询语句的序号，它是sql执行的顺序的标识，sql按照id从大到小执行，id相同的为一组，从上到下执行。表示查询的列被索引覆盖，这个是查询性能比较高的体现，即所要查询的信息搜在索引里面可以得到，不用回表，索引被正确的使用。
MYSQL执行计划详解.pdf
04-21
根据提供的文件内容，我们可以从中提取出关于MySQL执行计划的相关知识点。以下是对知识点的详细介绍： ### 执行计划的ID - `id` 表示查询的标识符，用于区分执行计划中的查询块。在简单的查询中，所有行的`id`值...
MySQL执行计划详解及其应用技巧
09-14
本文档详细解析了MySQL执行计划的各种特性以及具体操作参数的含义，涵盖不同类型的SQL执行情况（简单查询、子查询等），以及各类访问方法的具体含义。通过解释ID、类型和附加信息等相关字段的功能来展示SQL查询执行...
mysql 执行计划详解
04-17
MySQL执行计划是数据库管理员和开发人员优化SQL查询性能的关键工具。它揭示了MySQL如何解析查询，确定数据检索的顺序，以及使用哪些索引。在本文中，我们将深入探讨MySQL执行计划，了解其重要性，如何生成，以及如何...
MySql执行计划(Explain关键字详解)
qq_45525848的博客
08-31 1295
MySql执行计划(Explain关键字详解)
MySQL的执行计划详解（Explain）
热门推荐
samker的博客
01-05 1万+
MySQL的执行计划详解（Explain）
MySQL执行计划
互联网知识分享
07-21 924
MySQL执行计划是数据库查询优化过程中的一部分，它是一个计划（plan）的集合，通过这个计划，MySQL可以选择最佳的执行方式来执行给定的查询语句。执行计划包含了查询的各个步骤、执行顺序、访问方式和查询所需的资源等信息。MySQL的执行计划由查询优化器生成，它会根据查询语句、表结构、索引等相关信息，尝试找到最佳的执行计划。优化器会考虑多种执行方式，并选择成本最低的执行计划作为最终的执行方式。通过执行计划，我们可以了解到查询的每个步骤和对应的成本，从而帮助我们优化查询的性能。
详解mysql执行计划
qq_43418737的博客
12-16 4538
数据库执行计划(explain)的详细解析
科普文：解读MySQL 执行计划explain
为无为，事无事，味无味。
08-02 1490
在排查mysql执行慢的过程中，前面文章中都有解释explain执行计划，这里单独拿出来解读一下。慢查询的确会导致很多问题，我们要如何优化慢查询呢？主要解决办法有：监控sql执行情况，发邮件、短信报警，便于快速识别慢查询sql打开数据库慢查询日志功能简化业务逻辑代码重构、优化异步处理sql优化索引优化如何查看某条sql的索引执行情况呢？没错，在sql前面加上explain。
mysql执行计划
qq_35597828的博客
03-21 1135
通过mysql执行计划可以查看SQL语句的具体执行过程，达到优化SQL的目的，以提高SQL语句执行效率 执行计划包含的信息 Column Meaning id The SELECT identifier select_type The SELECT type table The table for the output row partitions The matching partitions type The join type possible_keys The p
mysql 执行计划 详解
08-04
回答: 执行计划是MySQL查询优化器生成的一组指令，用于执行SQL语句。在执行SQL语句时，MySQL会对SQL进行解析、优化、执行三个步骤。在优化阶段，MySQL查询优化器会生成一个执行计划，该计划告诉MySQL执行SQL语句的具体步骤，包括表的连接方式、扫描方式、过滤条件等。[1]你可以通过使用EXPLAIN关键字或MySQL Workbench来查看执行计划。优化查询效率的方法包括索引优化、避免使用SELECT *、优化SQL语句等。在系统设计和架构中，数据库是必不可少的一环，优化数据库查询效率也是非常重要的一环。MySQL是一个流行的关系型数据库管理系统，使用执行计划可以帮助我们优化查询效率。[2][3]
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值