【MYSQL高级】Mysql的SQL性能分析【借助EXPLAIN分析】

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: 【MYSQL高级】Mysql的SQL性能分析【借助EXPLAIN分析】

性能分析

要说sql有问题,需要拿出证据,因此需要性能分析

Mysql查询优化器(Mysql Query Optimizer)

1.Mysql中有专门负责优化SELECT语句的优化器模块,主要功能:通过计算分析系统中收集到的统计信息,为客户端请求的Query提供他认为最优的执行计划(它认为最优的数据检索方式,不见得是DBA认为是最优的,这部分最耗费时间,如果知道mysql底层是如何执行一条sql,可以帮助我们更好地优化sql)


2.当客户端向MySQL请求一条Query,命令解析器模块完成请求分类,区别出是 SELECT 转发给MysQL Query Optimizer,MysQL Query Optimizer首先会对整条Query进行优化,处理掉一些常量表达式的预算,直接换算成常量值。并对Query中的查询条件进行简化和转换,如去掉一些无用或显而易见的条件、进行结构调整。然后分析 Query 中的 Hint 信息(如果有),看显示Hint信息是否可以完全确定该Query 的执行计划。如果没有 Hint 或Hint 信息还不足以完全确定执行计划,则会读取所涉及对象的统计信息,根据 Query 进行写相应的计算分析,然后再得出最后的执行计划。

Mysql常见瓶颈

  • CPU: CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
  • IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
  • 服务器硬件的性能瓶颈(机器性能本来就弱): top,free,iostat和vmstat来查看系统的性能状态

EXPLAIN简介

EXPLAIN是什么?

EXPLAIN:SQL的执行计划,使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理SQL语句的,进而分析查询语句或是表结构的性能瓶颈。

EXPLAIN怎么使用?

语法:explain关键字 + 所写的SQL

【表格形式查看】

explain select * from tbl_emp;

【列形式查看】

mysql> explain select * from pms_category \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pms_category
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1425
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

EXPLAIN能干嘛?

通过上面的表格可以查看以下信息:

id列 了解表的读取顺序

值有以下三种情况:

  • id相同,执行顺序由上至下。
  • id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行。

  • id相同和不同 同时存在。永远是id大的优先级最高,id相等的时候从上到下顺序执行。

总结:id相同上下顺序走,不同id大的先走

select_type列 数据读取操作的操作类型。

select_type:数据查询的类型,主要是用于区别,普通查询、联合查询、子查询等复杂查询。

【常见值】

  • SIMPLE:简单的SELECT查询,查询中不包含子查询或者UNION
  • PRIMARY:查询中如果包含任何复杂的子部分,最外层查询则被标记为PRIMARY
  • SUBQUERY:在SELECT或者WHERE子句中包含了子查询。
  • DERIVED:在FROM子句中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表中。
  • UNION:如果第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为DERIVED
  • UNION RESULT:从UNION表获取结果的SELECT

table列 表的名字

标题已经通俗易懂了,哈哈

type列 访问类型详解及排列

【type列常见值】

  • system:一张表只有一行记录(如系统参数表,每个参数字段只存储一个值),这是const类型的特例(平时不会出现,这个也可以忽略不计)
  • const:表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快。如将主键置于where列表中,MySQL就能将该查询转化为一个常量。

  • eq_ref:唯一性索引扫描,读取本表中和关联表表中的每行组合成的一行,查出来只有一条记录。除了 systemconst 类型之外, 这是最好的连接类型。

  • ref:非唯一性索引扫描,返回本表和关联表某个值匹配的所有行,查出来有多条记录

【例子1】

【例子2】

  • range:只检索给定范围的行,一般就是在WHERE语句中出现了BETWEEN< >in等的查询。这种范围扫描索引比全表扫描要好,因为它只需要开始于索引树的某一点,而结束于另一点,不用扫描全部索引。

  • indexFull Index Scan,全索引扫描,indexALL的区别为index类型遍历索引树,不用全表扫描。也就是说虽然ALLindex都是读全表,但是index是从索引中读的,ALL是从磁盘中读取的。**因为索引文件通常比数据文件小,所以Index往往比ALL快。

  • ALLFull Table Scan,没有用到索引,全表扫描

从最好到最差依次是:system>const>eq_ref>ref>range>index>ALL。除了ALL没有用到索引,其他级别都用到索引了,表示全表扫描。


**总结:**多看sql的type,如果是All,争取优化成其他的,一般来说,得保证查询至少达到range级别,最好达到ref。

possible_keys列 哪些索引可能被使用

possible_keys:显示可能应用在这张表中的索引,一个或者多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。


key列 哪些索引被实际使用(判断索引是否失效)

key:实际使用的索引。如果为NULL,则没有使用索引。

查询中如果使用了覆盖索引,则该索引仅仅出现在key列表中,不出现在possible_keys中。

key_len列 索引中使用的字节数

key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。

在不损失精度的情况下,长度越短越好。一般是精度要求更高(根据姓名和城市查询一个人比只根据姓名查询一个人精度更高),key_len越长。


key_len计算规则:https://blog.csdn.net/qq_34930488/article/details/102931490


ref列 表之间的引用

ref:显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值


由key_len可知t1表的idx col1 col2被充分使用,cl1匹配t2表的col1,co2匹配了一个常量,即’ac’

rows列 每张表有多少行被优化器查询

rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录需要读取的行数

【没建索引之前】


【建索引之后】

Extra列 包含不适合在其他列中显示但十分重要的额外信息

Extra列也会有一系列的值,下面是这些值的解释:


Using filesort:说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为"文件内排序"。

总结:出现Using filesort尽快优化

Using temporary:使用了临时表(查询结束之后还要删除)保存中间结果,MySQL在対查询结果排序时使用了临时表。常见于排序order by和分组查询group by临时表対系统性能损耗很大。

总结:出现Using temporary尽快优化

  • Using index:表示相应的SELECT操作中使用了覆盖索引,避免访问了表的数据行,效率不错
  • 如果同时出现Using where,表示索引被用来执行索引键值的查找;
  • 如果没有同时出现Using where,表明索引用来读取数据而非执行查找动作。

覆盖索引:就是select的数据列只用从索引中就能够取得,不必从数据表中读取,换句话说查询列要被所使用的索引覆盖。 注意:如果要使用覆盖索引,不要写SELECT *,要写出具体的字段。


  • Using where:表明使用了WHERE过滤。
  • Using join buffer:使用了连接缓存。如果join特别多,可以调大配置文件的buffer。
  • impossible whereWHERE子句的值总是false,不能用来获取任何元组。

  • select tables optimized away:在没有GROUPBY子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算查询执行计划生成的阶段即完成优化。
  • distinct:找到第一个匹配的元组后停止同样值的动作

EXPLAIN案例

文章说明

本文章为本人学习尚硅谷的学习笔记,文章中大部分内容来源于尚硅谷视频(点击学习尚硅谷相关课程),也有部分内容来自于自己的思考,发布文章是想帮助其他学习的人更方便地整理自己的笔记或者直接通过文章学习相关知识,如有侵权请联系删除,最后对尚硅谷的优质课程表示感谢。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
1月前
|
SQL 存储 关系型数据库
MySQL体系结构详解:一条SQL查询的旅程
本文深入解析MySQL内部架构,从SQL查询的执行流程到性能优化技巧,涵盖连接建立、查询处理、执行阶段及存储引擎工作机制,帮助开发者理解MySQL运行原理并提升数据库性能。
|
10天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
|
10天前
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格:MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型,包括关系型与NoSQL,主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起,支持按需付费与优惠套餐,适用于各类应用场景。
|
1月前
|
SQL 监控 关系型数据库
SQL优化技巧:让MySQL查询快人一步
本文深入解析了MySQL查询优化的核心技巧,涵盖索引设计、查询重写、分页优化、批量操作、数据类型优化及性能监控等方面,帮助开发者显著提升数据库性能,解决慢查询问题,适用于高并发与大数据场景。
|
1月前
|
SQL 监控 关系型数据库
查寻MySQL或SQL Server的连接数,并配置超时时间和最大连接量
以上步骤提供了直观、实用且易于理解且执行的指导方针来监管和优化数据库服务器配置。务必记得,在做任何重要变更前备份相关配置文件,并确保理解每个参数对系统性能可能产生影响后再做出调节。
179 11
|
16天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎,提供高性价比、稳定安全的云数据库服务,适用于多种行业与业务场景。
|
1月前
|
安全 关系型数据库 MySQL
MySQL安全最佳实践:保护你的数据库
本文深入探讨了MySQL数据库的安全防护体系,涵盖认证安全、访问控制、网络安全、数据加密、审计监控、备份恢复、操作系统安全、应急响应等多个方面。通过具体配置示例,为企业提供了一套全面的安全实践方案,帮助强化数据库安全,防止数据泄露和未授权访问,保障企业数据资产安全。
|
16天前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能(下)
使用MYSQL Report分析数据库性能
53 3
|
22天前
|
关系型数据库 MySQL 数据库
自建数据库如何迁移至RDS MySQL实例
数据库迁移是一项复杂且耗时的工程,需考虑数据安全、完整性及业务中断影响。使用阿里云数据传输服务DTS,可快速、平滑完成迁移任务,将应用停机时间降至分钟级。您还可通过全量备份自建数据库并恢复至RDS MySQL实例,实现间接迁移上云。

推荐镜像

更多