如何使用 EXPLAIN 来*预测* MySQL 查询的性能？
我正在帮助维护一个程序，该程序本质上是一个大型且复杂的 MySQL 数据库的友好只读前端 - 该程序根据用户的输入构建临时 SELECT 查询，将查询发送到数据库，获取结果，对它们进行后处理，并将它们很好地显示给用户。
我想为构造的查询的预期性能添加某种形式的合理/启发式预测 - 有时用户无意中进行的查询不可避免地会花费很长时间（因为它们会返回巨大的结果集，或者因为它们' re“违背数据库索引方式”），我希望能够向用户显示一些“有点可靠”的信息/猜测查询将花费多长时间。 它不必是完美的，只要它不会与现实严重且频繁地脱节，从而导致用户学会忽视它的“狼来了”效应；-) 根据此信息，用户可能决定去喝杯咖啡（如果估计是 5-10 分钟），去吃午餐（如果是 30-60 分钟），终止查询并尝试其他内容（可能对他们请求的信息有更严格的限制） ）等。
我对 MySQL 的 EXPLAIN 语句不太熟悉——我看到很多关于如何使用它来优化查询或数据库模式、索引等的信息，但没有太多关于如何将它用于我更有限的目的——简单地进行预测，将数据库作为给定（当然，如果预测足够可靠，我最终可能会转而使用它们来在查询的替代形式之间进行选择）可以，但是，这是为了未来：现在，我很乐意向用户展示出于上述目的的性能估计）。
有什么指点吗...？
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.
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.
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).
Any pointers...?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
EXPLAIN 不会向您提供任何有关查询需要多长时间的指示。
最好的情况下，您可以使用它来猜测两个查询中哪一个可能更快，但除非其中一个查询明显写得很糟糕，否则即使这样也将非常困难。
您还应该注意，如果您使用子查询，即使运行 EXPLAIN 也可能很慢（在某些情况下几乎与查询本身一样慢）。
据我所知，MySQL 没有提供任何方法来估计查询运行所需的时间。 您能否记录每个查询运行所需的时间，然后根据过去类似查询的历史记录进行估计？
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.
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).
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 结果组件构建一个统计模型。 尝试基于思考 EXPLAIN 的内容来构建查询执行时间预测器只会花费太长时间，在它被细化为模糊的有用性之前给出令人尴尬的糟糕结果。
I think if you want to have a chance of building something reasonably reliable out of this, what you should do is build a statistical model out of table sizes and broken-down EXPLAIN result components correlated with query processing times. Trying to build a query execution time predictor based on thinking about the contents of an EXPLAIN is just going to spend way too long giving embarrassingly poor results before it gets refined to vague usefulness.
MySQL EXPLAIN 有一个名为
Key的列。 如果这个列中有东西，这是一个非常好的指示，它意味着查询将使用索引。使用索引的查询通常可以安全使用，因为它们可能是数据库设计者在设计数据库时考虑到的。
但是
还有另一个字段称为
Extra。 该字段有时包含文本using_filesort。这非常非常很糟糕。 从字面上看，这意味着 MySQL 知道查询的结果集将大于可用内存，因此将开始将数据交换到磁盘以对其进行排序。
结论
不要尝试预测查询所需的时间，只需查看这两个指标即可。 如果查询是
using_filesort，则拒绝用户。 并且根据您想要的严格程度，如果查询未使用任何键，您也应该拒绝它。详细了解 MySQL EXPLAIN 语句
MySQL EXPLAIN has a column called
Key. If there is something in this column, this is a very good indication, it means that the query will use an index.Queries that use indicies are generally safe to use since they were likely thought out by the database designer when (s)he designed the database.
However
There is another field called
Extra. This field sometimes contains the textusing_filesort.This is very very bad. This literally means MySQL knows that the query will have a result set larger than the available memory, and therefore will start to swap the data to disk in order to sort it.
Conclusion
Instead of trying to predict the time a query takes, simply look at these two indicators. If a query is
using_filesort, deny the user. And depending on how strict you want to be, if the query is not using any keys, you should also deny it.Read more about the resultset of the MySQL EXPLAIN statement