目录
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）; 此外，性能分析是进程级别的，而不是线程级别的，这意味着其他线程的活动可能会影响到你看到的计时信息。
