Mysql篇-语句执行计划详解(explain)

概述

使用 explain 输出 SELECT 语句执行的详细信息，包括以下信息：

  • 表的加载顺序

  • sql 的查询类型

  • 可能用到哪些索引，实际上用到哪些索引

  • 读取的行数

Explain 执行计划包含字段信息如下：分别是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra 12个字段。

通过explain extended + show warnings可以在原本explain的基础上额外提供一些查询优化的信息，得到优化以后的可能的查询语句（不一定是最终优化的结果）。

测试环境：

CREATE TABLE `blog` (`blog_id` int NOT NULL AUTO_INCREMENT COMMENT '唯一博文id--主键',`blog_title` varchar(255) NOT NULL COMMENT '博文标题',`blog_body` text NOT NULL COMMENT '博文内容',`blog_time` datetime NOT NULL COMMENT '博文发布时间',`update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,`blog_state` int NOT NULL COMMENT '博文状态--0 删除 1正常',`user_id` int NOT NULL COMMENT '用户id',PRIMARY KEY (`blog_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8CREATE TABLE `user` (`user_id` int NOT NULL AUTO_INCREMENT COMMENT '用户唯一id--主键',`user_name` varchar(30) NOT NULL COMMENT '用户名--不能重复',`user_password` varchar(255) NOT NULL COMMENT '用户密码',PRIMARY KEY (`user_id`),KEY `name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8CREATE TABLE `discuss` (`discuss_id` int NOT NULL AUTO_INCREMENT COMMENT '评论唯一id',`discuss_body` varchar(255) NOT NULL COMMENT '评论内容',`discuss_time` datetime NOT NULL COMMENT '评论时间',`user_id` int NOT NULL COMMENT '用户id',`blog_id` int NOT NULL COMMENT '博文id',PRIMARY KEY (`discuss_id`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8

id

表示查询中执行select子句或者操作表的顺序，id的值越大，代表优先级越高，越先执行

explain select discuss_body 
from discuss 
where blog_id = (select blog_id from blog where user_id = (select user_id from user where user_name = 'admin'));

三个表依次嵌套，发现最里层的子查询 id最大，最先执行。

select_type

表示 select 查询的类型，主要是用于区分各种复杂的查询，例如：普通查询、联合查询、子查询等。

  • SIMPLE：表示最简单的 select 查询语句，在查询中不包含子查询或者交并差集等操作。

  • PRIMARY：查询中最外层的SELECT（存在子查询的外层的表操作为PRIMARY）。

  • SUBQUERY：子查询中首个SELECT。

  • DERIVED：被驱动的SELECT子查询（子查询位于FROM子句）。

  • UNION：在SELECT之后使用了UNION

table

查询的表名，并不一定是真实存在的表，有别名显示别名，也可能为临时表。当from子句中有子查询时，table列是<derivenN>的格式，表示当前查询依赖 id为N的查询，会先执行 id为N的查询。

partitions

查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表命中的分区情况。

type

查询使用了何种类型，它在 SQL优化中是一个非常重要的指标

访问效率：const > eq_ref > ref > range > index > ALL

system

当表仅有一行记录时（系统表），数据量很少，往往不需要进行磁盘IO，速度非常快。比如，Mysql系统表proxies_priv在Mysql服务启动时候已经加载在内存中，对这个表进行查询不需要进行磁盘 IO。

const

单表操作的时候，查询使用了主键或者唯一索引。

eq_ref

多表关联查询的时候，主键和唯一索引作为关联条件。如下图的sql，对于user表（外循环）的每一行，user_role表（内循环）只有一行满足join条件，只要查找到这行记录，就会跳出内循环，继续外循环的下一轮查询。

ref

查找条件列使用了索引而且不为主键和唯一索引。虽然使用了索引，但该索引列的值并不唯一，这样即使使用索引查找到了第一条数据，仍然不能停止，要在目标值附近进行小范围扫描。但它的好处是不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内做扫描。

ref_or_null

类似 ref，会额外搜索包含NULL值的行

index_merge

使用了索引合并优化方法，查询使用了两个以上的索引。新建comment表，id为主键，value_id为非唯一索引，执行explain select content from comment where value_id = 1181000 and id > 1000;，执行结果显示查询同时使用了id和value_id索引，type列的值为index_merge。

range

有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。像between、and、>、<、in和or都是范围索引扫描。

index

index包括select索引列，order by主键两种情况。

order by主键。这种情况会按照索引顺序全表扫描数据，拿到的数据是按照主键排好序的，不需要额外进行排序。

select索引列。type为index，而且extra字段为using index，也称这种情况为索引覆盖。所需要取的数据都在索引列，无需回表查询。

all

全表扫描，查询没有用到索引，性能最差。

possible_keys

此次查询中可能选用的索引。但这个索引并不定一会是最终查询数据时所被用到的索引。

key

此次查询中确切使用到的索引

ref

ref 列显示使用哪个列或常数与key一起从表中选择数据行。常见的值有const、func、NULL、具体字段名。当 key 列为 NULL，即不使用索引时。如果值是func，则使用的值是某个函数的结果。

以下SQL的执行计划ref为const，因为使用了组合索引(user_id, blog_id)，where user_id = 13中13为常量

mysql> explain select blog_id from user_like where user_id = 13;
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user_like | NULL       | ref  | ul1,ul2       | ul1  | 4       | const |    2 |   100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+

而下面这个SQL的执行计划ref值为NULL，因为key为NULL，查询没有用到索引。

mysql> explain select user_id from user_like where status = 1;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_like | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

rows

估算要找到所需的记录，需要读取的行数。评估SQL 性能的一个比较重要的数据，mysql需要扫描的行数，很直观的显示 SQL 性能的好坏，一般情况下 rows 值越小越好

filtered

存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例

extra

表示额外的信息说明。为了方便测试，这里新建两张表。

CREATE TABLE `t_order` (`id` int NOT NULL AUTO_INCREMENT,`user_id` int DEFAULT NULL,`order_id` int DEFAULT NULL,`order_status` tinyint DEFAULT NULL,`create_date` datetime DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_userid_order_id_createdate` (`user_id`,`order_id`,`create_date`)
) ENGINE=InnoDB AUTO_INCREMENT=99 DEFAULT CHARSET=utf8CREATE TABLE `t_orderdetail` (`id` int NOT NULL AUTO_INCREMENT,`order_id` int DEFAULT NULL,`product_name` varchar(100) DEFAULT NULL,`cnt` int DEFAULT NULL,`create_date` datetime DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_orderid_productname` (`order_id`,`product_name`)
) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8

using where

表示在查询过程中使用了WHERE条件进行数据过滤。当一 个查询中包含WHERE条件时，MySQL会根据该条件过滤出满足条件的数据行，然后再进行后续的操作。这个过程 就被称为"Using Where”。

表示查询的列未被索引覆盖，，且where筛选条件是索引列前导列的一个范围，或者是索引列的非前导列，或者是非索引列。对存储引擎返回的结果进行过滤（Post-filter，后过滤），一般发生在MySQL服务器，而不是存储引擎层，因此需要回表查询数据。

using index

查询的列被索引覆盖，并且where筛选条件符合最左前缀原则，通过索引查找就能直接找到符合条件的数据，不需要回表查询数据。

Using where&Using index

查询的列被索引覆盖，但无法通过索引查找找到符合条件的数据，不过可以通过索引扫描找到符合条件的数据，也不需要回表查询数据。

包括两种情况（组合索引为(user_id, orde)）：

where筛选条件不符合最左前缀原则

where筛选条件是索引列前导列的一个范围

null

查询的列未被索引覆盖，并且where筛选条件是索引的前导列，也就是用到了索引，但是部分字段未被索引覆盖，必须回表查询这些字段，Extra中为NULL。

using index condition

索引下推（index condition pushdown，ICP），先使用where条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。

对于联合索引（a, b），在执行 select * from table where a > 1 and b = 2 语句的时候，只有 a 字段能用到索引，那在联合索引的 B+Tree 找到第一个满足条件的主键值（ID 为 2）后，还需要判断其他条件是否满足（看 b 是否等于 2），那是在联合索引里判断？还是回主键索引去判断呢？MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

不使用ICP的情况（set optimizer_switch='index_condition_pushdown=off'），如下图，在步骤4中，没有使用where条件过滤索引：

使用ICP的情况（set optimizer_switch='index_condition_pushdown=on'）：

下面的例子使用了ICP：

explain select user_id, order_id, order_status from t_order where user_id > 1 and user_id < 5\G;

关掉ICP之后（set optimizer_switch='index_condition_pushdown=off'），可以看到extra列为using where，不会使用索引下推。

using temporary

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

filesort

文件排序。表示无法利用索引完成排序操作，以下情况会导致filesort：

  • order by 的字段不是索引字段

  • select 查询字段不全是索引字段

  • select 查询字段都是索引字段，但是 order by 字段和索引字段的顺序不一致

using join buffer

Block Nested Loop，需要进行嵌套循环计算。两个关联表join，关联字段均未建立索引，就会出现这种情况。比如内层和外层的type均为ALL，rows均为4，需要循环进行4*4次计算。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。

文章转载自：Seven

原文链接：https://www.cnblogs.com/seven97-top/p/18554001

体验地址：引迈 - JNPF快速开发平台_低代码开发平台_零代码开发平台_流程设计器_表单引擎_工作流引擎_软件架构

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.xdnf.cn/news/20031.html

如若内容造成侵权/违法违规/事实不符，请联系一条长河网进行投诉反馈，一经查实，立即删除！

相关文章

.net 7.0 解决“The keyword field is required”的问题

.net 7.0 解决“The keyword field is required”的问题

在 .net 3.1项目的时候&#xff0c;使用 keyword 做 API 接口的模糊匹配&#xff0c;能够传入keyword “” 进行整表查询。但当我在 .net 7.0 项目中这么使用的时候&#xff0c;传入 keyword 不为空时能够进行匹配&#xff0c;但是当我传入 keyword “” 的时候就报错 “The …
阅读更多...
高效语言模型 Parler-TTS 上线，一键完成文本转语音

高效语言模型 Parler-TTS 上线，一键完成文本转语音

Parler-TTS 是一种轻量级的文本转语音 (TTS) 模型&#xff0c;可以生成具有给定说话者风格的高质量、自然语音&#xff0c;自由度及创新性非常高&#xff0c;并且可以通过 Prompt 控制说话者的性别、音色、语调以及所处的场景&#xff08;室内、室外、马路上、音乐厅等&#xf…
阅读更多...
网络安全与防范

网络安全与防范

1.重要性 随着互联网的发达&#xff0c;各种WEB应用也变得越来越复杂&#xff0c;满足了用户的各种需求&#xff0c;但是随之而来的就是各种网络安全的问题。了解常见的前端攻击形式和保护我们的网站不受攻击是我们每个优秀fronter必备的技能。 2.分类 XSS攻击CSRF攻击网络劫…
阅读更多...
JavaWeb——Maven、web入门

JavaWeb——Maven、web入门

1. maven maven是一款用于管理和构建Java项目的工具&#xff0c;它基于项目对象模型&#xff08;POM—Project Object Model&#xff09;的概念&#xff0c;通过一小段描述信息来管理项目的构建。 1.1. 作用 1.1.1. 依赖管理 方便快捷的管理项目依赖的资源&#xff08;jar包…
阅读更多...
【前端学习笔记】Javascript学习二（运算符、数组、函数）

【前端学习笔记】Javascript学习二（运算符、数组、函数）

一、运算符 运算符&#xff08;operator&#xff09;也被称为操作符&#xff0c;是用于实现赋值、比较和执行算数运算等功能的符号。 JavaScript中常用的运算符有&#xff1a; 算数运算符、递增和递减运算符、比较运算符、逻辑运算符、赋值运算符 算数运算符&#xff1a; 、-…
阅读更多...
【开源免费】基于Vue和SpringBoot的智慧食堂系统（附论文）

【开源免费】基于Vue和SpringBoot的智慧食堂系统（附论文）

本文项目编号 T 629 &#xff0c;文末自助获取源码 \color{red}{T629&#xff0c;文末自助获取源码} T629&#xff0c;文末自助获取源码 随着Internet的发展&#xff0c;人们的日常生活已经离不开网络。未来人们的生活与工作将变得越来越数字化&#xff0c;网络化和电子化。网…
阅读更多...
基因组之全局互作热图可视化

基因组之全局互作热图可视化

引言 PlotHiC 是一个专为 Hi-C 数据可视化分析而设计的 Python 包。Hi-C 技术是一种能够检测染色体三维结构的实验方法&#xff0c;它能揭示 DNA 在细胞核内的三维组织结构。为了更好地展示和解释这些复杂的数据&#xff0c;PlotHiC[1] 可以帮助用户方便地绘制Hi-C 数据的热图。…
阅读更多...
道本科技智慧合同管理平台，采用数字化技术帮助企业建立全生命周期的合同管理模式。

道本科技智慧合同管理平台，采用数字化技术帮助企业建立全生命周期的合同管理模式。

作为专业的企业合同管理平台建设专家&#xff0c;我们拥有丰富的实施经验和专业技术团队&#xff0c;致力于帮助企业搭建高效、安全的合同管理系统。我们的解决方案涵盖合同起草、审批、存储、分析和报告等多个环节&#xff0c;能够满足不同企业的多样化需求。 选择我们&#…
阅读更多...
AmazonS3集成minio实现https访问

AmazonS3集成minio实现https访问

最近系统全面升级到https&#xff0c;之前AmazonS3大文件分片上传直接使用http://ip:9000访问minio的方式已然行不通&#xff0c;https服务器访问http资源会报Mixed Content混合内容错误。 一般有两种解决方案&#xff0c;一是升级minio服务&#xff0c;配置ssl证书&#xff0c…
阅读更多...
QGIS使用WMS图层

QGIS使用WMS图层

目录 参考链接 参考链接 [1] 使用 WMS 数据 &#xff08;QGIS3&#xff09; 2023.8&#xff1b;
阅读更多...
华为防火墙技术基本概念学习笔记

华为防火墙技术基本概念学习笔记

1.防火墙概述 1.1防火墙与交换机、路由器对比 路由器与交换机的本质是转发&#xff0c;防火墙的本质是控制。 防火墙与路由器、交换机是有区别的。路由器用来连接不同的网络&#xff0c;通过路由协议保证互联互通&#xff0c;确保将报文转发到目的地;交换机则通常用来组建局域…
阅读更多...
面向FWA市场！移远通信高性能5G-A模组RG650V-NA通过北美两大重要运营商认证

面向FWA市场！移远通信高性能5G-A模组RG650V-NA通过北美两大重要运营商认证

近日&#xff0c;全球领先的物联网整体解决方案供应商移远通信宣布&#xff0c;其旗下符合3GPP R17标准的新一代5G-A模组RG650V-NA成功通过了北美两家重要运营商认证。凭借高速度、大容量、低延迟、高可靠等优势&#xff0c;该模组可满足CPE、家庭/企业网关、移动热点、高清视频…
阅读更多...
idea maven 重新构建索引

idea maven 重新构建索引

当设置maven仓库为离线模式的时候&#xff0c;会出现一些问题。 比如本地的仓库被各种方式手动更新之后&#xff0c; 举例&#xff1a;我需要一个spring的包&#xff0c;在pmo文件中写好了引入包的代码 但是由于是离线模式没有办法触发自动下载&#xff0c;那么这个时候我可以…
阅读更多...
React（二）

React（二）

文章目录 项目地址七、数据流7.1 子组件传递数据给父组件7.1.1 方式一：給父设置回调函数，传递给子7.1.2 方式二：直接将父的setState传递给子7.2 给props传递jsx7.2.1 方式一：直接传递组件给子类7.2.2 方式二：传递函数给子组件7.3 props类型验证7.4 props的多层传递7.5 cla…
阅读更多...
项目管理的核心指南：四管八理

项目管理的核心指南：四管八理

01项目管理核心&#xff1a;四管八理 项目管理的复杂性在于其多变的细节&#xff0c;但一旦掌握了核心框架和方法论&#xff0c;便能轻松应对。以下是项目管理的“四管八理”框架&#xff0c;旨在帮助项目经理构建自己的管理方法论。 02项目管理“四管” 1.团队协调 项目成功…
阅读更多...
消防设施操作员高频考点

消防设施操作员高频考点

1、职业是指从业人员为获取主要生活来源所从事的社会工作类别。&#xff08;正确&#xff09; 2、职业活动以获得现金或实物等报酬为目的&#xff0c;这属于职业特征的&#xff08;A&#xff09;。 A、目的性 B、社会性 C、稳定性 D、规范性 解析&#xff1a;…
阅读更多...
传输层协议TCP

传输层协议TCP

一.TCP协议格式 对于传输层协议我们之前是学过了UDP&#xff0c;对于传输层协议是存在了一定的了解的&#xff0c;所以现在我们再来看TCP协议格式&#xff1a; 我们之前学过UDP的报文格式&#xff0c;所以源端口和目的端口是不需要进行再次讲解的&#xff0c;对于32序号和确认序…
阅读更多...
【面试题】接口怎么测试？如何定位前后端的Bug？

【面试题】接口怎么测试？如何定位前后端的Bug？

接口怎么测试&#xff1f; 接口测试用来验证不同软件组件之间的交互是否正常。包括验证数据传输&#xff0c;参数传递&#xff0c;我在多个项目中有过测试接口的经验。&#xff08;… 当进行接口测试时&#xff0c;会使用Postman和Python的Requests库。首先根据接口文档设计测…
阅读更多...
【单点知识】基于PyTorch讲解自动编码器（Autoencoder）及其变种

【单点知识】基于PyTorch讲解自动编码器（Autoencoder）及其变种

文章目录 0. 前言1. 自动编码器的基本概念1.1 定义1.2 目标1.3 结构 2. PyTorch实现自动编码器2.1 导入必要的库2.2 定义自动编码器模型2.3 加载数据2.4 训练自动编码器 3. 自动编码器的意义4. 自动编码器的应用4.1 图像处理4.2自然语言处理&#xff1a;4.3推荐系统&#xff1a…
阅读更多...
Redis篇

Redis篇

Redis使用场景 一、缓存穿透: 一个get请求: api/news/getById/1 1.缓存穿透:查询一个不存在的数据&#xff0c;mysql查询不到数据也不会直接写入缓存&#xff0c;导致每次请求都直接请求查询数据库&#xff0c;导致数据库的压力增大从而宕机 2.解决方案一:缓存空数据&#x…
阅读更多...
最新文章

Copyright @ 2022~2023