MySQL在查询中如何处理ORDER BY和LIMIT？

Question

我有一个查询看起来像这样:

SELECT article FROM table1 ORDER BY publish_date LIMIT 20

ORDER BY是如何工作的？它会对所有记录进行排序，然后获取前20个记录，还是会获取20条记录并按publish_date字段对它们进行排序？

如果是后者，您无法保证确实获得最近的20篇文章。

- Alex

---

请注意，如果某些“publish_date”相同，则按它们排序不会给出确定的结果，这意味着如果您在分页时使用“LIMIT”，则可能会在不同的页面上获得相同的项目！ - Konrad Morawski

注意应用它们的顺序。如果先使用LIMIT再使用ORDER BY，会出现错误。ORDER BY必须在查询语句中排在第一位。 - Kalko

Answer 1

先进行排序，然后获取前20个。在ORDER BY之前，数据库还将处理WHERE子句中的任何内容。

- James

---

所以时间是一样的吗？ - Yasar Arafath

错误！LIMIT破坏了ORDER BY的顺序。使用LIMIT时，ORDER BY返回错误的结果。LIMIT会以某种方式重新排列ORDER BY返回的结果集。 - Green

@Green，你错了。阅读这篇文章来获得解释：https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html 当ORDER BY列被索引时，如果在该列中有多个记录具有相同的值，则使用LIMIT可能会以不同于没有LIMIT时的顺序返回记录。 - yitwail

解决这类问题的一个快速方法是添加一列排序，最好具有唯一值，这样当第一个排序列的值对于多行相同时，数据库可以获得一致的规则来排序行。 - rineez

Answer 2

LIMIT子句可用于限制SELECT语句返回的行数。LIMIT接受一个或两个数字参数，这两个参数必须都是非负整数常量（除了使用预处理语句时）。

有两个参数时，第一个参数指定要返回的第一行的偏移量，第二个参数指定要返回的最大行数。初始行的偏移量为0（而不是1）：

SELECT * FROM tbl LIMIT 5,10; # 检索行6-15

要检索从某个偏移量到结果集结尾的所有行，可以为第二个参数使用一些大数。此语句检索从第96行到最后一行的所有行：

SELECT * FROM tbl LIMIT 95,18446744073709551615;

只有一个参数时，该值指定要从结果集开头返回的行数：

SELECT * FROM tbl LIMIT 5; # 检索前5行

换句话说，LIMIT row_count相当于LIMIT 0，row_count。

更多细节请参阅：http://dev.mysql.com/doc/refman/5.0/en/select.html

- bensiu

---

难道不是检索第5-14行吗？ - Adonis K. Kakoulidis

@adonis 不是的。这个例子来自MySQL文档。 - dcaswell

数字5是第6行。忽略了5行（从0到4）。 - Phil Perry

但是在没有使用ORDER BY的情况下使用LIMIT可能会导致结果不一致！不幸的是，在应用LIMIT之前，整个结果集必须被排序，否则DBMS可以任意地对结果进行排序，然后在该集上进行OFFSET和LIMIT。我已经阅读过这可能是由于DBMS基于OFFSET和LIMIT选择了替代查询计划，因此产生了任意顺序。 - Barton

问题要求限制和排序方式，但答案与此问题无关。 - Shen liang

Answer 3

正如@James所说，它会对所有记录排序，然后获取前20行。

因此，您可以保证获得前20篇发布的文章，较新的文章将不会显示。

在您的情况下，我建议您在“order by publish_date”中添加“desc”，如果您想要最新的文章，则最新的文章将排在第一位。

如果您需要保持结果按升序排列，并且仍然只想要最新的10篇文章，您可以要求mysql对结果进行两次排序。

以下查询将对结果进行降序排序并将结果限制为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;

如果您需要所有列，则可以按以下方式完成：

select t.* 
from 
    (select * 
     from table1  
     order by publish_date desc limit 10) t 

order by t.publish_date asc;

当我手动编写查询来检查数据库中的各种内容时，我使用这种技术。虽然我没有在生产环境中使用过它，但现在当我对其进行了基准测试时，额外的排序并不会影响性能。

- emanciperingsivraren

---

您额外的排序实际上对性能几乎没有任何可测量的影响，因为它仅限于10行/项 :-).通常，在内存中对表进行排序（子选择生成的表）非常快速且几乎不可测量，除非您有数百万行或DBMS将结果集分页到磁盘上，因为它无法适应内存（在这种情况下，根据DBMS，它也可以中止查询）。 - Martin Kersten

Answer 4

您可以在order by的末尾添加[asc]或[desc]来获取最早或最晚的记录。例如，这将先显示最近的记录。

ORDER BY stamp DESC

在ORDER BY之后添加LIMIT子句。

- Paul

---

欢迎来到stackoverflow。我认为您可能误解了问题。我相信他们所问的是运算顺序，而不是“如何排序”。（但这已经无关紧要，因为问题早就被回答了 ;)） - Leigh

Answer 5

如果存在适当的索引，例如在 publish_date 字段上，则 MySQL 不需要扫描整个索引以获取所请求的 20 条记录 - 这 20 条记录将在索引开头找到。但如果没有适当的索引，则需要对整个表进行全面扫描。

2009 年有一篇关于此问题的MySQL 性能博客文章。

- martin clayton

Answer 6

您可以使用以下代码： SELECT article FROM table1 ORDER BY publish_date LIMIT 0,10 其中0是记录的起始限制，10是记录的数量。

- gaurangkathiriya

---

不，那不是必需的。LIMIT 10 是 LIMIT 0,10 的简写。 - Lawrence Dol

是的，不需要限制0到10，但你可以像这样要求限制10到20。 - gaurangkathiriya

Answer 7

LIMIT通常是作为最后一个操作执行的，所以结果会先被排序，然后限制在20个之内。实际上，一旦找到了前20个已排序的结果，排序就会停止。

- Egor Pavlikhin

---

你的第二句话与第一句话相矛盾。当找到前20个结果时，排序无法停止，因为正如你所说，排序将在结果返回之前完成。只有在排序完成后，MySQL才能知道前20个结果是什么。 - Tom

@Tom，如果按索引列排序，实际上是可以的。这里有解释：https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html - yitwail

@Tom：你可以比排序所有行并取前k行更有效地找到前k行。想一想。找到最小值（k=1）也不需要对所有行进行排序，然后取第一行，而是可以在O(n)的时间内完成。对于前k行，这可以在O(n + k * log k)的时间内完成，这就是数据库的做法。请参阅https://www.geeksforgeeks.org/k-largestor-smallest-elements-in-an-array/以获取可能算法列表。 - thomas.schuerger

Answer 8

可以简化为：

SELECT article FROM table1 ORDER BY publish_date DESC FETCH FIRST 20 ROWS ONLY;

您还可以在ORDER BY中添加许多参数，只需用逗号分隔，例如：ORDER BY publish_date、 tab2、tab3 DESC等等...

- Daniel Nelson