精通 DevOps 工具与最佳实践 - DevOps 知识中心 精通 DevOps 工具与最佳实践 - DevOps 知识中心

MySQL慢查询故障排查：逐步指南

一个实用的工作流程，用于查找MySQL慢查询、阅读执行计划、修复索引，并验证更改是否有效。

MySQL慢查询故障排查：逐步指南

MySQL慢查询故障排查从一个不舒服的规则开始：不要仅凭查询文本猜测。一个看起来丑陋的查询可能无害，因为它每天只运行一次。一个看起来简单的查询可能正在破坏数据库，因为它每分钟运行数千次，扫描了太多行，或者在锁后面等待。

有用的工作流程以最好的方式显得枯燥。捕获真实的慢查询，按成本分组，检查执行计划，更改一件事，然后再次测量。这样可以防止你添加随机索引、盲目更改全局设置，或者在应用程序发送可避免的查询模式时责怪MySQL。

我通常从三个问题开始：

  • 哪个查询正在伤害用户，而不仅仅是看起来可疑？
  • 时间花费在读取行、排序、等待锁还是等待应用程序上？
  • 我能否用EXPLAIN、计时和新的慢日志数据证明修复有效？

从慢查询日志开始

MySQL慢查询日志记录超过配置阈值的语句。根据MySQL手册，日志默认禁用，long_query_time默认为10秒，语句通常需要至少运行那么长时间并检查至少min_examined_row_limit行才会被记录。如果启用了log_queries_not_using_indexes，MySQL还可以记录未使用索引进行行查找的语句。该选项在诊断期间很有用，但在繁忙的系统上可能会产生大量噪音。

一个实用的起始配置如下所示：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_output = FILE

在许多生产系统上，1秒是一个合理的首次尝试。对于延迟敏感的API，你可以临时将其降低到0.5或0.2。这样做时要有计划并监控磁盘空间。一旦阈值降低，高流量数据库可能会写入大量慢日志数据。

你可以从MySQL会话中检查活动设置：

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';
SHOW VARIABLES LIKE 'min_examined_row_limit';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

对于临时调查，你可以在不编辑配置文件的情况下启用日志：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

请记住，SET GLOBAL更改在重启后可能不会保留，除非你通过正常的配置过程持久化它们。在MySQL 8上，一些团队使用SET PERSIST，但我仍然倾向于将预期的设置提交到配置管理中，以便下一个操作员可以看到。

如果你打开log_queries_not_using_indexes，考虑同时设置log_throttle_queries_not_using_indexes，这样单个嘈杂的端点不会淹没日志。MySQL支持这种节流，正是因为无索引日志可能会快速增长。

在阅读单个查询之前对日志进行分组

原始慢日志是重复的。你可能会看到相同的查询出现数百次，只有不同的ID。从头到尾阅读文件会浪费时间，并使罕见的可怕查询看起来比常见的昂贵查询更重要。

从mysqldumpslow开始，它在许多环境中随MySQL安装提供：

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

这会要求按查询时间排序的前十个模式。确切的标志因版本和平台而异，所以如果你的命令行为不同，请检查mysqldumpslow --help。有用的排序包括总时间、平均时间、锁定时间和检查的行数。

对于生产调查，Percona Toolkit中的pt-query-digest通常更好，因为它提供更丰富的分组和百分位级别的细节。该工具并不神奇；它只是节省了你手动计算的工作。重要的是按影响排序。一个每晚运行一次、耗时八秒的查询可能不如一个耗时120毫秒但每秒运行600次的查询紧急。

在阅读分组输出时，寻找模式：

  • 高总时间：可能对用户可见或资源密集。
  • 高计数：通常是应用程序循环或缺少缓存。
  • 高检查行数与低发送行数：通常是索引或过滤问题。
  • 高锁定时间：可能是事务、写争用、元数据锁或DDL问题。

不要假设高Rows_examined总是坏事。报告查询和批处理作业有时会故意扫描。问题是扫描是否与作业匹配，以及是否在正确的时间发生。

安全地重现一个查询

选择一个查询模式，并获取一个带有参数的真实样本。如果慢日志规范化了字面量，请在应用程序日志、APM跟踪或原始慢日志条目中找到原始查询。

在手动运行之前，检查影响范围。在副本上执行慢速SELECT通常是安全的。在生产中执行慢速UPDATE不是可以随意重新运行的事情。对于写查询，首先检查计划和事务模式，或者针对具有真实数据的暂存副本进行测试。

每个查询的有用笔记如下所示：

端点：GET /customers/123/orders
查询模式：按客户和状态排序的订单，最新的在前
观察结果：平均1.8秒，检查了420k行，发送了20行
表大小：1200万行
预期结果大小：一页订单
怀疑：缺少customer_id、status、created_at的复合索引

该笔记将工作与实际用户路径联系起来，而不是随机的SQL片段。

使用EXPLAIN，然后像操作员一样阅读它

在慢查询上运行EXPLAIN：

EXPLAIN
SELECT id, customer_id, status, created_at, total
FROM orders
WHERE customer_id = 123
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

对于MySQL 8，EXPLAIN ANALYZE可以执行查询并显示实际计时信息。在昂贵的查询上小心使用它，因为它确实会运行语句。对于受控环境中的普通SELECT查询，它可能非常有用。

我首先检查的列是type、possible_keys、key、rows、filtered和Extra。

type告诉你访问模式。const、eq_ref、ref和range通常是好迹象。index意味着MySQL正在扫描索引，这可能仍然是太多工作。ALL意味着全表扫描。在小表上全扫描并不自动错误，但在有数百万行的热表上则值得怀疑。

