轻松上手MYSQL：SQL优化之Explain详解

2024-06-10 03:44
文章标签 sql mysql 详解 优化 轻松 explain 上手 database

本文主要是介绍轻松上手MYSQL：SQL优化之Explain详解，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

​在这里插入图片描述​🌈 个人主页：danci_
🔥 系列专栏：《设计模式》《MYSQL应用》
💪🏻 制定明确可量化的目标，坚持默默的做事。

文章目录

  • 一、Explain
    • 1.1 explain作用
    • 1.2 explain列说明
      • `id`
      • `select_type`
      • `table`
      • `partiitons`
      • `type`
      • `select_type`
      • `possible_keys`
      • `key`
      • `key_len（key_len值计算）`
      • `key_len的计算：（举几个类型）`
      • `ref`
      • `rows`
      • `filtered`
      • `Extra`

一、Explain

1.1 explain作用

    在sql语句前添加explain，作用是查看mysql对这条sql的执行计划信息。思考：MYSQL执行SQL语句时一定按这个执行计划执行么？

 

1.2 explain列说明

 
    在一条简单SQL前面添加explain查看有哪些列，如下：
 
在这里插入图片描述
 

id

 
每个select对应一个id值，其值是按 select 出现的顺序增长的。
注：id值越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行
 

select_type

 
每个select对应一个select_type，表示select的复杂度，有：
SIMPLE：简单查询。查询不包含子查询和union，如上图

    PRIMARY：对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type值就是PRIMARYSUBQUERY：包含在 select 中的子查询(不在 from 子句中)DERIVED：对于包含‘派生表’的查询UNION：在 union 中的第二个和随后的 select

 

table

 

    这一列表示 explain 的一行正在访问哪个表。当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查 询。当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

 

partiitons

 

    匹配的分区信息

 

type

 

    这一列表示关联类型或访问类型效率从最优到最差分别为:system > const > eq_ref > ref > range > index > ALLSQL性能优化的目标：至少要达到range级别，要求是ref级别，最好是consts级别。system：当表中只有一条记录并且该表使用的存储引擎的统计数据都是精确地，表最多有一个匹配行，读取1次，速度比较快。const：system是 const的特例，表里只有一条元组匹配时为systemeq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会 找到多个符合条件的行。range：用索引获取某些范围区间的记录。

 

select_type

 

    每个select对应一个select_type，表示select的复杂度SIMPLE：简单查询。查询不包含子查询和union，如上图PRIMARY：对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type值就是PRIMARYSUBQUERY：包含在 select 中的子查询(不在 from 子句中)DERIVED：对于包含‘派生表’的查询UNION：在 union 中的第二个和随后的 select

 

possible_keys

 

    标识某个表查询时可能使用哪些索引来查找。

 

key

 

    实际使用哪个索引。当possible_keys有值，而key没有值时，可能是因为表数据很少，mysql认为没有必要走索引，直接全表查询了。当possible_keys为null时，可根据实际情况在where条件中添加索引来提升查询效率。

 

key_len（key_len值计算）

 

     实际使用到的索引的字节数，帮我们检查是否充分利用上了索引，对于联合索引有一定的参考意义。

    比如有列n和address的联合索引（表my_datas字段有id, n, address 和 time）
 
在这里插入图片描述
 
在这里插入图片描述
 

     key_len=5，通过计算索引占的字节数来判断出查询使用了联合索引中的第一个列。

 

key_len的计算：（举几个类型）

 
    测试表test1
 
在这里插入图片描述
 
 
 
字符串：char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节

 

  • char(n)：如果存汉字长度就是 4n 字节（若可为空 则+1）
     
    在这里插入图片描述
     

       col1_char是char(4)，那么len应该是 4*4 + 可为空1 = 17explain中key_len值为17

 

  • varchar(n)：如果存汉字则长度是 4n + 2 字节（若可为空 则+1），加的2字节用来存储字符串长度，因为 varchar是变长字符串。
     
    在这里插入图片描述
     

       col2_varchar是varchar(32)，那么len应该是 32 * 4 + 2 + 1 = 131explain中key_len值为131

 
