博客

1、show profile 查询出最近15条sql语句运行状态（运行时执行了哪些操作，各占了多长时间），以便开发者分析。
SHOW VARIABLES LIKE 'profiling';
// ON（开启）OFF（关闭）
2、开启功能，默认是关闭。
SET profiling=ON;
3、show profiles;
Query_ID Duration Query
87	0.00240725	SELECT STATE AS `状态`, ROUND(SUM(DURATION),7) AS `期间`, CONCAT(ROUND(SUM(DURATION)/0.925622*100,3), '%') AS `百分比` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=84 GROUP BY STATE ORDER BY SEQ
88	0.00033575	SELECT * FROM `smartpatroldb`.`acc_user_location` LIMIT 0
89	0.0006855	SHOW COLUMNS FROM `smartpatroldb`.`acc_user_location`
90	0.00035625	SET PROFILING=1
91	0.00301075	SHOW STATUS
92	0.0028175	SHOW STATUS
93	0.9529885	select * from acc_user_location
94	0.00307075	SHOW STATUS
95	0.00259625	SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID
96	0.00232375	SELECT STATE AS `状态`, ROUND(SUM(DURATION),7) AS `期间`, CONCAT(ROUND(SUM(DURATION)/0.952990*100,3), '%') AS `百分比` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=93 GROUP BY STATE ORDER BY SEQ
97	0.00039425	SELECT * FROM `smartpatroldb`.`acc_user_location` LIMIT 0
98	0.0011265	SHOW COLUMNS FROM `smartpatroldb`.`acc_user_location`
99	0.00031025	SET PROFILING=1
100	0.002962	SHOW STATUS
101	0.00276675	SHOW STATUS
4、单个分析sql语句
//SHOW PROFILE cpu, block io FOR QUERY 89;
//id为show profiles查出来的某条记录的id
Status      Duration CPU_user CPU_system Block_ops_in Block_ops_out
starting	5.5E-5	0	5.5E-5	0	0
checking permissions	1.6E-5	0	1.5E-5	0	0
Opening tables	2.5E-5	0	2.5E-5	0	0
init	2.9E-5	0	2.9E-5	0	0
System lock	2.6E-5	0	2.6E-5	0	0
optimizing	1.1E-5	0	1E-5	0	0
statistics	5.3E-5	0	5.4E-5	0	0
preparing	2.1E-5	0	2E-5	0	0
executing	1E-5	0	1E-5	0	0
Sending data	0.963627	0.254943	0	0	0
end	5.1E-5	4.7E-5	0	0	0
query end	2.1E-5	2.1E-5	0	0	0
closing tables	1.9E-5	1.9E-5	0	0	0
freeing items	2.5E-5	2.5E-5	0	0	0
cleaning up	5.5E-5	5.5E-5	0	0	0
下面这4中必须优化
converting HEAP to MyISAM 查询结果太大，内存都不够用了
Creating tmp table 创建临时表
Copying to tmp on disk 把内存中临时表复制到磁盘
locked 
5、
#show profile默认是关闭，它的开启只存在当前session，如果关闭/开启新的session，需要重新开启;
#通过执行show profiles查看最近保存15条sql，在比较Duration选出耗时比较长sql语句，然后通过show profile cpu，block io FOR QUERY ID来诊断sql语句执行情况;