MySQL 如何处理查询中的 ORDER BY 和 LIMIT？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4708708/
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
How does MySQL process ORDER BY and LIMIT in a query?
提问by Alex
I have a query that looks like this:
我有一个看起来像这样的查询：
SELECT article FROM table1 ORDER BY publish_date LIMIT 20
SELECT article FROM table1 ORDER BY publish_date LIMIT 20
How does ORDER BY work? Will it order all records, then get the first 20, or will it get 20 records and order them by the publish_datefield?
ORDER BY 如何运作？它是对所有记录进行排序，然后获取前 20 条记录，还是获取 20 条记录并按publish_date字段排序？
If it's the last one, you're not guaranteed to really get the most recent 20 articles.
如果是最后一篇，则不能保证您真的会获得最新的 20 篇文章。
采纳答案by James
It will order first, then get the first 20. A database will also process anything in the WHEREclause before ORDER BY.
它将首先排序，然后获取前 20 个。数据库还将处理WHERE之前子句中的任何内容ORDER BY。
回答by bensiu
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).
LIMIT 子句可用于限制 SELECT 语句返回的行数。LIMIT 接受一个或两个数字参数，它们都必须是非负整数常量（使用准备好的语句时除外）。
With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):
有两个参数，第一个参数指定要返回的第一行的偏移量，第二个参数指定要返回的最大行数。初始行的偏移量为 0（不是 1）：
SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:
要检索从某个偏移量到结果集末尾的所有行，您可以为第二个参数使用一些大数字。此语句检索从第 96 行到最后一行的所有行：
SELECT * FROM tbl LIMIT 95,18446744073709551615;
SELECT * FROM tbl LIMIT 95,18446744073709551615;
With one argument, the value specifies the number of rows to return from the beginning of the result set:
使用一个参数，该值指定从结果集的开头返回的行数：
SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows
SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows
In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.
换句话说，LIMIT row_count 等价于 LIMIT 0, row_count。
All details on: http://dev.mysql.com/doc/refman/5.0/en/select.html
回答by emanciperingsivraren
Just as @James says, it will order all records, then get the first 20 rows.
正如@James 所说，它将对所有记录进行排序，然后获取前 20 行。
As it is so, you are guaranteed to get the 20 first published articles, the newer ones will not be shown.
如此一来，您就可以保证获得第 20 篇首次发表的文章，较新的文章将不会显示。
In your situation, I recommend that you add descto order by publish_date, if you want the newest articles, then the newest article will be first.
在你的情况下，我建议你添加desc到 order by publish_date，如果你想要最新的文章，那么最新的文章会在最前面。
If you need to keep the result in ascending order, and still only want the 10 newest articles you can ask mysql to sort your result two times.
如果您需要按升序排列结果，并且仍然只想要 10 篇最新的文章，您可以让 mysql 对您的结果进行两次排序。
This query below will sort the result descending and limit the result to 10 (that is the query inside the parenthesis). It will still be sorted in descending order, and we are not satisfied with that, so we ask mysql to sort it one more time. Now we have the newest result on the last row.
下面的这个查询将对结果进行降序排序并将结果限制为 10（即括号内的查询）。还是会降序排序，我们不满意，所以请mysql再排序一次。现在我们在最后一行有了最新的结果。
select t.article
from
(select article, publish_date
from table1
order by publish_date desc limit 10) t
order by t.publish_date asc;
If you need all columns, it is done this way:
如果您需要所有列，请按以下方式完成：
select t.*
from
(select *
from table1
order by publish_date desc limit 10) t
order by t.publish_date asc;
I use this technique when I manually write queries to examine the database for various things. I have not used it in a production environment, but now when I bench marked it, the extra sorting does not impact the performance.
当我手动编写查询来检查数据库的各种事情时，我会使用这种技术。我没有在生产环境中使用过它，但是现在当我对它进行基准标记时，额外的排序不会影响性能。
回答by martin clayton
If there is a suitable index, in this case on the publish_datefield, then MySQL need not scan the whole index to get the 20 records requested - the 20 records will be found at the start of the index. But if there is no suitable index, then a full scan of the table will be needed.
如果有合适的索引，在这种情况下在publish_date字段上，那么 MySQL 不需要扫描整个索引来获取请求的 20 条记录 - 将在索引的开头找到 20 条记录。但是如果没有合适的索引，则需要对表进行全盘扫描。
There is a MySQL Performance Blog articlefrom 2009 on this.
2009 年有一篇关于此的MySQL 性能博客文章。
回答by Paul
You could add [asc] or [desc] at the end of the order by to get the earliest or latest records
您可以在订单末尾添加 [asc] 或 [desc] 以获取最早或最新的记录
For example, this will give you the latest records first
例如，这将首先为您提供最新的记录
ORDER BY stamp DESC
Append the LIMITclause after ORDER BY
在LIMIT后面附加条款ORDER BY
回答by gaurangkathiriya
You can use this code
SELECT article FROM table1 ORDER BY publish_date LIMIT 0,10where 0 is a start limit of record & 10 number of record
您可以使用此代码
SELECT article FROM table1 ORDER BY publish_date LIMIT 0,10，其中 0 是记录的起始限制和 10 的记录数
回答by Egor Pavlikhin
LIMIT is usually applied as the last operation, so the result will first be sorted and then limited to 20. In fact, sorting will stop as soon as first 20 sorted results are found.
LIMIT 通常作为最后一个操作应用，所以结果会先排序，然后限制为 20。实际上，只要找到前 20 个排序结果，排序就会停止。
回答by harish sharma
Also syntax of LIMIT is different according to databases, for example:
LIMIT 的语法也因数据库而异，例如：
mysql- LIMIT 1, 2
mysql- 限制 1、2
postgres- LIMIT 2 OFFSET 1
postgres- 限制 2 偏移 1
