Mysql实例MySQL性能分析工具profile使用教程

发布时间：2020-12-12 02:28:55 所属栏目：MySql教程来源：网络整理

导读：《Mysql实例MySQL性能分析工具profile使用教程》要点：本文介绍了Mysql实例MySQL性能分析工具profile使用教程，希望对您有用。如果有疑问，可以联系我们。分析SQL执行带来的开销是优化SQL的重要手段.在MySQL数据库中,可以通过配置profiling参数来启用SQL剖

《Mysql实例MySQL性能分析工具profile使用教程》要点：
本文介绍了Mysql实例MySQL性能分析工具profile使用教程，希望对您有用。如果有疑问，可以联系我们。

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

1、有关profile的描述MYSQL教程

代码如下:
--当前版本?
root@localhost[sakila]> show variables like 'version';?
+---------------+---------------------------------------+?
| Variable_name | Value???????????????????????????????? |?
+---------------+---------------------------------------+?
| version?????? | 5.6.17-enterprise-commercial-advanced |?
+---------------+---------------------------------------+?
?
--查看profiling系统变量?
root@localhost[sakila]> show variables like '%profil%';?
+------------------------+-------+?
| Variable_name????????? | Value |?
+------------------------+-------+?
| have_profiling???????? | YES?? |?? --只读变量,用于控制是否由系统变量开启或禁用profiling?
| profiling????????????? | OFF?? |?? --开启SQL语句剖析功能?
| profiling_history_size | 15??? |?? --设置保留profiling的数目,缺省为15,范围为0至100,为0时将禁用profiling?
+------------------------+-------+?
?
profiling [539]?
If set to 0 or OFF (the default),statement profiling is disabled. If set to 1 or ON,statement prof?
is enabled and the SHOW PROFILE and SHOW PROFILES statements provide access to prof?
information. See Section 13.7.5.32,“SHOW PROFILES Syntax”.?
?
This variable is deprecated in MySQL 5.6.8 and will be removed in a future MySQL release.?
profiling_history_size [539]?
The number of statements for which to maintain profiling information if profiling [539] is?
enabled. The default value is 15. The maximum value is 100. Setting the value to 0 effectively?
disables profiling. See Section 13.7.5.32,“SHOW PROFILES Syntax”.?
This variable is deprecated in MySQL 5.6.8 and will be removed in a future MySQL release.?
?
?
--获取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并获取真实的执行计划以及其开销信息?

2、开启porfilingMYSQL教程

代码如下:
--启用session级别的profiling?
root@localhost[sakila]> set profiling=1;?
Query OK,0 rows affected,1 warning (0.00 sec)?
?
--验证修改后的结果?
root@localhost[sakila]> show variables like '%profil%';?
+------------------------+-------+?
| Variable_name????????? | Value |?
+------------------------+-------+?
| have_profiling???????? | YES?? |?
| profiling????????????? | ON??? |?
| profiling_history_size | 15??? |?
+------------------------+-------+?
?
--发布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 |?
+---------+------+--------------------------------------------------------------------------------------------------------------+?

3、获取SQL语句的开销信息MYSQL教程

代码如下:
--可以直接使用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,3G;?
*************************** 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,1 warning (0.00 sec)?????

欢迎参与《Mysql实例MySQL性能分析工具profile使用教程》讨论，分享您的想法，编程之家PHP学院为您提供专业教程。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!