面试题：说下mysql性能分析命令

性能分析有下面三个

• 如何确认服务器是否达到了性能最佳状态
• 找出某条sql语句执行慢的原因
• 诊断被用户描述成“停顿”,“堆积”或者是“卡死”的间歇性疑难故障

## 查看mysql所有的日志信息的状态以及对应存储的位置
show variables like '%log%';

## 查看bin log是否开启
show variables like 'log_bin';

## 查看mysql的版本,我的当前为5,7
show variables like '%version%';

## 启用bin log
## /etc/mysql/mysql.conf.d/mysqld.cnf
server_id         =  1   # 随机数,集群中要保证唯一
log_bin     = /var/log/mysql/mysql-bin.log
max_binlog_size  = 100M
binlog_do_db = include_database_name

## 重启服务
service mysql start

## 再次查看bilog是否生效
show variables like 'log_bin';
 

查看binlog

/usr/bin/mysqlbinlog --base64-output=DECODE-ROWS -v /var/log/mysql/mysql-bin.000001

单条SQL查询性能分析

## 在mysql5之后可以通过profile来启用分析
## 查看是否启用
show variables like '%profil%';

## 设置启用
set profiling=1;

## 查看启用结果
show variables like '%profil%';

## 查看sql的执行性能
show profiles;

## 根据上述的结果获取queryId
show profile from query queryId;

## 格式化输出
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;