mysql数据库sql性能分析工具show profiles简介

以下均是在Centos 7.4-1 （ip:192.168.128.138）下进行

1，查询是否支持show profiles

show VARIABLES like '%profiling%';

2，默认情况下是关闭的，现在临时开启

set profiling=on;

3，执行一句sql，系统将记录下来

select * from big_table group by mobile limit 10;

4，查看结果

show profiles;
229	0.00160513	SHOW STATUS
230	0.00157194	SHOW STATUS
231	0.00137551	SHOW STATUS
232	0.00197131	SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID
233	0.00214341	SELECT STATE AS `Status`, ROUND(SUM(DURATION),7) AS `Duration`, CONCAT(ROUND(SUM(DURATION)/23.685934*100,3), '') AS `Percentage` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=218 GROUP BY SEQ, STATE ORDER BY SEQ
234	0.00039354	SET PROFILING = 1
235	0.00282954	SHOW STATUS
236	0.00128192	SHOW STATUS
237	23.1106200	select * from big_table group by mobile limit 10
238	0.00128381	SHOW STATUS
239	0.00157835	SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID
240	0.00122301	SELECT STATE AS `Status`, ROUND(SUM(DURATION),7) AS `Duration`, CONCAT(ROUND(SUM(DURATION)/0.003469*100,3), '') AS `Percentage` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=225 GROUP BY SEQ, STATE ORDER BY SEQ
241	0.0002871	SET PROFILING = 1
242	0.00246315	SHOW STATUS
243	0.00125441	SHOW STATUS

5，诊断sql

show profile cpu,block io for query 237;
starting	0.000133	0.000128	0	0	0
checking permissions	0.000009	0.000007	0	0	0
Opening tables	0.000018	0.000019	0	0	0
After opening tables	0.000006	0.000005	0	0	0
System lock	0.000004	0.000005	0	0	0
Table lock	0.000003	0.000003	0	0	0
After table lock	0.000006	0.000006	0	0	0
init	0.000025	0.000026	0	0	0
optimizing	0.000009	0.000008	0	0	0
statistics	0.000121	0.000125	0	0	0
preparing	0.00002	0.000016	0	0	0
executing	0.000004	0.000003	0	0	0
Creating tmp table	0.000042	0.000042	0	0	0
Copying to tmp table	0.119953	0.119864	0	0	0
converting HEAP to Aria	0.016273	0.01627	0	0	7200
Creating index	0.000045	0.000041	0	0	0
Repair by sorting	0.069061	0.069118	0	0	1744
Saving state	0.000034	0.000028	0	0	0
Creating index	0.000005	0.000005	0	0	0
converting HEAP to Aria	0.000213	0.000215	0	0	0
Copying to tmp table on disk	22.332173	22.325272	0	0	147576
Sorting result	0.556186	0.55674	0	0	1520
Sending data	0.00006	0.000054	0	0	0
end	0.000004	0.000003	0	0	0
removing tmp table	0.012917	0.012933	0	0	8
end	0.000043	0.000027	0	0	0
query end	0.000234	0.000236	0	0	0
closing tables	0.000043	0.00004	0	0	0
freeing items	0.000024	0.000024	0	0	0
updating status	0.000335	0.000337	0	0	0
logging slow query	0.002602	0.002607	0	0	8
cleaning up	0.000016	0.000009	0	0	0

也可以执行：

show profile all for query 237;

总结，status中出现如下4个字段时，表明有问题需高度重视了

（1），converting HEAP to Aria，表示查询结果太大，内存不够，往磁盘上搬了；
（2），Creating tmp table，表示创建了临时表；
（3），Copying to tmp table on disk，表示把内存中临时表复制到磁盘；
（4），locked