『Mysql进阶』Mysql explain详解(五)

已于 2024-10-10 18:32:41 修改
阅读量285 收藏 9
点赞数 12
分类专栏： msyql 文章标签： mysql 数据库
于 2024-10-10 18:12:52 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_46203834/article/details/142814399
版权

Explain 介绍

     EXPLAIN 语句提供有关 MySQL 如何执行语句的信息，使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 。

        我们根据 MySQL 的慢查询去记录一些执行时间比较久的SQL语句, 然后使用explain命令来查看这些SQL语句的执行计划, 根据执行计划内容，判断该SQL语句有没有使用上索引, 有没有做全表扫描等, 进而排查出SQL执行慢的原因。

官网介绍

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

Explain分析示例

  • CREATE TABLE `actor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
 INSERT INTO `actor` (`id`, `name`, `create_time`) VALUES (1,'a',NOW()), (2,'b',NOW()), (3,'c',NOW());


CREATE TABLE `film` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `actor_id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_actorid` (`actor_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
 INSERT INTO `film` (`id`, `actor_id`, `name`, `create_time`) VALUES (1,1,'一路向东',NOW());

explain中的列

 explain select * from actor;

1. id 列

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。id列越大执行优先级越高，id相同则从上往下执行。

  • id 相同，按table列由上至下顺序执行
  • id 不同，如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行。
  • id 想通不同同时存在，id如果相同，可以认为是一组，（本组内）从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。
  • 当使用union时，id会出现NULL值，同时table列会出现<unionM,N>的值，其表示id的值为M和N的行的联合。
2. select_type 列

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

  • simple：简单查询。查询不包含子查询和union

  • primary：复杂查询中最外层的 select

  • subquery：包含在 select 中的子查询（不在 from 子句中）

  • derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表(derived的英文含义)

  • union：在 union 中的第二个和随后的 select

  •  union result: 从union表获取结果的select, UNION的结果集
  • dependent union： UNION UNIONUNION中的第二个或以后的SELECT的SELECT的SELECT语句，取决于外部查询

  •  dependent subquery:  子查询中的第一个SELECT，依赖于外部查询，表示这个subquery的查询要受到外部表查询的影响

3. table 列

这一列表示 explain 的一行正在访问哪个表。

它也可以是以下值之一：

  • <unionM，N>：行引用ID值为M和N的行的并集。
  • <derivedN>：该行引用ID值为N的行的派生表结果。派生表可以由例如FROM子句中的子查询产生。
  • <subqueryN>：该行引用ID值为N的行的具体化子查询的结果。
4. partitions 列

使用的哪些分区（对于非分区表值为null）。

5. type 列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。

依次从最优到最差分别为：

system > const > eq_ref > ref >fulltext >ref_or_null >unique_subquery>index_subquery> range > index_merge>index > ALL

一般来说，得保证查询达到range级别，最好达到ref

explain select min(id) from actor

NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表.

  • system: 表中只有一行数据或者是空表。等于系统表，这是const类型的特列，平时不会出现，可以忽略不计 .

  • const : 使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type 是const。其他数据库也叫做唯一索引扫描。 

explain select * from actor where id=1;

  • eq_ref: primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。在连接表，被驱动表会出现.
explain select * from  actor  a  JOIN film b on a.id=b.actor_id

  • ref : 相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
explain select * from film where actor_id=1

  •  fulltext : 全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时， mysql不管代价，优先选择使用全文索引 .
  • ref_or_null : 与ref方法类似，只是增加了null值的比较。实际用的不多。
  • unique_subquery: 用于where中的in形式子查询，子查询返回不重复值唯一值
  • index_subquery: 用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复
    值，可以使用索引将子查询去重。
  • range : 范围扫描通常出现在 in(), between ,> ,= 等操作中。使用一个索引来检索给定范围的行。 
explain select * from actor where id >=1 and id <=3;

  • index_merge: 表示查询使用了两个以上的索引，最后取交集或者并集，常见 and ， or 的条件使 用了不同的索引.
  • index : 扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。(全叶子索引扫描，扫描的是二级索引) 
