注:本文章中,使用的是最新的 MySQL 8.0.32,采用主从架构。(未实现读写分离)
前言:数据表结构已经在本人的《MySQL调优分析篇1》中建好了,大家可以点击下面的链接先看看具体的数据表结构和数据,在这篇文章中不再过多赘述表数据方面的信息。另外,个人认为调优分析篇的顺序搞反了,或许EXPLAIN关键字更适合初学者去做SQL语句的性能分析,但是需要学习的知识比较多,而PROFILE分析更偏向于进阶的学习,并且可以作为SQL调优前后的对比依据来定位慢SQL的问题。MySQL调优分析篇1(SHOW PROFILES):SQL语句效率分析实战_ToneyMa的博客-CSDN博客https://blog.csdn.net/Mccson/article/details/129930907
那么,现在开始我们的SQL语句分析的学习。
Step1:生成模拟数据。
注:在本篇文章中,沿用了《MySQL调优分析篇1》中的数据表。
本次EXPLAIN关键字的学习中,为了更突出学习效果,在这里我将数据新增至50w条。
Step1.5:开启MySQL不显示查询结果集功能。
首先,在Linux环境中直接使用 SELECT * 做全表查询,会导致数据全部输出完才显示执行耗时,所以,这里我们需要先开启MySQL自带的一个不显示查询结果集的功能。
在MySQL中,直接输入以下指令以开启或关闭显示查询结果的功能:
#开启隐藏查询结果功能 pager cat > /dev/null #恢复显示查询结果 pager
功能开启成功后,如下图所示:
Step2:测试50w数据量的全表查询效率。
直接使用全表查询,观察执行效率。
SELECT * FROM sys_user_base_info;
我们从MySQL输出的信息(下图)来看,结果集总共50w行,耗时0.67秒。
50w行数据耗时0.67秒,此时,如果我们需要对该语句进行分析,就需要用到EXPLAIN关键字,去查看这条SQL语句的执行计划。
Step3:使用EXPLAIN关键字查看SQL的执行计划。
使用方法:在SQL语句前,加上EXPLIAN关键字。
EXPLAIN SELECT * FROM sys_user_base_info;
然后,我们就可以得到一行关于EXPLAIN后面的SQL语句的执行计划信息。
如下图:
Step4:分析EXPLAIN给出的执行计划的每一列的信息。
EXPLAIN属性解析
属性 说明 情况 id 查询的序号,包含一组数字,表示查询中执行 select 子句或操作表的顺序。 1. id 相同
:执行顺序从上往下;
2.id 不同
:id 值越大,优先级越高,越先执行。select_type 查询类型,主要用于区别普通查询,联合查询,子查询等的复杂查询。 1.simple:简单的 select 查询,查询中不包含子查询或者 UNION;
2.primary:查询中若包含任何复杂的子部分,最外层查询被标记;
3.subquery:在 select 或 where 列表中包含了的子查询;
4.derived:在 from 列表中包含的子查询被标记为 derived(衍生),MySQL 会递归执行这些子查询,把结果放到临时表中;
5.union:如果第二个 select 出现在 UNION 之后,则被标记为 UNION,如果 union 包含在 from 子句的子查询中,外层 select 被标记为 derived;
6.union result:UNION 的结果。
table 展示查询涉及到的表名。 展示查询涉及到的表名。 partition 匹配的分区。 匹配的分区。 type
(重要指标)
显示连结类型,显示查询使用了何种类型,按照从最佳到最坏类型排序。 1.system:表中仅有一行(=系统表)这是 const 联结类型的一个特例;
2.const:表示通过索引一次就找到,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据,所以如果将主键置于 where 列表中,mysql 能将该查询转换为一个常量;
3.eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于唯一索引或者主键扫描;
4.ref:非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,可能会找多个符合条件的行,属于查找和扫描的混合体;
5.range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引,一般就是 where 语句中出现了 between、in 等范围的查询。这种范围扫描索引扫描比全表扫描要好,因为它开始于索引的某一个点,而结束另一个点,不用全表扫描; 6.index:index 与 all 区别为 index 类型只遍历索引树。通常比 all 快,因为索引文件比数据文件小很多;
7.all:遍历全表以找到匹配的行; 注意:一般保证查询至少达到 range 级别,最好能达到 ref。
possible_keys 可能使用到的索引 MySQL通过分析SQL后,得到的执行此SQL可能会使用到的索引。 key 显示MySQL实际执行SQL时决定使用的索引。 显示 MySQL 实际决定使用的键(索引)。
如果没有选择索引,键是 NULL。
查询中如果使用覆盖索引,则该索引和查询的 select 字段重叠。
key_len 表示索引中使用的字节数。 该列计算查询中使用的索引的长度。
在不损失精度的情况下,长度越短越好。
如果键是 NULL,则长度为 NULL。
该字段显示为索引字段的最大可能长度,并非实际使用长度。
ref 显示索引的哪一列被使用了。 显示索引的哪一列被使用了,如果是一个常数,有可能哪些列或常量被用于查询索引列上的值。 rows 估算需要扫描的行数。 根据表统计信息以及索引选用情况,大致估算出找到所需的记录所需要读取的行数。
filtered 表示符合查询条件的数据百分比。 表示符合查询条件的数据百分比。 Extra
(重要指标)
附加信息。 1.Using filesort:说明 mysql 会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成排序操作称为“文件排序”;
2.Using temporary:使用了临时表保存中间结果,mysql 在查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by;
3.Using index:表示相应的 select 操作用使用覆盖索引,避免访问了表的数据行。如果同时出现 using where,表名索引被用来执行索引键值的查找;如果没有同时出现 using where,表名索引用来读取数据而非执行查询动作;
4.Using where:表明使用 where 过滤;
5.using join buffer:使用了连接缓存;
6.impossible where:where 子句的值总是 false,不能用来获取任何元组;
7.select tables optimized away:在没有 group by 子句的情况下,基于索引优化 min、max 操作或者对于 MyISAM 存储引擎优化 count(*),不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化;
8.distinct:优化 distinct 操作,在找到第一匹配的元组后即停止找同样值的动作。
了解了上面的列信息后,我们接着看图:
一行一行看:
select_type = SIMPLE #表示该查询是最普通的查询,没有任何连表、合并、子查询等。
table = sys_user_base_info #from关键字后面的表。
partitions = NULL #无任何分区,该列一般在集群架构中做了数据分区分组才有相关信息。
type = ALL #查询连接方式,此处为ALL,代表全表扫描。 (这是一种性能最差的方式)
possible_keys = NULL #表示不会用到任何索引,因为产生了全表扫描。
key = NULL #表示实际查询也没用到任何索引,使用with关键字可以强制使用索引。
key_len = NULL #实际索引最大可能长度为空,因为实际没有使用到索引,所以为null。
ref = NULL #没有索引被使用,所以为null。
rows = 494698 #估算要扫描494698行数据。
filtered = 100 #符合查询条件的数据占总数据的100%
Extra = NULL #没有附加信息
如有哪些地方写的不对,欢迎指出错误指出。