mysql慢查询可视化分析

背景

张总：小王，我们系统上线一段时间了，但是最近系统响应有点慢，同时系统系统访问量并不高，但mysql的cpu、内存资源一直在持续上涨，你去分析下慢sql，找出消耗资源的，进行优化，提升系统的性能

小王：好的，我去看下，有结果了，给你反馈

分析

找到mysql安排的目标机器，根据mysql的配置文件，就是my.conf文件找到慢sql记录的文件位置

打开慢sql日志文件，数据量实在是太大了，不好分析，有没有什么好的工具让我快速分析呢？

调研

经过夜以继日的查找，不负有心人，找到好的工具 percona-toolkit工具下的 pt-query-digest，其中就能对慢sql进行结构化的分析

pt-query-digest 正确打开方式

描述

pt-query-digest 可分析慢sql、常规和二进制日志文件中的 MySQL 查询。它还能分析来自 SHOW PROCESSLIST 的查询和来自 tcpdump 的 MySQL 协议数据。默认情况下，查询按指纹分组，并按查询时间降序报告（即最慢的查询先报告）。如果没有给出 FILES，工具将读取 STDIN。可选的 DSN 用于某些选项，如 --since 和 --until。

使用

查看使用方法

./pt-query-digest --help

部分截图，功能很多，可以看看文档

慢sql分析

那我们怎么使用这个工具分析的慢sql呢，看下面简单的例子感受下

-- 查询最近10分钟的慢sql，注意下type的值一定要是slowlog
./pt-query-digest /var/log/mysql4306-slow.log --type=slowlog --since='10m' >report.log

分析报告

第一部分：总体概况描述说明

-- 分析消耗的用户cpu时间，系统cpu的时间，物理内存占用大小，虚拟内存占用大小
# 26.6s user time, 2.3s system time, 132.00M rss, 211.10M vsz
-- 分析的当前日期
# Current date: Mon Nov 27 11:17:53 2023
-- 分析的主机名
# Hostname: hbdata2
-- 分析的慢sql日志文件所在的位置
# Files: /var/log/mysql4306-slow.log
-- 分析的整体情况：语句的数量，唯一语句数量，Qps和并发数
# Overall: 81 total, 23 unique, 0.02 QPS, 0.06x concurrency ______________
-- 分析的时间范围
# Time range: 2023-11-27T10:18:58 to 2023-11-27T11:16:39
--属性             总计      最小     最大     平均    95%   标准    中等
# 属性             total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
-- 执行时间
# Exec time           217s      2s     10s      3s      3s   938ms      2s
-- 锁的时间
# Lock time            15s     1us     10s   187ms    38us      1s     5us
-- 发送到客户端的行数
# Rows sent        844.68k       0 215.76k  10.43k  118.34  44.26k    9.83
-- 扫描的行数
# Rows examine      40.18M       0   2.68M 508.00k   2.62M 924.59k 106.99k
--查询的字节数
# Query size       374.06k     115  22.19k   4.62k  12.50k   4.88k   2.27k

-- 统计概况
# Profile
-- 排名，查询的指纹总响应时间百分比，执行的次数，执行的平均响应时间，响应时间Variance-to-mean的比率，查询的对象
# Rank Query ID                         Response time Calls R/Call V/M   I
# ==== ================================ ============= ===== ====== ===== =
#    1 0xDF99061A6D1697D34D07EDA3BF0... 35.8138 16.5%    12 2.9845  0.00 SELECT t_adconfig_floor t_adconfig_ad_position t_adconfig_advertisement b
#    2 0x8A01A12F5084F1D5B374BD67A2A... 24.1755 11.2%    11 2.1978  0.01 SELECT t_product t_product_price t_r_product t_product t_r_product t_product t_product_image t_product t_product_index t_product_price
#    3 0xD7881C474749029922D65931F22... 22.3766 10.3%     7 3.1967  0.00 SELECT UNION t_order t_order_detail t_product_stock_detail t_return t_order t_return_detail t_product_stock_detail t_order_detail t_order t_order_detail t_recover_second_hand_good_goods t_recover_s
tock t_return t_order t_return_detail t_recover_second_hand_good_goods t_order_detail t_recover_stock t_write_off_product t_special_user t_order t_order_detail t_special_user t_write_off_product

# MISC 0xMISC                           10.4528  4.8%     5 2.0906   0.0 <5 ITEMS>

第二部分 查询sql概况说明

