Mysql explain 优化解析

explain 解释

在这里插入图片描述

select_type 效率对比

MySQL 中 EXPLAIN 语句的 select_type 列描述了查询的类型,不同的 select_type 类型在效率上会有所差异。下面我们来比较一下各种 select_type 的效率:

  1. SIMPLE:
    这是最简单的查询类型,表示查询不包含子查询或 UNION 操作。
    这种查询通常是最高效的,因为 MySQL 可以更好地优化执行计划。
    当查询只涉及一个表时,select_type 就会显示为 SIMPLE。

explain select * from user where uid=1;
在这里插入图片描述

  1. PRIMARY:
    这种查询类型表示最外层的查询。
    与 SIMPLE 类型相比,它可能会稍微低效一些,因为可能包含子查询。
    当查询中包含子查询时,子查询的 select_type 会显示为 SUBQUERY。

explain select * from (select * from user where uid=1)b
在这里插入图片描述

  1. SUBQUERY:
    这种查询类型表示作为独立子查询执行的查询块。
    子查询的效率通常比外层查询低,因为它需要单独执行并返回结果。
    子查询可能会在外层查询中多次使用,每次都需要重新执行,因此效率较低。

explain select * from groups where gid =(select gid from user where uid=1)
在这里插入图片描述

  1. DERIVED:
    这种查询类型表示从 FROM 子句的结果集中派生出来的临时表。
    这种查询通常比较低效,因为需要在查询执行时动态计算临时表。
    使用临时表可能会导致 MySQL 使用 Using temporary; Using filesort 策略,从而降低查询效率。

explain select * from (select * from user where uid=1)b
在这里插入图片描述

  1. UNION:
    这种查询类型表示 UNION 操作,用于合并多个查询结果集。
    UNION 操作通常比较低效,因为需要合并多个结果集。
    如果 UNION 中的子查询可以独立执行,可以考虑将它们拆分成多个查询,然后在代码中进行合并。

explain select * from user where uid=1 union select * from user where uid=2
在这里插入图片描述

  1. DEPENDENT UNION
    依赖性(DEPENDENT): 这个子查询依赖于外层查询的结果。也就是说,子查询的执行需要依赖外层查询的结果。
    DEPENDENT UNION(从属联合)与DEPENDENT SUBQUERY(依赖子查询):
    当union作为子查询时，其中第二个union的select_type就是DEPENDENT UNION。第一个子查询的select_type则是DEPENDENT SUBQUERY。

在这里插入图片描述

  1. UNION RESULT:
    这种查询类型表示 UNION 操作的结果。
    这种查询通常是最低效的,因为需要额外的合并操作。
    如果可以,尽量避免使用 UNION。

explain select * from user where uid=1 union select * from user where uid=2
在这里插入图片描述

总的来说,效率从高到低的顺序是:

SIMPLE > PRIMARY > SUBQUERY > DERIVED > UNION > DEPENDENT UNION > UNION RESULT

当然,实际的效率还受到其他因素的影响,如表的大小、索引情况、查询条件等。因此在实际使用中,我们还需要通过 EXPLAIN 语句分析具体的查询计划,并根据结果进行针对性的优化。

同时,也要注意查询的语义和可读性。有时为了提高效率,可能需要牺牲一些查询的可读性,这需要权衡取舍。

总之,在优化 MySQL 查询时,不仅要关注 select_type 的效率,还要综合考虑其他因素,并进行适当的优化。

type 列

TYPE含义解释

在这里插入图片描述

TYPE效率对比

MySQL 中 EXPLAIN 语句的 type 列描述了表访问的类型,这个列的值可以反映查询的效率。以下是 type 列各个值的含义:
好的,那我们再深入探讨一下 MySQL 中 EXPLAIN 语句的 type 列各个值的更多细节:

  1. system:
    这是一种特殊的 const 类型,表示表中只有一条记录。
    这种类型的访问速度是最快的,因为只需要读取一条记录。
    通常出现在使用常量表的查询中,比如使用 LIMIT 1 的查询。
  2. const:
    当查询能够在查询一次后就确定结果时,表示"constant"。
    典型的例子是当查询的 WHERE 子句使用主键或唯一索引时,MySQL 能在查询一次之后就确定结果。
    这种访问类型的速度非常快,因为它只需要读取一次记录。
  3. eq_ref:
    当查询使用主键或唯一索引时,对于每个索引键,表中只有一条记录与之匹配。
    这种访问类型的效率仅次于 const,是一种非常高效的访问方式。
    常见于多表连接中根据主键或唯一索引列进行关联的情况。
  4. ref:
    当查询使用非唯一索引或者触发了部分索引列(比如最左前缀)时,返回匹配某个单值的所有行。
    这种访问类型的效率略低于 eq_ref,但仍然较为高效。
    常见于使用非唯一索引进行关联查询的情况。
  5. range:
    当使用索引来检索某个范围的记录时,该访问类型就会被使用。
    比如 WHERE col BETWEEN 10 AND 20 或 WHERE col IN (10, 20, 30)。
    这种访问方式需要检索索引中的部分键值,因此效率比 ref 稍低。
  6. index:
    当 MySQL 决定全表扫描要比使用等值或范围索引快时, 并且索引覆盖所需要的列（包括在查询和条件中）时，使用索引树来遍历数据。
    这种方式虽然比全表扫描快,但比使用传统的索引扫描慢。
    通常出现在查询的 WHERE 子句未能有效利用索引的情况。
  7. ALL:
    这是最差的访问类型,表示需要进行完整的表扫描。
    通常情况下,应该尽量避免查询出现这种访问类型。
    如果出现这种情况,通常意味着需要为相关列创建索引来优化查询。

