如何使用EXPLAIN来*预测*MySQL查询的性能？

Question

我正在帮助维护一个程序，它基本上是一个友好的只读前端，用于一个庞大而复杂的MySQL数据库。该程序根据用户的输入构建即席SELECT查询，将查询发送到数据库，获取结果，对其进行后处理，并将其漂亮地显示给用户。

我想为构建的查询的预期性能添加某种合理/启发式的预测 - 有时用户会无意中创建一些不可避免需要很长时间才能返回结果的查询（因为它们将返回巨大的结果集，或者因为它们“违背”了数据库索引的规则），我想能够向用户显示一些有关查询可能需要多长时间的“相当可靠”的信息/猜测。 它不必完美，只要它不会频繁大幅偏离实际情况，导致用户学会忽略它;-) 根据这些信息，用户可以决定去喝咖啡（如果估计时间为5-10分钟），去吃午饭（如果是30-60分钟），取消查询并尝试其他方法（也许是更严格的信息请求限制）等等。

我不太熟悉MySQL的EXPLAIN语句 - 我看到很多关于如何使用它来优化查询或数据库的模式，索引等的信息，但是对于我的更有限的目的，如何使用它并没有太多信息 - 仅作出预测，假设数据库已经存在（当然，如果预测足够可靠，我最终可能会切换到使用它们来选择查询可以采取的不同形式，但是，这是将来的事情：现在，我很高兴向用户显示性能猜测）。

有什么提示吗...？

- Alex Martelli

Answer 1

EXPLAIN无法提供关于查询所需时间的任何指示。

最好的情况下，您可以用它来猜测哪个查询可能更快，但除非其中一个明显写得很差，否则即使这样也会非常困难。

您还应该注意，如果使用子查询，即使运行EXPLAIN也可能很慢（在某些情况下几乎与查询本身一样慢）。

据我所知，MySQL没有提供任何估计查询执行时间的方法。 您可以记录每个查询运行的时间，然后根据类似查询的历史构建估算。

- Greg

---

目前我们并不生成子查询，所以这个问题不应该是个问题。但还是谢谢你的指引，以及告诉我估算查询成本没有好的方法（虽然这是一个坏消息，但总比在追逐幻象中花费无限时间之前得知要好！）。 - Alex Martelli

EXPLAIN非常有帮助。我不确定为什么这是"答案"。请检查基数-行数越多，搜索的工作量就越大。此外，它显示正在使用哪个索引（如果有）。这对于SELECT的性能至关重要。至于子查询，它们实际上很少需要使用-为了清晰起见，应尽可能将其重构出来。 - Adam Nelson

Answer 2

我认为，如果你想要用这个来建立一些相对可靠的东西，你应该根据表大小和分解的EXPLAIN结果组件来构建一个统计模型，该模型与查询处理时间相关。试图基于思考EXPLAIN内容来构建查询执行时间预测器，只会花费太长时间并得到令人尴尬的差结果，直到它被改进为模糊有用性。

- chaos

Answer 3

MySQL EXPLAIN有一个名为“Key”的列。如果这一列中有内容，那么这是一个非常好的指示，这意味着查询将使用索引。

通常情况下，使用索引的查询是安全的，因为它们很可能是数据库设计者在设计数据库时考虑到的。

然而，还有另一个字段叫做“Extra”。这个字段有时包含文本“using_filesort”。

这是非常糟糕的。这意味着MySQL知道查询结果集大于可用内存，因此将开始将数据交换到磁盘以进行排序。

结论

与其尝试预测查询所需的时间，不如查看这两个指标。如果查询正在使用“using_filesort”，则拒绝用户。根据您想要的严格程度，如果查询没有使用任何键，您也应该拒绝它。

了解更多关于MySQL EXPLAIN语句结果集的信息，请阅读此处。

- firelynx