-- 查询的顺序号和第一部分的rank对应，qps，并发，查询的指纹（唯一号）
# Query 1: 0.67 QPS, 2.76x concurrency, ID 0xD9114A7EDA241D08F67779A0775D0EF1 at byte 4069289265
-- 响应比例
# Scores: V/M = 0.01
-- 分析查询的时间范围
# Time range: 2023-11-27T11:41:09 to 2023-11-27T11:41:27
--属性       百分比 总数      最小      最大    平均    95%时间分布 标准 中等
# 属性       pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
-- 次数
# Count         23      12
-- 执行时间
# Exec time     27     50s      4s      4s      4s      4s   237ms      4s
-- 锁的时间
# Lock time     40   847us    45us   132us    70us    84us    21us    66us
-- 发送到客户端的行数
# Rows sent      0      12       1       1       1       1       0       1
-- 扫描的行数
# Rows examine   1 130.31k   9.68k  11.57k  10.86k  11.34k  566.22  10.80k
-- 查询的字节数
# Query size    45   1.33M 112.59k 113.83k 113.21k 112.33k       0 112.33k
# String:
-- 数据库
# Databases    small_oa_169
-- 远程查询的主机
# Hosts        hbdata4 (5/41%), hbdata5 (3/25%)... 2 more
-- 查询用户
# Users        small_oa
-- 查询执行时间的分布直方图：1微妙、10微妙、100微妙、10毫秒、100毫秒、1秒、10秒以上查询分布的情况
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
-- 表信息
# Tables
#    SHOW TABLE STATUS FROM `small_oa_169` LIKE 't_product_stock_detail'\G
#    SHOW CREATE TABLE `small_oa_169`.`t_product_stock_detail`\G
#    SHOW TABLE STATUS FROM `small_oa_169` LIKE 't_order_detail'\G
#    SHOW CREATE TABLE `small_oa_169`.`t_order_detail`\G
#    SHOW TABLE STATUS FROM `small_oa_169` LIKE 't_order'\G
#    SHOW CREATE TABLE `small_oa_169`.`t_order`\G
#    SHOW TABLE STATUS FROM `small_oa_169` LIKE 't_product_info'\G
#    SHOW CREATE TABLE `small_oa_169`.`t_product_info`\G
-- 执行计划信息
# EXPLAIN /*!50100 PARTITIONS*/
-- 查询的sql
SELECT

            sum(t20.statistics_in_price) statistics_in_price,
            SUM(t20.in_price) in_price,
            SUM(t20.statistics_gross_profit) statistics_gross_profit,
            sum(t20.gross_profit) gross_profit,


            sum(t20.examine_price) examine_price,
            SUM(t20.examine_gross_profit) examine_gross_profit,

通过上面两部分，就能清晰、结构化的分析sql的执行请，进行有效的分析优化，提升系统性能，同时减少资源的占用

使用场景

1. 分析所有的慢查询，这种数据量非常大，看情况使用

pt-query-digest /data/mysql/my3306/logs/slow.log --type=slowlog

2. 分析指定数据库的慢查询,数据库是指创建的逻辑库

pt-query-digest /data/mysql/my3306/logs/slow.log --type=slowlog --filter '($event->{db} || "") =~ m/^dbsql/i'

3. 分析执行用户的慢查询

pt-query-digest /data/mysql/my3306/logs/slow.log --type=slowlog --filter '($event->{user} || "") =~ m/^pmm/i'

4. 分析制定ip的慢查询

pt-query-digest /data/mysql/my3306/logs/slow.log --type=slowlog --filter '($event->{host} || $event->{ip} || "") =~ m/^192.168.16.*/i'

5. 分析指定时间范围的慢查询，这个比较实用，我经常就用这个

pt-query-digest /data/mysql/my3306/logs/slow.log --type=slowlog --since='2023-03-26 00:00:00' --until='2023-03-27 23:59:59'

## 时间也可以用时间戳
pt-query-digest /data/mysql/my3306/logs/slow.log --type=slowlog --since='1583048987' --until='1583049175'

6. 分析最近时间的慢查询，时间单位：h（小时）、m（分）、s（秒）

# 最近10小时慢查询，从执行命令的当前时间往前推10个小时
pt-query-digest /data/mysql/my3306/logs/slow.log --type=slowlog --since='10h

7. 分析指定类型的慢查询，select、update等

pt-query-digest /data/mysql/my3306/logs/slow.log --type=slowlog --filter '$event->{arg} =~ m/^update/i'

8. 分析慢查询，同时写入到表中,这种用的少

pt-query-digest --user=root --password=root --port=3306 --review h=192.168.16.111,D=slow_query_log,t=global_query_review --history h=192.168.16.111,D=slow_query_log,t=global_query_review_history --limit=0% --filter='($event->{Bytes} = length($event->{arg}) and $event->{hostname}="dbsql") and ($event->{host} || $event->{ip}) !~ m/^localhost$|^192.168.16.1$/i and $event->{arg} =~ m/^select/i' --since='2023-03-23 12:00:00' --until='2023-03-30 13:00:00' /data/mysql/my3306/logs/slow.log --no-report

这提供了一些使用的场景例子，可以参照文档，根据自身的实际情况使用，

（自研）根据pt-query-digest结构化数据生成html

指定输出的报告格式 --output json ，通过json的格式数据，二次开发生成html，方便查看，如有需要的可以私信，提供源代码

pt-query-digest  --output json

这样的界面给领导、同事查看，是不是很nice

写作不易，刚好你看到，刚好对你有帮助，麻烦点赞关注，谢谢。