MySQL SQL性能分析 show profile

MySQL show profile 和 show profiles 命令用于展示SQL语句执行过程中的资源使用情况,包括CPU的使用,CPU上下文切换,IO等待,内存使用等,这个命令对于分析某个SQL的性能瓶颈非常有帮助,借助于show profile的输出信息,能让我们知道一个SQL在哪个阶段耗时最长,消耗资源最多,从而为SQL优化,提高SQL性能提供重要的依据。

1. show profile语法

SHOW PROFILE [type [, type] ... ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]]

type: { ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS }

2. 启用profiling:

使用show profile命令之前,先启用profiling, profiling是session级变量,session关闭,该session的profiling信息也会丢失。

mysql> set profiling = 1;
3. show profiles

开启profiling之后,执行几条SQL,然后执行 show profiles 展示最近执行的几个SQL的执行耗时情况,具体能收集多少个SQL,由参数 profiling_history_size决定,默认值为15,最大值为100。如果设置为0,等同于关闭profiling。show profiles展示的是简要的耗时信息,如果想了解某个SQL的具体耗时情况,执行show profile 查看。

mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration   | Query                        |
+----------+------------+------------------------------+
|        1 | 0.00248450 | select * from blog.blog      |
|        2 | 0.00071050 | select * from blog.blog_type |
+----------+------------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
4. show profile for query n

show profile 展示一个SQL语句的执行耗时细节,如果不加 for query {n} 子句,默认展示最新的一次SQL的执行情况,加了for query {n},表示展示第n个 SQL的执行情况。比如看第1个SQL,执行show profile for query 1;

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000117 |
| checking permissions | 0.000015 |
| Opening tables       | 0.000033 |
| init                 | 0.000054 |
| System lock          | 0.000020 |
| optimizing           | 0.000006 |
| statistics           | 0.000021 |
| preparing            | 0.000017 |
| executing            | 0.000003 |
| Sending data         | 0.002045 |
| end                  | 0.000010 |
| query end            | 0.000060 |
| closing tables       | 0.000020 |
| freeing items        | 0.000047 |
| cleaning up          | 0.000019 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

在执行show profile时,可以加上类型子句,比如CPU,IPC等,查看具体某类资源的消耗情况,例如:
show profile cpu,ipc for query 1;

show profile 和 show profiles 除了这两个语句之外,可以展示所有的SQL语句的执行情况,包括有语法错误的SQL也会被监测到。比如下面Query_ID 为4的这个SQL,有语法错误,但仍然被记录到了show profiles;列表中。

mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration   | Query                        |
+----------+------------+------------------------------+
|        1 | 0.00248450 | select * from blog.blog      |
|        2 | 0.00071050 | select * from blog.blog_type |
|        3 | 0.00012125 | show profile for 1           |
|        4 | 0.00011900 | select from blog.blog        |
+----------+------------+------------------------------+
4 rows in set, 1 warning (0.00 sec)
5. show profile 将被废弃

在MySQL 5.7, 8.0版本里执行show profile命令,会有一个warning信息,提示如下:

'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead

show profile 命令将会被废弃,取而代之的是使用Performance Schema,Performance Schema 如何对SQL进行性能分析,见地址:

https://mytecdb.com/blogDetail.php?id=79

文章评论

0条评论