MySQL性能分析命令

服务性能简介

三个性能服务请求

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

性能定义

• 为完成某件任务所需要的时间度量,换言之,性能即响应时间,这是一个非常重要的原则.

SQL性能分析

• 执行分析 - 基于执行时间的分析
• 等待分析 - 基于等待时间的分析

SQL性能优化

在一定的工作负载下尽可能地降低响应时间,在SQL服务器中定义的吞吐量指标为单位时间内的查询量.

性能优化工具

慢查询日志是一种轻量而且功能全面的性能剖析功能,优化服务器查询的利器.

• 通过--processlist查看记录出现的时间和消失的时间

show full processlist

• 通过TCP网络抓包

tcpdump -i lo -l port 3306 -w lo.pcap | strings
tcpdump host mysql_ip and port mysql_port

开启binlog慢日志查询

## 查看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

参考

参考: https://blog.51cto.com/lookingdream/1921162

单条SQL查询性能分析

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

## 设置启用
set profiling=1;

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

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

## 根据上述的结果获取queryId
show profile for 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;

上述的执行输出结果如下:

非性能分析工具的show status

mysql的show status命令返回一些计数器,既有服务器级别的计数器,也有基于某个连接会话级别的计数器.可以查看服务器级别的从服务器启动开始计算的查询次数统计

截取部分结果图: