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

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

5. 查看 SQL 执行成本:SHOW PROFILE

22ae55b626e6e1ea54578d2b08018236.png

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   | #当前是关闭状态
+---------------+-------+
1 row in set (0.01 sec)
mysql> set profiling = 'ON';#开启
Query OK, 0 rows affected, 1 warning (0.00 sec)

然后执行相关的查询语句。接着看下当前会话下有哪些profiles

mysql> SELECT * FROM student WHERE stuno = 3455655;
//...
mysql> SELECT * FROM student WHERE name = 'ZfCwDz';
//...
mysql> show profiles;
+----------+------------+---------------------------------------------+
| Query_ID | Duration   | Query                                       |
+----------+------------+---------------------------------------------+
|        1 | 0.00133475 | show variables like 'profiling'             |
|        2 | 0.00021050 | SELECT * FROM student WHERE stuno = 3455655 |
|        3 | 0.00053600 | SELECT DATABASE()                           |
|        4 | 0.01693325 | show databases                              |
|        5 | 0.00375125 | show tables                                 |
|        6 | 1.75597875 | SELECT * FROM student WHERE stuno = 3455655 |
|        7 | 1.11115150 | SELECT * FROM student WHERE name = 'ZfCwDz' |
+----------+------------+---------------------------------------------+
7 rows in set, 1 warning (0.00 sec)

你能看到当前会话一共有7个查询,如果我们想要查看最近一次查询的开销,可以使用

show profile;

1a208a75a95c6b3b4b70bb5111a87b1d.png

我们也可以查看指定的Query ID的开销,只需要后面跟上 for num。也可以查看不同部分的开销,比如CPU、block.io等

show profile cpu,block io for query 7;

b05a9a0b095cd2b2496295c622fdf402.png


通过如果发现上一条 sql 慢的原因在于执行慢(executing字段耗时多),就可以接着用 Explain 进行分析具体的 sql 语句。等后面我们为其建立索引,就可以大大提高效率了


🔊 show profile的常用查询参数:


① ALL:显示所有的开销信息。

② BLOCK IO:显示块 IO 开销。

③ CONTEXT SWITCHES:上下文切换开销。

④ CPU:显示 CPU 开销信息。

⑤ IPC:显示发送和接收开销信息。

⑥ MEMORY:显示内存开销信息。

⑦ PAGE FAULTS:显示页面错误开销信息。

⑧ SOURCE:显示和 Source_function,Source_file,Source_line 相关的开销信息。

⑨ SWAPS:显示交换次数开销信息。


🎨 日常开发需要注意的结论:


① Coverting Heap to MyISAM:查询结果太大,内存不够,正在往磁盘中迁移

② Creating tmp table:创建临时表,先拷贝数据到临时表,用完再删除临时表

③Coping to tmp table on disk:把内存中临时表复制到磁盘上,警惕!

④ locked


如果在 show profile 的查询结果中,出现了以上4条结果中的任何一条。则sql 语句需要优化


最后,还需要注意:


SHOW PROFILE 命令将被弃用,不过我们可以从 information_schema 中的 profiling 数据表进行查看


6. 分析查询语句:EXPLAIN(重点)


6.1 EXPLAIN 概述


0f20ee3121dfd0daee25ee3ba1db8b43.png


https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html


de7fcc7b8dbf7185f725b3005b5dca59.png


3. 版本情况


MySQL 5.6.3以前只能EXPLAIN SELECT ;MYSQL 5.6.3以后就可以EXPLAIN SELECT,UPDATE,DELETE


在5.7以前的版本中,想要显示partitions 需要使用explain partitions命令;想要显示

filtered 需要使用explain extended 命令。在5.7版本后,默认explain直接显示partitions和

filtered中的信息


76dca21873abe282bc211d8ebd1acf87.png


注意:EXPLAIN 仅仅是查看执行计划,不会真实的执行 sql


6.2 基本语法


EXPLAIN 或 DESCRIBE语句的语法形式如下:

