《MySQL高级篇》七、性能分析工具的使用(慢查询日志 | EXPLAIN | SHOW PROFILING | 视图分析 )(三)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 《MySQL高级篇》七、性能分析工具的使用(慢查询日志 | EXPLAIN | SHOW PROFILING | 视图分析 )

6.4.3 select_type


6399b387426312a0db573048fbc61a69.png

dadcebd3c22a798e8dcf733fc5bdb336.png


①:查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型


3e649e49816e6c5f36d7a89200e8e9ec.png


再看下连接查询,可以看到 连接查询也算是SIMPLE类型


3f8103e5fd2aa2c57809d89bcecfb954.png


②:Union 联合查询。其左边的查询是 Primary,右边的查询类型是 Union,去重的临时表查询类型是: Union Result


对于包含UNION或者UNION ALL的大查询来说,它是由几个小查询组成的,其中除了最左边的那个查询的select_type值就是PRIMARY,其余的小查询的select_type值就是UNION


MySQL选择使用临时表来完成UNION查询的去重工作,针对该临时表的查询的select_type就是

UNION RESULT


对应子查询的大查询来说,子查询是外边的那个是PRIMARY


e8a95008ea4188099f116abb2aa471ef.png


ac17b7edf788865bde901b16ec2df81f.png


③:不会被优化成多表连接的子查询


如果包含子查询的查询语句不能够转为多表连接的形式(也就是不会被优化器进行自动的优化),并且该子查询是不相关的子查询


该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY。也就是外层查询是 Primary,内层查询是 SUBQUERY


579860e8c0a709e5f138cd7198b5274e.png


如果子查询不能被转换为多表连接的形式,并且该子查询是相关子查询。


比如下面的查询在内部子查询使用了外部的表。则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY。 外层查询是Primary,内层查询是DEPENDENT SUBQUERY


bf53d00e679cc002f7639a072ff679d8.png


需要注意的是 DEPENDENT SUBQUERY 的查询语句可能会被执行多次,因为内层查询依赖于外层的查询,因此可能会是外层传一个值,内层就执行一次的模式。


子查询需要执行多次,即采用循环的方式,先从外部查询开始,每次都传入子查询进行查询,然后再将结果反馈给外部,这种嵌套的执行方式就称为相关子查询。


子查询从数据表中查询了数据结果,如果这个数据结果只执行一次,然后这个数据结果作为主查询的条 件进行执行,那么这样的子查询叫做不相关子查询。


④:包含UNION或者UNION ALL的子查询


在包含 Union或者 Union All 的子查询 sql 中,如果各个小查询都依赖于外查询,那么除了最左边的小查询外,各个小查询的类型都是 DEPENDENT UNION


443c33090218e85489aa896c3d40bac5.png


外查询是 Primary,最左边的子查询是 DEPENDENT SUBQUERY,后面的子查询是 DEPENDENT UNION,临时去重表的类型是 Union Result。这里大家可能要困惑,第一个子查询中也没有看到依赖 s1 啊。这其实也是优化器会在执行时进行优化,将 IN 改成 Exist,并且把外部的表移到内部去。这里我们了解就行,以后会有文章给大家介绍优化器的。


⑤:关于派生表的子查询


对于包含派生表的查询,该派生表对应的子查询的select_type就是DERIVED


4b7255cc3499c770af4107d58d068f91.png


⑥:子查询的物化后与外层连接查询


当优化器在执行子查询时选择把子查询优化成为一张物化表,与外层查询进行连接查询时。


c7a1ad766c232bc9908fe3774c7f4405.png


从下往上看,子查询的查询类型是 MATERIALIZED;物化过程是基于 id 为 2 的查询结果表进行的,其 table 是 subquery 2,查询类型是 SIMPLE,而外层也相当于是与固定的直接值进行查询,其类型也是 SIMPLE


上面的介绍都是一些基本的情况,还没有真正的介绍与索引相关的情况哦。觉得是不是晕晕的了,我们用一个表格进行下总结吧


6.4.4 partitions (可略)


代表分区表中的命中情况,非分区表,该项为 NULL。一般情况下我们的查询语句的执行计划的partitions 列的值都是NULL


