MySQL基础（八）：性能分析（主要用于SQL查询语句优化）

最新推荐文章于 2023-08-08 21:59:23 发布

凤求凰的博客

最新推荐文章于 2023-08-08 21:59:23 发布

阅读量441

点赞数

分类专栏： # 数据库(MySQL) 文章标签：索引

本文链接：https://blog.csdn.net/weixin_44571270/article/details/106978907

版权

数据库(MySQL) 专栏收录该内容

16 篇文章 3 订阅

订阅专栏

下面是小凰凰的简介，看下吧！
💗人生态度：珍惜时间，渴望学习，热爱音乐，把握命运，享受生活
💗学习技能：网络 -> 云计算运维 -> python全栈( 当前正在学习中)
💗您的点赞、收藏、关注是对博主创作的最大鼓励，在此谢过！
有相关技能问题可以写在下方评论区，我们一起学习，一起进步。
后期会不断更新python全栈学习笔记，秉着质量博文为原则，写好每一篇博文。

文章目录

一、MySQL常见瓶颈

1. cpu
SQL中对大量数据进行比较、关联、排序、分组，其中最大的压力在于'比较' 
2. IO
实例内存满足不了缓存数据或排序等需要，导致产生大量 物理 IO。使用交换内存
查询执行效率低，扫描过多数据行。
3. 锁
不适宜的锁的设置，导致线程阻塞，性能下降。
死锁，线程之间交叉调用资源，导致死锁，程序卡住。
4. 服务器硬件的性能瓶颈
top,free, iostat和vmstat来查看系统的性能状态

Linux服务器硬件性能查看详解：https://blog.csdn.net/weixin_44571270/article/details/104883887

二、explain关键字

1、explain是什么？

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

2、explain能干嘛？

表的读取顺序
哪些索引可以使用
数据读取操作的操作类型
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

是不是看完一脸懵逼😄？很正常，请先往下看，小凰凰喊你回头看，你再回头来看这个，你就茅塞顿开了😯！

3、explain怎么用？

# 使用方法
Explain + SQL语句

执行计划包含的信息：
在这里插入图片描述
接下来我们就要对上面的信息逐一分析！这上面的信息也是你和面试官谝SQL优化的主要内容！其中重点内容是id、type、key、rows、Extra！

4、各字段的解释

（1）id

select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下
id相同，执行顺序由上至下
此例中先执行where 后的第一条语句 t1.id = t2.id 通过 t1.id 关联 t2.id 。而 t2.id 的结果建立在 t2.id=t3.id 的基础之上。
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id 不同，如果是子查询， id 的序号会递增， id 值越大优先级越高，越先被执行
id相同不同，同时存在
id如果相同，可以认为是一组，从上往下顺序执行；
不同的话，id值越大，优先级越高，越先执行
衍生表 = derived2 --> derived + 2 （2 表示由 id =2 的查询衍生出来的表。type 肯定是 all ，因为衍生的表没有建立索引）

（2）select_type

既然叫select_type（查询类型），那么有哪些类型？它又是用来干什么的呢？

# 用来干什么？
主要是用于区别普通查询、联合查询、子查询等的复杂查询

有哪些类型呢？

SIMPLE
释义：简单的 select 查询,查询中不包含子查询或者UNION

例如：
PRIMARY
释义：查询中若包含任何复杂的子部分，最外层查询则被标记为Primary

例如：
DERIVED
释义：在FROM某个表中包含的子查询被标记为DERIVED(衍生) ，MySQL会递归执行这些子查询, 把结果放在临时表里。

例如：
from a表，其中包含的子查询（id=2）select_type被标记为DERIVED，递归执行select t1.content from t1,把结果放在临时表a中
SUBQUERY
释义：在SELECT或WHERE列表中包含了子查询

例如：
UNION
释义：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED

例如：
UNION RESULT
释义：从UNION表获取结果的SELECT

例如：

（3）table

显示这一行的数据是关于哪张表的

（4）type

type显示的是访问类型，是较为重要的一个指标，其值从最好到最坏依次是 ： 

system>const>eq_ref>ref>range（尽量保证）>index>ALL 
 
 一般来说，得保证查询至少达到range级别，最好能达到ref。

下面我们对type的每个值进行深入解读：

system
释义：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
const
释义：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量

例如：
eq_ref
释义：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

例如：
ref
释义：非唯一性索引扫描，返回匹配某个单独值的所有行. 本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

例如：
range
释义：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

例如：
index
释义：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

例如：
all
释义：Full Table Scan，将遍历全表以找到匹配的行，没有索引

（5）possible-keys

