mysql如何通过慢查询日志定位性能瓶颈_mysql性能诊断

mysql需手动开启慢查询日志，设置slow_query_log=on和long_query_time（支持微秒），配置后重启或动态启用，并通过show variables验证；日志中rows_examined、lock_time等字段比query_time更能定位瓶颈；推荐用pt-query-digest分析并结合explain聚焦type、key、rows、extra字段优化。

如何开启并确认慢查询日志已生效

MySQL 默认不启用慢查询日志，必须手动配置。关键在于两个参数：是否开启（slow_query_log）和阈值（long_query_time）。5.7+ 版本还支持微秒级设置，比如设为 0.1 可捕获 100ms 以上的查询，对高敏系统更实用。

• 修改 my.cnf 或 mysqld.cnf，在 [mysqld] 段下添加：

  slow_query_log = ON slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 1 log_queries_not_using_indexes = OFF  <!-- 慎开，可能日志爆炸 -->

• 配置后需重启 MySQL 或执行 SET GLOBAL slow_query_log = ON（临时生效）
• 验证是否生效：执行 SHOW VARIABLES LIKE 'slow_query_log' 和 SHOW VARIABLES LIKE 'long_query_time'
• 注意：如果 MySQL 以 mysql 用户运行，确保日志路径有写权限，否则日志静默失败，无报错

慢查询日志里哪些字段真正有用

默认格式（尤其是老版本）只记录 SQL 文本和耗时，但缺失上下文。启用 log_output = FILE + log_slow_extra = ON（8.0.14+）可补全关键信息：

• Rows_examined：扫描行数，比执行时间更能反映索引效率。值远大于 Rows_sent 通常意味着没走对索引或存在全表扫描
• Lock_time：锁等待时间高说明并发冲突严重，不是 SQL 本身慢，而是被其他事务堵住
• Query_time 和 Rows_sent 要结合看：如果 Query_time 高但 Rows_sent 很小，可能是大排序（Using filesort）或临时表（Using temporary）
• 日志中出现 # Time: 后跟时间戳，注意时区是否与业务日志一致，避免排查时错位

用 mysqldumpslow 或 pt-query-digest 快速聚合分析

原始慢日志是流水账，人工翻效率极低。优先用工具归类：

• mysqldumpslow 是 MySQL 自带轻量工具，适合快速摸底：
  • mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log：按总耗时排序，取 Top 10
  • mysqldumpslow -s c -t 5：按出现次数排，找高频低效语句（如未加 LIMIT 的分页查询）
• 生产环境建议用 pt-query-digest（Percona Toolkit），它能解析执行计划、识别指纹化 SQL、输出报告 HTML：
  • pt-query-digest /var/log/mysql/mysql-slow.log --report --limit 10%
  • 关键看 “Profile” 表里的 Rank、Query_time 和 Rows_examined 三列组合
• 注意：如果日志启用了 log_slow_verbosity = full（8.0.26+），pt-query-digest 能提取更多执行细节，但会增大日志体积

定位到慢 SQL 后，EXPLAIN 看什么

EXPLAIN 输出里真正影响性能的只有几个字段，别被一堆列干扰：

• type：从好到坏是 const ≈ eq_ref > ref > range > index > ALL。出现 ALL 基本等于全表扫描
• key 和 key_len：是否命中索引？key 为空说明没走索引；key_len 过小（如只用了联合索引前半部分）说明索引利用不充分
• rows：预估扫描行数，和慢日志里的 Rows_examined 对比。若相差极大（比如 EXPLAIN 说 100 行，实际扫了 10 万），说明统计信息过期，需 ANALYZE TABLE
• Extra 中警惕：Using filesort（内存/磁盘排序）、Using temporary（建临时表）、Using join buffer（块嵌套连接，小数据还行，大数据很伤）

慢查询日志只是入口，真正瓶颈常藏在索引设计不合理、统计信息陈旧、或应用层反复执行同一类低效查询里。拿到日志后别急着改 SQL，先确认它是偶发还是稳定复现，再结合 SHOW PROCESSLIST 和 information_schema.PROFILING（如启用）交叉验证。