profiles在mysql中的应用

MYSQL的profiling功能要在Mysql版本5.0.37以上才能使用。
profile主要的功能是在于了解一条sql语句在开销（cpu利用情况，IO情况等等）

开启profiling:
set profiling=1;

查看是否设置生效：
select @@profiling;
默认是0，设置成功是1

select * from bb where id = 1;

set prfiling = 0;

SET @query_id = 1;
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.226487 | 98.17 | 1 | 0.0000000007 |
| executing | 0.003153 | 1.37 | 1 | 0.0000000001 |
| statistics | 0.000574 | 0.25 | 1 | 0.0000000001 |
| freeing items | 0.000166 | 0.07 | 1 | 0.0000000001 |
| starting | 0.000132 | 0.06 | 1 | 0.0001320000 |
| init | 0.000061 | 0.03 | 1 | 0.0000610000 |
| preparing | 0.000041 | 0.02 | 2 | 0.0000205000 |
| optimizing | 0.000041 | 0.02 | 2 | 0.0000205000 |
| Opening tables | 0.000019 | 0.01 | 1 | 0.0000190000 |
| Table lock | 0.000010 | 0.00 | 1 | 0.0000100000 |
| System lock | 0.000007 | 0.00 | 1 | 0.0000070000 |
| cleaning up | 0.000005 | 0.00 | 1 | 0.0000050000 |
| end | 0.000005 | 0.00 | 1 | 0.0000050000 |
| logging slow query | 0.000004 | 0.00 | 1 | 0.0000040000 |
| query end | 0.000002 | 0.00 | 1 | 0.0000020000 |
+--------------------+----------+-------+-------+--------------+
15 rows in set (0.08 sec)

更多相关文章

随机推荐