分析诊断工具之三：使用show profiles分析SQL性能

最新推荐文章于 2024-05-11 10:55:06 发布
最新推荐文章于 2024-05-11 10:55:06 发布
阅读量374 收藏 1
点赞数

转自http://www.cnblogs.com/duanxz/archive/2013/01/23/2872537.html

 

分析诊断工具之三：使用show profiles分析SQL性能

　　分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。该参数可以在全局和session级别来设置。对于全局级别则作用于整个MySQL实例，而session级别紧影响当前session。该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。根据这些开销进一步分析当前SQL瓶颈从而进行优化与调整。本文描述了如何使用MySQL profile，不涉及具体的样例分析。

如何查看执行SQL的耗时的步骤：开启profile、发送sql、查看profile的资源开销结果、关闭profile。

使用show profiles分析sql性能。

Show profiles是5.0.37之后添加的，要想使用此功能，要确保版本在5.0.37之后。

查看数据库版本

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              --显示交换次数相关开销的信息   
  
The SHOW PROFILE and SHOW PROFILES statements display profiling  
information that indicates resource usage for statements executed  
during the course of the current session.  
  
*Note*: These statements are deprecated as of MySQL 5.6.7 and will be  
removed in a future MySQL release. Use the Performance Schema instead;  
see http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html.  
--上面描述从5.6.7开始该命令将会被移除，用Performance Schema instead代替  
--在Oracle数据库中，是通过autotrace来剖析单条SQL并获取真实的执行计划以及其开销信息  

 

