引言
慢查询日志是 MySQL 性能优化的重要工具，它可以记录执行时间超过指定阈值的 SQL 语句。通过分析慢查询日志，我们可以快速定位性能瓶颈，针对性地进行优化。
本章将深入讲解：
- 慢查询日志的配置与管理
- 慢查询分析工具的使用
- 慢查询优化实战案例
- 生产环境的最佳实践
慢查询日志基础
什么是慢查询日志
慢查询日志（Slow Query Log） 记录了所有执行时间超过指定阈值的 SQL 语句。通过分析这些 SQL，我们可以：
- 发现性能瓶颈
- 定位慢查询原因
- 针对性优化 SQL 和索引
- 持续监控数据库性能
慢查询日志参数
| 参数名 | 说明 | 默认值 | 建议值 |
|---|---|---|---|
| slow_query_log | 是否开启慢查询日志 | OFF | ON |
| slow_query_log_file | 慢查询日志文件路径 | 数据目录 | /var/log/mysql/slow.log |
| long_query_time | 慢查询阈值（秒） | 10 | 1-2 |
| log_queries_not_using_indexes | 记录未使用索引的查询 | OFF | ON（开发环境） |
| log_slow_admin_statements | 记录管理语句 | OFF | OFF |
| min_examined_rows | 最小检查行数阈值 | 0 | 100 |
配置慢查询日志
临时配置（重启失效）
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
-- 设置慢查询阈值（秒）
SET GLOBAL long_query_time = 1;
-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';
-- 查看配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';永久配置（修改配置文件）
Linux（/etc/my.cnf 或 /etc/mysql/my.cnf）：
[mysqld]
# 开启慢查询日志
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
# 慢查询阈值（秒）
long_query_time = 1
# 记录未使用索引的查询（开发环境开启）
log_queries_not_using_indexes = ON
# 最小检查行数
min_examined_rows = 100Windows（my.ini）：
[mysqld]
slow-query-log = ON
slow-query-log-file = D:/mysql/logs/slow.log
long-query-time = 1
log-queries-not-using-indexes = ON重启 MySQL 生效：
# Linux
sudo systemctl restart mysqld
# Windows
net stop mysql
net start mysql验证配置
-- 查看慢查询日志状态
SHOW VARIABLES LIKE 'slow_query_log';
-- +------------------+-------+
-- | Variable_name | Value |
-- +------------------+-------+
-- | slow_query_log | ON |
-- +------------------+-------+
-- 查看日志文件位置
SHOW VARIABLES LIKE 'slow_query_log_file';
-- +------------------+----------------------------+
-- | Variable_name | Value |
-- +------------------+----------------------------+
-- | slow_query_log_file | /var/log/mysql/slow.log |
-- +------------------+----------------------------+
-- 查看慢查询阈值
SHOW VARIABLES LIKE 'long_query_time';
-- +-----------------+-----------+
-- | Variable_name | Value |
-- +-----------------+-----------+
-- | long_query_time | 1.000000 |
-- +-----------------+-----------+
-- 测试慢查询日志
SELECT SLEEP(2);
-- 这条语句会被记录到慢查询日志慢查询日志格式
日志格式示例
# Time: 2024-08-15T10:30:45.123456Z
# User@Host: app_user[app_user] @ localhost [] Id: 123
# Query_time: 2.345678 Lock_time: 0.000123 Rows_sent: 1000 Rows_examined: 100000
# Rows_affected: 0 Bytes_sent: 51200
use ecommerce;
SET timestamp=1723716645;
SELECT * FROM orders WHERE user_id = 100 AND status = 1 ORDER BY create_time DESC;字段说明
| 字段 | 说明 | 优化方向 |
|---|---|---|
| Query_time | 查询执行时间 | 越短越好 |
| Lock_time | 等待锁的时间 | 过长说明锁竞争严重 |
| Rows_sent | 返回的行数 | 结合 Rows_examined 分析 |
| Rows_examined | 扫描的行数 | 越少越好 |
| Rows_affected | 影响的行数 | UPDATE/DELETE 时关注 |
| Bytes_sent | 发送的数据量 | 过大考虑分页或限制 |
性能分析指标
扫描效率：
扫描效率 = Rows_sent / Rows_examined
-- 示例 1：高效查询
Rows_sent: 10 Rows_examined: 10 → 效率：100% ✅
-- 示例 2：低效查询
Rows_sent: 10 Rows_examined: 10000 → 效率：0.1% ❌优化目标：
- Rows_examined / Rows_sent < 10（理想）
- Rows_examined / Rows_sent < 100（可接受）
- Rows_examined / Rows_sent > 1000（必须优化）
慢查询分析工具
1. mysqldumpslow（MySQL 自带）
基本用法：
# 查看最慢的 10 条查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 按返回行数排序
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log
# 按扫描行数排序
mysqldumpslow -s e -t 10 /var/log/mysql/slow.log
# 按查询次数排序（最常用）
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
# 过滤特定查询
mysqldumpslow -s t -t 10 -g "SELECT.*FROM orders" /var/log/mysql/slow.log参数说明：
-s：排序方式（t=时间，c=次数，r=行数，e=扫描行数）-t：返回前 N 条-g：正则过滤-a：不将数字抽象为 N-n：抽象数字为 N
输出示例：
Count: 100 Time=2.34s (234s) Lock=0.00s (0s) Rows_sent=1000.0(100000), Rows_examined=10000.0(1000000)
app_user@app_user
SELECT * FROM orders WHERE user_id = N AND status = N ORDER BY create_time DESC;解读：
- Count: 100（执行了 100 次）
- Time=2.34s (234s)：平均 2.34 秒，总共 234 秒
- Lock=0.00s (0s)：锁等待时间
- Rows_sent=1000.0(100000)：平均返回 1000 行，总共 10 万行
- Rows_examined=10000.0(1000000)：平均扫描 1 万行，总共 100 万行
2. pt-query-digest（Percona 工具）
安装：
# Ubuntu/Debian
sudo apt-get install percona-toolkit
# CentOS/RHEL
sudo yum install percona-toolkit
# 或下载单独脚本
wget https://www.percona.com/get/pt-query-digest
chmod +x pt-query-digest
sudo mv pt-query-digest /usr/local/bin/基本用法：
# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log
# 分析最近 1 小时的日志
pt-query-digest --since=1h /var/log/mysql/slow.log
# 分析特定查询
pt-query-digest --filter='$_->{db} =~ /ecommerce/' /var/log/mysql/slow.log
# 输出到文件
pt-query-digest /var/log/mysql/slow.log > /tmp/slow_analysis.txt输出示例：
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============== ====== ====== ===== ====================
# 1 0x1234567890abcdef 234.5678 50.3% 100 2.3456 0.02 SELECT orders
# 2 0xfedcba0987654321 87.6543 18.8% 50 1.7531 0.01 SELECT users
# Query 1: SELECT orders
# Count: 100
# Exec time: 234s total, 2s avg
# Lock time: 0s total, 0ms avg
# Rows sent: 1000 avg
# Rows examined: 10000 avg
# Users: 1 unique
# Databases: 1 unique
# Tables: 1 unique
# EXPLAIN
SELECT * FROM orders WHERE user_id = ? AND status = ? ORDER BY create_time DESC;优势：
- 更详细的分析报告
- 自动聚合相似查询
- 提供优化建议
- 支持多种日志格式
3. MySQL Workbench（图形化工具）
步骤：
- 打开 MySQL Workbench
- 连接数据库
- 菜单：Management → Slow Log
- 查看和分析慢查询
4. 自定义分析脚本
Python 脚本示例：
#!/usr/bin/env python3
import re
from collections import defaultdict
def analyze_slow_log(log_file):
queries = defaultdict(list)
with open(log_file, 'r') as f:
content = f.read()
# 解析慢查询日志
pattern = r'# Time:.*?\n# User@Host:.*?\n# Query_time: (\d+\.\d+).*?Rows_sent: (\d+).*?Rows_examined: (\d+).*?\nuse.*?\n(.*?);'
matches = re.findall(pattern, content, re.DOTALL)
for query_time, rows_sent, rows_examined, sql in matches:
# 规范化 SQL（去除具体值）
normalized_sql = re.sub(r'\b\d+\b', 'N', sql.strip())
queries[normalized_sql].append({
'time': float(query_time),
'sent': int(rows_sent),
'examined': int(rows_examined)
})
# 输出分析结果
for sql, stats in sorted(queries.items(), key=lambda x: sum(s['time'] for s in x[1]), reverse=True)[:10]:
avg_time = sum(s['time'] for s in stats) / len(stats)
total_time = sum(s['time'] for s in stats)
print(f"Count: {len(stats)}, Avg Time: {avg_time:.2f}s, Total: {total_time:.2f}s")
print(f"SQL: {sql[:100]}...\n")
if __name__ == '__main__':
analyze_slow_log('/var/log/mysql/slow.log')慢查询优化实战
案例 1：未使用索引
慢查询日志：
# Query_time: 3.456789 Rows_sent: 100 Rows_examined: 500000
SELECT * FROM orders WHERE user_id = 100 AND status = 1;分析：
- 扫描 50 万行，返回 100 行
- 效率：100/500000 = 0.02%
诊断：
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;
-- type: ALL（全表扫描）
-- key: NULL（未使用索引）优化：
-- 创建联合索引
CREATE INDEX idx_user_status ON orders(user_id, status);
-- 验证
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;
-- type: ref
-- key: idx_user_status
-- rows: 100（从 50 万降到 100）
-- 优化后 Query_time: 0.001s（提升 3000 倍）案例 2：函数导致索引失效
慢查询日志：
# Query_time: 5.678901 Rows_sent: 1000 Rows_examined: 1000000
SELECT * FROM orders WHERE YEAR(create_time) = 2024;分析：
- YEAR() 函数导致索引失效
- 全表扫描 100 万行
优化：
-- 改写为范围查询
SELECT * FROM orders
WHERE create_time >= '2024-01-01 00:00:00'
AND create_time < '2025-01-01 00:00:00';
-- 创建索引
CREATE INDEX idx_create_time ON orders(create_time);
-- 优化后 Query_time: 0.05s（提升 100 倍）案例 3：排序导致文件排序
慢查询日志：
# Query_time: 2.345678 Rows_sent: 10 Rows_examined: 50000
SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time DESC LIMIT 10;分析：
- 扫描 5 万行，只返回 10 行
- Extra: Using filesort
优化：
-- 创建联合索引（包含排序列）
CREATE INDEX idx_user_time ON orders(user_id, create_time DESC);
-- 验证
EXPLAIN SELECT * FROM orders
WHERE user_id = 100
ORDER BY create_time DESC
LIMIT 10;
-- Extra: NULL（无 filesort）
-- 优化后 Query_time: 0.002s（提升 1000 倍）案例 4：隐式类型转换
慢查询日志：
# Query_time: 4.567890 Rows_sent: 1 Rows_examined: 200000
SELECT * FROM users WHERE phone = 13800138000;分析：
- phone 是 VARCHAR 类型，但查询使用了数字
- 隐式转换导致索引失效
优化：
-- 使用正确的数据类型
SELECT * FROM users WHERE phone = '13800138000';
-- 确保索引存在
CREATE INDEX idx_phone ON users(phone);
-- 优化后 Query_time: 0.001s（提升 4000 倍）案例 5：LIKE 通配符
慢查询日志：
# Query_time: 6.789012 Rows_sent: 100 Rows_examined: 1000000
SELECT * FROM users WHERE name LIKE '%张%';分析：
- LIKE 以通配符开头，索引失效
- 全表扫描 100 万行
优化方案 1：使用全文索引
-- 创建全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_name (name);
-- 使用全文搜索
SELECT * FROM users WHERE MATCH(name) AGAINST('张三');
-- 优化后 Query_time: 0.1s（提升 60 倍）优化方案 2：前缀匹配
-- 如果可以改为前缀匹配
SELECT * FROM users WHERE name LIKE '张%';
-- 创建普通索引
CREATE INDEX idx_name ON users(name);
-- 优化后 Query_time: 0.01s（提升 600 倍）生产环境最佳实践
1. 慢查询日志配置
开发环境：
long_query_time = 1
log_queries_not_using_indexes = ON生产环境：
long_query_time = 2
log_queries_not_using_indexes = OFF # 避免日志过大
min_examined_rows = 100 # 只记录扫描 100 行以上的查询2. 日志轮转
防止日志文件过大：
# /etc/logrotate.d/mysql-slow
/var/log/mysql/slow.log {
daily
rotate 7
compress
delaycompress
missingok
create 640 mysql adm
postrotate
mysqladmin flush-logs
endscript
}3. 定期分析
每日分析脚本：
#!/bin/bash
# /usr/local/bin/daily_slow_query_analysis.sh
LOG_FILE="/var/log/mysql/slow.log"
REPORT_FILE="/var/log/mysql/slow_report_$(date +%Y%m%d).txt"
# 分析昨天的慢查询
pt-query-digest --since=24h $LOG_FILE > $REPORT_FILE
# 发送邮件报告
mail -s "MySQL Slow Query Report $(date +%Y-%m-%d)" dba@example.com < $REPORT_FILE
# 清空慢查询日志
mysql -e "FLUSH SLOW LOGS;"设置定时任务：
# crontab -e
0 9 * * * /usr/local/bin/daily_slow_query_analysis.sh4. 监控告警
慢查询数量监控：
-- 查看慢查询数量
SHOW GLOBAL STATUS LIKE 'Slow_queries';
-- 计算每秒慢查询数
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SLEEP(10);
SHOW GLOBAL STATUS LIKE 'Slow_queries';
-- (第二次值 - 第一次值) / 10 = 每秒慢查询数告警阈值：
- 每秒慢查询 > 10：警告
- 每秒慢查询 > 50：严重
5. 性能基线
建立性能基线：
-- 记录正常情况下的指标
SELECT
VARIABLE_NAME,
VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME IN (
'Queries',
'Slow_queries',
'Questions',
'Threads_connected'
);定期对比：
- 每周对比慢查询数量
- 每月分析趋势变化
- 及时发现性能退化
注意事项
1. 性能影响
开启慢查询日志会有轻微性能开销（约 1-3%）：
-- 生产环境建议
-- 1. 不要设置过低的 long_query_time
-- 2. 定期清理日志文件
-- 3. 使用独立的磁盘存储日志2. 磁盘空间
# 监控日志文件大小
ls -lh /var/log/mysql/slow.log
# 限制日志文件大小
-- 在 my.cnf 中添加
max_binlog_size = 100M3. 隐私保护
慢查询日志可能包含敏感数据：
# 限制日志文件权限
chmod 640 /var/log/mysql/slow.log
chown mysql:adm /var/log/mysql/slow.log
# 定期清理旧日志
find /var/log/mysql -name "slow.log.*" -mtime +30 -delete总结
慢查询优化流程
graph LR
A[开启慢查询日志] --> B[收集慢查询 SQL]
B --> C[使用工具分析]
C --> D[定位问题原因]
D --> E[制定优化方案]
E --> F[实施优化]
F --> G[验证效果]
G --> H[持续监控]
核心要点
- 合理配置：根据环境设置合适的阈值
- 定期分析：使用工具自动化分析
- 针对性优化：根据具体原因选择优化方案
- 持续监控：建立性能基线和告警机制
工具选择
| 工具 | 适用场景 | 优势 |
|---|---|---|
| mysqldumpslow | 快速分析 | MySQL 自带，无需安装 |
| pt-query-digest | 深度分析 | 功能强大，报告详细 |
| 自定义脚本 | 特殊需求 | 灵活定制 |
| 监控平台 | 实时监控 | 图形化，告警 |
下一步
掌握慢查询分析后，下一章我们将学习：
- 索引优化实战案例
- 综合性能调优方法
- 生产环境问题排查流程
参考资料
- MySQL 官方文档 - 慢查询日志
- Percona Toolkit 文档
- 《高性能 MySQL》第 7 章：MySQL 高级特性
- MySQL 性能优化最佳实践