key显示MySQL选择的索引。如果possible_keys列出了有希望的索引但key不同，优化器可能认为另一个索引更便宜。这可能是因为选择性差、统计信息过时，或者索引不匹配过滤和排序。

rows是一个估计值，不是承诺。如果估计值严重错误，请在适当的维护窗口运行ANALYZE TABLE，或检查数据分布是否倾斜。

Extra通常能说明问题。Using filesort意味着MySQL需要一个单独的排序步骤；它不一定意味着磁盘排序，但在结果集很大时值得检查。Using temporary通常出现在分组、去重查询或复杂排序中。Using index可能是好的，因为查询仅从索引中满足，无需读取表行。

根据完整的查询形状修复索引

最常见的慢查询修复不是“向WHERE子句中的列添加索引”。更好的规则是：构建一个与查询过滤、连接、排序和限制行的方式匹配的索引。

对于上面的订单查询，customer_id上的单列索引可能有帮助，但它可能仍然让MySQL对该客户进行大量排序。复合索引通常更有用：

CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at);

如果查询按最新的在前排序，MySQL通常可以反向扫描索引。在MySQL 8中，你还可以在适合更大模式时定义降序索引：

CREATE INDEX idx_orders_customer_status_created_desc
ON orders (customer_id, status, created_at DESC);

列顺序很重要。首先放置等值过滤，然后是与查询匹配的范围或排序列。例如，对于WHERE customer_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20，customer_id, status, created_at通常比created_at, customer_id, status更有用。

不要添加每个看似有用的索引。索引加速读取但减慢写入并消耗存储。如果表接收大量插入或更新，新的复合索引有实际成本。首先检查现有索引：

SHOW INDEX FROM orders;

有时正确的答案是用一个更好的复合索引替换两个弱索引，而不是保留所有三个。

重写阻止索引使用的查询

一些慢查询之所以慢，是因为它们将索引值隐藏在函数或MySQL无法高效使用的模式后面。

这个版本很常见且令人痛苦：

SELECT *
FROM orders
WHERE YEAR(created_at) = 2026;

如果created_at被索引，将其包装在YEAR()中可能会阻止正常的范围查找。将谓词写为范围：

SELECT id, customer_id, status, created_at, total
FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01';

同样的想法适用于前导通配符搜索：

WHERE email LIKE '%@example.com'

普通的B树索引无法跳转到字符串的中间。如果后缀搜索很重要，你可能需要一个生成列、单独的规范化字段，或为此用例设计的搜索系统。

还要注意SELECT *。它在开发时看起来无害，但它可能迫使MySQL读取表行，而较小的投影可以使用覆盖索引。它还通过网络发送不必要的数据。

当查询计划看起来正常时检查锁

一个查询可能有不错的计划但仍然很慢，因为它正在等待。慢日志中的Lock_time可以指向那个方向，但它不能解释每种等待。如果用户报告随机暂停，请检查活动会话：

SHOW PROCESSLIST;

在MySQL 8上，Performance Schema和sys模式视图可以提供更好的细节，具体取决于服务器的配置方式。为了快速查看，我经常在更改索引之前检查长时间运行的事务和阻塞的语句。

一个真实的例子：UPDATE orders SET status = ? WHERE id = ?查询应该很快。如果它出现在慢日志中并且使用了主键查找，问题可能是一个事务在行上留下了锁，同时在做无关的工作。修复方法不是另一个索引。修复方法是缩短事务并将慢速外部调用移到事务外部。

元数据锁可以创建类似的陷阱。运行ALTER TABLE的迁移可能等待旧事务，而新查询堆积在待处理的DDL后面。慢查询日志会显示症状，但根本原因是部署行为。

仅在查询工作之后调整服务器设置

配置很重要，但很容易被过度使用作为第一答案。如果一个查询扫描五百万行返回十行，增加内存可能只会让糟糕的计划不那么痛苦。

对于InnoDB密集型系统，innodb_buffer_pool_size是第一个要检查的设置。在专用MySQL服务器上，它通常设置为内存的很大一部分，但正确的值取决于主机上运行的其他内容、数据集大小和工作负载。不要盲目地从博客文章中复制百分比。

还要检查数据库是否在等待磁盘。如果工作集不适合内存，或者存储饱和，即使索引良好的查询也可能停滞。将查询审查与主机指标配对：CPU、磁盘延迟、IOPS、内存压力和连接数。

连接池可以使慢查询看起来更糟。如果一个端点触发太多慢语句，池子填满，无关的请求等待连接，整个应用程序感觉崩溃。在这种情况下，修复查询仍然是主要工作，但池限制和超时决定了系统优雅失败的程度。

证明修复有效

在添加索引或重写查询后，再次运行EXPLAIN。你希望看到更少的估计行数、更好的选择键和更少的昂贵额外步骤。然后使用实际参数测试实际查询。

不要止步于一次快速运行。热缓存可以隐藏问题。尝试常见、大型和尴尬的情况：

  • 一个有很多订单的客户。
  • 一个没有匹配订单的客户。
  • 跨越繁忙时期的日期范围。
  • 匹配大多数行的状态值。

然后在部署后观察慢日志。最好的结果不是“查询在暂存环境中看起来更好”。最好的结果是查询模式从顶级违规者中消失，CPU或I/O压力下降，用户路径更快。

MySQL慢查询故障排查主要是纪律性的证据收集。使用合理的阈值启用日志，对昂贵的模式进行分组，检查计划，修复查询形状，并使用新数据进行验证。这种习惯可以防止修复不足和过度修复，这正是数据库已经处于压力下时你想要的。