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个匹配行时基本停止？
5 个解决方案
#1
17
MySQL is smart in that if you specify a LIMIT 5000 in 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，并且可以在不先生成整个结果集的情况下生成该结果，那么它将不会构建整个结果。
For instance, the following query:
例如，以下查询：
SELECT * FROM table ORDER BY column LIMIT 5000
This query will need to scan the whole table unless 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.
除非列上有索引，否则此查询将需要扫描整个表，在这种情况下，它执行智能操作并使用索引查找具有最小列的行。
#2
6
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 5000
这将显示从1001到5000的记录
#3
3
@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ławGomułka是正确的如果您使用LIMIT和ORDER BY，MySQL会在找到排序结果的第一行row_count行后立即结束排序，而不是对整个结果进行排序。如果使用索引完成排序，则速度非常快。在任何一种情况下，在找到初始行之后，不需要对结果集的任何其余部分进行排序，而MySQL也不会这样做。如果set没有排序，只要它有足够的行到结果集，它就会终止SELECT操作。
#4
2
Complexity of such query is O(LIMIT) (unless you specify order by).
这种查询的复杂性是O（LIMIT）（除非您指定顺序）。
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行与您的查询匹配，并且您指定limit等于5000，则复杂度将为O（5000）。
#5
0
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中指定的行数。
#1
17
MySQL is smart in that if you specify a LIMIT 5000 in 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，并且可以在不先生成整个结果集的情况下生成该结果，那么它将不会构建整个结果。
For instance, the following query:
例如，以下查询：
SELECT * FROM table ORDER BY column LIMIT 5000
This query will need to scan the whole table unless 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.
除非列上有索引，否则此查询将需要扫描整个表，在这种情况下，它执行智能操作并使用索引查找具有最小列的行。
#2
6
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 5000
这将显示从1001到5000的记录
#3
3
@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ławGomułka是正确的如果您使用LIMIT和ORDER BY，MySQL会在找到排序结果的第一行row_count行后立即结束排序，而不是对整个结果进行排序。如果使用索引完成排序，则速度非常快。在任何一种情况下，在找到初始行之后，不需要对结果集的任何其余部分进行排序，而MySQL也不会这样做。如果set没有排序，只要它有足够的行到结果集，它就会终止SELECT操作。
#4
2
Complexity of such query is O(LIMIT) (unless you specify order by).
这种查询的复杂性是O（LIMIT）（除非您指定顺序）。
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行与您的查询匹配，并且您指定limit等于5000，则复杂度将为O（5000）。
#5
0
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中指定的行数。