mysql查询报告_MySQL SHOW PROFILE（剖析报告）的查看

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

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

1.1 参数的查看

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

mysql> show variables like 'profiling%';+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| profiling | ON |

| profiling_history_size | 15 |

+------------------------+-------+

2 rows in set (0.01 sec)

2 rows in set

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

开启操作如下：

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

关闭的操作：

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.00150375 | SELECT `uid`,`username`,`avatar`,`mobile` FROM `user` WHERE `uid` IN (529382,531148,532507,530375,525429,534772,534008,539138,536897,527714,529094,531355,535168,536490,536282,525414,533864,536137,531421,534989,526775,534302,536229,539128,536567,534593,531800,531644,536750,536515,533898,529180,527445 |

| 2 | 0.00024575 | SET profiling=on |

| 3 | 0.00028750 | SELECT `uid`,`username`,`avatar`,`mobile` FROM `user` WHERE `uid` IN (529382,531148,532507,530375,525429,534772,534008,539138,536897,527714,529094,531355,535168,536490,536282,525414,533864,536137,531421,534989,526775,534302,536229,539128,536567,534593,531800,531644,536750,536515,533898,529180,527445 |

| 4 | 0.00028550 | SET profiling=on |

| 5 | 0.92883025 | SELECT `uid`,`username`,`avatar`,`mobile` FROM `user` WHERE `uid` IN (529382,531148,532507,530375,525429,534772,534008,539138,536897,527714,529094,531355,535168,536490,536282,525414,533864,536137,531421,534989,526775,534302,536229,539128,536567,534593,531800,531644,536750,536515,533898,529180,527445 |

+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

5 rows in set, 1 warning (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 5;+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000208 |

| checking permissions | 0.000038 |

| Opening tables | 0.000039 |

| init | 0.000053 |

| System lock | 0.000023 |

| optimizing | 0.000037 |

| statistics | 0.000106 |

| preparing | 0.000034 |

| executing | 0.000016 |

| Sending data | 0.928018 |

| end | 0.000057 |

| query end | 0.000027 |

| closing tables | 0.000035 |

| freeing items | 0.000120 |

| cleaning up | 0.000023 |

+----------------------+----------+

15 rows in set, 1 warning (0.00 sec)

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

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;