使用 MySQL 的“LIMIT 1"有什么意义吗?查询索引/唯一字段时?
例如，我正在查询一个我知道将是唯一的字段，并且已编入索引，例如主键.因此我知道这个查询只会返回 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.
相关文章