慢查询治理实战：从slow_query_log到索引优化的完整流程
大约 7 分钟
慢查询治理实战：从slow_query_log到索引优化的完整流程
适读人群：Java后端开发、DBA、对SQL性能优化感兴趣的工程师 | 阅读时长：约24分钟
开篇故事
2022年，我加入了一个做了3年的老项目，上线就是一堆告警。
MySQL CPU持续在80%以上，慢查询日志每天产生10000+条。
我的任务：一个月内把慢查询降低80%。
做下来，花了三周。方法论比较重要，今天把完整流程记录下来。
核心结论是：慢查询治理不是靠感觉，要靠数据和工具。
一、慢查询的定位工具链
工具链：
slow_query_log（MySQL慢查询日志）
↓
pt-query-digest（分析汇总，找Top慢查询）
↓
EXPLAIN（分析执行计划）
↓
添加/优化索引
↓
验证优化效果（再次EXPLAIN + 实测）二、底层原理：慢查询日志的完整字段解析
2.1 开启慢查询日志
# my.cnf配置
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1 # 超过1秒记录（生产可以调到0.5秒）
log_queries_not_using_indexes = ON # 记录未使用索引的查询（即使不超过阈值）
log_slow_admin_statements = ON # 记录慢的管理语句（OPTIMIZE TABLE等）
min_examined_row_limit = 1000 # 扫描行数超过1000才记录动态开启（不重启MySQL）：
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = ON;2.2 慢查询日志字段解析
# Time: 2022-06-18T10:23:45.123456Z
# User@Host: root[root] @ localhost [] Id: 100
# Query_time: 13.728642 Lock_time: 0.000189 Rows_sent: 20 Rows_examined: 9876543
# Thread_id: 100 Schema: mydb QC_hit: No
SET timestamp=1655547825;
SELECT * FROM orders WHERE DATE(created_at) = '2022-06-01';
关键字段：
Query_time: 13.728642 → 查询耗时13.7秒（需要优化）
Lock_time: 0.000189 → 等锁时间0.0002秒（锁竞争不严重）
Rows_sent: 20 → 返回给客户端20行
Rows_examined: 9876543 → 扫描了987万行！
扫描行数 >> 返回行数：典型的索引失效或全表扫描问题三、完整解决方案与代码
3.1 用pt-query-digest分析慢查询
# 安装（CentOS）
yum install percona-toolkit
# 分析最近24小时的慢查询
pt-query-digest /var/log/mysql/slow.log \
--since 24h \
--limit 20 \ # 显示Top20
--report-format query_report \
> slow_query_report.txt
# 关键报告字段解读：# Query 1: 2.89k QPS, 28.92x concurrency, ID 0xABC123 at byte 1234567
# This item is included in the report because it matches --limit.
# pct total min max avg 95% stddev median
# Count 43 12000
# Exec time 67 2873s 5ms 42s 240ms 1s 450ms 120ms
# ^^^ ^^^^^
# 最小5ms 95分位1s（最慢5%的请求超过1s）
# Rows sent 0 240K 5 50 20 40 12 20
# Rows examine 98 941M 8K 10M 78K 200K 120K 50K
# ^^^^ ^^^^
# 最少扫8K行 平均扫78K行
#
# SELECT * FROM orders WHERE DATE(created_at) = '2022-06-01' AND status = ?
# ↑ 这条SQL出现12000次，平均耗时240ms，95%的请求超过1秒
# ↑ 平均扫描78K行（全表扫描的特征）3.2 建立慢查询治理的Java工具类
/**
* 慢SQL自动分析工具
* 定期扫描慢查询日志，自动EXPLAIN并输出优化建议
*/
@Component
public class SlowQueryAnalyzer {
@Autowired
private DataSource dataSource;
@Autowired
private SlowQueryReportService reportService;
private static final long SLOW_THRESHOLD_MS = 1000; // 1秒
/**
* 从performance_schema获取慢查询（比解析日志文件更方便）
*/
@Scheduled(fixedDelay = 3600_000) // 每小时分析一次
public void analyzeSlowQueries() throws SQLException {
String sql = """
SELECT
DIGEST_TEXT AS sql_template,
COUNT_STAR AS exec_count,
SUM_TIMER_WAIT / 1000000000 AS total_time_ms,
AVG_TIMER_WAIT / 1000000000 AS avg_time_ms,
MAX_TIMER_WAIT / 1000000000 AS max_time_ms,
SUM_ROWS_EXAMINED AS total_rows_examined,
SUM_ROWS_SENT AS total_rows_sent,
SUM_NO_INDEX_USED AS no_index_count
FROM performance_schema.events_statements_summary_by_digest
WHERE AVG_TIMER_WAIT / 1000000000 > ?
AND SCHEMA_NAME = DATABASE()
ORDER BY total_time_ms DESC
LIMIT 20
""";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setLong(1, SLOW_THRESHOLD_MS);
ResultSet rs = ps.executeQuery();
List<SlowQueryInfo> slowQueries = new ArrayList<>();
while (rs.next()) {
SlowQueryInfo info = new SlowQueryInfo();
info.setSqlTemplate(rs.getString("sql_template"));
info.setExecCount(rs.getLong("exec_count"));
info.setAvgTimeMs(rs.getLong("avg_time_ms"));
info.setMaxTimeMs(rs.getLong("max_time_ms"));
info.setTotalRowsExamined(rs.getLong("total_rows_examined"));
info.setNoIndexCount(rs.getLong("no_index_count"));
// 计算优化优先级
info.setPriority(calculatePriority(info));
slowQueries.add(info);
}
// 发送报告
if (!slowQueries.isEmpty()) {
reportService.sendSlowQueryReport(slowQueries);
}
}
}
/**
* 优化优先级计算：执行次数 × 平均耗时（影响最大的优先处理）
*/
private long calculatePriority(SlowQueryInfo info) {
return info.getExecCount() * info.getAvgTimeMs();
}
}3.3 完整的慢查询优化案例
以开篇提到的项目为例，完整的优化流程：
第一步：找出Top5慢查询
-- 查看performance_schema中的慢查询
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'mydb'
ORDER BY AVG_TIMER_WAIT DESC LIMIT 5;结果（模拟数据）：
SQL1: SELECT * FROM orders WHERE DATE(created_at) = ? avg=13700ms count=5000
SQL2: SELECT * FROM user_behavior WHERE user_id=? AND ... avg=2300ms count=50000
SQL3: SELECT COUNT(*) FROM orders GROUP BY status avg=1800ms count=1000
SQL4: SELECT * FROM product WHERE name LIKE ? avg=1200ms count=10000
SQL5: SELECT o.*, u.name FROM orders o LEFT JOIN users u ... avg=800ms count=20000第二步：逐个EXPLAIN分析
-- SQL1: DATE函数导致索引失效
EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2022-06-01';
-- type=ALL, rows=9876543
-- 修复方案
SELECT * FROM orders
WHERE created_at >= '2022-06-01 00:00:00'
AND created_at < '2022-06-02 00:00:00';
-- type=range, rows=27321
-- SQL2: 联合索引未覆盖所有过滤条件
EXPLAIN SELECT * FROM user_behavior
WHERE user_id = 12345 AND event_type = 'CLICK' AND created_at > '2022-01-01';
-- 发现只用了user_id，event_type和created_at的索引未命中
-- 修复方案：创建联合索引
ALTER TABLE user_behavior
ADD INDEX idx_user_event_time (user_id, event_type, created_at);第三步：创建索引
-- 批量创建优化索引（按优先级处理）
-- 生产环境用Online DDL，不锁表
ALTER TABLE orders
ADD INDEX idx_created_at (created_at),
ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE user_behavior
ADD INDEX idx_user_event_time (user_id, event_type, created_at),
ALGORITHM=INPLACE, LOCK=NONE;第四步：验证效果
-- 验证SQL1优化效果
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2022-06-01 00:00:00' AND created_at < '2022-06-02 00:00:00';
-- Before: type=ALL, rows=9876543, time=13.7s
-- After: type=range, rows=27321, time=0.02s3.4 自动化的慢查询巡检脚本
#!/bin/bash
# slow_query_check.sh
# 定时巡检并发邮件告警
MYSQL_USER="root"
MYSQL_PASSWORD="password"
MYSQL_DB="mydb"
THRESHOLD_SECONDS=1
mysql -u$MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DB << 'EOF'
SELECT
DIGEST_TEXT,
COUNT_STAR AS 执行次数,
ROUND(AVG_TIMER_WAIT/1e9, 2) AS 平均耗时秒,
ROUND(MAX_TIMER_WAIT/1e9, 2) AS 最大耗时秒,
SUM_NO_INDEX_USED AS 未走索引次数
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = DATABASE()
AND AVG_TIMER_WAIT/1e9 > 1
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;
EOF四、踩坑实录
坑1：添加索引后优化器还是选择全表扫描
-- 现象：明明有索引，但EXPLAIN还是ALL
ALTER TABLE orders ADD INDEX idx_status (status);
EXPLAIN SELECT * FROM orders WHERE status = 'PAID';
-- 仍然 type=ALL!
-- 原因：status字段只有5种值，选择性太低
-- 优化器计算：通过索引扫描 + 大量回表，比全表扫描更慢
-- 优化器放弃了索引！解决方案：
-- 方案1：联合索引（高选择性字段在前）
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);
-- user_id先过滤到少量行，再用status过滤，有意义
-- 方案2：接受全表扫描（低选择性单字段就是不适合单独建索引）
-- 不要强制让优化器走索引，它的选择通常是对的
-- 强制走索引（临时验证，不建议长期）
SELECT * FROM orders FORCE INDEX (idx_status) WHERE status = 'PAID';坑2：统计信息过期，EXPLAIN rows不准
-- EXPLAIN显示rows=100，但实际执行扫描了10万行
-- 原因：表的统计信息太旧，优化器的估算不准确
-- 查看统计信息的最后更新时间
SELECT TABLE_NAME, UPDATE_TIME, TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb';
-- 手动更新统计信息
ANALYZE TABLE orders;
-- 重新收集统计信息后，EXPLAIN的估算更准确
-- 或者等MySQL自动更新（当数据变化超过10%时自动触发）坑3：慢查询日志文件过大，占满磁盘
告警：磁盘使用率99%，MySQL无法写入
查看慢查询日志大小：
ls -lh /var/log/mysql/slow.log
-rw-r--r-- 1 mysql mysql 45G Jan 10 02:00 slow.log ← 45GB！解决方案：
# 在MySQL里执行（清空慢查询日志而不停服务）
mysql -e "FLUSH SLOW LOGS;"
# 这会关闭当前日志文件，创建新的日志文件
# 配置日志轮转（logrotate）
cat > /etc/logrotate.d/mysql-slow << 'EOF'
/var/log/mysql/slow.log {
daily # 每天轮转
rotate 7 # 保留7天
missingok
compress
delaycompress
sharedscripts
postrotate
/usr/bin/mysql -e "FLUSH SLOW LOGS;"
endscript
}
EOF五、总结与延伸
慢查询治理的完整方法论：
1. 量化：用pt-query-digest或performance_schema找Top N慢查询
- 不要靠感觉，要按「总耗时 = 执行次数 × 平均耗时」排优先级
2. 分析：EXPLAIN + EXPLAIN FORMAT=JSON + EXPLAIN ANALYZE（8.0+）
- 重点关注type=ALL/index和Extra中的Using filesort/Using temporary
3. 优化：索引优化是第一选择，其次是SQL重写
- 80%的慢查询可以通过加索引解决
4. 验证：优化后必须重新EXPLAIN，并在测试环境实测
- 不要盲目上线，确认效果后再推生产
5. 监控：建立持续的慢查询巡检机制
- 代码上线后可能引入新的慢查询，需要持续监控一个重要的心得：慢查询治理是一个持续的过程，不是一次性工程。建立自动化的监控和告警机制，才能保持系统长期健康。