MySQL SHOW PROFILE 详解

[TOC] # 目录 ## MySQL SQL性能分析 > TIPS - 本文基于MySQL 8.0 ### SHOW PROFILE SHOW PROFILE 是 MySQL 的一个性能分析命令，可以跟踪SQl各种资源消耗。使用格式如下：语法： ```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 ```sql mysql> SELECT @@have_profiling; +------------------+ | @@have_profiling | +------------------+ | YES | +------------------+ 1 row in set, 1 warning (0.00 sec) ``` - 查看当前是否启用了 SHOW PROFILE 功能，0表示未启用，1表示已启用 ```sql mysql> SELECT @@profiling; +-------------+ | @@profiling | +-------------+ | 0 | +-------------+ 1 row in set, 1 warning (0.00 sec) ``` - 使用如下命令为当前会话开启或关闭性能分析，设成1表示开启，0表示关闭 ```sql 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具有禁用分析的实际效果。 ```sql -- 默认展示15条 -- 扩展到100条 set profiling_history_size = 100; ``` 例子： ```sql ## 查看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 ```sql 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）; 此外，性能分析是进程级别的，而不是线程级别的，这意味着其他线程的活动可能会影响到你看到的计时信息。