使用查询语句对消耗进行排序
| mysql> SELECT STATE, SUM(DURATION) AS Total_R,ROUND( 100 * SUM(DURATION) / (SE CT SUM(DURATION) -> FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 1), 2) AS Pct_R, CO T(*) AS Calls,SUM(DURATION) / COUNT(*) AS "R/Call" -> FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 1 GROUP BY STATE O ER BY Total_R DESC; |
| +--------------------------------+----------+-------+-------+--------------+ | STATE | Total_R | Pct_R | Calls | R/Call | +--------------------------------+----------+-------+-------+--------------+ | Copying to tmp table | 0.704931 | 81.26 | 2 | 0.3524655000 | | Sending data | 0.154089 | 17.76 | 6 | 0.0256815000 | | removing tmp table | 0.006735 | 0.78 | 3 | 0.0022450000 | | Opening tables | 0.000963 | 0.11 | 1 | 0.0009630000 | | Creating tmp table | 0.000197 | 0.02 | 2 | 0.0000985000 | | Table lock | 0.000169 | 0.02 | 1 | 0.0001690000 | | freeing items | 0.000144 | 0.02 | 1 | 0.0001440000 | | checking query cache for query | 0.000099 | 0.01 | 1 | 0.0000990000 | | statistics | 0.000061 | 0.01 | 3 | 0.0000203333 | | optimizing | 0.000053 | 0.01 | 3 | 0.0000176667 | | preparing | 0.000043 | 0.00 | 3 | 0.0000143333 | | starting | 0.000018 | 0.00 | 1 | 0.0000180000 | | System lock | 0.000015 | 0.00 | 1 | 0.0000150000 | | storing result in query cache | 0.000011 | 0.00 | 1 | 0.0000110000 | | executing | 0.000008 | 0.00 | 3 | 0.0000026667 | | cleaning up | 0.000006 | 0.00 | 1 | 0.0000060000 | | logging slow query | 0.000003 | 0.00 | 1 | 0.0000030000 | | query end | 0.000003 | 0.00 | 1 | 0.0000030000 | +--------------------------------+----------+-------+-------+--------------+ 18 rows in set (0.01 sec) |
show profile额外一些命令：
* ALL - displays all information
* BLOCK IO - displays counts for block input and output Operations
* CONTEXT SWITCHES - displays counts for voluntary and involuntary context switches
* 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
最后说明：profile是一个非常量化的子标，可以根据这些量化指标来比较各项资源的消耗，有利于我们对该语句的整体把控！
