在 MySQL 中仅检索固定数量的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10048298/
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
Retrieving only a fixed number of rows in MySQL
提问by Sandman
I am testing my database design under load and I need to retrieve only a fixed number of rows (5000)
我正在负载下测试我的数据库设计，我只需要检索固定数量的行 (5000)
I can specify a LIMIT to achieve this, however it seems that the query builds the result set of all rows that match and then returns only the number of rows specified in the limit. Is that how it is implemented?
我可以指定一个 LIMIT 来实现这一点，但是似乎查询构建了所有匹配行的结果集，然后只返回限制中指定的行数。是这样实现的吗？
Is there a for MySQL to read one row, read another one and basically stop when it retrieves the 5000th matching row?
MySQL是否可以读取一行，读取另一行并在检索第5000个匹配行时基本停止？
回答by Keith Randall
MySQL is smart in that if you specify a LIMIT 5000in your query, and it is possible to produce that result without generating the whole result set first, then it will not build the whole result.
MySQL 很聪明，因为如果您LIMIT 5000在查询中指定 a ，并且可以在不首先生成整个结果集的情况下生成该结果，那么它不会构建整个结果。
For instance, the following query:
例如，以下查询：
SELECT * FROM table ORDER BY column LIMIT 5000
This query will need to scan the whole tableunless there is an index on column, in which case it does the smart thing and uses the index to find the rows with the smallest column.
此查询将需要扫描整个，table除非在 上有索引column，在这种情况下，它会做聪明的事情并使用索引来查找具有最小 的行column。
回答by Teja
SELECT * FROM `your_table` LIMIT 0, 5000
This will display the first 5000 results from the database.
这将显示数据库中的前 5000 个结果。
SELECT * FROM `your_table` LIMIT 1001, 5000
This will show records from 1001 to 6000 (counting from 0).
这将显示从 1001 到 6000 的记录（从 0 开始计数）。
回答by aquasan
@Jaros?aw Gomu?ka is right
If you use LIMIT with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. In either case, after the initial rows have been found, there is no need to sort any remainder of the result set, and MySQL does not do so.
if the set is not sorted it terminates the SELECT operation as soon as it's got enough rows to the result set.
@Jaros?aw Gomu?ka 是对的
如果您将 LIMIT 与 ORDER BY 一起使用，MySQL 会在找到排序结果的第一个 row_count 行后立即结束排序，而不是对整个结果进行排序。如果使用索引完成排序，则速度非常快。在任何一种情况下，在找到初始行之后，都不需要对结果集的任何剩余部分进行排序，而 MySQL 不会这样做。如果该集合未排序，它会在获得足够的行到结果集后立即终止 SELECT 操作。
回答by Jaros?aw Gomu?ka
Complexity of such query is O(LIMIT) (unless you specify order by).
此类查询的复杂性为 O(LIMIT)（除非您指定 order by）。
It means that if 10000000 rows will match your query, and you specify limit equal to 5000, then the complexity will be O(5000).
这意味着如果 10000000 行将匹配您的查询，并且您指定的限制等于 5000，那么复杂度将为 O(5000)。
回答by mdoyle
The exact plan the query optimizer uses depends on your query (what fields are being selected, the LIMIT amount and whether there is an ORDER BY) and your table (keys, indexes, and number of rows in the table). Selecting an unindexed column and/or ordering by a non-key column is going to produce a different execution plan than selecting a column and ordering by the primary key column. The later will not even touch the table, and only process the number of rows specified in your LIMIT.
查询优化器使用的确切计划取决于您的查询（选择了哪些字段、LIMIT 数量以及是否有 ORDER BY）和您的表（键、索引和表中的行数）。选择未索引的列和/或按非键列排序将产生与选择列并按主键列排序不同的执行计划。后者甚至不会触及表格，只会处理您的 LIMIT 中指定的行数。
回答by Vlad Mihalcea
As I explained in this article, each database defines its own ay of limiting the result set size depends on the database you are using.
正如我在本文中所解释的，每个数据库都定义了自己的限制结果集大小的方法，具体取决于您使用的数据库。
While the SQL:2008 specification defines a standard syntax for limiting a SQL query, MySQL 8 does not support it.
虽然 SQL:2008 规范定义了用于限制 SQL 查询的标准语法，但 MySQL 8 不支持它。
Therefore, on MySQL, you need to use the LIMIT clause to restrict the result set to the Top-N records:
因此，在 MySQL 上，您需要使用 LIMIT 子句将结果集限制为 Top-N 记录：
SELECT
title
FROM
post
ORDER BY
id DESC
LIMIT 50
Notice that we are using an ORDER BY clause since, otherwise, there is no guarantee which are the first records to be included in the returning result set.
请注意，我们使用的是 ORDER BY 子句，否则无法保证哪些记录是返回结果集中包含的第一条记录。
