Ubuntu PHP日志中的慢查询如何优化

Ubuntu PHP慢查询优化实操指南

一 明确慢查询来源与采集

• 区分两类慢：一类是PHP-FPM 请求慢（脚本执行时间长），另一类是数据库 SQL 慢（MySQL 执行时间长）。
• 开启 PHP-FPM 慢日志：编辑 /etc/php/7.x/fpm/pool.d/www.conf，设置阈值与日志路径，例如：
  • request_slowlog_timeout = 1s
  • slowlog = /var/log/php-fpm/www-slow.log
    重启服务：sudo systemctl restart php7.x-fpm。
• 开启 MySQL 慢查询日志：编辑 /etc/mysql/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf，例如：
  • slow_query_log = 1
  • slow_query_log_file = /var/log/mysql/slow.log
  • long_query_time = 1（单位秒）
  • log_queries_not_using_indexes = 1（可选，记录未走索引的查询）
    重启服务：sudo systemctl restart mysql。
• 分析工具：
  • MySQL 慢日志聚合分析：mysqldumpslow -s at -t 10 /var/log/mysql/slow.log
  • 更强大的分析：pt-query-digest /var/log/mysql/slow.log
• 定位具体 SQL 的执行计划：EXPLAIN SELECT …，关注 type、key、rows、Extra（避免 ALL、Using filesort、Using temporary）。

二 数据库层优化

• 索引优化：为 WHERE、JOIN、ORDER BY 涉及的高选择性列建立合适索引；避免全表扫描。
• SQL 重写：避免 **SELECT ***，减少返回列；优化 JOIN 与子查询；分页避免大 OFFSET（可记录上一页最大 ID 做“seek method”）。
• 执行计划治理：用 EXPLAIN 检查是否走索引、扫描行数是否过大，必要时改写 SQL 或调整索引。
• 缓存策略：引入 Redis/Memcached 做查询结果缓存，热点数据尽量命中缓存，降低数据库压力。
• 结构优化：合理范式与字段类型；大表可按业务做分区/分表；定期维护（如碎片整理/统计信息更新）。
• 配置优化：结合实例规格与负载，调整 InnoDB 缓冲池、连接数、临时表/排序区等参数（循序渐进，变更留痕）。

三 PHP与PHP-FPM层优化

• 启用并调优 OPcache（PHP 执行层加速）：
  • opcache.enable=1
  • opcache.memory_consumption=128
  • opcache.interned_strings_buffer=8
  • opcache.max_accelerated_files=4000
  • opcache.revalidate_freq=60
• 调整 PHP-FPM 进程模型与并发：
  • pm = dynamic
  • pm.max_children = 50（依据内存与单进程 RSS 估算）
  • pm.start_servers = 5；pm.min_spare_servers = 5；pm.max_spare_servers = 35
  • request_terminate_timeout = 30s（防止长时间阻塞）
  • pm.max_requests = 500（可选，防内存泄漏累积）
• 代码与数据访问优化：
  • 使用预处理语句（PDO/MySQLi）提升安全性与性能；
  • 避免在循环中查询数据库（合并为批量查询或一次性获取）；
  • 减少不必要函数调用与深层嵌套，优化业务逻辑；
  • 耗时任务（导出、统计、图片处理等）改为异步队列（如 Redis/RabbitMQ）。

四 Web服务器与监控告警

• Web 服务器与 FPM 对接：
  • Nginx 示例：fastcgi_pass unix:/run/php/php7.x-fpm.sock; include fastcgi_params;
  • Apache 示例：SetHandler “proxy:unix:/run/php/php7.x-fpm.sock|fcgi://localhost”
• 观测与排障：
  • 实时查看 FPM 慢日志：tail -f /var/log/php-fpm/www-slow.log
  • 查看 MySQL 慢日志：tail -f /var/log/mysql/slow.log
  • 使用 php-fpm status 页面与 top/htop 观察进程与资源；
  • 接入 APM/监控（如 New Relic、Datadog、Prometheus + Grafana）建立基线、设置告警、持续复盘。

五 快速排查清单与示例配置

• 排查清单（建议按顺序）：
  1. 开启并观察 PHP-FPM 慢日志与 MySQL 慢日志；
  2. 用 pt-query-digest/mysqldumpslow 找出 Top N 慢 SQL；
  3. 对 Top SQL 执行 EXPLAIN，优先解决“全表扫描/文件排序/临时表/扫描行数过大”的问题；
  4. 增加或改写索引，必要时重写 SQL；
  5. 引入 Redis/Memcached 缓存热点数据；
  6. 优化 PHP 代码，消除 N+1、循环查库；
  7. 调整 PHP-FPM（进程数、超时）与 OPcache；
  8. 回归验证（基准测试与线上 APM 指标），并持续监控。
• 示例最小可用配置（按需微调）：
  • PHP-FPM（/etc/php/7.x/fpm/pool.d/www.conf）：
    • pm = dynamic
    • pm.max_children = 50；pm.start_servers = 5；pm.min_spare_servers = 5；pm.max_spare_servers = 35
    • request_slowlog_timeout = 1s；slowlog = /var/log/php-fpm/www-slow.log
    • request_terminate_timeout = 30s
  • MySQL（/etc/mysql/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf）：
    • slow_query_log = 1；slow_query_log_file = /var/log/mysql/slow.log；long_query_time = 1
    • log_queries_not_using_indexes = 1
  • OPcache（/etc/php/7.x/cli/php.ini 或 fpm/php.ini）：
    • opcache.enable=1；opcache.memory_consumption=128；opcache.interned_strings_buffer=8
    • opcache.max_accelerated_files=4000；opcache.revalidate_freq=60
      每次变更后重启对应服务并观察错误与性能指标，逐步迭代到稳定状态。