官方文档:https://dev.mysql.com/doc/refman/8.0/en/alter-table-partition-operations.html


如果想详细了解,可以如下方式测试。创建分区表:

-- 创建分区表,
-- 按照id分区,id<100 p0分区,其他p1分区
CREATE TABLE user_partitions (id INT auto_increment,
    NAME VARCHAR(12),PRIMARY KEY(id))
    PARTITION BY RANGE(id)(
    PARTITION p0 VALUES less than(100),
    PARTITION p1 VALUES less than MAXVALUE
 );

查询 id 大于200(200>100,p1分区)的记录,查看执行计划,partitions 是 p1,符合我们的分区规则


64bc2fbe360a54815bef3eaf3cd0baca.png


6.4.5 type ☆


7c4a44543222a55b2b32b0f6bdbe7e80.png


完整的访问方法如下: system 、const、eq_ref 、ref 、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、 range、index、ALL。


我们详细解释一下:


1️⃣system


当表中只有一条记录,并且该表中存储引擎统计数据是精确的,比如 MYISAM,Memory,那么其访问方法就是System。这种方式几乎是性能最高的,当然我们几乎用不上。


CREATE TABLE t(i int) Engine=MyISAM;#创建表
INSERT INTO t VALUES(1);# 插入第一条记录
EXPLAIN SELECT * FROM t;# 查看性能

5a16dae8a7ed78b4c964cc5e0a090ab1.png

但凡我们再插入一条数据,其访问方式就变成了性能最差的全表扫描 ALL。


62fdaf009f7df3825af794fa7f08e940.png


如果存储引擎是InnoDB,即使只有一条数据,其访问方式也是ALL,这是因为 InnnoDB 访问数据不是精确的


7795326f2fbf062b3f780738d090513a.png


2️⃣Const


当我们根据主键或者唯一的二级索引,与常数进行等值匹配时,对单表的访问方法就是 const。这个访问方式的效率低于 system,但也是很高效的。


比如对主键与常数匹配,进行等值查询


EXPLAIN SELECT * FROM s1 WHERE id = 10005;

abd6b80cf9af83f23f96405ba7a508ce.png


比如对Unique标识的唯一二级索引key2与常数匹配,进行等值查询。


EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;

33fed1ec92753f57c04b30d4e49408e4.png


当我们把where后面的关键字改成key3 普通索引时,由于key3的字段值是可重复的。type类型就变成了 All,全表查询


039a334bda693450a704db328f63232a.png


注意:此时可能有细心的小伙伴可能注意到我们的key3不是也有索引idx_key3,性能再查也不至于全表查询吧~ 但是忘记了一个细节,key3本身是varchar类型的, key3 = 10066会进行一个隐式类型转换,从而会导致 索引失效,我们也可以看到 key列对应的为空。


当修改成key3 = 10066后,结果如下图:


aa7cef25b51eed6717a0dcca611cb57c.png


3️⃣eq_ref


再进行连接查询时,如果被驱动表是通过主键或者唯一二级索引等值匹配的方式进行查询的,那么被驱动表的访问方式是 eq_ref。这也是一种性能很不错的方式。


EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;


上面连接查询语句,对于驱动表来说,就是对s1全表进行扫描,找到符合条件的数据,因此其type是All,对被驱动表来说,相于直接访问驱动表查询到的数据进行等值查询,因此其访问方式是eq_ref


4️⃣ref


当使用普通的二级索引与常量进行等值匹配时,type 是 ref。


EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

a9d443aa81518ab0953bfbab0fe7d804.png


下面考考你。以下 sql 的引用类型是什么呢?


EXPLAIN SELECT * FROM s1 WHERE key3 = 10066;

看看答案。你是不是猜错了。是 All。这是因为 key3 的字段 varchar 类型,但是我们这里常量值是整形,因此需要使用函数进行隐式的类型转换,一旦使用函数,索引就失效了,因此访问类型变成了全表扫描 All


8e0bf84c12e29232846f33c0d5ccb348.png


当我们常量使用对应的类型,就是期望的ref访问类型了


4cd4dc518ad4a0d5e6d1aff2a5702c88.png


5️⃣ref_or_null


当使用普通的二级索引进行等值匹配时,当索引值可以是 Null 时,type 是 ref_or_null。


EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;

da666279f585df3d32ed6283bf355e3e.png


6️⃣index_merge


当进行单表访问时,如果多个查询字段分别建立了单列索引,使用 OR 连接,其访问类型是 index_merge。同时还可以看到 key 这一字段,是使用了多个索引


EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

dd0ba7cdae1855b1be885c705fe66371.png


猜猜下面 sql 的引用类型


EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

猜对了吗?答案是 ref,这是因为用 AND 连接两个查询时,实际上只使用了 key1 的索引。

55ced78a07a00fd64e29d529b18f0b3a.png


7️⃣unique_subquery


针对一些包含 IN 的 子查询的查询语句中,如果优化器决定将 IN 子查询优化为 EXIST 子查询,而且子查询可以使用主键进行等值匹配的话,那么该子查询执行计划的 type 就是 unique_subquery


EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';

9c7a7bbc8c50f71475d666d9cd2d37ef.png


8️⃣range


如果使用索引获取某些范围区间的记录,那么就可能使用到range访问方法


EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';

072c8b19335c8248750bb53fac6d67d1.png


9️⃣index


当我们可以使用索引覆盖,但是需要扫描的全部的索引记录时,该表的访问方式就是 index。索引覆盖后面文章介绍优化器时会详细介绍,为了便于大家理解,先简单介绍如下。比如下面 sql 语句中,key_part2 ,key_part2 都属于联合索引 idx_key_part(key_part1, key_part2, key_part3) 的一部分,在查找数据时可以用上这个联合索引,而不用进行回表操作,这种情况即索引覆盖


EXPLAIN SELECT key_part2 FROM s1 WHERE key_part2 = 'a';

cd850a3f00131c5bf7a465d85ccf6fee.png

1️⃣0️⃣ALL


最熟悉的全表扫描 ALL


EXPLAIN SELECT * FROM s1;


c84a8446fde8bf53098ace03aa7c8b0f.png

❤温馨提示:这里很多小伙伴会觉得记不住,其实您可以收藏这篇博客,执行 EXPLAIN 时对应结果,反向查找博文对应内容,毕竟咱们只需要能够读懂性能分析的结果


小结


结果值从最好到最坏依次是: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 其中比较重要的几个提取出来(见绿色部分)


SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,最好是 consts 级别(阿里巴巴开发手册要求)


6.4.6 possible_keys 和 key


EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';

d2360c18b0684cb61dde5357d294e9a9.png


对应优化器来说,可以选择的 possible_keys 越少越好,因为选项越多,进行过滤花的时间也就对应更多。另外,优化器会对各个索引进行查询的效率进行评估,以此来选择实际使用的 key。而且由于优化器会对 sql 进行优化,完全可能会出现 possible_keys 是 null,但是 key 不为 null 的情况


6.4.7 key_len ☆


实际使用的索引的长度,单位是字节。可以帮助你检查是否充分利用了索引,主要针对联合索引具有一定的参考,对同一索引来说,key_len 值越大越好(与自己比较,后面将解释)。


① 下面SQL执行结果是 4,这个结果怎么算出来的呢?


a4316c0090b8f48957d5b3c1c1835af9.png


这是因为使用的是主键 id 作为索引,其类型是 int,占 4 个字节


② 再来猜猜下面的 key_len 是多少~


EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;

什么?你猜的是 4,而答案是 5~


75ea9a870793560b37ce236fe1bfc5dd.png


这是因为虽然 key2 也是 int 类型,但是它被 unique 修饰,并没有标识非空(而主键都是非空的),因此加上空值标记,一共是5字节


③ 字符类型的索引长度为多少呢


 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

438e61f3fbac7c2c247e6f0790983738.png


答案是 303,因为类型是 varchar(100),100 个字符,utf-8 每个字符占 3 个字节,共 300 个字节,加上变长列表 2 个字节与一个空值标识占一个字节,共 303 字节。


④ 看看联合索引的情况


看下面的联合索引,key_len还是303,不需要解释了吧

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';

91c29df3acfd7f2ff812283d986eaac5.png


再看看下面这个联合索引,其结果是 606

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';

74e99d43b8edeca80cac319d6ad3683c.png

