mysqldumpslow分析 MySQL 的慢查询日志

mysqldumpslow 是 MySQL 自带的工具，用于分析 MySQL 的慢查询日志（Slow Query Log），帮助开发者快速定位执行效率低下的 SQL 语句。以下是详细使用方法及场景分析：

---

1. 基本用法

命令语法：

mysqldumpslow [选项] [慢查询日志文件路径]

常用选项：

选项	说明
-s ORDER	排序方式，可选值：t（按总时间排序）、l（按锁定时间排序）、r（按返回行数排序）、c（按出现次数排序）
-t NUM	仅显示前 NUM 条结果
-g PATTERN	用 grep 过滤匹配特定模式的 SQL
-a	不将数字和字符串抽象化（显示完整 SQL）
-v	调试模式（显示详细信息）

---

2. 典型场景示例

示例 1：按总执行时间排序，显示前 10 条慢查询

mysqldumpslow -s t -t 10 /var/lib/mysql/mysql-slow.log

示例 2：按出现次数排序，显示最频繁的 5 条 SQL

mysqldumpslow -s c -t 5 /var/lib/mysql/mysql-slow.log

示例 3：过滤包含 SELECT 的慢查询

mysqldumpslow -g "SELECT" /var/lib/mysql/mysql-slow.log

---

3. 输出结果解析

假设输出如下：

Count: 12  Time=5.23s (62s)  Lock=0.00s (0s)  Rows=100.0 (1200), user1[user1]@host1
  SELECT * FROM orders WHERE user_id = N

• Count: 该 SQL 出现的次数（12 次）
• Time: 平均执行时间（5.23秒），括号内是总时间（62秒）
• Lock: 平均锁定时间（0秒）
• Rows: 平均返回行数（100行），括号内是总行数（1200行）
• SQL 语句: SELECT * FROM orders WHERE user_id = N（N 是抽象化的值）

---

4. 常见问题及排查

问题 1：权限不足

mysqldumpslow: 无法读取日志文件: Permission denied

解决方法：
使用 sudo 或以 MySQL 用户身份运行：

sudo mysqldumpslow /var/lib/mysql/mysql-slow.log

问题 2：日志文件未启用

mysqldumpslow: 找不到慢查询日志文件

解决方法：
在 MySQL 配置文件（my.cnf 或 my.ini）中启用慢查询日志：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
long_query_time = 2  # 记录执行时间超过2秒的查询

---

5. 高阶技巧

结合 grep 和 awk 提取关键信息

# 提取执行时间超过10秒的查询
mysqldumpslow -a /var/lib/mysql/mysql-slow.log | grep "Time=10"

导出为 CSV 分析

mysqldumpslow -a -s t /var/lib/mysql/mysql-slow.log | awk '{print $3,$5,$7}' > slow.csv

---

6. 优化建议

1. 索引优化
   对频繁出现在 WHERE 或 JOIN 中的字段添加索引：

   ALTER TABLE orders ADD INDEX idx_user_id (user_id);
   

2. 拆分复杂查询
   将大查询拆分为多个小查询（如分页查询）。

3. 缓存策略
   使用 MySQL 查询缓存或 Redis 缓存高频查询结果。

4. 监控工具
   结合 pt-query-digest（Percona Toolkit）生成更详细的报告：

   pt-query-digest /var/lib/mysql/mysql-slow.log
   

---

7. 安全警告

若发现大量异常慢查询（如全表扫描、未授权的复杂 JOIN），需排查：

• 是否遭受 SQL 注入攻击？
• 是否有恶意爬虫高频访问？

---

通过 mysqldumpslow 分析慢查询日志，可以快速定位性能瓶颈并针对性优化。