如何分析MySQL慢查询日志？日志调优你做对了吗？

在数据库性能优化领域，超过60%的SQL性能问题都源自慢查询。当电商系统遭遇大促卡顿、当OA系统处理报表时响应迟缓，其根本原因往往潜藏在MySQL的慢查询日志中。本文将从配置到实战，揭秘如何精准定位SQL性能瓶颈，助您避开常见调优误区。

一、开启慢查询日志，定位耗时SQL

1.1 日志配置三部曲

通过SHOW VARIABLES LIKE '%slow_query_log%'确认日志状态后，按需配置核心参数：

永久生效配置（需重启）
slow_query_log = ON
long_query_time = 1   单位：秒
slow_query_log_file = /var/log/mysql/slow.log

1.2 日志解析技巧

使用mysqldumpslow进行多维分析：

按执行次数排序
mysqldumpslow -s c -t 10 slow.log

 锁定特定时间段的查询
mysqldumpslow --start='2025到01-01 09:00:00' --stop='2025到01-01 18:00:00'

二、性能分析三板斧

2.1 EXPLAIN执行计划分析

重点关注type列（扫描类型）和rows列（扫描行数）：

• ALL类型：全表扫描（立即优化）
• index：全索引扫描（需评估索引合理性）
• Extra中的Using filesort：出现非预期排序

2.2 SHOW PROFILE深度追踪

SET profiling = 1;
执行目标SQL;
SHOW PROFILES;
SHOW PROFILE CPU, BLOCK IO FOR QUERY 2;

通过Sending data阶段耗时定位网络传输瓶颈，Creating tmp table识别临时表滥用问题。

2.3 执行成本精确计算

使用EXPLAIN ANALYZE（MySQL 8.0+）获取真实执行数据：

EXPLAIN ANALYZE 
SELECT  FROM orders WHERE create_time BETWEEN '2023到01-01' AND '2023到12-31';

三、调优实战：电商系统分页优化

3.1 原始慢查询

SELECT  FROM products 
WHERE category_id=5 
ORDER BY sales_volume DESC 
LIMIT 100000,20;

3.2 问题诊断

执行计划显示：type=ALL，rows=120万，存在Using filesort。

3.3 优化方案

1. 创建复合索引(category_id, sales_volume)
2. 改写为游标分页：

   SELECT  FROM products 
   WHERE category_id=5 AND id > 100000 
   ORDER BY sales_volume DESC 
   LIMIT 20;

优化后执行时间从3.2秒降至8毫秒。

四、调优避坑指南

4.1 典型误区

• 阈值设置一刀切：将long_query_time设置为0.1秒，导致日志爆炸式增长
• 索引滥用：为所有WHERE条件字段单独建索引，引发索引合并问题
• 忽略上下文：脱离业务场景优化SQL（如缓存机制下的高频查询）

4.2 最佳实践

1. 建立慢查询分级处理机制：紧急问题（>5s）、重要问题（1到5s）、观察项（0.5到1s）
2. 使用Percona Toolkit的pt-query-digest进行多维分析
3. 定期生成索引健康度报告（使用sys库的schema_index_statistics）

终极建议：建立慢查询看板，监控P99响应时间变化趋势。当优化遇到瓶颈时，考虑架构层面的分库分表或读写分离方案。记住：慢查询优化是持续过程，而非一次性任务。