MYSQL SHOW PROFILE（剖析报告）的查看

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

一、参数的开启和关闭设置

1.1  参数的查看

默认情况下，参数处于关闭状态，并保存最近15次的运行结果

mysql>  show variables like ‘profiling%‘;
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
2 rows in set
 

1.2 参数的开启和关闭（参数为会话级参数，只对当前会话有效）

开启操作如下：

mysql> SET profiling=1;或 SET profiling=on;

mysql>  SET profiling=on;
Query OK, 0 rows affected
 
mysql>  show variables like ‘profiling%‘;
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+
2 rows in set
 

关闭的操作：
mysql> SET profiling=0;或 SET profiling=off;

二、操作步骤

2.1 进行开启操作： SET profiling=on;

2.2 运行相应的SQL语句；

2.3 查看总体结果：show profiles;

2.4 查看详细的结果：SHOW PROFILE FOR QUERY n，这里的n就是对应SHOW PROFILES输出中的Query_ID；

mysql> show profiles;
 
+----------+------------+------------------------------------+
| Query_ID | Duration   | Query                              |
+----------+------------+------------------------------------+
|        1 | 0.00077425 | select count(*) from tab_user_info |
|        2 |  0.0013575 | select count(*) from tab_tel_area  |
|        3 |     9.7E-5 | select count(*) from tab_tel_area  |
|        4 |   0.005193 | show variables like ‘profiling%‘   |
+----------+------------+------------------------------------+
4 rows in set
 
mysql> show profile for query 2;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 2E-5     |
| checking query cache for query | 4.7E-5   |
| Opening tables                 | 0.001163 |
| System lock                    | 4E-6     |
| Table lock                     | 4.1E-5   |
| init                           | 1.6E-5   |
| optimizing                     | 6E-6     |
| executing                      | 1.4E-5   |
| end                            | 5E-6     |
| query end                      | 3E-6     |
| freeing items                  | 3.1E-5   |
| storing result in query cache  | 5E-6     |
| logging slow query             | 3E-6     |
| cleaning up                    | 2E-6     |
+--------------------------------+----------+
14 rows in set

说明：报告给出了查询执行的每个步骤及花费的时间，当语句是很简单的一次执行的时候，可以很清楚的看出语句每个顺序花费的时间，但是当语句是嵌套循环等操作的时候，看这个报告就会变得很痛苦，因此整理了以下语句对相同类型的操作进行汇总，脚本如下：

mysql> SET @QUERY_ID=1;

mysql> 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;

总结：这个工具又让我联想到了oracle的autotrace工具，如果有相应的执行计划一起带出来，那么对语句的调优帮助将更大；

MYSQL SHOW PROFILE（剖析报告）的查看

原文：http://blog.itpub.net/12679300/viewspace-1383291/