MySQL 慢查询分析

慢查询概述

慢查询日志是 MySQL 提供的一种日志记录，用于记录执行时间超过指定阈值的 SQL 语句。通过分析慢查询日志，DBA 可以找出系统中存在的性能瓶颈，进行针对性优化。

版本差异

特性	MySQL 5.6	MySQL 5.7	MySQL 8.0
慢查询精度	秒级	微秒级	微秒级
log_slow_extra	不支持	不支持	支持（记录额外信息）
日志格式	FILE/TABLE	FILE/TABLE	FILE/TABLE
自动轮转	不支持	不支持	支持
直方图统计	不支持	支持	支持
自适应哈希索引	支持	支持	支持
并行查询	不支持	不支持	支持（企业版）

慢查询日志配置

启用慢查询日志

动态配置

sql

-- 查看当前慢查询日志配置
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE '%log_queries_not_using_indexes%';

-- 启用慢查询日志
SET GLOBAL slow_query_log = 1;
-- 设置慢查询阈值（单位：秒）
SET GLOBAL long_query_time = 1;
-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 1;
-- 设置慢查询日志文件路径
SET GLOBAL slow_query_log_file = '/var/lib/mysql/mysql-slow.log';
-- 记录管理语句
SET GLOBAL log_slow_admin_statements = 1;
-- 记录从库上的慢查询
SET GLOBAL log_slow_slave_statements = 1;

静态配置（my.cnf）

ini

[mysqld]
# 启用慢查询日志
slow_query_log = 1
# 慢查询日志文件路径
slow_query_log_file = /var/lib/mysql/mysql-slow.log
# 慢查询阈值
long_query_time = 1
# 记录未使用索引的查询
log_queries_not_using_indexes = 1
# 记录管理语句
log_slow_admin_statements = 1
# 记录从库上的慢查询
log_slow_slave_statements = 1
# 日志格式（可选：FILE 或 TABLE）
log_output = FILE

慢查询日志格式

慢查询日志的基本格式如下：

# Time: 2024-01-01T12:00:00.000000Z
# User@Host: user[database] @ localhost []  Id: 12345
# Query_time: 2.500000  Lock_time: 0.100000 Rows_sent: 1000  Rows_examined: 1000000
SET timestamp=1609459200;
SELECT * FROM users WHERE name LIKE '%test%';

各字段含义：

Time: 查询执行时间
User@Host: 执行查询的用户和主机
Id: 连接 ID
Query_time: 查询执行时间（秒）
Lock_time: 锁定时间（秒）
Rows_sent: 返回给客户端的行数
Rows_examined: 扫描的行数

慢查询分析工具

mysqldumpslow

mysqldumpslow 是 MySQL 自带的慢查询分析工具，用于汇总慢查询日志。

基本用法

bash

# 查看帮助
mysqldumpslow --help

# 分析慢查询日志，按查询次数排序
mysqldumpslow -s c /var/lib/mysql/mysql-slow.log

# 分析慢查询日志，按查询时间排序，显示前10条
mysqldumpslow -s t -t 10 /var/lib/mysql/mysql-slow.log

# 分析慢查询日志，按锁定时间排序
mysqldumpslow -s l /var/lib/mysql/mysql-slow.log

# 分析慢查询日志，按返回行数排序
mysqldumpslow -s r /var/lib/mysql/mysql-slow.log

# 显示完整的SQL语句（不截断）
mysqldumpslow -a /var/lib/mysql/mysql-slow.log

# 显示数字（不转换为N）
mysqldumpslow -n /var/lib/mysql/mysql-slow.log

常用组合

bash

# 按查询时间排序，显示前20条，不截断SQL，显示数字
mysqldumpslow -s t -t 20 -a -n /var/lib/mysql/mysql-slow.log

# 仅显示SELECT语句的慢查询
mysqldumpslow -s t -t 10 -a -n "SELECT" /var/lib/mysql/mysql-slow.log

pt-query-digest

pt-query-digest 是 Percona Toolkit 中的一个工具，用于更详细地分析慢查询日志。

安装 Percona Toolkit

bash

# CentOS/RHEL
yum install percona-toolkit

# Debian/Ubuntu
dpkg -i percona-toolkit_*.deb
apt-get install -f

基本用法

bash

# 分析慢查询日志
pt-query-digest /var/lib/mysql/mysql-slow.log

# 分析慢查询日志，保存结果到文件
pt-query-digest /var/lib/mysql/mysql-slow.log > slow_query_analysis.txt

