MySQL 慢日志开启与 mysqldumpslow 分析封面配图

MySQL 慢日志开启与分析：mysqldumpslow 定位低效 SQL

作者

MySQL 慢日志开启与 mysqldumpslow 分析封面配图

在 Web 业务运维与系统管理中，数据库往往是决定整站响应速度与并发能力的关键瓶颈。当页面加载缓慢、接口超时或服务器 CPU 使用率居高不下时，盲目重启服务往往无法根治问题，排查的核心突破口在于精准定位拖垮性能的低效 SQL。本文将为你提供一份系统的实战指南，帮助你彻底解决数据库慢查询排查难题：从开启 MySQL 慢日志、科学权衡记录阈值，到利用官方工具 mysqldumpslow 聚合提炼核心慢语句，并结合执行计划进行深度优化，全面掌握定位与消除低效查询的完整流程。

开启慢日志：动态生效与 my.cnf 持久化配置

MySQL 慢查询日志（Slow Query Log）是定位性能短板最权威的内置机制，专门负责捕获执行时间超过阈值或未使用有效索引的语句。开启慢日志主要有两种方式：线上应急排查推荐通过全局变量动态调整，无需重启数据库服务；日常运维管理则应写入配置文件实现持久化。

1. 运行时动态配置（即时生效，无需重启）

在业务高峰期遇到突发性能告警时，直接重启数据库会中断业务连接并引发级联风暴。此时可通过具备管理员权限的账户登录 MySQL 终端，在线调整全局系统变量。

首先检查慢查询日志当前的运行状态与主要配置参数：

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

若结果显示 slow_query_log 为 OFF，可依次执行以下指令开启慢日志并指定日志存储路径：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 1.0;

需要特别注意的是，执行 SET GLOBAL long_query_time 仅对后续新建的数据库连接生效。当前终端若需立即测试慢查询记录，应同步执行 SET SESSION long_query_time = 1.0;。此外，动态参数仅驻留在内存中，一旦 MySQL 实例重启便会失效，因此完成线上排查后须同步修改配置文件。

2. 配置文件持久化配置（my.cnf）

为了保证数据库重启后慢日志依然有效，需要在 MySQL 配置文件（Linux 系统通常位于 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf）的 [mysqld] 配置段添加相应指令：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1.0
log_queries_not_using_indexes = 0

配置时须确认运行 MySQL 服务的系统账户对指定的日志目录具备充分的写入权限。若目录不存在或权限受限，服务在启动时可能因无法创建日志文件而报错。对于多地区运营的外贸业务或动态交互站点，数据库延迟会直接拉长首字节响应，若想系统排查整站瓶颈，可参考网站加载速度优化了解首字节时间（TTFB）的构成与优化路径。

MySQL 慢查询日志触发与记录流程概念图

阈值取舍：兼顾排查精度与磁盘 I/O 开销

慢日志的核心参数是 long_query_time，即判定为慢查询的时间分界线。MySQL 默认的 10 秒在现代高并发 Web 业务中显然过高，一条查询耗时超过 1 秒就足以造成应用线程堆积与连接池耗尽。

日常运维建议将阈值设定在 1.0 至 2.0 秒之间。MySQL 5.1 起已支持微秒级精度（例如 0.2 代表 200 毫秒），在压测演练或针对特定模块排查时可临时调小以捕捉隐蔽慢 SQL，排查结束后再行回调，避免磁盘写入过载。

另一个关键变量是 log_queries_not_using_indexes。开启后全表扫描语句无论耗时多短都会被记录。该选项适合在开发测试环境排查索引缺失，但在生产环境须格外谨慎：业务中若存在大量几十行记录的小配置表，优化器主动选择全表扫描也会被持续记录，导致慢日志瞬间刷屏并消耗大量 I/O。若确需开启，建议配合 log_throttle_queries_not_using_indexes 限制每分钟记录频次，并配置系统 logrotate 机制定期轮转与清理归档文件。

使用 mysqldumpslow 汇总与提炼 TOP 慢查询

随着系统运行，慢日志文件中往往记录了成千上万行文本。若直接使用 grep 或 cat 检索，相同的慢查询会重复出现数千次，极易淹没关键信息。MySQL 官方自带的 mysqldumpslow 工具能智能将 SQL 中的具体数字替换为 N、字符串替换为 'S'，把同构语句聚合归一，并统计执行次数与平均耗时。

mysqldumpslow 参数抽象与模式聚合概念图

熟练掌握该工具的核心参数，能针对不同场景快速定位高危 SQL：

  • -s（排序规则）：支持按总耗时 t、平均耗时 at、执行次数 c、总锁等待时间 l 等维度倒序排列。
  • -t N（输出条数）：仅展示排名前 N 条的慢查询模式，锁定系统核心短板。
  • -g pattern（正则过滤）：根据关键字或表名筛选特定模块的 SQL 语句。
  • -v（详细模式）：在终端打印解析进度与更详尽的处理信息。