总的来说,type 列的取值越往后,查询的效率就越低。提高查询效率的一个重要方法,就是尽量使用更高效的访问类型,如const、eq_ref、ref等。一般来说，至少保证查询达到range级别，最好达到ref。这需要为相关列建立合适的索引,并根据查询的条件进行针对性的优化。

实例分析

调优思路

  • 拆分sql，并发查询出符合标签的group_id, 效果不理想
  • 干掉多余的subquery，有效果
  • in转换成join，效果不理想，跟数据量、数据分布、索引情况都有关系
    • 当数据量巨大（百万以上）、且数据散列分布均匀时，此时应该采用join
    • 大数据量不大或者数据分布聚集时，此时in效率更好
  • 减少子查询，减少派生临时表，效果立竿见影

优化前后对比

优化前:

SELECT t1.*,t2.*
FROM(SELECT a.*FROM syyy_dest aWHERE a.del_flag = 0AND a.id IN(SELECT t3.group_idFROM(SELECT group_id,group_concat(tag_id)AS tag_idsFROM syyy_group_tagWHERE delete_flag = 0AND tag_id IN ('123')GROUP BY group_idHAVING find_in_set('123', tag_ids)) t3) ) t1
LEFT JOIN(SELECT group_id,group_concat(category_name, ':', tag_name) AS tagNameFROM syyy_group_tag gtLEFT JOIN syyy_tag c ON gt.tag_id = c.idLEFT JOIN syyy_tag_category stc ON c.tag_category_id = stc.idWHERE gt.delete_flag = 0GROUP BY group_id) t2 ON t1.id = t2.group_id
ORDER BY t1.id DESC
LIMIT 10;

优化前查询结果：
在这里插入图片描述

优化后:

SELECTa.*,gt.group_id,group_concat(stc.category_name, ':', c.tag_name) as tagNameFROM  syyy_dest aLEFT JOIN (SELECT group_id, tag_id FROM syyy_group_tag WHERE delete_flag = 0) gtON a.id = gt.group_idLEFT JOIN syyy_tag cON gt.tag_id = c.idLEFT JOIN syyy_tag_category stcON c.tag_category_id  = stc.idWHERE a.del_flag = 0         and a.id in(select t3.group_id from(select group_id , group_concat(tag_id)as tag_ids from syyy_group_tagwhere delete_flag = 0and tag_id in(  123) group by group_idhavingfind_in_set( 123, tag_ids)) t3 where 1=1) GROUP BY a.idorder by a.id desc LIMIT 10

优化后查询结果：在这里插入图片描述

减少派生临时表字查询的优化分析

因为需要在查询执行时动态计算临时表，因此这种查询通常比较低效

优化前

explain
SELECT t1.*,t2.tag_name
FROM(SELECT a.*FROM syyy_dest aWHERE a.del_flag = 0 ) t1
LEFT JOIN(SELECT group_id,group_concat(category_name, ':', tag_name) AS tag_nameFROM syyy_group_tag gtLEFT JOIN syyy_tag c ON gt.tag_id = c.idLEFT JOIN syyy_tag_category stc ON c.tag_category_id = stc.idWHERE gt.delete_flag = 0GROUP BY group_id) t2 ON t1.id = t2.group_id
ORDER BY t1.id DESC

执行计划：
在这里插入图片描述

优化后

explain
SELECTa.*,group_concat(stc.category_name, ':', c.tag_name) as tag_nameFROM  syyy_dest aLEFT JOIN (SELECT group_id, tag_id FROM syyy_group_tag WHERE delete_flag = 0) gtON a.id = gt.group_idLEFT JOIN syyy_tag cON gt.tag_id = c.idLEFT JOIN syyy_tag_category stcON c.tag_category_id  = stc.idWHERE a.del_flag = 0
GROUP BY a.id
ORDER BY a.id desc

执行计划：
在这里插入图片描述

