1.概述
(1).说明
MySQL的Query Profiler是一个使用非常方便的Query诊断分析工具，通过该工具可以获取一条Query在整个执行过程中多种资源的消耗情况，如CPU、IO、IPC、SWAP等，以及发生的PAGE FAULTS、CONTEXT SWITCHE等，同时还能得到该Query执行过程中的MySQL所调用的各个函数在源文件中的位置。
MySQL5.0.37版本以上支持PROFILING调试功能，让您可以了解SQL语句消耗资源的详细信息。因为它需要调用系统的getrusage()函数，所以只是在Linux/Unix类平台上才能使用，而不能在Windows平台上使用。而且，PROFILING是针对处理进程(process)而不是线程(thread)的，服务器上的其他应用，可能会影响您的调试结果，因此，这个工具适合开发过程中的调试，如果要在生产环境中调试使用，则要注意它的局限性。
(2).SHOW PROFILE命令
格式
SHOW PROFILE [type [, type] ... ] [FOR QUERY n] [LIMITrow_count [OFFSET offset]]
FOR QUERY n
指定查询的query_id。可用的query id，从show profiles可知。
LIMIT row_count{OFFSET offset}
指定输出的范围。
选项 -TYPE
ALL
displays all information
BLOCK IO
displays counts for block input and output operations
CONTEXT SWITCHES
displays counts for voluntary and involuntary contextswitches
CPU
displays user and system CPU usage times
IPC
displays counts for messages sent and received
MEMORY
is not currently implemented
PAGE FAULTS
displays counts for major and minor page faults
SOURCE
displays the names of functions from the source code,together with the name and line number of the file in which the function occurs
SWAPS
displays swap counts
示例
SHOW PROFILE FOR QUERY 2;
or
SELECT STATE, FORMAT(DURATION, 6) AS DURATION
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = 2
ORDER BY SEQ;
2.相关操作
(1).开启QueryProfiler功能
mysql> select @@profiling;
mysql> set profiling=1;
*默认情况下profiling的值为0表示MySQLSQL Profiler处于OFF状态，开启SQL性能分析器后profiling的值为1。
(2).执行SQL语句
mysql> select count(*) from t1;
(3).获取概要信息
使用"show profile"命令获取当前系统中保存的多个Query的profile的概要信息。
mysql> show profiles;
+----------+------------+-------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------+
| 1 |0.00039300 | select count(*) from t1 |
+----------+------------+-------------------------+
profiling_history_size参数
这个List的长度由MySQL的数据库变量参数profiling_history_size决定，预设为15。最大值为100，设置为0则禁用此功能。
(4).针对单个Query获取详细的profile信息
在获取概要信息之后，就可以根据概要信息的Query_ID来获取某个Query的执行过程中详细的profile信息。
mysql> show profile for query 1;
mysql> show profile cpu,block io for query 1;
(5).排序profile信息
缺省报告时显示查询执行的每个步骤及其花费的时间，但很难快速定位到那个步骤花费时间最长，因为输出是按照执行顺序排序，而不是按照花费时间排序。可以使用下面方法，通过直接查询INFORMATION_SCHEMA中的对应表完成排序格式化输出。
SET @query_id=1;
SELECT STATE,SUM(DURATION) ASTotal_R,ROUND(100*SUM(DURATION)/(SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILINGWHERE 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;