# 分析指定时间段内的慢查询
pt-query-digest --since '2024-01-01 00:00:00' --until '2024-01-02 00:00:00' /var/lib/mysql/mysql-slow.log

# 分析并按主机分组
pt-query-digest --group-by host /var/lib/mysql/mysql-slow.log

# 分析并按用户分组
pt-query-digest --group-by user /var/lib/mysql/mysql-slow.log

高级用法

bash

# 仅分析SELECT语句
pt-query-digest --filter '($event->{arg} =~ m/^SELECT/)' /var/lib/mysql/mysql-slow.log

# 仅分析执行时间超过5秒的查询
pt-query-digest --filter '($event->{query_time} > 5)' /var/lib/mysql/mysql-slow.log

# 分析慢查询日志并将结果导入到MySQL表中
pt-query-digest --user=root --password=password --review h=localhost,D=mysql,t=query_review /var/lib/mysql/mysql-slow.log

mysqlsla

mysqlsla 是另一个常用的慢查询分析工具，提供了更友好的分析报告。

安装 mysqlsla

bash

yum install -y perl-DBI perl-DBD-MySQL perl-Time-HiRes
wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
tar -zxvf mysqlsla-2.03.tar.gz
cd mysqlsla-2.03
perl Makefile.PL
make && make install

基本用法

bash

# 分析慢查询日志
mysqlsla -lt slow /var/lib/mysql/mysql-slow.log

# 按查询时间排序，显示前10条
mysqlsla -lt slow -s t -n 10 /var/lib/mysql/mysql-slow.log

# 显示详细的查询信息
mysqlsla -lt slow -d /var/lib/mysql/mysql-slow.log

慢查询优化实战

案例分析：未使用索引的查询

慢查询日志片段：

# Query_time: 2.500000  Lock_time: 0.000000 Rows_sent: 1000  Rows_examined: 1000000
SELECT * FROM users WHERE name LIKE '%test%';

优化步骤：

查看表结构和索引
sql
```
DESCRIBE users;
SHOW INDEX FROM users;
```

分析执行计划

sql

EXPLAIN SELECT * FROM users WHERE name LIKE '%test%';

优化方案
- 如果是前缀匹配（如 'test%'），可以添加普通索引
- 如果是中缀或后缀匹配（如 '%test%' 或 '%test'），可以考虑：
  - 添加全文索引
  - 使用 Elasticsearch 等搜索引擎
  - 优化业务逻辑，避免全表扫描
sql
```
-- 添加全文索引
ALTER TABLE users ADD FULLTEXT INDEX idx_name_fulltext (name);

-- 使用全文索引查询
SELECT * FROM users WHERE MATCH(name) AGAINST('test' IN BOOLEAN MODE);
```

案例分析：索引失效

慢查询日志片段：

# Query_time: 3.200000  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 500000
SELECT * FROM orders WHERE order_date > '2024-01-01' AND status = 1;

优化步骤：

查看执行计划

sql

EXPLAIN SELECT * FROM orders WHERE order_date > '2024-01-01' AND status = 1;

检查索引情况
sql
```
SHOW INDEX FROM orders;
```

优化方案

检查索引是否存在
检查索引是否被正确使用
创建联合索引

sql

-- 创建联合索引
ALTER TABLE orders ADD INDEX idx_order_date_status (order_date, status);

案例分析：大表查询优化

慢查询日志片段：

# Query_time: 5.800000  Lock_time: 0.000100 Rows_sent: 10000  Rows_examined: 1000000
SELECT * FROM logs WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';

优化步骤：

查看表结构和数据量

sql

SELECT COUNT(*) FROM logs;
SHOW CREATE TABLE logs;

优化方案

分区表
垂直拆分
只查询需要的字段
增加查询条件，减少返回行数

sql

-- 创建分区表（按时间范围分区）
ALTER TABLE logs PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01'))
);

-- 只查询需要的字段
SELECT id, user_id, action FROM logs WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';

慢查询监控与告警

Prometheus + Grafana 监控

配置 MySQL Exporter 采集慢查询指标
- MySQL Exporter 会自动采集慢查询相关指标，如 mysql_global_status_slow_queries
Grafana 仪表盘
- 创建慢查询监控面板，包含：
  - 慢查询数量趋势图
  - 慢查询TOP SQL
  - 慢查询平均执行时间
  - 未使用索引查询数量

Prometheus 告警规则

yaml

