SQL慢查询日志如何分析？

本文目录导读：

1. 开启并获取慢查询日志
2. 逐条分析慢SQL（核心步骤）
3. 高级分析工具
4. 典型慢SQL案例与优化
5. 日常巡检的黄金规则
6. 生产环境注意事项

分析SQL慢查询日志是数据库性能优化的核心环节，以下是系统化的分析方法和最佳实践（以MySQL为例，但思路通用）：

开启并获取慢查询日志

确认是否开启

-- MySQL
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';  -- 默认10秒
SHOW VARIABLES LIKE 'log_queries_not_using_indexes'; -- 是否记录无索引查询

临时开启（生产环境谨慎）

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 单位秒，建议从2秒开始
SET GLOBAL log_queries_not_using_indexes = 'ON';  

定位日志文件

SHOW VARIABLES LIKE 'slow_query_log_file';
-- 或查看MySQL配置文件my.cnf中的路径

使用mysqldumpslow工具分析（最常用）

# 按查询时间排序，显示前10条
mysqldumpslow -s t -t 10 /var/lib/mysql/hostname-slow.log
# 按平均查询时间排序
mysqldumpslow -s at -t 20 /var/lib/mysql/hostname-slow.log
# 按锁定时间排序
mysqldumpslow -s l -t 10 /var/lib/mysql/hostname-slow.log
# 按查询次数排序（最频繁的慢查询）
mysqldumpslow -s c -t 20 /var/lib/mysql/hostname-slow.log

参数说明：

• -s：排序方式 c(次数) t(时间) l(锁等待时间) at(平均时间)
• -t：显示前N条
• -g：正则过滤, -g "SELECT.*user" 只显示包含user的查询

逐条分析慢SQL（核心步骤）

拿到慢SQL后,按以下顺序分析：

使用EXPLAIN分析执行计划

EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 1\G

关键指标解读：

• type：访问类型（性能从好到差）
  • system/const：最优，主键或唯一索引查找
  • eq_ref：联合查询，使用唯一索引
  • ref：普通索引查找
  • range：索引范围扫描（>、<、BETWEEN、IN）
  • index：全索引扫描（比全表好一点）
  • ALL：全表扫描（危险信号！需要添加索引）
• rows：预估扫描的行数（越小越好,10万行以上需警惕）
• Extra：重点关注
  • Using filesort：需要优化排序，考虑建立覆盖索引
  • Using temporary：使用了临时表，需优化GROUP BY或DISTINCT
  • Using index：覆盖索引（好现象）
  • Using where; Using index：理想情况，索引覆盖且条件过滤

检查索引使用情况

• 缺失索引：WHERE条件、JOIN字段、ORDER BY字段未加索引
• 索引失效：
  • 对索引列使用了函数：WHERE DATE(create_time) = '2024-01-01' → 应改为 WHERE create_time >= '...' AND create_time < '...'
  • 隐式类型转换：WHERE user_id = '123'（如果user_id是数字类型）
  • 左模糊查询：LIKE '%keyword'（不触发索引）
  • OR条件中存在非索引列
  • 联合索引未满足最左前缀原则

分析查询逻辑

• 返回不必要的数据：SELECT * 改为只选需要的列
• 分页偏移过大：LIMIT 100000,20 → 考虑游标分页或子查询优化
• N+1查询问题：循环中查询数据库，改为JOIN或批量查询
• 嵌套子查询：尽量改为JOIN（MySQL对子查询优化有限）

高级分析工具

Performance Schema (MySQL 5.6+)

-- 查看最耗时的查询（无需慢日志）
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

pt-query-digest (Percona Toolkit，强烈推荐)

# 安装后使用
pt-query-digest /var/lib/mysql/slow.log > analysis_report.txt
# 实时分析（不写日志）
pt-query-digest --processlist h=localhost,u=root,p=password

输出优势：

• 自动分组（归一化带参数的SQL）
• 给出时间分布、命中率、建议
• 找出“高频+慢”的重合SQL

第三方监控工具

• MySQL Enterprise Monitor（商业）
• Prometheus + Grafana + mysqld_exporter（开源）
• Datadog / SkyWalking（APM类）

典型慢SQL案例与优化

案例1：全表扫描

-- 原始SQL
SELECT * FROM orders WHERE status = 1;  -- 假设status有0/1两种值，数据量大
-- 分析
EXPLAIN输出type=ALL，rows=100万
-- 优化
1. 选择性高的字段才适合建索引（status选择性50%，不建议）
2. 若必须查询，考虑分区表（按时间分区）或加其他条件

案例2：分页偏移

-- 原始SQL
SELECT id, name FROM users ORDER BY id LIMIT 100000, 20;
-- 优化：游标分页
SELECT id, name FROM users WHERE id > 100000 ORDER BY id LIMIT 20;
-- 或延迟关联
SELECT * FROM users 
INNER JOIN (SELECT id FROM users ORDER BY id LIMIT 100000, 20) AS tmp 
ON users.id = tmp.id;

案例3：GROUP BY + 排序

-- 原始SQL
SELECT user_id, COUNT(*) FROM orders 
WHERE create_time > '2024-01-01' 
GROUP BY user_id 
ORDER BY COUNT(*) DESC 
LIMIT 10;
-- 分析
EXPLAIN中出现Using temporary; Using filesort
-- 优化
1. 索引：(create_time, user_id) 覆盖WHERE和GROUP BY
2. 如果数据量大，先查大范围再分组会更慢，可考虑物化视图

日常巡检的黄金规则

指标	健康值	警告值	危险值
慢查询数量(每小时)	< 10	10-100	> 100
单条慢查询时间	< 1秒	1-5秒	> 5秒
全表扫描次数(每小时)	< 5	5-50	> 50
索引命中率	> 99%	95-99%	< 95%

生产环境注意事项

1. 谨慎开启慢查询：long_query_time 初始设为2秒，防止日志过大
2. 使用pt-query-digest自动分析：输出报告更易读
3. 建立慢查询监控看板：Grafana + MySQL Exporter 实时展示
4. 定期清理日志：慢查询日志可能迅速增长（设置 log_slow_admin_statements=OFF 避免记录管理命令）

总结一句话：先看EXPLAIN的type和rows，再查是否用了索引，最后优化查询逻辑，大部分情况下，加对索引能解决80%的慢查询问题。