显示该查询可能会用到的索引。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

（6）key

实际使用的索引。如果为NULL，则没有使用索引。

就像我请客，准备请的人是100人，这个就像是possible keys
但是实际来的人大于、小于、等于这都是又可能的，这个就像是key！

例如：
在这里插入图片描述

（7）key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确度的情况下，长度越短越好
在这里插入图片描述上图中第一个查询相比第二个查询，两个限制条件，精确度高，因此key_len大，耗费高！

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，key_len是根据表定义计算得到的，不是通过表内检索出的！你可以简单理解为key_len就是得到一个查询结果，根据表定义计算而出的一个索引成本！因此满足要求的情况下，key_len应该尽量的小！

注：若key为NULL，那么key_len一定也为NULL！因为key_len为索引成本，索引都没有谈何成本

（8）ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
在这里插入图片描述 注意：

1. 这里t1是主表哈，t2是外键关联的表，就像员工表关联部门表一样！
2. 我们先看`t1.col1 = t2.col1`，因为t1表它引用了t2表的col1字段，因此ref为shared.t2.col1（`shared库下t2表下的col1字段`）
3. t1.col2 = 'ac'，因为它直接就等于一个固定的量，因此ref为const常数

注意：key如果为NULL，那么ref必定为NULL，因为它是显示索引的哪一列被使用了！

（9）rows

rows列显示MySQL认为它执行查询时必须检查的行数。越少越好
在这里插入图片描述上图我们只关注t2表就行了，第一个查询语句，t2表查询行数为640行，索引也没用，第二个语句我们为它创建了一个col1和col2的复合索引，第三个查询语句t2表用到了索引，rows减到了142行！

没有索引你就需要全表扫描，检查行数当然多！

好，到此为止，你就可以回头看刚才那个我让各位小伙伴忽略的内容了，相信你肯定能看懂！

（10）Extra

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

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

出现filesort的情况：

索引常用于查询或排序，col1=‘ac’，用到了部分索引col1，因为我们这里是个复合索引嘛（indx_col1_col2_col3），ref=const，说明col1确实用到了索引，因此问题就出在 order by col3，你看下面哪一个查询语句，就没有using filesort，这是为什么？

看下里面的创建索引的情况：https://blog.csdn.net/weixin_44571270/article/details/106972583

我们把复合索引（indx_col1_col2_col3）比作三层楼
第一个查询语句只使用了col1和col3索引，col2索引没有使用，相当于就是二楼断开了，因此无法直接使用复合索引，因此mysql就会use filesort！
第二个查询语句col1、col2、col3都使用了，因此好着的！

Using temporary
释义：使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
Using join buffer
释义：使用了连接缓存
当你使用左连接、右连接、内连接、或者联表查询时都有可能出现出现！
出现在当两个表连接时
驱动表(被连接的表、left join 左边的表、right join右边的表、inner join 中数据少的表) 没有索引的情况下。如上图就是被连接的表没有索引的情况！
给驱动表建立索引可解决此问题。且 type 将改变成 ref
Using index
表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！ 如果同时出现using where，表明索引被用来执行索引键值的查找; 如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
Using where
表明使用了where过滤
impossible where
where子句的值总是false，不能用来获取任何元组
不可能一个人的名字既叫july又叫z3吧！因此impossible where
select tables optimized away（不用看）
在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

myisam 中会维护总行数 (还有其他参数)这个参数，所以在执行查询时不会进行全表扫描。而是直接读取这个数。
但会对增删产生一定的影响。根据业务情况决定谁好谁坏
innodb 中没有这个机制。

5、实战训练

求出下面的sql语句的执行顺序！
在这里插入图片描述执行顺序解析：
执行顺序答案：

select name,id from t2
	👇
select id,name from t1 where other_column=""
	👇
select id from t3
	👇
select d1,name ...
	👇
两个结果union操作

凤求凰的博客

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
打赏
1
评论
MySQL基础（八）：性能分析（主要用于SQL查询语句优化）

下面是小凰凰的简介，看下吧！????人生态度：珍惜时间，渴望学习，热爱音乐，把握命运，享受生活????学习技能：网络 -> 云计算运维 -> python全栈( 当前正在学习中)????您的点赞、收藏、关注是对博主创作的最大鼓励，在此谢过！有相关技能问题可以写在下方评论区，我们一起学习，一起进步。后期会不断更新python全栈学习笔记，秉着质量博文为原则，写好每一篇博文。文章目录一、MySQL常见瓶颈二、explain关键字1、explain是什么？2、explain能干嘛？3、e
复制链接

扫一扫