当您知道只有 1 个结果时，将“LIMIT 1”添加到 MySQL 查询是否会使它们更快？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/455476/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does adding 'LIMIT 1' to MySQL queries make them faster when you know there will only be 1 result?
提问by Logan Serman
When I add LIMIT 1 to a MySQL query, does it stop the search after it finds 1 result (thus making it faster) or does it still fetch all of the results and truncate at the end?
当我将 LIMIT 1 添加到 MySQL 查询时，它是在找到 1 个结果后停止搜索（从而使其更快）还是仍然获取所有结果并在最后截断？
采纳答案by Eran Galperin
Depending on the query, adding a limit clause can have a huge effect on performance. If you want only one row (or know for a fact that only one row can satisfy the query), and are not sure about how the internal optimizer will execute it (for example, WHERE clause not hitting an index and so forth), then you should definitely add a LIMIT clause.
根据查询，添加限制子句会对性能产生巨大影响。如果您只需要一行（或者知道只有一行可以满足查询的事实），并且不确定内部优化器将如何执行它（例如，WHERE 子句未命中索引等），则你绝对应该添加一个 LIMIT 子句。
As for optimized queries (using indexes on small tables) it probably won't matter much in performance, but again - if you are only interested in one row than add a LIMIT clause regardless.
至于优化查询（在小表上使用索引），它可能对性能没有太大影响，但同样 - 如果您只对一行感兴趣，则无论如何都不要添加 LIMIT 子句。
回答by rjamestaylor
Limit can affect the performance of the query (see comments and the link below) and it also reduces the result set that is output by MySQL. For a query in which you expect a single result there is benefits.
限制会影响查询的性能（请参阅下面的评论和链接），并且还会减少 MySQL 输出的结果集。对于您期望单个结果的查询有好处。
Moreover, limiting the result set can in fact speed the total query time as transferring large result sets use memory and potentially create temporary tables on disk. I mention this as I recently saw a application that did not use limit kill a server due to huge result sets and with limit in place the resource utilization dropped tremendously.
此外，限制结果集实际上可以加快总查询时间，因为传输大型结果集会使用内存并可能在磁盘上创建临时表。我提到这一点是因为我最近看到一个应用程序由于巨大的结果集而没有使用限制杀死服务器，并且限制到位资源利用率急剧下降。
Check this page for more specifics: MySQL Documentation: LIMIT Optimization
查看此页面了解更多详情：MySQL 文档：LIMIT 优化
回答by Kris Erickson
If there is only 1 result coming back, then no, LIMIT will not make it any faster. If there are a lot of results, and you only need the first result, and there is no GROUP or ORDER by statements then LIMIT will make it faster.
如果只有 1 个结果返回，则不，LIMIT 不会使其更快。如果有很多结果，并且您只需要第一个结果，并且没有 GROUP 或 ORDER by 语句，那么 LIMIT 会使其更快。
回答by Max Alexander Hanna
The answer, in short, is yes.If you limit your result to 1, then even if you are "expecting" one result, the query will be faster because your database wont look through all your records. It will simply stop once it finds a record that matches your query.
简而言之，答案是肯定的。如果您将结果限制为 1，那么即使您“期望”一个结果，查询也会更快，因为您的数据库不会查看所有记录。一旦找到与您的查询匹配的记录，它就会停止。
回答by driAn
If you really only expect one single result, it really makes sense to append the LIMIT to your query. I don't know the inner workings of MySQL, but I'm sure it won't gather a result set of 100'000+ records just to truncate it back to 1 at the end..
如果您真的只期望一个结果，那么将 LIMIT 附加到您的查询中真的很有意义。我不知道 MySQL 的内部工作原理，但我确定它不会收集 100'000+ 条记录的结果集只是为了在最后将其截断回 1 ..