执行业务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)
yipinrfr
关注
MySQL之Show Profiles
会者定离，一期一祈
02-20 1410
定义 Show Profiles是MySQL提供，可以分析SQL语句执行的资源消耗情况，可用于SQL调优。 通过配置profiling参数启用SQL剖析，该参数可以在全局和session级别来设置。 全局级别作用于整个MySQL实例，而session级别只影响当前回话。 该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等 Show profiles是5.0.37之后添加的，要想使用此功能，要确保MySQL版本 > 5.0.37。 如何使用 selec
Mysql自带profiling性能分析工具使用分享
12-15
MySQL自带的`profiling`性能分析工具是一种非常实用的功能，它可以帮助数据库管理员和开发者深入了解SQL查询的执行过程，从而找出性能瓶颈，优化数据库性能。在本文中，我们将深入探讨如何使用`profiling`以及它提供...
mysql show profiles 使用分析sql 性能
热门推荐
aeoluspu的专栏
08-01 4万+
Show profiles是5.0.37之后添加的，要想使用此功能，要确保版本在5.0.37之后。 查看一下我的数据库版本 mysql> Select  version(); +---------------------+ | version()           | +---------------------+ | 5.0.82-community-nt | +--------
Show profiles
u010502101的博客
08-06 4597
Show profiles是MySql用来分析当前会话SQL语句执行的资源消耗情况，可以用于SQL的调优测量。 Show profiles默认状态下是关闭的，执行set profiling=true;打开状态 可以用show profile查看最近执行的SQL语句执行情况 其中Query_ID为每条执行SQL的编号，Duration为执行时间，Query为SQL语句。 可以查询每个...
show profiles
weixin_40524659的博客
08-22 185
mysql提供的可以用来分析当前会话语句执行资源消耗情况的命令profile.默认是关闭的。 have_profiling=YES表示当前版本支持 该功能,我们需要开启profile功能， set profiling=1;来开启（当前会话关闭前，只需要执行一次） 执行一条SQL语句： select *from t3; 通过 show profiles查看SQL运行结果。 show profiles; +----------+------------+--------------------
show profile
最新发布
youhebuke225的博客
05-11 883
默认情况下，profiling 可能没有被启用。这个命令会返回一个表格，其中包含了多个列，如。每一行都代表查询执行过程中的一个步骤，命令来查看 profiling 的结果。列显示了该步骤所花费的时间。执行你想要分析的查询。
MySQL性能优化：启用与使用SQL剖析(profile)教程
MySQL性能分析工具profile是数据库管理员用来诊断SQL查询性能问题的关键工具。通过启用profile，我们可以获取到每条SQL语句在执行过程中各个阶段所消耗的时间，包括解析、执行、收集统计信息等步骤，这对于识别和...
MySQL优化实战：show profile剖析SQL性能
5. 如果需要更深入的诊断，可以使用show profile cpu和block io for query，附上SQL编号，分析CPU和I/O使用情况。 在日常开发中，当遇到SQL运行效率低下但explain未给出明确答案的情况，务必考虑启用show profile。...
【MySQL】SQL性能分析 (七).pdf
05-04
它是诊断性能瓶颈的重要工具之一。 - **配置与启用**： - 通常情况下，慢查询日志需要手动启用。首先检查系统变量`slow_query_log`的状态。如果未启用，需要在MySQL的配置文件（通常是`/etc/my.cnf`）中添加如下...
MYSQL性能分析之show profiles
kwinH的博客
06-21 686
打开profile分析语句show variables like ‘%profiling%’; #on开off关 开启 set profiling=on;mysql> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | pr
MySQL调优利器【show profiles】
我就是我的博客
03-01 1834
show profiles 是mysql提供可用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优测量。 默认情况下，参数处于关闭状态，并保存最近15次的运行结果 分析步骤 1、查看当前版本sql是否支持show profiles mysql> show variables like 'profiling%'; +------------------------+-------+ ...
show profiles 详解
cuizhu0832的博客
01-14 350
12.5.5.29.SHOW PROFILES Syntax This section does not apply to MySQL Enterprise Server users. SHOW PROFILE [type...
MySQL优化工具之Show profiles分析SQL性能
像豆芽一样优秀
08-22 389
使用show profiles分析SQL性能 分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL刨析。该参数开启后，后续执行的SQL语句都将记录其资源开销，例如IO、上下文切换、CPU、Memory等等。根据这些开销进一步分析SQL瓶颈从而进行优化与调整。 方法/步骤 show profiles是5.0.37之后添加的，如果想使用此功能，确保版本在5.0.37之后。 查看数据库版本 SELECT VERSION() 查看是否打开pro
Mysql show Profiles详解
菜鸡的博客
01-03 2126
命令用于展示SQL语句的资源使用情况，包括CPU的使用，CPU上下文切换，IO等待，内存使用等，这个命令对于分析某个SQL的性能瓶颈非常有帮助，借助于。的输出信息，能让我们知道一个SQL在哪个阶段耗时最长，消耗资源最多，从而为SQL优化，提高SQL性能提供重要的依据。调整show profile单次查询的条数，默认15条，最大100条，设为0，默认关闭profiling。展示的是简要的耗时信息，如果想了解某个SQL的具体耗时情况，执行。决定，默认值为15，最大值为100。如果设置为0，等同于关闭。
MySQL性能调优（二）show profiles
Twilight blog
02-16 422
show profiles是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量。 默认是关闭的，使用前需要开启 show variables like 'profiling'; set profiling = on; 运行慢SQL select * from emp group by id%10 limit 150000; ...
show profiles的用法
A_java_c的博客
07-14 388
查看慢sql日志是否开启 -- 查看慢查询是否开启 on代表开启 show variables like '%slow_query_log%'; set global slow_query_log_file='D:/log/mysql/slow.log';
oracle show profile,Oracle Profile使用详解
weixin_35711852的博客
04-15 455
一、目的：Oracle系统中的profile可以用来对用户所能使用的数据库资源进行限制，使用Create Profile命令创建一个Profile，用它来实现对数据库资源的限制使用，如果把该profile分配给用户，则该用户所能使用的数据库资源都在该profile的限制之内。prfile文件可以控制：CPU的时间 、I/O的使用 、IDLE TIME(空闲时间) 、CONNECT TIME(连接时...
show profile 分析性能
邵小雨的博客
03-19 876
show profiles 是mysql 5.0.37版本后添加的功能，使用之前必须保证本机mysql版本高于5.0.37.1.查看mysq版本的方法mysql>show version()2.开启 show profileshow profile 在mysql中默认是关闭的，（但是会话级别可以开启这个功能）使用之前应该先开启show profile。开启show profile 可以让my...
MySQL 5.0以上版本使用profiles分析SQL执行计划
利用MySQL的profiles特性，我们可以对SQL语句进行深入的性能分析，发现潜在的性能瓶颈，并采取相应的优化措施，提高数据库的整体运行效率。这在日常的数据库维护和性能调优工作中是非常有价值的。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值