在MySQL中仅检索固定数量的行 [英] Retrieving only a fixed number of rows in MySQL

问题描述

我正在测试负载下的数据库设计，我只需要检索固定数量的行(5000)

I am testing my database design under load and I need to retrieve only a fixed number of rows (5000)

我可以指定一个LIMIT来实现这一点，但是查询似乎建立了所有匹配行的结果集，然后仅返回该限制中指定的行数.是这样实现的吗?

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?

MySQL是否有读取一行，读取另一行并在检索到第5000个匹配行时基本上停止的行?

Is there a for MySQL to read one row, read another one and basically stop when it retrieves the 5000th matching row?

推荐答案

MySQL的聪明之处在于，如果在查询中指定LIMIT 5000，则可以在不首先生成整个结果集的情况下生成该结果.它不会建立整个结果.

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.

例如，以下查询:

SELECT * FROM table ORDER BY column LIMIT 5000

该查询将需要扫描整个table，除非column上有索引，在这种情况下，它很聪明，并使用索引查找了column最小的行.

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.

这篇关于在MySQL中仅检索固定数量的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！