EXPLAIN SELECT select_options
或者
DESCRIBE SELECT select_options

如果我们想看看某个查询的执行计划的话,可以在具体的查询语句前边加一个EXPLAIN ,就像这样:

mysql> EXPLAIN SELECT 1;


9a5762c760918391af90e5a10af5c529.png


7e286d10ad9a06464f4911aa5505bb79.png

EXPLAIN 语句输出的各个列的作用如下:


6f96464bb4a6d101534a0b93e49741b5.png

在这里把它们都列出来只是为了描述一个轮廓,让大家有一个大致的印象。

6.3 数据准备

1. 建表

CREATE TABLE s1 (
     id INT AUTO_INCREMENT,
     key1 VARCHAR(100),
     key2 INT,
     key3 VARCHAR(100),
     key_part1 VARCHAR(100),
     key_part2 VARCHAR(100),
     key_part3 VARCHAR(100),
     common_field VARCHAR(100),
      PRIMARY KEY (id),
      INDEX idx_key1 (key1),
      UNIQUE INDEX idx_key2 (key2),
      INDEX idx_key3 (key3),
      INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;
CREATE TABLE s2 (
     id INT AUTO_INCREMENT,
     key1 VARCHAR(100),
     key2 INT,
     key3 VARCHAR(100),
     key_part1 VARCHAR(100),
     key_part2 VARCHAR(100),
     key_part3 VARCHAR(100),
     common_field VARCHAR(100),
      PRIMARY KEY (id),
      INDEX idx_key1 (key1),
      UNIQUE INDEX idx_key2 (key2),
      INDEX idx_key3 (key3),
      INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

注:建两个表方便联合查询

2. 创建存储函数

DELIMITER 
CREATE FUNCTION rand_string1(n INT)
    RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
        SET i = i + 1;
    END WHILE;
    RETURN return_str;
END 
DELIMITER ;

创建函数,假如报错,需设置参数 log_bin_trust_function_creators,允许创建函数设置

set global log_bin_trust_function_creators=1;   # 不加global只是当前窗口有效。

3. 创建存储过程

创建往 s1 表中插入数据的存储过程:

DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO s1 VALUES(
     (min_num + i),
     rand_string1(6),
     (min_num + 30 * i + 5),
     rand_string1(6),
     rand_string1(10),
     rand_string1(5),
     rand_string1(10),
     rand_string1(10));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END //
DELIMITER ;

创建往 s2 表中插入数据的存储过程:

DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO s2 VALUES((min_num + i),
        rand_string1(6),
        (min_num + 30 * i + 5),
        rand_string1(6),
        rand_string1(10),
        rand_string1(5),
        rand_string1(10),
        rand_string1(10));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END //
DELIMITER ;

4. 调用存储过程

s1 表数据的添加:加入 1 万条记录:

CALL insert_s1(10001,10000);

s2 表数据的添加:加入 1 万条记录:

CALL insert_s2(10001,10000);


6.4 EXPLAIN 各列作用

为了让大家有比较好的体验,我们调整了下EXPLAIN输出列的顺序。

6.4.1 table


不论我们的查询语句有多复杂,里边儿包含了多少个表 ,到最后也是需要对每个表进行单表访问的,所以 MySQL 规定 EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法,该条记录的 table 列代表着该表的表名(有时不是真实的表名字,可能是简称)。


EXPLAIN SELECT * FROM s1 INNER JOIN s2;

如下图,一张表对应一个记录

7422bfdc6cf19b57ded5f4254ee65e35.png


注:临时表也会有对应的记录,比如我们使用UNION时就会出现临时表

6.4.2 id

例1:下面的查询结果,两个记录似乎id都是1.这是为什么呢?


81e902a5c7c9b5f221123284878eb8fa.png

实际上,在查询语句中每出现一个SELECT关键字,MySQL就会为它分配一个唯一的id ,代表着一次查询。这个id 就是 EXPLAIN语句的第一列。

例2:下面的查询中只有一个SELECT,所以EXPLAIN的结果中也就只有一条id为 1 的记录喽~


4ef38bf5e4227c4d210bbe65b1795fee.png

例3:下面的查询有两个SELECT,所以EXPLAIN的结果中 会有两条记录,且id分别就是1和2喽~ 。其中 s1被称为驱动表,s2被称为 被驱动表

fc6d95febc423e95d0260b14a944808d.png


例4:下面这条SQL有一个坑,请注意!!!

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');


两个记录的 id 都是 1,小小的眼睛是否充满了大大的疑惑?

f8df290a5abe558b37d4d2d30214511a.png

这是因为优化器会对上面的 sql 语句进行优化,将其转换为多表连接,而不是子查询。因为子查询其实是一种嵌套查询的情况,其时间复杂度是 O(n^m),其中 m 是嵌套的层数,而多表查询的时间复杂度是 O(n*m)


例5:再看看 Union 联合查询的情况。

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;


结果是这样,竟然会出现三张表~ Amazing!

839dcd890063ae9e8bee825a692ee3a0.png

这是因为 Union 是取表的并集,需要建临时表进行去重,因此会有三条记录。可以看到第三条记录的 Extra 就标识了它是一张临时表哦。临时表 id 是 Null


例6:再看看 Union ALL:

EXPLAIN SELECT * FROM s1  UNION ALL SELECT * FROM s2;


产生两条记录,因为它不会去重~

19c7755d36ff35764187600cbcc8a589.png

💌小结

  1. id如果相同,可以认为是一组,从上往下顺序执行
  2. 在所有组中,id值越大,优先级越高,越先执行
  3. 关注点:id号每个号码,表示一趟独立的查询, 一个sql的查询趟数越少越好




相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
8天前
|
监控 关系型数据库 MySQL
《MySQL 简易速速上手小册》第7章:MySQL监控和日志分析(2024 最新版)
《MySQL 简易速速上手小册》第7章:MySQL监控和日志分析(2024 最新版)
43 3
|
6天前
|
关系型数据库 MySQL 数据库
mysql数据库bin-log日志管理
mysql数据库bin-log日志管理
|
8天前
|
DataWorks 关系型数据库 MySQL
DataWorks产品使用合集之在DataWorks中,如何通过PolarDB for MySQL来查看binlog日志
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
35 1
|
8天前
|
SQL 监控 关系型数据库
【MySQL学习】MySQL的慢查询日志和错误日志
【MySQL学习】MySQL的慢查询日志和错误日志
|
8天前
|
SQL 存储 关系型数据库
MySQL慢日志的介绍以及如何使用问题
MySQL慢日志的介绍以及如何使用问题
26 0
|
8天前
|
存储 SQL 关系型数据库
mysql数据库日志
mysql数据库日志
|
8天前
|
SQL 存储 关系型数据库
Mysql主从同步 清理二进制日志的技巧
Mysql主从同步 清理二进制日志的技巧
16 1
|
存储 SQL 关系型数据库
慢查询与MySQL语句优化(下)
如果我们了解了Mysql中的索引原理之后,(详见探秘数据库 —— 事务 + InnoDB存储引擎),如何利用索引并对一些执行较慢的sql进行优化也是必要的,所以我们可以结合索引的原理来探究一下慢查询与优化的知识。
慢查询与MySQL语句优化(下)
|
存储 SQL 关系型数据库
慢查询与MySQL语句优化(上)
如果我们了解了Mysql中的索引原理之后,(详见探秘数据库 —— 事务 + InnoDB存储引擎),如何利用索引并对一些执行较慢的sql进行优化也是必要的,所以我们可以结合索引的原理来探究一下慢查询与优化的知识。
慢查询与MySQL语句优化(上)
|
6天前
|
关系型数据库 MySQL API
实时计算 Flink版产品使用合集之可以通过mysql-cdc动态监听MySQL数据库的数据变动吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
82 0