explain select actor_id from film;

  • ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

    explain select * from film;

6. possible_keys 列

这一列显示查询可能使用哪些索引来查找。

explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

7. key 列

查询真正使用到的索引

8. key_len 列

显示MySQL决定使用的索引size

key_len 计算规则:

  1. 索引字段，非NOT NULL，加1个字节。
  2. 定长字段：tinyint占1个字节、smallint占2个字节、int占4个字节、bitint占8个字节、date占3个字节、timestamp占4个字节，datetime占8个字节，char(n)占n个字符。
  3. 变长字段：varchar(n)占n个字符+2个字节。
  4. 不同的字符集，一个字符占用的字节数不同：
    • latin1编码，每个字符占用一个字节
    • gbk编码，每个字符占用两个字节
    • utf8编码，每个字符占用三个字节
    • utf8mb4编码，每个字符占用四个字节

例子

字段：phone varchar(20) DEFAULT NULL COMMENT ‘手机号’

条件：where phone=‘xxx’

通过explain查看key_len

utf8mb4编码下，key_len=83，即20*4+2+1

utf8编码下，key_len=63，即20*3+2+1

9. ref 列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

10. rows 列

显示此查询一共扫描了多少行，这个是一个估计值，不是精确的值。

11. filtered 列

表示此查询条件所过滤的数据的百分比

12. Extra 列

这一列展示的是额外信息。常见的重要值如下：

  • Using where：使用 where 语句来处理结果，表示MySQL将对InnoDB提取的结果在SQL Layer层进行过滤，过滤条件字段无索引； 

explain select * from film where name='一路向东';

  • Using index：使用覆盖索引-----不会回表

覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值

explain select actor_id from film where actor_id=1;

  •  Using index condition: 用不了索引, 但用了索引下推优化,使之可以使用到索引.
explain select * from film where actor_id>=1;

  • Using temporary: mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。(先扫描出来放在临时表，然后再去重)
explain select DISTINCT name from film;

  • Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

explain select * from film ORDER BY name;

  • Using join buffer (Block Nested Loop)：在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度 .