数值类型：
 

  • tinyint：1字节（若可为空 则+1）
     
    在这里插入图片描述
     

       col3_tinyint是tinyint，那么len应该是 1+可为空1 = 2explain中key_len值为2

 

  • smallint：2字节（若可为空 则+1）
     
    在这里插入图片描述
     

       col4_smallint是smallint，那么len应该是 2+可为空1 = 3explain中key_len值为3

 

  • int：4字节（若可为空 则+1）
     
    在这里插入图片描述
     

       col5_int是int，那么len应该是 4+可为空1 = 5explain中key_len值为5

 

  • bigint：8字节 （若可为空 则+1）
     
    在这里插入图片描述
     

       col6_bigint是bigint，那么len应该是 8+可为空1 = 9explain中key_len值为9

 
时间类型：
 

  • date：3字节（若可为空 则+1）
     
    在这里插入图片描述
     

       col7_date是date，那么len应该是 3 + 可为空1 = 4explain中key_len值为4

 

  • timestamp：4字节（若可为空 则+1）
     
    在这里插入图片描述
     

       col8_timestamp是timestamp，那么len应该是 4+可为空1 = 5explain中key_len值为5datetime：无小数秒位数，占5个字节。datetime(n) 其中n是保留的小数秒位数，额外占的存储空间分别为n=0时      额外空间0字节n=1(或2)  额外空间1字节n=3(或4)  额外空间2字节n=5(或6)  额外空间3字节col9_datetime是datetime，那么len应该是 5+可为空1 = 6explain中key_len值为6

    注：

     - myisam 表，单列索引，最大长度不能超过 1000 bytes，否则会报警，但是创建成功，最终创建的是前缀索引（取前333个字符）；- myisam 表，组合索引，索引长度和不能超过 1000 bytes，否则会报错，创建失败；- innodb 表，单列索引，超过 767 bytes的，给出warning，最终索引创建成功，取前缀索引（取前 255 字符）；- innodb 表，组合索引，各列长度不超过 767 bytes ，如果有超过 767 bytes 的，则给出报警，索引最后创建成功， 但是对于超过 767 字节的列取前缀索引，与索引列顺序无关，总和不得超过 3072 ，否则失败，无法创建；

 

ref

 

    这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有:const(常量)、字段名（库名.表名.列名 如test.test.col1_char)

 

rows

 

    这一列是mysql估计要读取并检测的行数，值越小越优注意这个不是结果集里的行数

 

filtered

 

    通过索引扫描表估计要读取并检测的行数rows。使用额外的查询条件对rows行的数据进行过滤行得有行数n占rows的比例，即 n/rows * 100%

 

Extra

 

    sql执行计划比较重要的参考信息，常见重要信息如下：

 

  • 1 Using index：使用索引覆盖
     
    索引覆盖：查询的字段信息从这条sql使用的索引（辅助索引）树中获取。如：
     
    在这里插入图片描述
     

      查询的字段是索引（col4_smallint）中的字段col4_smallint信息也就是说，不需要通过辅助索引找到主键，再通过主键树获取想要的信息

 

  • 2 Using where：使用where查询数据，需要回表去获取需要的数据
     
    在这里插入图片描述
     
     
  • 3 Using index condition：相当于索引覆盖后通过主键回表查询，再通过where过滤
     
    在这里插入图片描述
     
     
  • 4 Using temporary：创建一张临时表来处理查询
     
    在这里插入图片描述
     
     
  • 5 Using filesort：顾名思义，使用文件（磁盘中）排序。mysql做了优化，数据较少时排序是在内在中进行的，数据量较大时才会在磁盘中进行排序。出现这种情况，就要考虑添加索引来优化SQL了。
     
    在这里插入图片描述
     
        col1_char 未创建索引，mysql先预览整个表对col1_char进行排序和对应的主键值序列，再通过主键值回主键索引树查询数据返回。
        对 col1_char 添加索引之后，执行计划结果如下：
     
    在这里插入图片描述
     
     
  • 6 Select tables optimized away：使用函数来查询某个索引信息时
     
    在这里插入图片描述
     
     
        希望你喜欢这篇文章！不要忘记 "点赞" 和 "关注" 哦，我们下次见！🎈
     

​

这篇关于轻松上手MYSQL：SQL优化之Explain详解的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1047122。

相关文章

一文详解如何在idea中快速搭建一个Spring Boot项目

一文详解如何在idea中快速搭建一个Spring Boot项目

