MySQL SHOW PROFILE 详解 | Eddie'Blog
MySQL SHOW PROFILE 详解

MySQL SHOW PROFILE 详解

eddie 325 2021-05-12

目录

MySQL SQL性能分析

TIPS

- 本文基于MySQL 8.0

SHOW PROFILE

SHOW PROFILE 是 MySQL 的一个性能分析命令,可以跟踪SQl各种资源消耗。使用格式如下:

语法:

SHOW PROFILE [type [, type] ...]
    [FOR QUERY n]
    [Limit row_count [OFFSET offset]]
type:{
    ALL                 显示所有的开销信息
    BLOCK IO            显示阻塞的输入输出次数(块IO开销)
    CONTEXT SWITCHES    显示自愿及非自愿的上下文切换次数
    CPU                 显示用户与系统CPU使用时间
    IPC                 显示消息发送与接收的次数
    MEMORY              显示内存相关的开销,目前未实现此功能
    PAGE FAULTS         显示页错误相关开销信息
    SOURCE              列出相应操作对应的函数名及其在源码中的位置(行)
    SWAP                显示swap交换次数
}

默认情况下,SHOW PROFILE 只展示Status和Duration两列,如果想展示更多信息,可指定type

使用步骤

  • 使用如下命令,查看是否支持 SHOW PROFILE 功能,yes标记支持。从MySQL5.0.37开始,MySQL支持SHOW PROFILE
mysql> SELECT @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)
  • 查看当前是否启用了 SHOW PROFILE 功能,0表示未启用,1表示已启用
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
  • 使用如下命令为当前会话开启或关闭性能分析,设成1表示开启,0表示关闭
mysql> set @@profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

## 查看是否开启
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)
  • 使用 SHOW PROFILES 命令,可为最近发送的SQL语句做一个概要的性能分析。展示的条目数由profiling_history_size会话变量设置,该变量的默认值为15。最大值未100。将值设置为0具有禁用分析的实际效果。
-- 默认展示15条
-- 扩展到100条
set profiling_history_size = 100;

例子:

## 查看salaries
mysql> SELECT * FROM salaries;

## 获取查询ID
mysql> show profiles;
+----------+------------+----------------------------------+
| Query_ID | Duration   | Query                            |
+----------+------------+----------------------------------+
|        1 | 0.00034525 | set @@profiling = 1              |
|        2 | 0.00040400 | SELECT @@profiling               |
|        3 | 0.00035925 | set profiling_history_size = 100 |
|        4 | 0.81526975 | SELECT * FROM salaries           |
+----------+------------+----------------------------------+
4 rows in set, 1 warning (0.00 sec)

## 根据查询ID获取整条查询消耗在哪里?
mysql> show profile for query 4;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000102 |   ## 开始
| Executing hook on transaction  | 0.000019 |   ## 在事务上执行钩子
| starting                       | 0.000020 |   ## 开始
| checking permissions           | 0.000018 |   ## 检查权限
| Opening tables                 | 0.000064 |   ## 打开表
| init                           | 0.000020 |   ## 初始化
| System lock                    | 0.000024 |   ## 系统锁
| optimizing                     | 0.000015 |   ## 优化
| statistics                     | 0.000032 |   ## 统计
| preparing                      | 0.000038 |   ## 准备
| executing                      | 0.814699 |   ## 执行
| end                            | 0.000012 |   ## 结束
| query end                      | 0.000004 |   ## 查询结束
| waiting for handler commit     | 0.000007 |   ## 等待处理程序提交
| closing tables                 | 0.000008 |   ## 关闭表
| freeing items                  | 0.000010 |   ## 释放
| logging slow query             | 0.000139 |   ## 慢查询日志
| cleaning up                    | 0.000039 |   ## 清理
+--------------------------------+----------+
18 rows in set, 1 warning (0.00 sec)

## 查看CPU与IO的开销
mysql> show profile cpu,block io for query 4;   
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000102 | 0.000036 |   0.000062 |            0 |             0 |
| Executing hook on transaction  | 0.000019 | 0.000006 |   0.000011 |            0 |             0 |
| starting                       | 0.000020 | 0.000008 |   0.000013 |            0 |             0 |
| checking permissions           | 0.000018 | 0.000006 |   0.000011 |            0 |             0 |
| Opening tables                 | 0.000064 | 0.000024 |   0.000041 |            0 |             0 |
| init                           | 0.000020 | 0.000007 |   0.000012 |            0 |             0 |
| System lock                    | 0.000024 | 0.000009 |   0.000015 |            0 |             0 |
| optimizing                     | 0.000015 | 0.000006 |   0.000010 |            0 |             0 |
| statistics                     | 0.000032 | 0.000012 |   0.000021 |            0 |             0 |
| preparing                      | 0.000038 | 0.000014 |   0.000024 |            0 |             0 |
| executing                      | 0.814699 | 0.794159 |   0.000000 |        73632 |             0 |
| end                            | 0.000012 | 0.000008 |   0.000000 |            0 |             0 |
| query end                      | 0.000004 | 0.000004 |   0.000000 |            0 |             0 |
| waiting for handler commit     | 0.000007 | 0.000007 |   0.000000 |            0 |             0 |
| closing tables                 | 0.000008 | 0.000008 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000010 | 0.000010 |   0.000000 |            0 |             0 |
| logging slow query             | 0.000139 | 0.000139 |   0.000000 |            0 |            16 |
| cleaning up                    | 0.000039 | 0.000038 |   0.000000 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
18 rows in set, 1 warning (0.00 sec)

还可以使用其他的参数比如 all、CPU等等参数...

最后使用完毕后,需要关闭 profiling

mysql> set @@profiling = 0;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

TIPS

- MySQL官方文档声明SHOW PROFILE已被废弃,并建议使用 Performance Schema作为替代品。

- 在某些系统上,性能分析只有部分功能可用。比如:部分功能在Windows系统下无效(show profile使用了getrusage()这个API,而在Windows上将会返回false,因为Windows不支持这个API); 此外,性能分析是进程级别的,而不是线程级别的,这意味着其他线程的活动可能会影响到你看到的计时信息。