这个查询的 key-len 比上面的查询大,性能就比上面的好,怎么理解呢?其实只要你看过我之前介绍B+树的文章就很容易理解了。因为在目录页我除了考虑 key_part1 ,还会考虑 key_part2,定位到的数据就更加精准,范围更小,需要加载 I/O 的数据页数量就会更少,这样是不是性能就比较好啊~


猜猜下面的 sql 执行后 key_len 是多少

EXPLAIN SELECT * FROM s1 WHERE key_part3 = 'a';

085a9fd83bb9a904f8e19ef3c3e30b86.png


是空哦,因为都不会使用到索引,这就是我们一直在提的最左前缀原则,后面会详细介绍的。


📚练习:key_len的长度计算公式:


varchar(10)变长字段且允许NULL = 10 *( character set:utf8=3, gbk=2, latin1=1) + 1(NULL)+2(变长字段)

varchar(10)变长字段且不允许NULL = 10* ( character set:utf8=3 ,gbk=2, latin1=1) +2(变长字段)

char(10)固定字段且允许NULL = 10 *( character set:utf8=3, gbk=2, latin1=1) +1(NULL)

char(10)固定字段且不允许NULL = 10* ( character set:utf8=3,gbk=2,latin1=1)


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
11天前
|
Linux 应用服务中间件 PHP
性能工具之linux常见日志统计分析命令
通过本文的介绍,我相信同学们一定会发现 linux三剑客强大之处。在命令行中,它还能够接受,和执行外部的 AWK 程序文件,可以对文本信息进行非常复杂的处理,可以说“只有想不到的,没有它做不到的。
62 1
|
3天前
|
关系型数据库 MySQL 索引
【MySQL】视图 -- 详解
【MySQL】视图 -- 详解
|
1天前
|
存储 监控 Ubuntu
Linux系统之GoAccess实时Web日志分析工具的基本使用
【5月更文挑战第22天】Linux系统之GoAccess实时Web日志分析工具的基本使用
9 0
|
11天前
|
关系型数据库 MySQL
MySQL第四战:视图以及常见面试题(上)
MySQL第四战:视图以及常见面试题(上)
|
11天前
|
存储 SQL 关系型数据库
MySQL 底层数据结构 聚簇索引以及二级索引 Explain的使用
MySQL 底层数据结构 聚簇索引以及二级索引 Explain的使用
33 0
|
11天前
|
SQL 监控 关系型数据库
【MySQL学习】MySQL的慢查询日志和错误日志
【MySQL学习】MySQL的慢查询日志和错误日志
|
11天前
|
数据可视化
R语言两阶段最小⼆乘法2SLS回归、工具变量法分析股息收益、股权溢价和surfaces曲面图可视化
R语言两阶段最小⼆乘法2SLS回归、工具变量法分析股息收益、股权溢价和surfaces曲面图可视化
|
11天前
|
监控 Linux 开发者
【专栏】`head`命令是Linux系统中用于快速查看文件开头内容的工具,常用于处理日志文件
【4月更文挑战第28天】`head`命令是Linux系统中用于快速查看文件开头内容的工具,常用于处理日志文件。基本用法包括指定查看行数(如`head -n 10 file.txt`)和与其他命令(如`grep`)结合使用。高级用法涉及动态查看日志、过滤内容、管道操作及在脚本中的应用。实际应用案例包括监控系统日志、排查错误和分析应用日志。使用时注意文件存在性、行数选择及权限问题。熟练掌握head命令能提升工作效率,结合其他工具可实现更多功能,助力Linux用户提升技能。
|
11天前
工具变量法(两阶段最小二乘法2SLS)线性模型分析人均食品消费时间序列数据和回归诊断2
工具变量法(两阶段最小二乘法2SLS)线性模型分析人均食品消费时间序列数据和回归诊断
|
存储 SQL 关系型数据库
慢查询与MySQL语句优化(下)
如果我们了解了Mysql中的索引原理之后,(详见探秘数据库 —— 事务 + InnoDB存储引擎),如何利用索引并对一些执行较慢的sql进行优化也是必要的,所以我们可以结合索引的原理来探究一下慢查询与优化的知识。
慢查询与MySQL语句优化(下)