轻松上手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。

相关文章

MySQL常用字符串函数示例和场景介绍

MySQL常用字符串函数示例和场景介绍

《MySQL常用字符串函数示例和场景介绍》MySQL提供了丰富的字符串函数帮助我们高效地对字符串进行处理、转换和分析,本文我将全面且深入地介绍MySQL常用的字符串函数,并结合具体示例和场景,帮你熟练... 目录一、字符串函数概述1.1 字符串函数的作用1.2 字符串函数分类二、字符串长度与统计函数2.1
阅读更多...
Python标准库之数据压缩和存档的应用详解

Python标准库之数据压缩和存档的应用详解

《Python标准库之数据压缩和存档的应用详解》在数据处理与存储领域,压缩和存档是提升效率的关键技术,Python标准库提供了一套完整的工具链,下面小编就来和大家简单介绍一下吧... 目录一、核心模块架构与设计哲学二、关键模块深度解析1.tarfile：专业级归档工具2.zipfile：跨平台归档首选3.
阅读更多...
SQL Server跟踪自动统计信息更新实战指南

SQL Server跟踪自动统计信息更新实战指南

《SQLServer跟踪自动统计信息更新实战指南》本文详解SQLServer自动统计信息更新的跟踪方法,推荐使用扩展事件实时捕获更新操作及详细信息,同时结合系统视图快速检查统计信息状态,重点强调修... 目录SQL Server 如何跟踪自动统计信息更新：深入解析与实战指南 核心跟踪方法1️⃣ 利用系统目录
阅读更多...
MySQL 内存使用率常用分析语句

MySQL 内存使用率常用分析语句

《MySQL内存使用率常用分析语句》用户整理了MySQL内存占用过高的分析方法,涵盖操作系统层确认及数据库层bufferpool、内存模块差值、线程状态、performance_schema性能数据... 目录一、 OS层二、 DB层1. 全局情况2. 内存占js用详情最近连续遇到mysql内存占用过高导致
阅读更多...
idea的终端(Terminal)cmd的命令换成linux的命令详解

idea的终端(Terminal)cmd的命令换成linux的命令详解

《idea的终端(Terminal)cmd的命令换成linux的命令详解》本文介绍IDEA配置Git的步骤：安装Git、修改终端设置并重启IDEA,强调顺序,作为个人经验分享,希望提供参考并支持脚本之... 目录一编程、设置前二、前置条件三、android设置四、设置后总结一、php设置前二、前置条件
阅读更多...
python中列表应用和扩展性实用详解

python中列表应用和扩展性实用详解

《python中列表应用和扩展性实用详解》文章介绍了Python列表的核心特性：有序数据集合,用[]定义,元素类型可不同,支持迭代、循环、切片,可执行增删改查、排序、推导式及嵌套操作,是常用的数据处理... 目录1、列表定义2、格式3、列表是可迭代对象4、列表的常见操作总结1、列表定义是处理一组有序项目的
阅读更多...
python使用try函数详解

python使用try函数详解

《python使用try函数详解》Pythontry语句用于异常处理,支持捕获特定/多种异常、else/final子句确保资源释放,结合with语句自动清理,可自定义异常及嵌套结构,灵活应对错误场景... 目录try 函数的基本语法捕获特定异常捕获多个异常使用 else 子句使用 finally 子句捕获所
阅读更多...
Mysql中设计数据表的过程解析

Mysql中设计数据表的过程解析

《Mysql中设计数据表的过程解析》数据库约束通过NOTNULL、UNIQUE、DEFAULT、主键和外键等规则保障数据完整性,自动校验数据,减少人工错误,提升数据一致性和业务逻辑严谨性,本文介绍My... 目录1.引言2.NOT NULL——制定某列不可以存储NULL值2.UNIQUE——保证某一列的每一
阅读更多...
C++11范围for初始化列表auto decltype详解

C++11范围for初始化列表auto decltype详解

《C++11范围for初始化列表autodecltype详解》C++11引入auto类型推导、decltype类型推断、统一列表初始化、范围for循环及智能指针,提升代码简洁性、类型安全与资源管理效... 目录C++11新特性1. 自动类型推导auto1.1 基本语法2. decltype3. 列表初始化3
阅读更多...
解密SQL查询语句执行的过程

解密SQL查询语句执行的过程

《解密SQL查询语句执行的过程》文章讲解了SQL语句的执行流程,涵盖解析、优化、执行三个核心阶段,并介绍执行计划查看方法EXPLAIN,同时提出性能优化技巧如合理使用索引、避免SELECT*、JOIN... 目录1. SQL语句的基本结构2. SQL语句的执行过程3. SQL语句的执行计划4. 常见的性能优
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2