《一文详解如何在idea中快速搭建一个SpringBoot项目》IntelliJIDEA作为Java开发者的‌首选IDE‌,深度集成SpringBoot支持,可一键生成项目骨架、智能配置依赖,这篇文... 目录前言1、创建项目名称2、勾选需要的依赖3、在setting中检查maven4、编写数据源5、开启热
阅读更多...
SQL Server修改数据库名及物理数据文件名操作步骤

SQL Server修改数据库名及物理数据文件名操作步骤

《SQLServer修改数据库名及物理数据文件名操作步骤》在SQLServer中重命名数据库是一个常见的操作,但需要确保用户具有足够的权限来执行此操作,：本文主要介绍SQLServer修改数据... 目录一、背景介绍二、操作步骤2.1 设置为单用户模式（断开连接）2.2 修改数据库名称2.3 查找逻辑文件名
阅读更多...
Python常用命令提示符使用方法详解

Python常用命令提示符使用方法详解

《Python常用命令提示符使用方法详解》在学习python的过程中,我们需要用到命令提示符(CMD)进行环境的配置,：本文主要介绍Python常用命令提示符使用方法的相关资料,文中通过代码介绍的... 目录一、python环境基础命令【Windows】1、检查Python是否安装2、 查看Python的安
阅读更多...
SQL Server数据库死锁处理超详细攻略

SQL Server数据库死锁处理超详细攻略

《SQLServer数据库死锁处理超详细攻略》SQLServer作为主流数据库管理系统,在高并发场景下可能面临死锁问题,影响系统性能和稳定性,这篇文章主要给大家介绍了关于SQLServer数据库死... 目录一、引言二、查询 Sqlserver 中造成死锁的 SPID三、用内置函数查询执行信息1. sp_w
阅读更多...
HTML5 搜索框Search Box详解

HTML5 搜索框Search Box详解

《HTML5搜索框SearchBox详解》HTML5的搜索框是一个强大的工具，能够有效提升用户体验,通过结合自动补全功能和适当的样式，可以创建出既美观又实用的搜索界面,这篇文章给大家介绍HTML5... html5 搜索框（Search Box）详解搜索框是一个用于输入查询内容的控件，通常用于网站或应用程
阅读更多...
Python中使用uv创建环境及原理举例详解

Python中使用uv创建环境及原理举例详解

《Python中使用uv创建环境及原理举例详解》uv是Astral团队开发的高性能Python工具,整合包管理、虚拟环境、Python版本控制等功能,：本文主要介绍Python中使用uv创建环境及... 目录一、uv工具简介核心特点：二、安装uv1. 通过pip安装2. 通过脚本安装验证安装：配置镜像源（可
阅读更多...
C++ 函数 strftime 和时间格式示例详解

C++ 函数 strftime 和时间格式示例详解

《C++函数strftime和时间格式示例详解》strftime是C/C++标准库中用于格式化日期和时间的函数,定义在ctime头文件中,它将tm结构体中的时间信息转换为指定格式的字符串,是处理... 目录C++ 函数 strftipythonme 详解一、函数原型二、功能描述三、格式字符串说明四、返回值五
阅读更多...
LiteFlow轻量级工作流引擎使用示例详解

LiteFlow轻量级工作流引擎使用示例详解

《LiteFlow轻量级工作流引擎使用示例详解》：本文主要介绍LiteFlow是一个灵活、简洁且轻量的工作流引擎,适合用于中小型项目和微服务架构中的流程编排,本文给大家介绍LiteFlow轻量级工... 目录1. LiteFlow 主要特点2. 工作流定义方式3. LiteFlow 流程示例4. LiteF
阅读更多...
canal实现mysql数据同步的详细过程

canal实现mysql数据同步的详细过程

《canal实现mysql数据同步的详细过程》：本文主要介绍canal实现mysql数据同步的详细过程,本文通过实例图文相结合给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的... 目录1、canal下载2、mysql同步用户创建和授权3、canal admin安装和启动4、canal
阅读更多...
CSS3中的字体及相关属性详解

CSS3中的字体及相关属性详解

《CSS3中的字体及相关属性详解》：本文主要介绍了CSS3中的字体及相关属性，详细内容请阅读本文，希望能对你有所帮助... 字体网页字体的三个来源：用户机器上安装的字体，放心使用。保存在第三方网站上的字体，例如Typekit和Google,可以link标签链接到你的页面上。保存在你自己Web服务器上的字
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2