使用MySQL“LIMIT 1”是否有任何意义？查询索引/唯一字段时？ [英] Is there any point using MySQL "LIMIT 1" when querying on indexed/unique field?

问题描述

例如，我在一个我知道将是唯一的字段上查询并被索引，例如主键。因此我知道这个查询只返回1行（即使没有LIMIT 1）

For example, I'm querying on a field I know will be unique and is indexed such as a primary key. Hence I know this query will only return 1 row (even without the LIMIT 1)

SELECT * FROM tablename WHERE tablename.id = 123 LIMIT 1

或仅更新1行

UPDATE tablename SET somefield ='somevalue'WHER tablename.id = 123 LIMIT 1

将添加 LIMIT 1 改善查询执行时间？

Would adding the LIMIT 1 improve query execution time if the field is indexed?

推荐答案

使用MySQLLIMIT 1时是否有任何意义查询主键/唯一字段？

使用过滤条件查询时，使用 LIMIT 1 不是一个好习惯这是针对主键或唯一约束的。主键或唯一约束意味着表中只有一个行/记录具有该值，只返回一行/记录。在主键/唯一字段上使用 LIMIT 1 是矛盾的 - 稍后维护代码的人可能会误认为重要性&第二个猜你的代码。

Is there any point using MySQL "LIMIT 1" when querying on primary key/unique field?

It is not good practice to use LIMIT 1 when querying with filter criteria that is against either a primary key or unique constraint. A primary key, or unique constraint, means there is only one row/record in the table with that value, only one row/record will ever be returned. It's contradictory to have LIMIT 1 on a primary key/unique field--someone maintaining the code later could mistake the importance & second guess your code.

但最终指标是解释计划：

But the ultimate indicator is the explain plan:

explain SELECT t.name FROM USERS t WHERE t.userid = 4

...返回：

id  | select_type | table   | type  | possible_keys  | key      | key_len  |  ref  |  rows  |  Extra
-----------------------------------------------------------------------------------------------------
1   | SIMPLE      | users   | const | PRIMARY        | PRIMARY  | 4        | const | 1      |

...和：

explain SELECT t.name FROM USERS t WHERE t.userid = 4 LIMIT 1

...返回：

id  | select_type | table   | type  | possible_keys  | key      | key_len  |  ref  |  rows  |  Extra
-----------------------------------------------------------------------------------------------------
1   | SIMPLE      | users   | const | PRIMARY        | PRIMARY  | 4        | const | 1      |

结论

没有区别，没有必要。它似乎在这种情况下进行了优化（仅针对主键进行搜索）。

Conclusion

No difference, no need. It appears to be optimized out in this case (only searching against the primary key).

索引字段不保证被过滤值的唯一性，可能会出现多次。所以 LIMIT 1 是有意义的，假设你想要返回一行。

An indexed field doesn't guarantee uniqueness of the value being filtered, there could be more than one occurrence. So LIMIT 1 would make sense, assuming you want to return one row.

这篇关于使用MySQL“LIMIT 1”是否有任何意义？查询索引/唯一字段时？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！