PHP项目慢查询SQL如何定期分析优化

本文目录导读：

1. 基础监控：开启慢查询日志
2. 定期收集与聚合（每周/每日）
3. 人工分析（关键步骤）
4. 优化方案落地
5. 自动化定期执行
6. 优化效果验证（闭环）
7. 补充：PHP层面的常见坑
8. 建设制度化流程

针对PHP项目的慢查询SQL定期分析与优化，可以按以下步骤建立一套完整的监控-分析-优化-复检闭环流程：

---

基础监控：开启慢查询日志

这是最根本的数据来源,需要在MySQL中开启慢查询日志。

-- 查看当前状态
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
-- 开启慢查询（生产环境建议动态开启，不要重启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 1; -- 超过1秒的记录，单位秒
SET GLOBAL log_queries_not_using_indexes = ON; -- 记录未用索引的查询

注意事项：

• long_query_time 设为 1 秒或 5 秒，不要设为 0（全量记录会打爆磁盘）。
• 生产环境建议使用 PT-Query-Digest 或 mysqlsla 等工具进行日志聚合,避免手动阅读大量日志。

---

定期收集与聚合（每周/每日）

推荐工具：pt-query-digest（Percona Toolkit）

# 分析昨天的慢查询日志
pt-query-digest /var/log/mysql/mysql-slow.log > /tmp/slow_report_$(date +%Y%m%d).txt
# 或直接分析最近1小时的日志
pt-query-digest --since=1h /var/log/mysql/mysql-slow.log

报告会提供：

• 总执行时间、总次数、平均时间
• 最慢的N条SQL（按总时间倒序）
• 每一条SQL的执行频率、响应时间分布、锁时间、返回行数

---

人工分析（关键步骤）

拿到报告后，重点看 Rank 1-10 的SQL,从以下5个维度分析：

索引问题（占80%以上慢查询）

• 全表扫描：EXPLAIN 看 type = ALL
• 没用到索引：possible_keys 不为NULL但 key 为NULL
• 索引选择性差：rows 扫描行数很大，但 Extra 显示 Using index condition 可能是索引不够精准

分析命令：

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

数据量问题

• 单表数据量 > 500万行且全表扫描
• 分页查询：LIMIT 100000,20 这种深度分页

锁与死锁

• EXPLAIN 的 Extra 出现 Using temporary; Using filesort 表示需要优化排序
• 观察慢查询日志中的 Lock_time 是否过高（排除锁等待）

SQL写法问题

• 隐式类型转换：WHERE phone = 123456（phone是varchar）
• 函数导致索引失效：WHERE DATE(create_time) = '2024-01-01' 应改为 WHERE create_time >= ... AND create_time < ...
• **SELECT ***：经常只需要3个字段却全字段查询

业务逻辑问题

• N+1查询：循环中查数据库，常见于ORM（如Laravel/ThinkPHP的延迟加载）
• 非必要的大查询：后台报表需要跑5分钟,是否可以改成异步或缓存？

---

优化方案落地

针对分析结果,按优先级实施：

问题类型	解决方案
缺少索引	添加复合索引，注意字段顺序（等值条件放前面，范围条件放后面）
索引失效	改写SQL（避免函数、隐式转换、LIKE左模糊等）
深度分页	使用游标分页（WHERE id > last_id LIMIT 20）或覆盖索引
数据量过大	分库分表（按时间/用户ID拆分）、归档历史数据
锁竞争	缩小事务范围、调整隔离级别、使用乐观锁
查询频率高	加Redis缓存（缓存热点数据，注意缓存穿透/雪崩）

具体例子：

-- 原慢SQL（status、create_time各自独立索引）
SELECT * FROM orders WHERE status = 1 AND create_time > '2024-06-01' ORDER BY id DESC LIMIT 100;
-- 优化后：添加复合索引 (status, create_time, id)，且去掉不必要的SELECT *
SELECT id, order_no, amount FROM orders WHERE status = 1 AND create_time > '2024-06-01' ORDER BY id DESC LIMIT 100;

---

自动化定期执行

方案1：Crontab + Shell脚本（每周一凌晨执行）

# /usr/local/bin/slow_query_analyze.sh
#!/bin/bash
LOGFILE="/var/log/mysql/mysql-slow.log"
REPORT_DIR="/data/slow_report"
DATE=$(date +%Y%m%d)
# 1. 生成报告
pt-query-digest $LOGFILE > $REPORT_DIR/report_$DATE.txt
# 2. 每日清理旧日志（重要！防止日志无限增长）
mv $LOGFILE $LOGFILE.$DATE
mysqladmin flush-logs
# 3. 保留最近30天报告
find $REPORT_DIR -name "*.txt" -mtime +30 -delete

# crontab -e
0 3 * * 1 /bin/bash /usr/local/bin/slow_query_analyze.sh

方案2：接入APM工具（更适合大团队）

• 阿里云RDS慢查询分析（自带）
• Datadog / NewRelic：自动抓取慢查询，甚至给出优化建议
• Yearning / Archery：开源的SQL审核平台，可对接慢查询日志并生成工单

---

优化效果验证（闭环）

每次优化后必须验证：

1. 再次执行 EXPLAIN 确认 type 从 ALL 变成 ref 或 range，rows 大幅下降
2. 上线后观察该SQL的平均响应时间（建议用SkyWalking或Xhprof）
3. 下一周的慢查询报告中，该SQL是否跌出Rank前10
4. 压力测试：用JMeter/Siege模拟高并发，观察QPS是否提升

---

补充：PHP层面的常见坑

1. ORM框架不当使用

   • Laravel：Model::all()->chunk() → 应改为 chunkById() 或原生SQL
   • ThinkPHP：db('user')->where(...)->select() 在数据量 > 5000条时务必加 limit

2. 长连接/连接池：PHP-FPM短连接天生慢，建议使用 Swoole 或连接池（如 Hyperf）

3. 索引未生效：PHP代码中拼SQL时，参数类型要与数据库字段类型一致（$userId 是int,SQL中不要加引号）

4. 未关闭查询：特别是 PDOStatement::closeCursor() 未调用，可能导致锁长时间未释放

---

建设制度化流程

频率	动作
每日	查看慢查询日志中是否有新增的极端慢SQL（>10秒）
每周	pt-query-digest 生成周报，开发组Review Top10
每次上线	对新增SQL进行 EXPLAIN 强制审核
每月	清理历史慢查询日志，复盘未优化的瓶颈

这套体系不需要昂贵的软件，用好MySQL自带工具 + PT工具 + 定期人工审核，就能覆盖90%的慢查询问题，如果团队人力充足，建议引入SQL审核平台（如Yearning）实现流程自动化。