MySQL 查询速度减慢直到重新启动

Question

我有一个基于 MySQL 5.5 数据库 (INNODB) 的服务。该服务有一个应该每周左右运行的后台作业。在高层次上，后台作业执行以下操作:

1. 在一个事务中进行一些初始数据库读写
2. 在一个事务中使用一组参数执行 UMQ(如下所述)。
   • 如果没有返回任何记录，我们就完成了!
3. 处理来自 UMQ 的结果(这有点繁重，所以它是在任何数据库之外完成的交易)
4. 在一个事务中将上一步的结果写入数据库(这写入由 UMQ 查询的表，并确保相同的记录不会被 UMQ 再次找到)。
5. 转到第 2 步。

UMQ - 丑陋的怪物查询:这是一个讨厌的数据库查询，它连接了一堆表，对其中几个表中的列有条件，并包括一个 NOT EXISTS 子查询和更多的连接和条件. UMQ 包括 ORDER BY 也有 LIMIT 1000。尽管查询很糟糕，但我已尽我所能 - 所有过滤的列都有索引，并且连接都是外键关系。

我确实希望 UMQ 很重并且需要一些时间，这就是它在后台作业中执行的原因。然而，我看到的是性能迅速下降，直到它最终导致我的服务超时(10 次迭代后可能慢 50 倍)。

起初我以为是因为 UMQ 查询的数据发生了变化(见上面的第 4 步)，但事实并非如此，因为如果我从慢查询日志中取出最后一个查询(导致超时的那个)并执行我自己直接得到了相同的行为，直到我重新声明了 MySQL 服务。重新启动后，对完全相同数据的精确查询在重新启动前花费了 >30 秒，现在花费了 <0.5 秒。我每次都可以通过将数据库恢复到初始状态并重新启动进程来重现此行为。

此外，使用此 question 中描述的技巧我可以看到查询在重新启动后扫描了大约 60K 行，而不是之前的 18M 行。 EXPLAIN 告诉我应该扫描大约 10K 行并且 EXPLAIN 的结果总是相同的。没有其他进程同时访问数据库，慢查询日志中的 lock_time 始终为 0。重启前后的 SHOW ENGINE INNODB STATUS 没有给我任何提示。

最后的问题是:有人知道我为什么会看到这种行为吗？我该如何进一步分析呢？

我觉得我需要以某种方式对 MySQL 进行不同的配置，但我疯狂地搜索和测试却没有找到任何有影响的东西。

Answer 1

事实证明，我看到的行为是 MySQL 优化器如何使用 InnoDB 统计信息来决定执行计划的结果。 This article让我走上正轨(即使它没有完全讨论我的问题)。我从中学到的最重要的事情是 MySQL 在启动时计算统计信息，然后偶尔计算一次。然后使用此统计信息来优化查询。

我在表 T 中设置测试数据的方式在第 4 步中完成了大部分写入，开始时是空的。每次迭代后，T 将包含越来越多的记录，但 InnoDB 统计数据尚未更新以反射(reflect)这一点。正因为如此，MySQL 优化器总是为 UMQ 选择一个执行计划(其中包括与 T 的 JOIN)，当 T 为空时，该计划运行良好，但记录越多越糟 < strong>T 包含。

为了验证这一点，我在每次执行 UMQ 之前添加了一个ANALYZE TABLE T;，并且快速降级消失了。没有闪电性能但可以接受。我还看到，将数据库保留半小时左右(可能短一点，但至少超过几分钟)将使 InnoDB 统计信息自动刷新。

在真实场景中，UMQ 中涉及的表的索引基数的相对差异看起来会完全不同，并且不会很快改变，所以我决定我真的不需要对此做任何事情。