一，show profile 分析sql性能：
1，环境准备
select @@have_profiling; #查看是否支持
select @@profiling;
#查看是否开启
set profiling=1; #默认为0，表示不开启。在session级别开启
2，测试sql
select count(1) from t;
3，查看:
show profiles;
#记下目标id
show profile for query ${id};
4, 排序查看：
set @query_id :=6;
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;
5，单项查看,可以找到具体是哪个硬件消耗最多：
show profile all|cpu|block io|context switch|page faults for query ${id}show
记得关闭：
set profiling=0;
二，trace命令使用： 1,打开并设置tarce使用内存的大小。 set optimizer_trace="enabled=on",end_markers_in_json=on; #两个参数都是session级别。 set optimizer_trace_max_mem_size =1000000; #默认16384（16M），不报错则可以不设置。 2,执行sql select * from t; 3,查看 select * from information_schema.optimizer_trace \G 4,关闭 set optimizer_trace="enabled=off",end_markers_in_json=off;
