使用show-profile调优测量SQL

一、介绍

show profile是mysql中提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量。

二、使用

查看是否开启:

show variables like 'profiling%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+

开启:

set profiling=ON

使用方式:

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

我们先不带任何参数执行一次,查看执行结果:

show profiles;

通过执行上述命令,列出最近执行的sql语句,默认15条,如下:

+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 0.01163900 | show variables like 'profiling' |
|        2 | 0.00063200 | select * from article           |
|        3 | 0.00036100 | SELECT DATABASE()               |
|        4 | 0.00330700 | show databases                  |
|        5 | 0.00287000 | show tables                     |
|        6 | 0.00065100 | select * from article  

我们看到有三列,分别是查询ID,消耗时间以及对应的sql,我们如何查看资源的消耗情况呢?

show profilestype参数:

type 说明
ALL 显示所有开销
BLOCK IO 显示块IO相关开销
CONTEXT SWITCHES 上下文切换相关开销
CPU 显示CPU相关开销信息
IPC 显示发送和接收相关信息
MEMORY 显示内存相关开销信息
PAGE FAULTS 显示和source_function,source_file,source_linexiang guan
SWAPS 显示交换次数相关开销
SOURCE 显示和Source_function,Source_file和Source_line相关开销信息

我们查看某一条的queryCPU开销信息:

SHOW PROFILE CPU FOR QUERY 6;

+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000077 | 0.000068 |   0.000009 |
| checking permissions | 0.000013 | 0.000008 |   0.000004 |
| Opening tables       | 0.000025 | 0.000023 |   0.000002 |
| init                 | 0.000078 | 0.000045 |   0.000020 |
| System lock          | 0.000036 | 0.000018 |   0.000018 |
| optimizing           | 0.000007 | 0.000004 |   0.000003 |
| statistics           | 0.000021 | 0.000019 |   0.000002 |
| preparing            | 0.000016 | 0.000016 |   0.000012 |
| executing            | 0.000017 | 0.000002 |   0.000002 |
| Sending data         | 0.000263 | 0.000138 |   0.000125 |
| end                  | 0.000006 | 0.000003 |   0.000003 |
| query end            | 0.000024 | 0.000010 |   0.000014 |
| closing tables       | 0.000010 | 0.000009 |   0.000002 |
| freeing items        | 0.000024 | 0.000010 |   0.000014 |
| cleaning up          | 0.000034 | 0.000019 |   0.000015 |
+----------------------+----------+----------+------------+

三、总结

我们查看status状态信息,当出现以下状态说明非常危险了,需要需要调优:

  • converting HEAP to MyISAM :查询结果太大,导致内存不够用,数据往硬盘上搬
  • Creating tmp table:创建临时表,耗时动作:拷贝数据到临时表;用完再删除
  • Copying to tmp table on disk:把内存中的临时表复制到磁盘,很危险。
  • locked:锁表

四、参考

SHOW PROFILE Syntax,by mysql

发表评论

电子邮件地址不会被公开。 必填项已用*标注