mysql show profiles 性能跟踪诊断工具

发布时间:2022-03-01 10:43:46 作者:yexindonglai@163.com 阅读(63)

前言

Show profiles最大的好处是可以查看执行sql语句时各个环节的执行时间,
Show profiles是5.0.37之后添加的,要想使用此功能,要确保版本在5.0.37之后。

可以执行sqlselect version();查看当前数据库版本。

开启 profiling

启动数据库后,profiles默认是关闭的,并且开启后只存在当前会话,因为它是保存在内存中的,不会持久化存储,重新登录或者重启后就会重置;可通过下面的语句开启

  1. -- 以下2条语句选其一即可
  2. set profiling = 1;
  3. set profiling=on;

通过下面的语句查看profiles的启动状态,Value值为ON 表示是开启的

  1. -- 2条语句选其一即可
  2. show variables like 'profiling';
  3. select @@profiling;

show profiles 查看sql执行所需时间

接下来我们查询一条sql,

  1. mysql> select * from student limit 1;
  2. +-----+--------+------+
  3. | id | name | age |
  4. +-----+--------+------+
  5. | 145 | 王五 | 30 |
  6. +-----+--------+------+
  7. 1 row in set (0.00 sec)

然后在输入show profiles;,就会显示刚刚查询的sql执行了多长时间,其中(Druation的单位为秒)

  1. mysql> show profiles;
  2. +----------+------------+---------------------------------+
  3. | Query_ID | Duration | Query |
  4. +----------+------------+---------------------------------+
  5. | 7 | 0.00023875 | select * from student limit 1 |
  6. +----------+------------+----------------------------------

我们看到结果中有个Query_ID,输入 命令show profile for query 7;,则可以看到更加详细的时间,我们可以非常清楚的查看每一步的耗时,其中Druation的单位为秒)。这样,当我们遇到一条慢SQL时,就能很清楚的知道,为什么慢,慢在哪一步了。

  1. mysql> show profile for query 7;
  2. +----------------------+----------+
  3. | Status | Duration |
  4. +----------------------+----------+
  5. | starting | 0.000062 |
  6. | checking permissions | 0.000007 |
  7. | Opening tables | 0.000023 |
  8. | init | 0.000021 |
  9. | System lock | 0.000008 |
  10. | optimizing | 0.000005 |
  11. | statistics | 0.000014 |
  12. | preparing | 0.000012 |
  13. | executing | 0.000003 |
  14. | Sending data | 0.000044 |
  15. | end | 0.000004 |
  16. | query end | 0.000007 |
  17. | closing tables | 0.000008 |
  18. | freeing items | 0.000010 |
  19. | cleaning up | 0.000012 |
  20. +----------------------+----------+
  21. 15 rows in set, 1 warning (0.00 sec)

那么这些status都代表什么意思呢?我们选择几个比较重要的项说明一下

  • starting

    lex+yacc 语法语义解析,得到解析树

  • checking permissions

    检查权限,但是在某些SQL语句下,状态为checking permission时,并不一定真的在做权限检查。

所以不要频繁的向数据库发送show full tables from test like ‘%demo%’,尤其是在表的数量很多时。

  • Opening tables

    打开表做好table cache,做好和innodb表物理文件的关联,同时加MDL LOCK 主要函数open_tables

  • System lock

    主要函数handler::ha_external_lock,之前会实现myisam等引擎的mysql层表锁,innodb做共享表锁。

  • Sending data

    “Sending data”并不是单纯的发送数据,而是包括“收集 + 发送 数据”。这里是收集是从磁盘收集数据;

show warnings 查看警告信息

持此之外我们还看到最后一行有有一个警告

  1. 15 rows in set, 1 warning (0.00 sec)

接下来我们可以通过# show warnings命令看看这个警告是什么

  1. mysql> show warnings;
  2. +---------+------+-------------------------------------------------------------------------------------------------------------+
  3. | Level | Code | Message |
  4. +---------+------+-------------------------------------------------------------------------------------------------------------+
  5. | Warning | 1287 | 'SHOW PROFILE' is deprecated and will be removed in a future release. Please use Performance Schema instead |
  6. +---------+------+-------------------------------------------------------------------------------------------------------------+
  7. 1 row in set (0.00 sec)

这个警告是告诉你“SHOW PROFILE”已弃用,将在将来的版本中删除。请改用性能模式;虽然说会被弃用,但是在低版本中,这个命令还是会被经常用到。

其他用法

除此之外,show profile [type] 还有一个type属性,可以指定 可选值以显示特定的附加信息类型:

  1. ==ALL==: 显示所有信息
  2. ==BLOCK IO==: 显示块输入和输出操作的计数
  3. ==CONTEXT SWITCHES== :显示自愿和非自愿上下文切换的计数
  4. ==CPU== :显示用户和系统 CPU 使用时间
  5. ==IPC== :显示发送和接收消息的计数
  6. ==MEMORY== :目前未实施
  7. ==PAGE FAULTS== :显示主要和次要页面错误的计数
  8. ==SOURCE== :显示源代码中函数的名称,以及函数所在文件的名称和行号
  9. ==SWAPS==: 显示交换计数

关键字Mysql