使用 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' WHERE 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屋！