profiling和trace语句使用

一，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;