MySQL WHERE 条件基于 PK 的查询是否建议使用“LIMIT 1”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1774361/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is "LIMIT 1" recommended for query where WHERE condition is based on PK?
提问by justinl
I am querying a mySQL database to retrieve the data from 1 particular row. I'm using the table primary key as the WHERE constraint parameter.
我正在查询 mySQL 数据库以从 1 个特定行检索数据。我使用表主键作为 WHERE 约束参数。
E.g.
例如
SELECT name FROM users WHERE userid = 4
The userid column is the primary key of the table. Is it good practice to use LIMIT 1 on the end of that mySQL statement? Or are there any speed benefits?
userid 列是表的主键。在该 mySQL 语句的末尾使用 LIMIT 1 是一种好习惯吗？或者有什么速度优势？
回答by Bartek
I would call that a bad practice as when it comes to something like a useridit's generally unique and you won't have more than one. Therefore, having LIMIT 1seems pretty contradictory and someone who comes to maintain your code later may have to second-guess your design.
我会称这是一种不好的做法，因为当涉及到类似的东西时，userid它通常是独一无二的，而且不会超过一个。因此，拥有LIMIT 1似乎非常矛盾，稍后来维护您的代码的人可能不得不重新猜测您的设计。
Also, I don't think it has any speed benefit at all. You can check out mySQL's Explainfor a simple tool to analyze a query.
此外，我认为它根本没有任何速度优势。您可以查看 mySQL 的Explain以获取分析查询的简单工具。
Note, as mentioned in the comments. LIMIT #does have speed and general benefits in other cases, just not this one.
请注意，如评论中所述。LIMIT #在其他情况下确实具有速度和一般优势，但不是这种情况。
回答by OMG Ponies
The userid column is the primary key of the table. Is it good practice to use LIMIT 1 on the end of that mySQL statement? Or are there any speed benefits?
userid 列是表的主键。在该 mySQL 语句的末尾使用 LIMIT 1 是一种好习惯吗？或者有什么速度优势？
It is not good practice to use LIMIT 1at the end of the example - it's completely unnecessary, because the userid column is a primary key. A primary key means there is only one row/record in the table with that value, only one row/record will ever be returned.
LIMIT 1在示例的末尾使用不是一个好习惯——完全没有必要，因为 userid 列是一个主键。主键意味着表中只有一行/记录具有该值，只会返回一行/记录。
But the ultimate indicator is the explain plan:
但最终的指标是解释计划：
explain SELECT t.name FROM USERS t WHERE t.userid = 4
...returns:
...返回：
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
-----------------------------------------------------------------------------------------------------
1 | SIMPLE | users | const | PRIMARY | PRIMARY | 4 | const | 1 |
...and:
...和：
explain SELECT t.name FROM USERS t WHERE t.userid = 4 LIMIT 1
...returns:
...返回：
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).
没区别，没必要。在这种情况下它似乎被优化了（仅针对主键进行搜索）。
The LIMIT clause
限制条款
Using LIMITwithout an ORDER BYwill return an arbitrary row/record if more than one is returned. For example, using the "John Smith" scenario where 2+ people can have the name "John Smith":
如果返回多个，LIMIT不使用ORDER BY将返回任意行/记录。例如，使用“John Smith”场景，其中 2 个以上的人可以使用“John Smith”这个名字：
SELECT t.userid
FROM USERS t
WHERE t.first_name = 'John'
AND t.last_name = 'Smith'
LIMIT 1
...risks returning any of the possible useridvalues where the first name is "John" and the last name is "Smith". It can't be guaranteed to always return the same value, and the likelihood of getting a different value every time increases with the number of possible records.
...返回任何可能的userid值的风险，其中名字是“John”，姓氏是“Smith”。不能保证总是返回相同的值，每次得到不同值的可能性随着可能记录的数量增加而增加。
Personally I don't care for the use of LIMIT. The syntax isn't supported on Oracle, SQL Server or DB2 - making queries less portable. LIMIT is a tool to be used conservatively, not the first thing you reach for - know when to use aggregate and/or analytic functions.
我个人不关心 LIMIT 的使用。Oracle、SQL Server 或 DB2 不支持该语法 - 使查询的可移植性降低。LIMIT 是一种谨慎使用的工具，而不是您首先要使用的工具 - 知道何时使用聚合和/或分析函数。
