MySQL show profile使用概述

分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。该参数可以在全局和session级别来设置。对于全局级别则作用于整个MySQL实例，而session级别紧影响当前session。该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。根据这些开销进一步分析当前SQL瓶颈从而进行优化与调整。本文描述了如何使用MySQL profile，不涉及具体的样例分析。 如何查看执行SQL的耗时的步骤：开启profile、发送sql、查看profile的资源开销结果、关闭profile。 mysql> select version(); profile默认是不打开的 mysql> show profiles; Empty set (0.02 sec) 验证修改后的结果 mysql> show variables like "%pro%"; 可以看到profiling 默认是OFF的。 开启profile，然后测试 开启profile mysql> set profiling=1; 获取profile的帮助   help profile;   root@localhost[sakila]> help profile;   Name: 'SHOW PROFILE'   Description:   Syntax:   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             --显示和Source_function，Source_file，Source_line相关的开销信息     | SWAPS              --显示交换次数相关开销的信息       执行业务SQL，并用profile分析示例： --发布SQL查询   root@localhost[sakila]> select count(*) from customer;   +----------+   | count(*) |   +----------+   |      599 |   +----------+      --查看当前session所有已产生的profile   root@localhost[sakila]> show profiles;   +----------+------------+--------------------------------+   | Query_ID | Duration   | Query                          |   +----------+------------+--------------------------------+   |        1 | 0.00253600 | show variables like '%profil%' |   |        2 | 0.00138150 | select count(*) from customer  |   +----------+------------+--------------------------------+   2 rows in set, 1 warning (0.01 sec)      --我们看到有2个warning，之前一个，现在一个   root@localhost[sakila]> show warnings;    --下面的结果表明SHOW PROFILES将来会被Performance Schema替换掉   +---------+------+--------------------------------------------------------------------------------------------------------------+   | Level   | Code | Message                                                                                                      |   +---------+------+--------------------------------------------------------------------------------------------------------------+   | Warning | 1287 | 'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead |   +---------+------+--------------------------------------------------------------------------------------------------------------+  获取SQL语句的开销信息 --可以直接使用show profile来查看上一条SQL语句的开销信息   --注，show profile之类的语句不会被profiling，即自身不会产生Profiling   --我们下面的这个show profile查看的是show warnings产生的相应开销   root@localhost[sakila]> show profile;     +----------------+----------+   | Status         | Duration |   +----------------+----------+   | starting       | 0.000141 |   | query end      | 0.000058 |   | closing tables | 0.000014 |   | freeing items  | 0.001802 |   | cleaning up    | 0.000272 |   +----------------+----------+      --如下面的查询show warnings被添加到profiles   root@localhost[sakila]> show profiles;   +----------+------------+--------------------------------+   | Query_ID | Duration   | Query                          |   +----------+------------+--------------------------------+   |        1 | 0.00253600 | show variables like '%profil%' |   |        2 | 0.00138150 | select count(*) from customer  |   |        3 | 0.00228600 | show warnings                  |   +----------+------------+--------------------------------+      --获取指定查询的开销（第二条查询的开销明细）  root@localhost[sakila]> show profile for query 2;   +----------------------+----------+   | Status               | Duration |   +----------------------+----------+   | starting             | 0.000148 |   | checking permissions | 0.000014 |   | Opening tables       | 0.000047 |   | init                 | 0.000023 |   | System lock          | 0.000035 |   | optimizing           | 0.000012 |   | statistics           | 0.000019 |   | preparing            | 0.000014 |   | executing            | 0.000006 |   | Sending data         | 0.000990 |   | end                  | 0.000010 |   | query end            | 0.000011 |   | closing tables       | 0.000010 |   | freeing items        | 0.000016 |   | cleaning up          | 0.000029 |   +----------------------+----------+      --查看特定部分的开销，如下为CPU部分的开销   root@localhost[sakila]> show profile cpu for query 2 ;   +----------------------+----------+----------+------------+   | Status               | Duration | CPU_user | CPU_system |   +----------------------+----------+----------+------------+   | starting             | 0.000148 | 0.000000 |   0.000000 |   | checking permissions | 0.000014 | 0.000000 |   0.000000 |   | Opening tables       | 0.000047 | 0.000000 |   0.000000 |   | init                 | 0.000023 | 0.000000 |   0.000000 |   | System lock          | 0.000035 | 0.000000 |   0.000000 |   | optimizing           | 0.000012 | 0.000000 |   0.000000 |   | statistics           | 0.000019 | 0.000000 |   0.000000 |   | preparing            | 0.000014 | 0.000000 |   0.000000 |   | executing            | 0.000006 | 0.000000 |   0.000000 |   | Sending data         | 0.000990 | 0.001000 |   0.000000 |   | end                  | 0.000010 | 0.000000 |   0.000000 |   | query end            | 0.000011 | 0.000000 |   0.000000 |   | closing tables       | 0.000010 | 0.000000 |   0.000000 |   | freeing items        | 0.000016 | 0.000000 |   0.000000 |   | cleaning up          | 0.000029 | 0.000000 |   0.000000 |   +----------------------+----------+----------+------------+      --如下为MEMORY部分的开销   root@localhost[sakila]> show profile memory for query 2 ;   +----------------------+----------+   | Status               | Duration |   +----------------------+----------+   | starting             | 0.000148 |   | checking permissions | 0.000014 |   | Opening tables       | 0.000047 |   | init                 | 0.000023 |   | System lock          | 0.000035 |   | optimizing           | 0.000012 |   | statistics           | 0.000019 |   | preparing            | 0.000014 |   | executing            | 0.000006 |   | Sending data         | 0.000990 |   | end                  | 0.000010 |   | query end            | 0.000011 |   | closing tables       | 0.000010 |   | freeing items        | 0.000016 |   | cleaning up          | 0.000029 |   +----------------------+----------+      --同时查看不同资源开销   root@localhost[sakila]> show profile block io,cpu for query 2;   +----------------------+----------+----------+------------+--------------+---------------+   | Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |   +----------------------+----------+----------+------------+--------------+---------------+   | starting             | 0.000148 | 0.000000 |   0.000000 |            0 |             0 |   | checking permissions | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |   | Opening tables       | 0.000047 | 0.000000 |   0.000000 |            0 |             0 |   | init                 | 0.000023 | 0.000000 |   0.000000 |            0 |             0 |   | System lock          | 0.000035 | 0.000000 |   0.000000 |            0 |             0 |   | optimizing           | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |   | statistics           | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |   | preparing            | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |   | executing            | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |   | Sending data         | 0.000990 | 0.001000 |   0.000000 |            0 |             0 |   | end                  | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |   | query end            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |   | closing tables       | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |   | freeing items        | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |   | cleaning up          | 0.000029 | 0.000000 |   0.000000 |            0 |             0 |   +----------------------+----------+----------+------------+--------------+---------------+      --下面的SQL语句用于查询query_id为2的SQL开销，且按最大耗用时间倒序排列   root@localhost[sakila]> set @query_id=2;      root@localhost[sakila]> SELECT STATE, SUM(DURATION) AS Total_R,          ROUND(               100 * SUM(DURATION) /                  (SELECT SUM(DURATION)                   FROM INFORMATION_SCHEMA.PROFILING                   WHERE QUERY_ID = @query_id               ), 2) AS Pct_R,            COUNT(*) AS Calls,            SUM(DURATION) / COUNT(*) AS "R/Call"         FROM INFORMATION_SCHEMA.PROFILING         WHERE QUERY_ID = @query_id         GROUP BY STATE         ORDER BY Total_R DESC;   +----------------------+----------+-------+-------+--------------+   | STATE                | Total_R  | Pct_R | Calls | R/Call       |   +----------------------+----------+-------+-------+--------------+   | Sending data         | 0.000990 | 71.53 |     1 | 0.0009900000 |--最大耗用时间部分为发送数据   | starting             | 0.000148 | 10.69 |     1 | 0.0001480000 |   | Opening tables       | 0.000047 |  3.40 |     1 | 0.0000470000 |   | System lock          | 0.000035 |  2.53 |     1 | 0.0000350000 |   | cleaning up          | 0.000029 |  2.10 |     1 | 0.0000290000 |   | init                 | 0.000023 |  1.66 |     1 | 0.0000230000 |   | statistics           | 0.000019 |  1.37 |     1 | 0.0000190000 |   | freeing items        | 0.000016 |  1.16 |     1 | 0.0000160000 |   | preparing            | 0.000014 |  1.01 |     1 | 0.0000140000 |   | checking permissions | 0.000014 |  1.01 |     1 | 0.0000140000 |   | optimizing           | 0.000012 |  0.87 |     1 | 0.0000120000 |   | query end            | 0.000011 |  0.79 |     1 | 0.0000110000 |   | end                  | 0.000010 |  0.72 |     1 | 0.0000100000 |   | closing tables       | 0.000010 |  0.72 |     1 | 0.0000100000 |   | executing            | 0.000006 |  0.43 |     1 | 0.0000060000 |   +----------------------+----------+-------+-------+--------------+      --开启profiling后，我们可以通过show profile等方式查看，其实质是这些开销信息被记录到information_schema.profiling表   --如下面的查询，部分信息省略   profiling   root@localhost[information_schema]> select * from profiling limit 3,3\G;   *************************** 1. row ***************************              QUERY_ID: 1                   SEQ: 5                 STATE: init              DURATION: 0.000020              CPU_USER: 0.000000            CPU_SYSTEM: 0.000000     CONTEXT_VOLUNTARY: 0   CONTEXT_INVOLUNTARY: 0          BLOCK_OPS_IN: 0         BLOCK_OPS_OUT: 0         MESSAGES_SENT: 0     MESSAGES_RECEIVED: 0     PAGE_FAULTS_MAJOR: 0     PAGE_FAULTS_MINOR: 0                 SWAPS: 0       SOURCE_FUNCTION: mysql_prepare_select           SOURCE_FILE: sql_select.cc           SOURCE_LINE: 1050      --停止profile,可以设置profiling参数，或者在session退出之后,profiling会被自动关闭   root@localhost[sakila]> set profiling=off;   Query OK, 0 rows affected, 1 warning (0.00 sec)