如何使用EXPLAIN来“预测" MySQL查询的性能? [英] How do I use EXPLAIN to *predict* performance of a MySQL query?

问题描述

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

I'm helping maintain a program that's essentially a friendly read-only front-end for a big and complicated MySQL database -- the program builds ad-hoc SELECT queries from users' input, sends the queries to the DB, gets the results, post-processes them, and displays them nicely back to the user.

我想为构造的查询的预期性能添加某种形式的合理/启发式预测-有时用户无意中进行的查询不可避免地会花费很长时间(因为它们将返回巨大的结果集，或者因为它们违背了对数据库建立索引的方式"，我希望能够向用户显示一些相当可靠"的信息/猜测，询问查询将花费多长时间.只要它不会变得如此糟糕和频繁地脱离现实而在用户学会忽略它的情况下引起哭狼"效应，它就不必是完美的；-)根据此信息，用户可能会决定去喝咖啡(如果估计是5-10分钟)，去吃午餐(如果是30-60分钟)，取消查询并尝试其他操作(也许是他们要求的信息限制更严格) )等

I'd like to add some form of reasonable/heuristic prediction for the constructed query's expected performance -- sometimes users inadvertently make queries that are inevitably going to take a very long time (because they'll return huge result sets, or because they're "going against the grain" of the way the DB is indexed) and I'd like to be able to display to the user some "somewhat reliable" information/guess about how long the query is going to take. It doesn't have to be perfect, as long as it doesn't get so badly and frequently out of whack with reality as to cause a "cry wolf" effect where users learn to disregard it;-) Based on this info, a user might decide to go get a coffee (if the estimate is 5-10 minutes), go for lunch (if it's 30-60 minutes), kill the query and try something else instead (maybe tighter limits on the info they're requesting), etc, etc.

我对MySQL的EXPLAIN语句不是很熟悉-我看到了很多有关如何使用它来优化查询或数据库的架构，索引等的信息，但并没有很多关于如何将其用于我的更有限的目的-只需做一个预测，将数据库作为给定值(当然，如果预测足够可靠，我可能最终会改用它们来在查询可能采用的其他形式之间进行选择，但是，那是为了将来:就目前而言，我很高兴能向用户展示性能估算值，以达到上述目的.

I'm not very familiar with MySQL's EXPLAIN statement -- I see a lot of information around on how to use it to optimize a query or a DB's schema, indexing, etc, but not much on how to use it for my more limited purpose -- simply make a prediction, taking the DB as a given (of course if the predictions are reliable enough I may eventually switch to using them also to choose between alternate forms a query could take, but, that's for the future: for now, I'd be plenty happy just to show the performance guesstimates to the users for the above-mentioned purposes).

任何指针...?

推荐答案

EXPLAIN不会给您任何查询将花费多长时间的指示. 充其量您可以使用它来猜测两个查询中的哪个查询可能会更快，但是除非其中一个查询显然写得不好，否则这将是非常困难的.

EXPLAIN won't give you any indication of how long a query will take. At best you could use it to guess which of two queries might be faster, but unless one of them is obviously badly written then even that is going to be very hard.

您还应该意识到，如果您使用的是子查询，即使运行EXPLAIN也会很慢(在某些情况下，运行速度与查询本身一样慢).

You should also be aware that if you're using sub-queries, even running EXPLAIN can be slow (almost as slow as the query itself in some cases).

据我所知，MySQL没有提供任何方法来估计查询运行所花费的时间.您能否记录每个查询的运行时间，然后根据过去类似查询的历史记录进行估算?

As far as I'm aware, MySQL doesn't provide any way to estimate the time a query will take to run. Could you log the time each query takes to run, then build an estimate based on the history of past similar queries?

这篇关于如何使用EXPLAIN来“预测" MySQL查询的性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！