mysql - 'LIMIT'参数在sql中是如何工作的？

Question

例如我有 4000 行，我定义了 X 限制。

查询在找到 X 行后停止？或者查询找到所有行，然后从找到的行中取出 X 行？

谢谢。

Answer 1

来自 MySQL Reference Manual :

If you use LIMIT row_count 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. If a filesort must be done, all rows that match the query without the LIMIT clause must be selected, and most or all of them must be sorted, before it can be ascertained that the first row_count rows have been found. 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.

因此，在应用 LIMIT 之前，整个结果集似乎是可能已知的。但是 MySQL 会尝试一切它做不到的事情。您可以通过提供与您的查询相匹配的有用索引来帮助它。

编辑:此外，如果集合未排序，它会在将足够多的行流式传输到结果集后立即终止 SELECT 操作。