优化分析：

  1. 优化后减少了一次子查询，减少了派生临时表的生成

  2. select_type优化后为smiple,性能最优

  3. 优化后连接类型type，c和stc表为eq_ref，因为使用了主键连接；syyy_group_tag为ref，因为虽然使用了唯一建，但是只是触发了部分索引列(最左前缀)，因此连接方式不是eq_ref, 如下：
    在这里插入图片描述

  4. 优化后a表的连接类型依旧为index，扫描整个索引树，这种访问方式比全表扫描快,但相比使用其他索引访问方式(如 ref、eq_ref 等)仍然较慢。是因为连接条件a.del_flag = 0的数据离散度较小，数据分布极不均匀（只有0和1），所以mysql引擎优化的结果是不使用索引的等值查找（ref），即使存在del_flag字段的索引，如下：
    在这里插入图片描述

参考

MySQL Explain（执行计划）详解

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

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

相关文章

html+css 实现水波纹按钮

html+css 实现水波纹按钮

前言：哈喽，大家好，今天给大家分享htmlcss 绚丽效果！并提供具体代码帮助大家深入理解，彻底掌握！创作不易，如果能帮助到大家或者给大家一些灵感和启发，欢迎收藏关注哦 💕 文…
阅读更多...
python机器学习8--网络

python机器学习8--网络

1.超文本传输协议HTTP GET 在实际开发应用程序时，一定都会利用WiFi网络进行连接，再通过HTTP的方式读入后台的数据，并下载和显示在用户的PC上。这靠的是网络服务的技术，也就是大家提到的Web Service。而与HTTP服务器交换数据有两种…
阅读更多...
STM32-寄存器时钟配置指南

STM32-寄存器时钟配置指南

目录 启动 SystemInit SetSysClock 总结 启动 从startup_stm32f0xx.s内的开头的Description可以看到 ;* Description : STM32F051 devices vector table for EWARM toolchain. ;* This module performs: ;* - Set the in…
阅读更多...
Java解析epub电子书文件实战demo

Java解析epub电子书文件实战demo

如何使用 Java、Spring Boot 和 Epublib 库解析存储在阿里云对象存储服务（OSS）上的 EPUB 文件。这里将指导您完成设置必要依赖项、配置 OSS 客户端以及编写服务以读取和提取 EPUB 文件章节的全过程。 步骤1：添加依赖项 首先，将 E…
阅读更多...
08 字符串和字节串

08 字符串和字节串

使用单引号、双引号、三单引号、三双引号作为定界符（delimiter）来表示字符串，并且不同的定界符之间可以相互嵌套。 很多内置函数和标准库对象也都支持对字符串的操作。 x hello world y Python is a great language z Tom said, "Le…
阅读更多...
【ESP01开发实例】-ESP-01开发环境搭建与固件烧录

【ESP01开发实例】-ESP-01开发环境搭建与固件烧录

ESP-01开发环境搭建与固件烧录 文章目录 ESP-01开发环境搭建与固件烧录1、ESP-01介绍2、开发环境搭建3、固件下载3.1 使用 Arduino UNO 板对 ESP8266 (ESP-01) 模块进行编程3.2 使用USB 转串口转换器对 ESP8266 (ESP-01) 模块进行编程4、点亮LED本文将详细介绍如何使用 Arduino…
阅读更多...
springboot在加了mapper之后报错

springboot在加了mapper之后报错

springboot在加了mapper之后报错 最后发现是spring boot版本不兼容，spring-boot-starter-parent换成3.0.5之后可以了
阅读更多...
Asp .Net Core 系列：详解授权以及实现角色、策略、自定义三种授权和自定义响应

Asp .Net Core 系列：详解授权以及实现角色、策略、自定义三种授权和自定义响应

什么是授权（Authorization）？ 在 ASP.NET Core 中，授权（Authorization）是控制对应用资源的访问的过程。它决定了哪些用户或用户组可以访问特定的资源或执行特定的操作。授权通常与身份验证（Auth…
阅读更多...
【Git-驯化】一文搞懂git中rm命令的使用技巧

【Git-驯化】一文搞懂git中rm命令的使用技巧

【Git-驯化】一文搞懂git中rm命令的使用技巧 本次修炼方法请往下查看 🌈 欢迎莅临我的个人主页 👈这里是我工作、学习、实践 IT领域、真诚分享 踩坑集合，智慧小天地！ 🎇 免费获取相关内容文档关注：微信公…
阅读更多...
JS+H5在线文心AI聊天（第三方接口）

JS+H5在线文心AI聊天（第三方接口）

源码在最后面 调用的不是文心官方接口 可以正常聊天 有打字动画 效果图 源代码 <!DOCTYPE html> <html lang"zh"> <head><meta charset"UTF-8"><meta name"viewport" content"widthdevice-width, initial-s…
阅读更多...
OpenSSL学习笔记及在项目中的使用

