mysql中使用日志文件分析数据库性能瓶颈

要确认 slow_query_log 是否真正生效，需同时检查 slow_query_log=ON、long_query_time 设置合理、log_output 目标可写（文件路径权限正确或 slow_log 表存在且可插入），并用超时 sql 实测。

怎么看 slow_query_log 是否真正生效

mysql 的慢查询日志不是开了就一定记录，常见误区是只改了 slow_query_log = ON 就以为万事大吉。实际还必须同时设置 long_query_time（默认 10 秒，对线上基本无效）和 log_output（决定日志写到文件还是表）。如果 log_output = table 但没查 mysql.slow_log 表，或者日志路径权限不对导致写入失败，都会“看起来没日志”。

实操建议：

• 用 SHOW varIABLES LIKE 'slow_query_log%'; 和 SHOW VARIABLES LIKE 'long_query_time'; 确认运行时值，不是只看配置文件
• 执行一条明确超时的语句测试，例如 select SLEEP(2);（前提是 long_query_time ）
• 检查日志文件路径权限：ls -l /var/lib/mysql/your_host-slow.log，确保 mysqld 进程用户（如 mysql）有写权限
• 若用 log_output = FILE，注意 slow_query_log_file 路径必须是绝对路径，且不能被 SElinux 或 appArmor 拦截

用 mysqldumpslow 快速定位高频慢 SQL

mysqldumpslow 是 MySQL 自带的轻量分析工具，比手动 grep 或写脚本更可靠，尤其擅长合并相似 SQL（忽略参数、空格、大小写差异）并按次数或时间排序。

实操建议：

• 统计最耗时的 5 条：

  mysqldumpslow -s t -t 5 /var/lib/mysql/your_host-slow.log

• 统计出现次数最多的 5 条（可能暴露 N+1 查询）：

  mysqldumpslow -s c -t 5 /var/lib/mysql/your_host-slow.log

• 加 -g 过滤关键词，比如只看含 JOIN 的慢查询：

  mysqldumpslow -g "JOIN" /var/lib/mysql/your_host-slow.log

• 注意：它默认把数字常量替换成 N、字符串替换成 S，所以 WHERE id = 123 和 WHERE id = 456 会被归为同一条，这是优点也是盲点——真实参数分布需结合原始日志看

解析日志时别漏掉 “Rows_examined” 和 “Rows_sent”

慢查询日志里每条记录都包含 # Rows_examined: 123456 和 # Rows_sent: 10，这两个值比执行时间更能说明问题。高 Rows_examined + 低 Rows_sent 是典型的“扫描多、返回少”，大概率缺索引或索引未命中；而 Rows_examined ≈ Rows_sent 通常说明查询本身合理，瓶颈可能在锁、磁盘 I/O 或网络。

实操建议：

• 用 awk 快速筛查扫描行数过万的语句：

  awk '/Rows_examined:/ {if ($3 > 10000) {getline; print}}' /var/lib/mysql/your_host-slow.log

• 对比 Rows_examined 和执行计划中的 rows 字段：如果相差极大（比如日志写 50 万，EXPLaiN 显示 500），说明统计信息过期，需 ANALYZE TABLE
• 注意：开启 log_queries_not_using_indexes 会把所有未走索引的查询都记进慢日志，即使 long_query_time 没超，容易淹没真正慢的语句

生产环境启用慢日志的关键取舍

慢日志本身有开销，尤其高并发场景下频繁写磁盘可能拖慢性能。MySQL 8.0 后支持将慢日志写入 csv 表（log_output = TABLE），但表默认是 CSV 引擎，不支持索引，查起来反而更慢。

实操建议：

• 优先用 log_output = FILE，配合 log_rotation（如 logrotate）避免单文件过大
• 不要长期开启 log_queries_not_using_indexes，只在专项排查时临时打开
• 若用 Percona Server 或 MySQL 8.0.14+，可考虑 performance_schema.events_statements_history_long 替代部分慢日志功能，内存中采集，无 I/O 开销
• 日志文件路径务必避开系统盘或高 IO 争抢的磁盘，最好单独挂载 SSD

真正卡住人的往往不是怎么开日志，而是日志里那条 Rows_examined: 2893421 却只返回 1 行的 SELECT —— 它背后可能是缺失的复合索引、失效的分区裁剪，或者一个被忽略的隐式类型转换。盯住 Rows_examined，比盯着 Query_time 更接近真相。