LIMIT 0,1会加快主键上的SELECT速度吗? [英] Would LIMIT 0,1 speed up a SELECT on a Primary Key?

问题描述

有人知道这两个查询之间是否存在速度差异(显然对于足够大的表而言):

Does anyone know if there is any speed difference (obviously for tables that are sizable enough) between these two queries:

SELECT field FROM table WHERE primary_key = "a particular value"

或:

SELECT field FROM table WHERE primary_key = "a particular value" LIMIT 0,1

我应该注意primary_key字段实际上是主键.

I should note that the primary_key field is actually a primary key.

现在LIMIT 0,1确实可以帮助查询继续进行以查找其他匹配项.我假设，当涉及到主键时，由于其唯一性，它应该会自动停止.所以我的猜测是，添加它不会有任何收益.

Now LIMIT 0,1 does help when a query would otherwise carry on to find other matches. I'm assuming though that when a primary key is involved it should automatically stop due to it's uniqueness. So my guess is that there wouldn't be any gain by adding it.

有人遇到过吗?我目前没有足够大的数据集可用于测试.我还假设相同的答案将适用于已设置为UNIQUE的任何字段.

Anyone come across this before? I don't have a sufficiently large dataset lying around to test with at the moment. I'd also assume that the same answer would apply for any field that has been set to be UNIQUE.

推荐答案

因此，在读取Hammerite提供的信息(谢谢)后，在两个查询中均运行explain会产生:

So after reading the information provided by Hammerite (Thanks for that) running explain on both queries produces:

• id = 1
• select_type =简单
• 表=表
• 类型= CONST
• possible_keys = PRIMARY
• 键=主键
• key_len = 767
• ref = const
• 行= 1
• 额外=

• id = 1
• select_type = SIMPLE
• table = table
• type = CONST
• possible_keys = PRIMARY
• key = PRIMARY
• key_len = 767
• ref = const
• rows = 1
• Extra =

将这两个查询都标识为CONST，链接将其定义为:

Which identifies both queries as CONST, which the link defines as:

常量

该表最多有一个匹配行，该行从 查询.由于只有一行，因此该列中的值 该行可以被优化器的其余部分视为常量. const表非常快，因为它们只能读取一次.

The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once.

因此，假设我正确理解了这两个查询，它们将被视为相同-这正是我所期望的.

So assuming I understand both queries correctly, they would be treated the same - which is what I had expected.

这篇关于LIMIT 0,1会加快主键上的SELECT速度吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！