在排查中，累计总耗时最高与执行频次最高的语句最值得优先处理。前者是消耗系统 CPU 和 I/O 算力的主要元凶，后者则持续占用数据库连接池：

mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
mysqldumpslow -s c -t 10 -g "wp_posts" /var/log/mysql/mysql-slow.log

工具输出的典型摘要如下所示：

Count: 420  Time=2.35s (987s)  Lock=0.02s (8s)  Rows=1.0 (420), root[root]@localhost
  SELECT * FROM wp_posts WHERE post_status = 'S' AND post_type = 'S' ORDER BY post_date DESC LIMIT N;

其中 Count: 420 代表该模式执行了 420 次；Time=2.35s (987s) 表示单次平均耗时 2.35 秒、累计耗时 987 秒；Lock 为单次与累计锁等待耗时；Rows 为单次平均与累计返回行数。带占位符的 SQL 结构一目了然，便于后续针对性重构。

慢查询治理：结合 EXPLAIN 展开针对性优化

定位到问题 SQL 仅是第一步，真正的治理必须依据执行计划展开，切忌凭主观推测随意增设索引。

慢 SQL 诊断与执行计划优化闭环架构图

将抽象 SQL 还原为具体业务参数后，在终端执行 EXPLAIN（或 MySQL 8.0+ 的 EXPLAIN ANALYZE）获取执行计划：

EXPLAIN SELECT * FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post' ORDER BY post_date DESC LIMIT 20;

重点核查以下指标：访问类型 type 是否为最差的全表扫描 ALL，理想应达到 ref、range 或 const；实际命中索引 key 是否为 NULL；预估扫描行数 rows 是否过大；以及 Extra 中是否出现了代价昂贵的 Using filesort（额外文件排序）或 Using temporary（建立临时表）。

针对执行计划暴露出的问题，通常从三个维度展开治理：

  • 索引优化：按照最左前缀原则建立复合索引，将区分度高的列前置，并将排序字段加入复合索引末尾以消除 Using filesort；尽量设计覆盖索引，避免回表查询带来的随机 I/O。
  • SQL 重构：避免滥用 SELECT *，仅获取必要字段；对大偏移量深分页改用延迟关联优化；避免在 WHERE 条件的索引字段上使用函数运算导致索引失效。
  • 缓存与架构防护：高频热点只读数据可引入 Redis 缓存削峰，防止慢查询直接击穿到底层。若是 CMS 类站点，可参考WordPress 运维指南了解对象缓存与数据库维护策略。

当 SQL 逻辑与索引已经优化完毕，但高并发业务依然面临性能瓶颈时，底层硬件的物理 I/O 与内存容量往往成为制约关键。中小型站点可选用配备高速 NVMe 存储与充沛资源的VPS云主机方案保障平稳吞吐；而对于海量交易或重度数据运算，则建议选择拥有独享计算与独立磁盘通道的美国独立服务器租用，从物理硬件层面消除多租户资源争抢，为数据库稳定运行奠定基石。

日常运维误区与排查注意事项

在慢日志的常态化维护中，运维人员需注意规避以下常见误区：

  • 锁等待干扰判断：慢日志记录的执行时间包含了锁等待时间。某些简单查询因长事务未提交而被锁堵塞，耗时超标入选慢日志，此时应结合 Lock 时间和 SHOW ENGINE INNODB STATUS 排查锁争用，而非盲目改写 SQL。
  • 忽视调试参数复原：线上临时调低 long_query_time 后若遗忘复原，可能导致日志飞速增长撑爆磁盘分区。关于服务器磁盘监控与空间预警机制，可参考服务器运维基础中的系统管理内容。
  • 漏检从库慢查询：在读写分离架构下，只读从库承担大量检索压力，主库没有慢查询不代表系统无恙，需对从库集群实施相同的慢日志监控与分析。

总结与行动建议

开启 MySQL 慢查询日志并运用 mysqldumpslow 聚合工具，能够将繁杂无序的底层日志转化为清晰直观的问题清单，再结合 EXPLAIN 深入执行计划开展索引调优与代码重构，是运维团队保障数据库高效运转的标准路径。

面对业务增长带来的性能挑战，建议运维与开发团队在生产环境中建立常态化排查机制：先通过动态全局参数平稳开启慢日志并设置 1 秒阈值，再每周定期使用 mysqldumpslow 提炼 TOP 5 慢查询纳入重构清单。如果你需要承载更高并发的动态业务或电商大促，单靠应用层调优可能仍会受制于底层 I/O 资源，此时可以考虑引入读写分离架构，或迁移至计算与磁盘通道更充足的高性能独立硬件环境，从软件执行逻辑与基础设施双重维度保障业务的长久顺畅运行。

另外，如果你的数据库瓶颈已经反映出共享资源的上限，Hostease 的独立服务器提供独享 CPU 与磁盘 IO，可以考虑作为读写分离架构的承载平台。

发表评论