groups:
- name: mysql-slow-queries
  rules:
  - alert: MySQLHighSlowQueries
    expr: increase(mysql_global_status_slow_queries[5m]) > 10
    for: 1m
    labels:
      severity: warning
    annotations:
      summary: "MySQL 慢查询数量过高"
      description: "实例 {{ $labels.instance }} 在过去5分钟内有 {{ $value }} 条慢查询"
  
  - alert: MySQLNoIndexQueries
    expr: increase(mysql_global_status_queries_not_using_indexes[5m]) > 20
    for: 1m
    labels:
      severity: warning
    annotations:
      summary: "MySQL 未使用索引查询数量过高"
      description: "实例 {{ $labels.instance }} 在过去5分钟内有 {{ $value }} 条未使用索引的查询"

Zabbix 监控

创建监控项
- 慢查询数量：mysql.status[Slow_queries]
- 未使用索引查询数量：mysql.status[Queries_not_using_indexes]
创建触发器
- 慢查询数量5分钟内增加超过10条
- 未使用索引查询数量5分钟内增加超过20条
创建告警动作
- 发送邮件告警
- 发送短信告警
- 调用API通知监控平台

慢查询日志管理

日志轮换

使用 logrotate 进行日志轮换

bash

# 创建 logrotate 配置文件
vi /etc/logrotate.d/mysql-slow

# 配置内容
/var/lib/mysql/mysql-slow.log {
    daily
    rotate 7
    missingok
    compress
    delaycompress
    notifempty
    create 644 mysql mysql
    postrotate
        /bin/kill -HUP $(cat /var/run/mysqld/mysqld.pid) 2>/dev/null || true
    endscript
}

清理旧日志

bash

# 手动清理30天前的慢查询日志
find /var/lib/mysql -name "mysql-slow.log.*" -mtime +30 -delete

最佳实践

合理设置慢查询阈值
- 一般建议设置为1秒
- 对于高并发系统，可以设置为0.5秒或更低
- 根据业务场景调整，避免过多日志影响性能
定期分析慢查询日志
- 每日：查看慢查询数量和TOP SQL
- 每周：进行全面的慢查询分析和优化
- 每月：总结慢查询优化成果，调整优化策略
结合其他性能指标分析
- 结合 QPS、TPS 等指标
- 结合 CPU、内存、磁盘 I/O 等系统指标
- 结合 InnoDB 状态指标
优化后验证效果
- 查看慢查询日志，确认优化后的SQL不再出现在慢查询中
- 查看执行计划，确认索引被正确使用
- 监控系统性能指标，确认整体性能提升
建立慢查询优化流程
- 收集慢查询日志
- 分析慢查询原因
- 制定优化方案
- 实施优化
- 验证优化效果
- 文档记录

总结

慢查询分析是 MySQL 性能优化的重要手段之一。通过合理配置慢查询日志，使用专业的分析工具，结合实际业务场景进行优化，可以有效提升 MySQL 数据库的性能。DBA 应该将慢查询分析作为日常运维工作的一部分，建立完善的慢查询监控和优化体系，确保数据库系统的高效稳定运行。

MySQL 慢查询分析 ​

慢查询概述 ​

版本差异 ​

慢查询日志配置 ​

启用慢查询日志 ​

动态配置 ​

静态配置（my.cnf） ​

慢查询日志格式 ​

慢查询分析工具 ​

mysqldumpslow ​

基本用法 ​

常用组合 ​

pt-query-digest ​

安装 Percona Toolkit ​

基本用法 ​

高级用法 ​

mysqlsla ​

安装 mysqlsla ​

基本用法 ​

慢查询优化实战 ​

案例分析：未使用索引的查询 ​

案例分析：索引失效 ​

案例分析：大表查询优化 ​

慢查询监控与告警 ​

Prometheus + Grafana 监控 ​

Zabbix 监控 ​

慢查询日志管理 ​

日志轮换 ​

清理旧日志 ​

最佳实践 ​

总结 ​

MySQL 慢查询分析

慢查询概述

版本差异

慢查询日志配置

启用慢查询日志

动态配置

静态配置（my.cnf）

慢查询日志格式

慢查询分析工具

mysqldumpslow

基本用法

常用组合

pt-query-digest

安装 Percona Toolkit

基本用法

高级用法

mysqlsla

安装 mysqlsla

基本用法

慢查询优化实战

案例分析：未使用索引的查询

案例分析：索引失效

案例分析：大表查询优化

慢查询监控与告警

Prometheus + Grafana 监控

Zabbix 监控

慢查询日志管理

日志轮换

清理旧日志

最佳实践

总结