使用LIMIT在SQL语句中能提升多少性能？

Question

假设我在数据库中有一个包含 1,000,000 条记录的表格。

如果我执行：

SELECT * FROM [Table] LIMIT 1000

如果我有一个包含 1000 条记录的表，只执行以下查询语句，这个查询需要花费相同的时间吗？

SELECT * FROM [Table]

我并不是想知道第一种查询需要花费多长时间，我只是想知道第一种查询是否比第二种查询执行时间要长得多。

我说过有 1,000,000 条记录的情况，但实际可能是 20,000,000。那只是一个例子。

编辑：
当然，如果使用 LIMIT 并且不在同一张表中使用它，使用 LIMIT 构建的查询应该更快，但这不是我的问题...

为了让问题更通用:

Table1：有 X 条记录
Table2：有 Y 条记录

(X << Y)

我想比较的是：

SELECT * FROM Table1

和

SELECT * FROM Table2 LIMIT X

编辑2:
这是我提出这个问题的原因：

我有一个数据库，其中包含5个表，它们之间存在关系。其中一个表（我可以100％确定）将包含约 5,000,000 条记录。我正在使用 SQL Server CE 3.5、Entity Framework 作为 ORM 和 LINQ to SQL 进行查询。

我需要执行基本上三种非简单的查询，我正在考虑向用户显示记录限制（就像许多网站一样）。如果用户想要查看更多记录，则选项是更加限制搜索。

所以，这个问题出现了，因为我在考虑做到这一点（每次查询限制为 X 条记录）或者仅存储 X 条结果（最近的那些），这将需要在数据库中进行一些删除，但我只是在思考...

因此，该表可能包含 5,000,000 条或更多记录，我不想向用户显示 1000 条记录，甚至在这种情况下，查询仍然与返回 5,000,000 行时一样慢。

- Oscar Mederos

---

LIMIT 是仅适用于 MySQL 的。可能应该加上标签。 - Stephen Chung

@Stephen 我相信在PostgreSQL中也有LIMIT :) - wired00

@wired00：你说得对，LIMIT被PostgreSQL支持，而TOP只有TSQL/SQL Server支持。FETCH FIRST x ROWS ONLY现在是ANSI标准，但据我所知，只有DB2实现了。 - OMG Ponies

@Oscar FETCH FIRST n ROWS 是标准语法。在Oracle中，您可以使用 WHERE rownum < n。 - Aleksi Yrttiaho

@Ponies。每当我想起DB2时，我就会感到不寒而栗。我对那个东西有着糟糕的回忆... - wired00

Answer 1

从1000000条记录的表中TAKE 1000条记录，将比它需要查看（和返回）1000/1000000条记录的速度快1000000/1000（=1000）倍。由于它做得更少，所以自然会更快。

结果将是相当（伪）随机的，因为您没有指定TAKE的顺序。但是，如果您引入了一个顺序，那么以下两个中的一个就成为真实情况：

1. ORDER BY子句遵循索引——上述语句仍然成立。 2. ORDER BY子句无法使用任何索引——它只比没有TAKE略快，因为 * 它必须检查所有记录，并按ORDER BY排序 * 只提供一个子集(TAKE count) * 因此，在第一步中它不会更快，但第二步涉及的IO/网络比所有记录少。

如果从1000条记录的表中TAKE 1000条记录，则与从10亿条记录中TAKE 1000条记录相同（只要您遵循（1）没有ORDER BY或（2）对索引进行ORDER BY的情况）。

- RichardTheKiwi

---

这意味着如果列 A 没有被索引，而我在该列上进行了 ORDER BY，那么在使用 LIMIT X 和不使用 LIMIT X 的情况下查询相同的数据库，在性能方面将非常相似，因为它需要加载所有数据进行排序，然后返回前 X 条记录。 - Oscar Mederos

更快做什么？如果是将结果传达到某个地方，那么它受IO限制，数据库基本上并不重要。 - dkretz

@Oscar / 如果你使用 ORDER BY someunindexedcolumn LIMIT X（或者TAKE或TOP或FETCH FIRST等），它需要拉取所有记录，对它们进行排序，然后返回X条记录。我撒谎了 - 如果是从10亿中选择1000条记录，网络流量会有显著的不同。无论如何，你已经了解了所涉及的步骤以及X的工作原理。 - RichardTheKiwi

Answer 2

假设两个表在索引、行大小和其他结构方面是相等的。还假设您正在运行这个简单的SELECT语句。如果您的SQL语句中有ORDER BY子句，那么显然更大的表会更慢。我想你不是在问这个。

如果X=Y，那么它们应该以类似的速度运行，因为查询引擎将按照完全相同的顺序浏览记录，基本上是表扫描，对于这个简单的SELECT语句，查询计划不会有任何区别。

如果Y仅比X高一点，则速度也相似。但是，如果Y>>X（意思是Y比X多很多行），那么LIMIT版本可能会更慢。不是因为查询计划--应该是相同的--而仅仅是因为数据布局的内部结构可能导致多几个级别。例如，如果数据存储在树的叶子上，则可能会有更多的树级别，因此访问相同数量的页面可能需要稍微更长的时间。

换句话说，1000行可以存储在10页的1个树级别中。1000000行可能存储在10000页的3-4个树级别中。即使只从这10000页中取出10页，存储引擎仍然必须经过3-4个树级别，这可能需要稍微更长的时间。

现在，如果存储引擎按顺序存储数据页面或作为链接列表，则执行速度没有区别。

- Stephen Chung

Answer 3

只要您不指定任何字段、排序和所有记录，它就会近似线性。但这并没有给您带来太多帮助。一旦查询想要执行有用的操作，它就会崩溃。

如果您打算得出一些有用的结论，并告诉我们在某个上下文中如何使用它来进行设计选择，那么这将会更加有趣。

感谢澄清。

根据我的经验，具有真实用户的实际应用程序很少有返回整个百万行表格的有趣或有用的查询。用户想知道他们自己的活动，或特定的论坛主题等。因此，除非您的情况特殊，否则当您真正掌握了他们的选择标准时，您将谈论合理的结果大小。

无论如何，用户不能对数百行以上的许多行做任何有用的事情，传输它们需要很长时间，而且他们无法以任何合理的方式滚动查看。

MySQL具有LIMIT 和OFFSET（起始记录#）修饰符，主要是为了创建像您描述的分页列表的块。

在使用了这个和其他一些策略之前，开始考虑模式设计和记录清除是非常低效的。在这种情况下，不要解决你还没有遇到的问题。只要正确地建立索引，几百万行的表在实际上并不算大。

- dkretz