如何迭代优化MySQL查询？

Question

我正在尝试迭代优化一个缓慢的MySQL查询，这意味着我运行查询，获取时间，微调它，重新运行查询，再次获取时间等等。问题在于时间是非稳态的，后续执行查询与之前执行查询的表现差异很大。

我知道可以在执行之间清除查询缓存或关闭它。我还知道，在某种程度上，操作系统会以MySQL无法控制或理解的方式影响查询性能。但总体来说，对于这种迭代查询优化，我能做的最好的事情是什么，以便我可以比较苹果和苹果？

- shanusmagnus

---

你尝试过使用 SELECT SQL_NO_CACHE ... 吗？通常这足以使查询运行时间在多次运行中保持稳定。 - Galz

我还没有，但我会的，然后我会回报。谢谢。 - shanusmagnus

好的，我试过了——没有效果。一旦在初始查询后性能提升，查询速度就会快得多。但是感谢你的提示，这对于其他情况也会很有用。 - shanusmagnus

Answer 1

你在查询优化方面最好的工具是 EXPLAIN。需要一些时间来学习输出的含义，但是学习之后，您将了解MySQL的（可怕、损坏、倒退）查询规划器如何决定最佳检索请求的数据。

查询参数的更改可能会导致完全不同的查询计划，因此这可能解释了您遇到的一些问题。

您可能希望考虑使用慢查询日志来捕获所有可能出现性能低下的查询。也许您会发现，在使用某些参数时，所涉及到的查询仅属于性能低下的类别？

- Charles

---

谢谢你提供关于EXPLAIN的提示，这将会是一个很大的帮助。我已经在仔细审查慢查询日志了，我认为也许这最终会是我需要更加努力的地方：建立一个缓慢查询的语料库，然后批量运行它们，希望那些进入慢查询日志的查询足够不同，以产生对缓存效应具有鲁棒性的性能，这一点我在我的评论中向squawknull解释过。 - shanusmagnus

@shanusmagnus，如果这只是磁盘块缓存（或缺乏）的问题，您应该检查MySQL配置，以确定它是否已经针对给定数据库大小在该硬件上进行了性能调整。Percona的MySQL性能博客可能会引起您的兴趣。 - Charles

Answer 2

创建一个脚本，运行查询1000次，或者任何使结果稳定的迭代次数。

然后按照上述描述的过程进行操作，但要确保不依赖于单个执行，而是多次执行的平均值，因为你是正确的，随着行数的变化和你的机器正在做其他事情，结果将不稳定。

此外，尽量使用各种各样的查询输入，如果这对您的用例有意义的话。

- squawknull

---

这是一般情况下的好建议，但对于我的特殊情况来说并没有太大帮助。在我的情况下，单个查询的执行时间从25秒缩短到700毫秒。显然，缓存正在起作用，并且似乎是相关磁盘块上的操作系统级缓存。相同类型的查询如果“足够不同”，则可以将执行时间提高到25秒左右，但自动创建足够不同以此方式激活系统的查询并不容易。 - shanusmagnus