gorgor在码农
关注
专栏目录
【MySQL进阶】Explain关键字详解
2401_83045332的博客
08-05 644
PRIMARY：复杂查询中最外层查询，比如使用 union 或 union all 时，id 为 1 的记录 select_type 通常是 primary，如上图所示。eq_ref：基于主键或唯一索引连接两个表，对于每个索引键值，只有一条匹配记录，被驱动表的类型为 eq_ref，这种类型只出现在 join。range：范围扫描，检索指定范围的行，一般出现在 where 语句出现 between、< 、>、in 等的查询。索引使用的字节数，可通过该列计算查询中使用的索引的长度，主要用于联合索引。
MySQL进阶——EXPLAIN详解
发现问题，面对问题，分析问题，解决问题，总结问题
11-14 1450
EXPLAIN 命令是查看查询优化器如何决定执行 查询的主要方法。这个功能有局限性，并不总会说出真相，但它的输出 是可以获取的最好信息，值得花时间了解，因为可以学习到查询是如何 执行的。学会解释EXPLAIN 将帮助你了解MySQL优化器是如何工作的 在查询中每个表在输出中只有一行。如果查询是两个表的联接，那 么输出中将有两行。别名表单算为一个表，因此，如果把一个表与自己 联接，输出中也会有两行。
MySQL进阶系列： 一文详解explain各字段含义
yyyy11119的博客
08-30 2376
explain有何用处呢：为了知道优化SQL语句的执行，需要查看SQL语句的具体执行过程，以加快SQL语句的执行效率。 可以使用explain+SQL语句来模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。通过查看执行计划了解执行器是否按照我们想的那样处理SQL。 explain执行计划中包含的信息如下： id: 查询序列号 select_type: 查询类型 table: 表名或者别名 partitions: 匹配的分区 type: 访问类型 possible_keys: 可能用到
MySQL进阶之（七）EXPLAIN 详解
qq_50994235的博客
04-10 855
id 如果相同，可以认为是一组，自上往下顺序执行。在所有组中，id 值越大，优先级越高，越先执行。id 的每个值都代表一趟独立的查询，一个 sql 的查询趟数越少越好。
《Mysql进阶》-- explain详解和索引最佳实践
lmj3732018的博客
04-17 1万+
explain 两个变种 （1）explain extended：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过show warnings命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。 mysql> explain extended select...
【MySQL进阶】 explain 执行计划详解
Welcome,Everyone.
10-07 621
MySQL进阶之 explain 的应用1、什么是 explain2、explain 能干啥3、explain 怎么使用4、各字段的解释id 字段select_type 字段DERIVED 1、什么是 explain 使用 explain 关键字可以模拟 MySQL 优化器执行 SQL 语句，从而知道 MySQL 是如何处理我们的 SQL 语句的。 可以分析 SQL 查询语句或者表结构的性能瓶颈。 2、explain 能干啥 显示表的读取顺序 显示数据读取操作的类型 显示哪些索引可以使用 显示哪些索引被实
MySQL explain详解
莫等闲 白了少年头 空悲切
07-23 1597
MySQL 的 EXPLAIN 是一个用于查询优化的关键字。它用于分析和评估查询语句的执行计划，帮助开发者理解查询语句的性能问题以及优化查询的方式。使用关键字，可以获取关于查询执行计划的详细信息，包括表的访问顺序、连接方式、索引使用情况等，这些信息能够帮助你确定查询是否有效利用了索引，以及是否存在潜在的性能瓶颈。要使用执行以上语句后，MySQL会返回一个结果集，包含了查询执行计划的详细信息。该结果集中的每一行对应查询执行计划中的一步操作，你可以通过分析这些数据来确定查询的性能瓶颈以及优化的方向。
MYSQL explain详解
热门推荐
Coder
02-23 9万+
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 虽然这篇文章我写的很长，但看起来真的不会困啊，真的都是干货啊！！！！ 先解析一条sql语句，看出现什么内容 EXPLAINSELECTs.uid,s.username,s.name,f.email,f.mobile,f.phone,f.postalcode,f.address FROMuchome_spaceASs,uchome_spacefieldASf WHER...
MySQL-Explain详解
fsdfkjai的博客
02-10 6万+
MySQL-Explain详解
一文了解MySQL explain详解
时间如瑾 代码如诗
07-20 1021
MySQL 的 EXPLAIN 是一个用于查询优化的关键字。它用于分析和评估查询语句的执行计划，帮助开发者理解查询语句的性能问题以及优化查询的方式。使用EXPLAIN关键字，可以获取关于查询执行计划的详细信息，包括表的访问顺序、连接方式、索引使用情况等，这些信息能够帮助你确定查询是否有效利用了索引，以及是否存在潜在的性能瓶颈。要使用EXPLAIN执行以上语句后，MySQL会返回一个结果集，包含了查询执行计划的详细信息。
MySQL-Explain 详解
08-07
MySQL Explain 详解 MySQL Explain 是 MySQL 中的一个重要工具，用于解释和优化 SQL 查询语句。通过 Explain，可以了解 MySQL 是如何执行查询语句的，并且可以根据 Explain 的结果来优化查询语句。 一、用法 ...
mysql索引和explain的详解
12-14
MySQL中的索引是一种为了加速数据查询的特殊...总之，了解MySQL的索引原理和`EXPLAIN`的使用是提升数据库性能的关键。根据业务需求和数据特点，合理设计索引并利用`EXPLAIN`进行查询分析，可以有效地优化数据库的性能。
MySQL进阶系列： 一文详解explain各字段含义，分层展示的架构图
m0_64867896的博客
12-19 609
二，select_type列 ============================================================================ 主要用来分辨查询的类型，是普通查询还是联合查询还是子查询 1. sample: 简单的查询，不包含子查询和union mysql> explain select * from emp; primary: 查询中若包含任何复杂的子查询，最外层查询则被标记为Primary mysql> explain sel..
MySQL【知识改变命运】03
2402_84062064的博客
10-07 1582
表的创建
Mysql（索引与事务）
qq_63074244的博客
10-02 822
在早些年代会出现，A给B转了钱而B却并未收到，因此引入了事务中的回滚（指的是通过检查若没有执行成功就回退回去，比如A给B转了钱而B却并未收到时，那么就会触发回滚把A转出金额再返回给A）来解决这一问题。事务是把多个SQL打包成为一个整体，要么都执行，要么做到看起来都不执行的样子。原子性：保证一系列操作都能正确执行或者恢复如初一致性：事务执行之前之后，数据都不能太离谱持久性：事务做出的修改，都是在硬盘上持久保存的。服务器重启数据依然存在隔离性：解决数据库处理并非事务中出现的问题。
基于微信小程序的生鲜订购小程序系统(SpringBoot+Vue+Uniapp+MySQL)
源码好优多
10-06 1858
开发者不再需要手动配置大量的 XML 文件或繁琐的注解，框架提供了默认的配置，根据项目的依赖关系和约定，自动完成配置。系统测试主要是为了避免用户在使用时发生问题，增强用户体验感，为了不影响用户的使用，我们需要从多角度、多思路去考虑系统可能遇到的问题，通过不同的模拟场景来发现缺陷并解决问题。添加用户时，必填项不填，检验系统是否有非空检验；登录功能测试方案：当需要登入该系统时，通过账户密码等功能点进行验证，用户在输入时需要输入与数据库内存储的数据匹配的内容，当其中某项输入错误时系统将提示输入错误。
MySQL存储和处理XML数据
howard2005的专栏
10-02 466
MySQL不直接支持XML数据类型，但可以通过TEXT或BLOB类型字段存储XML数据，利用`ExtractValue`和`UpdateXML`函数进行查询和更新。首先，创建表并插入XML数据，然后通过XPath表达式提取信息。对于大型XML文件，推荐在应用层处理后存储结果。在操作XML数据时，确保使用正确的XPath表达式，并考虑性能影响。
mysql
最新发布
mnwl12_0的博客
10-09 916
事先经过编译并存储在数据库中的一段 SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。对视图进行增删改本质上就是对基表的修改，视图并不存储数据，当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。
MySQL 实验 10：数据查询（3）—— 聚合函数与分组查询
睿思达DBA
10-08 779
对MySQL查询条件的各种形式进行总结
mysql Explain 详解
05-19
MySQL的EXPLAIN是一个非常有用的工具，可以帮助开发人员优化查询语句并提高查询性能。通过执行EXPLAIN，可以了解查询的执行计划、表之间的连接方式以及使用的索引等信息。下面是一份比较详细的MySQL Explain解释： 1. id：查询标识符，表示查询中的每个SELECT子句或操作的唯一编号。 2. select_type：查询类型，表示MySQL查询的类型，例如简单查询、联合查询、子查询等。 3. table：表名，表示查询的表名。 4. partitions：分区情况，表示查询时使用了哪些分区。 5. type：访问类型，表示MySQL在表中查找行的方式，包括系统常量等。 6. possible_keys：可选索引，表示MySQL可以使用哪些索引来执行查询。 7. key：实际使用索引，表示MySQL实际使用的索引。 8. key_len：使用索引的长度，表示MySQL使用的索引的长度。 9. ref：参考列，表示MySQL在使用索引时参考的列。 10. rows：扫描的行数，表示MySQL扫描了多少行数据。 11. filtered：过滤比例，表示MySQL过滤掉了多少数据。 12. Extra：附加信息，表示MySQL执行查询时使用的额外信息，例如排序、分组等。 需要注意的是，EXPLAIN并不是完全准确的，它只是根据MySQL的统计信息估算出查询语句的执行计划。因此，在优化查询语句时，需要结合实际情况进行分析和优化。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值