MYSQL 和 LIMIT 子句
我想知道向查询添加 LIMIT 1 是否会加快处理速度？
例如...
我有一个查询，大多数情况下会返回 1 个结果，但偶尔会返回 10 条、100 条甚至 1000 条记录。但我只想要第一张唱片。
限制 1 会加快速度还是没有影响？
我知道我可以使用 GROUP BY 返回 1 个结果，但这只会增加更多的计算。
I was wondering if adding a LIMIT 1 to a query would speed up the processing?
For example...
I have a query that will most of the time return 1 result, but will occasionally return 10's, 100's or even 1000's of records. But I will only ever want the first record.
Would the limit 1 speed things up or make no difference?
I know I could use GROUP BY to return 1 result but that would just add more computation.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
这取决于您是否有 ORDER BY。 ORDER BY 无论如何都需要整个结果集，因此可以对其进行排序。
如果你没有任何 ORDER BY 它应该运行得更快。
在所有情况下，它的运行速度至少会快一点，因为当然不需要发送整个结果集。
It depends if you have an ORDER BY. An ORDER BY needs the entire result set anyway, so it can be ordered.
If you don't have any ORDER BY it should run faster.
It will in all cases run at least a bit faster since the entire result set needn't be sent of course.
是的！会的！
但可以肯定的是，在 sql 语句末尾添加“Limit 1”只需要一秒钟，所以为什么不尝试一下呢？
Yep! it will!
But to be sure, it should only take a sec to add 'Limit 1' to the end of your sql statement so why not give a shot and see
这一切都取决于查询本身。如果您正在进行索引查找（Where indexedColumn = somevalue）或对索引列进行排序（没有Where 子句），那么 limit 1 确实会加快速度。如果您有连接或多个 where/order 子句，那么事情很快就会变得非常复杂。但最重要的是，使用“LIMIT 1”将永远减慢查询速度。它有时会加快速度，但永远不会减慢速度。
现在，处理 PHP 时还有另一个问题。默认情况下，PHP 会在查询返回之前缓冲整个结果集（mysql_query 或 mysqli->query 仅在下载所有记录后才返回）。因此，虽然查询时间可能会因限制 1 而发生很小的变化，但 PHP 用于缓冲结果的时间和内存却很大。假设每行有 200 字节的数据。现在，您的查询返回 10,000 行。这意味着 PHP 必须额外分配 2mb 的内存（实际上接近 10mb，加上 php 变量结构的开销），而您永远不会使用它。分配内存可能非常昂贵，因此一般规则是只分配您需要的（或认为您将需要的）。当您只需要 1 行时下载 10,000 行只是浪费。
结合这两种效果，您就会明白为什么如果您只想要 1 行，则应该始终使用“LIMIT 1”。
It all depends on the query itself. If you're doing an indexed lookup (Where indexedColumn = somevalue)or a sort on an indexed column (with no Where clause), then limit 1 will really speed it up. If you have joins or multiple where/order clauses, then things get really complicated really quickly. But the major thing to take away, using "LIMIT 1" will NEVER slow down a query. It will sometimes speed it up, but it will never slow it down.
Now, there is another issue when dealing with PHP. By default, PHP will buffer the entire result set before returning from the query (mysql_query or mysqli->query will only return after all the records are downloaded). So while the query time may be altered little by the limit 1, the time and memory that PHP uses to buffer the result are significant. Imagine each row has 200 bytes of data. Now, your query returns 10,000 rows. That means PHP has to allocate an additional 2mb of memory (actually closer to 10mb with the overhead of php's variable structure) that you'll never use. Allocating memory can be very expensive, so the general rule is only ever allocate what you need (or think you will need). Downloading 10,000 rows when you only want 1 is just wasteful.
Combine these two effects, and you can see why if you want only 1 row, you should ALWAYS use "LIMIT 1".
这里有一些相关文档，来自MySQL 站点。
看起来它可以以不同的方式加快速度，具体取决于查询的其他部分。我不确定当您没有
ORDER或GROUP或HAVING子句时它是否有帮助，除了能够立即停止而不是给出返回每个结果行（如果您要返回 100,000 条记录，这可能已经足够快了）。Here is some relevant documentation on the subject from the MySQL site.
It seems it can speed things up in different ways, depending on the other parts of the query. I'm not sure if it helps when you have no
ORDERorGROUPorHAVINGclauses, aside from being able to stop immediately rather than give back every single result row (which may be a big enough speed up if you are getting back 100,000 records).这实际上是 LIMIT 子句的基本目的：P 如果您知道想要多少个结果，则应该始终指定该数字作为 LIMIT，不仅因为它更快（除非您正在执行 ORDER BY），而且还因为它更快高效地利用 PHP 脚本中的内存。
注意：我假设您正在将 MySQL 与 PHP 一起使用，因为您将 PHP 添加到了标签中。如果您只是直接从 MySQL 中进行选择（在脚本语言之外），那么在使用 ORDER BY 时使用 LIMIT 的优点纯粹是使结果更易于管理。
This is the fundamental purpose of the LIMIT clause actually :P If you know how many results you want, you should ALWAYS specify that number as the LIMIT not only because it is faster (unless you are doing an ORDER BY), but to be more efficient with memory in your PHP script.
Note: I'm assuming you're using MySQL with PHP since you added PHP to the tags. If you're just selecting from MySQL directly (outside of a scripting language) the advantage to using LIMIT when also using ORDER BY is purely to make the results easier to manage.