MySQL查询慢下来直到重新启动

Question

我有一个位于MySQL 5.5数据库(INNODB)之上的服务。该服务有一个背景作业，应该每周运行一次。在较高级别上，后台工作完成以下工作：

1. 在一个事务中执行一些初始DB读写操作
2. 在一个事务中使用一组参数执行UMQ (如下所述)。
   • 如果没有返回记录，我们就完成了！

​

1. 处理来自UMQ的结果(这有点重，所以它是在任何DB事务之外完成的)
2. 在一个事务中将上一步的结果写入DB (这将写入由UMQ查询的表，并确保UMQ不会再次找到相同的记录)。
3. 去第二步。

UMQ - Ugly Monster查询:这是一个令人讨厌的数据库查询，它连接了一组表，在其中几个表中有列的条件，并包含一个NOT子查询，还有一些联接和条件。UMQ包括ORDER BY也有限制1000。尽管查询很糟糕，但我已经在这里做了我能做的事情--所有列上都有索引，所有的连接都包含在外键关系中。

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

首先，我认为这是因为UMQ查询的数据发生了变化(参见上面的步骤4)，但这并不是因为如果我从慢速查询日志中获取最后一个查询(导致超时的查询)并亲自直接执行它，我只会得到相同的行为，直到我重新声明了MySQL服务。在重新启动完全相同的数据(在重新启动之前花费了>30秒)上的准确查询之后，现在重新启动所花费的时间小于0.5秒。我每次都可以通过将数据库还原到它的初始状态并重新启动进程来再现这种行为。

此外，使用这个问题中描述的技巧，我可以看到，在重新启动之后，查询扫描了大约60K行，而不是之前的18M行。解释告诉我，大约10K行应该被扫描，解释的结果总是一样的。没有其他进程同时访问数据库，慢速查询日志中的lock_time始终为0。显示引擎INNODB状态在重新启动之前和之后给我没有任何提示。

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

我有一种感觉，我需要在某种程度上对MySQL进行不同的配置，但我已经疯狂地搜索和测试过了，而没有想出任何有意义的东西。

Answer 1

结果表明，我看到的行为是MySQL优化器如何使用InnoDB统计信息来决定执行计划的结果。这篇文章使我走上了正确的道路(尽管它没有确切地讨论我的问题)。我从中学到的最重要的一件事是，MySQL在启动时计算统计数据，然后偶尔计算。然后使用这些统计数据来优化查询。

我设置测试数据的方式--表T，其中大多数写都是在步骤4中完成的--一开始是空的。每次迭代之后，T将包含越来越多的记录，但InnoDB统计数据尚未更新以反映这一点。正因为如此，MySQL优化器总是为UMQ选择一个执行计划(其中包括与T的连接)，当T为空时，该计划运行良好，但记录T包含的记录越多，它就越糟糕。

为了验证这一点，我添加了一个分析表T；在每次执行UMQ之前，快速降级就消失了。没有雷电性能，但可以接受。我还看到，离开数据库大约半个小时(可能更短一些，但至少超过几分钟)将允许InnoDB统计信息自动刷新。

在实际场景中，UMQ中涉及的表的索引基数的相对差异看起来会非常不同，并且不会改变得那么快，所以我决定不需要对它做任何事情。

Answer 2

非常感谢您的分析和回答。在MariaDB10.1和bacula服务器9.4 (debian )上的ci中，我已经搜索这个问题好几天了。

情况是，在CI循环期间安装了新的服务器之后，前两个测试(备份和恢复)在未重新启动的mariadb服务器上顺利运行，而只有第三个测试显示，一个特定的UMQ花费了大约20分钟(在还原过程中从表中构建目录树的时间大约为30k行)。

除非重新启动mardiadb服务器或分析表，否则问题不会消失。ANALYZE TABLE或重新启动更改了字段和内部查询处理的基数，与链接文章中所述完全相同。