OpenSSL学习笔记及在项目中的使用

OpenSSL官方命令手册&#xff1a;OpenSSL commands - OpenSSL Documentation 参考教程&#xff1a; 操作&#xff1a;OpenSSL的基本使用教程(一&#xff09;_openssl.exe使用教程-CSDN博客 操作&#xff1a;Linux和Shell回炉复习系列文章总目录 - 骏马金龙 - 博客园 (cnblog…
阅读更多...
vscode 调试web后端

vscode 调试web后端

1、调试环境配置 一、安装python环境管理器 其中要先在vscode选择对应的python环境&#xff0c;最方便的是按照环境管理器后从中选择。其中在【externsions】里面安装python即可。 如下&#xff1a; 二、编写launch.json文件 其中如下&#xff1a; {// Use IntelliSense …
阅读更多...
从丢失到找回：2024年U盘数据恢复软件全攻略

从丢失到找回：2024年U盘数据恢复软件全攻略

优盘作为我们日常短时间存储分享数据来说非常方便&#xff0c;毕竟小巧便携。但是也正因为他小巧数据很容易丢失&#xff0c;如果有备份还好&#xff0c;没有备份就麻烦了。但是只要掌握U盘数据恢复方法就可以缩小我们的损失。 1.福foxit昕数据恢复工具 一键直达>>http…
阅读更多...
【算法题】颜色分类，一文彻底搞会！

【算法题】颜色分类，一文彻底搞会！

目录 一、题目描述 二、解题思路 1、什么是荷兰国旗问题&#xff1f; 2、如何解决荷兰国旗问题&#xff1f; 三、参考答案 一、题目描述 颜色分类 给定一个包含红色、白色和蓝色、共n个元素的数组nums &#xff0c;原地对它们进行排序&#xff0c;使得相同颜色的元素相邻&am…
阅读更多...
【电源专题】结合锂电池相关资料和华为手机聊聊锂离子电池使用条件限制

【电源专题】结合锂电池相关资料和华为手机聊聊锂离子电池使用条件限制

在文章：【电源专题】锂电池的特点和工作原理 中我们讲到了一些关于锂电池种类和特点、工作原理等。但是对于锂离子电池使用条件限制却没有介绍，本文基于手机产商 锂离子电池使用条件-电池性能和应用介绍 | 华为官网 (huawei.com)提供的介绍文档再次深入学习锂离子电池的一些特…
阅读更多...
浅析JWT原理及牛客出现过的相关面试题

浅析JWT原理及牛客出现过的相关面试题

原文链接&#xff1a;https://kixuan.github.io/posts/f568/ 对jwt总是一知半解&#xff0c;而且项目打算写个关于JWT登录的点&#xff0c;所以总结关于JWT的知识及网上面试考察过的点 参考资料&#xff1a; Cookie、Session、Token、JWT_通俗地讲就是验证当前用户的身份,证明-…
阅读更多...
【SpringBoot】2 项目搭建

【SpringBoot】2 项目搭建

创建项目 1&#xff09;确实本地 jdk 版本 打开命令行窗口&#xff1a;快捷键 Windows R&#xff0c;输入 CMD&#xff0c;敲回车 执行命令&#xff1a;java -version 2&#xff09;在项目 clone 的位置创建 Spring Boot 项目&#xff0c;使用 Maven 进行依赖管理&#xff…
阅读更多...
uniapp通过绝对路径解压zpi中的shpe转化为geojson

uniapp通过绝对路径解压zpi中的shpe转化为geojson

uniapp通过绝对路径解压zpi中的shpe转化为geojson async fileResult() {const filepath11 /storage/emulated/0/importData/Export_Output_6.zip;// Base64解码函数function base64ToArrayBuffer(base64) {const binaryString atob(base64.split(,)[1]);const len binaryStr…
阅读更多...
【计算机网络】DHCP实验

【计算机网络】DHCP实验

一&#xff1a;实验目的 1&#xff1a;深入理解DHCP&#xff08;动态主机配置协议&#xff09;的工作原理和数据包交换过程。 2&#xff1a;掌握如何通过命令行释放和重新获取IP地址&#xff0c;并通过抓包软件分析DHCP消息的具体内容。 二&#xff1a;实验仪器设备及软件 硬…
阅读更多...
人工智能类——计算机科学与技术

人工智能类——计算机科学与技术

计算机科学与技术是一个非常大的门类。目前计算机科学与技术类招生的专业主要有计算机科学与技术、软件工程、网络工程、信息安全、物联网工程等&#xff0c;后面的几个专业是计算机科学与技术的重要分支&#xff0c;而这个门类的其他分支并没有单列出来一个本科专业&#xff0…
阅读更多...
最新文章

Copyright @ 2022~2023