4.2 慢查询⽇志

慢查询日志是 MySQL 中一项至关重要的诊断工具它专门记录执行时间超过指定阈值的 SQL 语句。通过分析这些“慢 SQL”你可以精准定位性能瓶颈从而进行索引优化、SQL 重写或架构调整。⚙️ 一、慢查询日志的核心配置要使用慢查询日志首先需要理解几个关键的系统变量。参数说明默认值 / 推荐设置slow_query_log开关设为ON启用OFF生产环境建议ONslow_query_log_file日志文件路径host_name-slow.log通常指定为/var/log/mysql/slow.loglong_query_time慢查询阈值秒执行时间超过此值的语句会被记录10(秒)根据业务调整OLTP 系统可设0.1~1log_queries_not_using_indexes是否记录未使用索引的查询即使执行很快OFF开启后可能产生大量日志谨慎使用min_examined_row_limit扫描行数超过此值的查询才会被记录0可设为1000等过滤掉扫极少行但偶发慢的查询log_slow_admin_statements是否记录 DDL 语句如ALTER TABLEOFF根据需要开启log_slow_replica_statements(8.0.26)从库是否记录复制过来的慢 SQL替换旧变量log_slow_slave_statementsOFFlog_slow_extra(MySQL 8.0.14)详细记录额外信息如线程ID、Rows_examined、Bytes_sent等OFF强烈建议开启如何修改配置SETGLOBALslow_query_logON;SETGLOBALlong_query_time0.5;同时需在配置文件my.cnf中设置保证重启后生效。 二、一条查询如何被判定为“慢查询”MySQL 处理慢查询的流程如下否是否是是否否是SQL 执行完毕日志开启?不记录执行时间 long_query_time?未使用索引且开启了 log_queries_not_using_indexes?标记为慢查询检查 min_examined_row_limit扫描行数 min_examined_row_limit?写入慢查询日志关键点执行时间指实际消耗的墙钟时间包括等待锁、I/O 等全部耗时。即使查询因为用了查询缓存QC而极快返回若 QC 命中不会计入慢查询QC 在 8.0 已移除。若有log_slow_extra日志中会额外显示Query_time与Lock_time的详细分解。 三、慢查询日志的内容详解日志文件以纯文本形式存储每条慢查询包含若干行。一个典型条目如下启用log_slow_extraON后信息更丰富# Time: 2025-06-15T14:12:31.123456Z # UserHost: app_user[app_user] db1 [10.0.0.5] # Thread_id: 45 Schema: ecommerce QC_hit: No # Query_time: 2.234567 Lock_time: 0.000123 Rows_sent: 20 Rows_examined: 50000 # Rows_affected: 0 Bytes_sent: 2048 SET timestamp1718460751; SELECT id, product_name, amount FROM orders WHERE user_id10086 ORDER BY create_time DESC LIMIT 20;各行含义字段说明# Time查询开始执行的时间UTC若未配置--log-timestamps则可能为系统时区# UserHost用户名、客户端主机# Thread_id执行该查询的线程 IDperformance_schema.threads可关联Schema使用的数据库QC_hit是否命中查询缓存8.0 已无Query_time总执行时间秒精度微秒Lock_time获得表锁的时间不是行锁InnoDB 下行锁等待不包含在这里Rows_sent返回给客户端的行数Rows_examined扫描的行数包括索引层扫描是评估索引效率的关键Rows_affected修改操作影响的行数Bytes_sent发送给客户端的字节数SET timestamp...语句执行时刻的 Unix 时间戳最后一行实际执行的 SQL 文本注意Lock_time仅包含获取元数据锁和存储引擎表锁的时间。InnoDB 的行锁等待反映在Query_time整体中但未单独列出这也是Query_time长而Lock_time短的一个常见原因。️ 四、日志存储方式文件 vs 表慢查询日志可同时写入文件或mysql.slow_log表。存储方式优点缺点文件 (File)性能开销极小适合长期记录方便用外部工具分析需登录服务器查看需手动轮转表 (Table)可实时 SQL 查询分析方便按条件过滤产生额外写入负载高并发下可能成为瓶颈表位于 CSV 引擎不支持索引除非转为 MyISAM/InnoDB启用表记录SETGLOBALlog_outputTABLE;-- 也可 FILE,TABLE然后查询mysql.slow_log即可。⚠️ 生产环境建议使用文件方式结合pt-query-digest等工具离线分析避免干扰数据库本身。 五、慢查询日志的分析工具1. mysqldumpslow官方内置用于简单聚合慢查询日志。# 显示出现次数最多的 5 条查询mysqldumpslow-sc-t5/var/log/mysql/slow.log# 显示平均执行时间最长的查询mysqldumpslow-sat-t10slow.log-s排序依据c次数t时间l锁时间r返回行at平均时间等。输出会将数字、字符串抽象为N、S便于归类。2. pt-query-digestPercona Toolkit功能强大的命令行分析工具能生成详细报表包括响应时间分布、最差查询、表/索引建议等。pt-query-digest /var/log/mysql/slow.logdigest_report.txt支持多种输入源慢日志、通用日志、tcpdump 抓包等。是 DBA 调优的必备神器。3. MySQL Workbench 与第三方监控MySQL Workbench 提供了图形化的慢查询分析功能。云平台如 RDS通常内置慢查询分析面板。4. 利用 sys 库和 Performance SchemaMySQL 8.0 的sys库提供了基于performance_schema的汇总视图可以实时发现近期慢查询而不必解析日志SELECT*FROMsys.statement_analysisWHEREavg_latency0.5ORDERBYavg_latencyDESCLIMIT10;SELECT*FROMsys.statements_with_runtimes_in_95th_percentile;这些视图给出了规范化的查询文本、平均延迟、行扫描等适合快速定位问题。 六、MySQL 8.0 中慢查询日志的增强log_slow_extra前面提到的详细字段无需额外配置即可获得Rows_examined,Rows_sent,Thread_id等信息极大提升了日志的可分析性。log_slow_replica_statements便于在从库上捕获复制的慢 SQL排查复制延迟问题。禁用日志时的性能关闭slow_query_log几乎无性能影响开启后每条 SQL 执行完会增加一次时间比对开销极小可以安全启用。与 Performance Schema 的整合PS 的events_statements_summary_by_digest等表可以看作内存版的慢查询聚合同时可通过sys.format_statement()函数格式化 SQL。 七、最佳实践与优化闭环合理设置阈值OLTP 系统设为 0.1~0.5 秒OLAP 或报表系统可适当放宽到 1~5 秒。定期轮转日志避免单个文件过大使用mysqladmin flush-logs或配合logrotate。分析而不是“看”用pt-query-digest生成报表优先优化执行次数多、总耗时高的慢查询。与 EXPLAIN 结合针对每条瓶颈 SQL 执行EXPLAIN检查索引使用、扫描类型、是否回表等。持续监控将慢查询数量纳入监控告警若突增应立即分析。不要过度依赖log_queries_not_using_indexes在全表扫描但表很小的情况下这些查询未必是问题滥用会淹没真正重要的慢查询。慢查询日志是性能优化的起点而非终点。从记录到分析再到定位具体 SQL 并优化它构成了一个完整的性能保障闭环。掌握它你就能在问题发生时第一时间找到证据而不是在黑暗中猜测。