侧边栏壁纸
博主头像
码森林博主等级

一起走进码森林,享受编程的乐趣,发现科技的魅力,创造智能的未来!

  • 累计撰写 146 篇文章
  • 累计创建 74 个标签
  • 累计收到 4 条评论

目 录CONTENT

文章目录

MySQL | 通过 Show Profile 分析 SQL 执行性能

码森林
2022-12-08 / 0 评论 / 1 点赞 / 504 阅读 / 1,301 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2022-12-09,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

通过 EXPLAIN 分析执行计划,仅仅是停留在分析 SQL 的外部的执行情况,如果我们 想要深入到 MySQL 内核中,从执行线程的状态和时间来分析的话,这个时候我们就可以选择 Profile。

Show Profile 介绍

Show ProfileMySQL 提供的可以用来分析当前查询 SQL 语句执行的资源消耗情况的工具,可用于 SQL 调优的测量。默认情况下处于关闭状态,开启会消耗一定的性能,一般在 SQL 分析和优化的时候使用,只保存最近15次的运行结果。

通过 SHOW PROFILESSHOW PROFILE 显示语句剖析信息,其指示当前会话期间执行的语句的资源使用情况。

其可以分析执行线程的状态和时间,还支持进一步选择 ALL、CPU、MEMORY、 BLOCK IO、CONTEXT SWITCHES 等类型来查询 SQL 语句在不同系统资源上所消耗的时间。

其中 SHOW PROFILES显示发送到服务器的最新语句列表。列表的大小由profiling_history_size会话变量控制,该变量的默认值为 15。最大值为 100。将值设置为 0 具有禁用剖析的实际效果。

SHOW PROFILE 显示有关单个语句的详细信息。没有FOR QUERY n 子句,输出与最近执行的语句有关。如果包含FOR QUERY n,将显示语句n的信息。n 的值对应于SHOW PROFILES显示的Query_ID值。

使用步骤

1、开启功能

默认该功能是关闭的,使用前需提前开启 Show Profile 功能。

查看功能是否开启:

mysql> SHOW VARIABLES LIKE 'profiling';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

开启功能,使用 SET profiling = ON;SET profiling = 1;

mysql> SET profiling = ON;

Query OK, 0 rows affected (0.01 sec)
 
mysql> SHOW VARIABLES LIKE 'profiling';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set (0.01 sec)

2、执行语句

SELECT * FROM test.business_order WHERE out_trade_no = '2009122144108953'
SELECT * FROM test.business_order WHERE order_type = 2;

3、通过 SHOW PROFILES 查看结果

mysql> SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                     |
+----------+------------+---------------------------------------------------------------------------+
|        1 | 0.00048500 | SELECT * FROM test.business_order WHERE out_trade_no = '2009122144108953' |
|        2 | 2.89989175 | SELECT * FROM test.business_order WHERE order_type = 2                    |
+----------+------------+---------------------------------------------------------------------------+
2 rows in set (0.02 sec)

其中列 Duration 代表耗时,单位为秒。

4、使用 SHOW PROFILE 对 SQL 语句进行诊断

语法:SHOW PROFILE type1,type2,.. FOR QUERY n,通过 type 值可以显示特定的额外信息,其中 type 的类型有如下几种:

  • ALL:显示所有开销信息。
  • BLOCK IO:显示阻塞的输入输出次数。
  • CONTEXT SWITCHES:显示自愿和非自愿上下文切换的计数。
  • CPU:显示用户和系统CPU使用时间。
  • IPC:显示已发送和接收的消息计数。
  • MEMORY:显示内存开销信息,目前无用。
  • PAGE FAULTS:显示主要和次要页面故障计数。
  • SOURCE:显示源代码中的函数名称,以及函数发生文件的名称和行号。
  • SWAPS:显示 swap 交换计数。

查询 QUERY_ID 为 2 的语句信息:

mysql> SHOW PROFILE CPU, CONTEXT SWITCHES, BLOCK IO FOR QUERY 2;

执行结果为:

image-20221206153702737

可以看到 executing 该状态执行耗时最长,因为线程正在读取和处理SELECT语句的行,并将数据发送到客户端。由于在此状态下执行大量的磁盘访问(读取)操作,因此它通常是给定查询生命周期中运行时间最长的状态。(基于MySQL 8.0.17之后版本)

行字段含义(其与 information_schema.PROFILING 字段对应,该表同已废弃,未来将会被删除) :

+---------------------+--------------+------+-----+----------+-------+
| Field               | Type         | Null | Key | Default  | Extra |
+---------------------+--------------+------+-----+----------+-------+
| QUERY_ID            | int(20)      | NO   |     | 0        |       | # 语句ID
| STATE               | varchar(30)  | NO   |     |          |       | # 状态
| DURATION            | decimal(9,6) | NO   |     | 0.000000 |       | # 持续时间,单位s
| CPU_USER            | decimal(9,6) | YES  |     | NULL     |       | # 用户态CPU时间,单位s
| CPU_SYSTEM          | decimal(9,6) | YES  |     | NULL     |       | # 系统态CPU时间,单位s
| CONTEXT_VOLUNTARY   | int(20)      | YES  |     | NULL     |       | # 自愿上下文切换次数
| CONTEXT_INVOLUNTARY | int(20)      | YES  |     | NULL     |       | # 非自愿上下文切换次数
| BLOCK_OPS_IN        | int(20)      | YES  |     | NULL     |       | # 块输入次数
| BLOCK_OPS_OUT       | int(20)      | YES  |     | NULL     |       | # 块输出次数
| MESSAGES_SENT       | int(20)      | YES  |     | NULL     |       | # 发送的消息数量
| MESSAGES_RECEIVED   | int(20)      | YES  |     | NULL     |       | # 接收的消息数量
| PAGE_FAULTS_MAJOR   | int(20)      | YES  |     | NULL     |       | # 主要页面错误数量
| PAGE_FAULTS_MINOR   | int(20)      | YES  |     | NULL     |       | # 次要页面错误数量
| SWAPS               | int(20)      | YES  |     | NULL     |       | # 交换次数
| SOURCE_FUNCTION     | varchar(30)  | YES  |     | NULL     |       | # 源代码函数
| SOURCE_FILE         | varchar(20)  | YES  |     | NULL     |       | # 源代码文件
| SOURCE_LINE         | int(20)      | YES  |     | NULL     |       | # 源代码行数
+---------------------+--------------+------+-----+----------+-------+

STATUS 列表示线程的状态,同 information_schema.PROCESSLIST 中的 STATE 列。详见《MySQL | PROCESSLIST 详解》

注:在 MySQL 5.7 中 SHOW PROFILESHOW PROFILES 已被弃用,预计会在后续 MySQL 版本中被删除。建议使用 Performance Schema。

小结

从本文讲解了通过 SHOW PROFILE 的方法来分析 SQL,从而通过分析结果找到性能瓶颈。

1

评论区