使用MySQL“LIMIT 1”有什么意义吗?当查询索引/唯一字段时?
例如,我正在查询一个我知道将是唯一的并且已建立索引(例如主键)的字段。因此我知道这个查询只会返回 1 行(即使没有 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
是否会提高查询执行时间?
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
or only update 1 row
UPDATE tablename SET somefield='somevalue' WHERE tablename.id=123 LIMIT 1
Would adding the LIMIT 1
improve query execution time if the field is indexed?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
在主键/唯一字段上查询时使用MySQL“LIMIT 1”有什么意义吗?
当使用违反主键或唯一约束的过滤条件进行查询时,使用 LIMIT 1 并不是一个好习惯。主键或唯一约束意味着表中只有一行/记录具有该值,并且只会返回一行/记录。在主键/唯一字段上使用
LIMIT 1
是矛盾的 - 稍后维护代码的人可能会错误地理解其重要性和重要性。第二次猜测你的代码。但最终的指标是解释计划:
...returns:
...and:
...returns:
结论
没有区别,没有必要。在这种情况下,它似乎已被优化(仅针对主键进行搜索)。
索引字段怎么样?
索引字段不保证被过滤的值的唯一性,可能会出现多次。因此,假设您想返回一行,
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 haveLIMIT 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:
...returns:
...and:
...returns:
Conclusion
No difference, no need. It appears to be optimized out in this case (only searching against the primary key).
What about an indexed field?
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.如果该字段上有唯一索引,我根本看不到执行时间的改善 - 即使按照微优化标准,这也是一个延伸。
然而,我可以看到它可能掩盖其他问题。假设您向这些查询添加 LIMIT 1,然后您正在查询的字段会以某种方式丢失其唯一索引,并且数据库会获取具有相同值的多行。这段代码将继续愉快地运行 - 我认为您可能希望快速失败,以意识到(并修复)更大的潜在问题。
在我自己的数据库接口代码中,我有一个方法 queryOneRow() ,它运行一些 SQL 并在返回多于一行时抛出异常。对我来说,在应用程序层显式处理这个问题而不是在 SQL 中防御性地处理这个问题最有意义。
If the field has a unique index on it, I can't see this improving execution time at all - it's a stretch even by micro-optimization standards.
However, I can see it potentially masking other problems. Say you add LIMIT 1 to these queries, and then somehow the field upon which you're querying loses its unique index and the db gets multiple rows with the same value. This code will keep chugging along happily - I'd think you might want to fail fast instead, to become aware of (and fix) the bigger underlying problem.
In my own db interface code, I have a method queryOneRow() that runs some SQL and throws an exception if it gets more than one row back. It makes the most sense to me to handle this explicitly at the application layer rather than defensively in SQL.
在大多数情况下,当我查询唯一字段时,我仍然使用
LIMIT 1
。我这样做主要是因为我想确保无论任何人对数据库/表做什么,我的查询都不会返回或操作多于一行。通常,这还应该涵盖动态畸形查询弄乱整个数据库的情况(不 - 我不是在谈论 SQL 注入)。
In most of the cases where I query on unique fields I still use the
LIMIT 1
. I mostly do this because I want to ensure that no matter what anyone does with the database/tables my query will never return or manipulate more than one row.Typically this should also cover the case of a dynamically malformed query to mess up the complete database (and no - I'm not talking about SQL injection).
由于查询在任何给定时刻只会返回一条记录;那么添加
LIMIT 1
不会增加你的执行时间。我不确定 MySQL 使用哪种算法来执行 LIMIT 1 请求。然而讽刺的是,它可能使用比较表达式或迭代器仅返回 1 条记录 - 在这种情况下,添加
LIMIT 1
实际上可能延迟执行时间多条指令。Since the query will only return, in any given moment, one record; then the addition of
LIMIT 1
will not increase your execution time.I'm not sure which algorithm MySQL uses in order to perform
LIMIT 1
requests. However ironic, it may use comparison expressions or iterators to return only 1 record – in which case, the addition ofLIMIT 1
may actually delay execution time by several instructions.简短回答:
如果您正在搜索索引唯一字段:没有差异。
但是请注意:
如果您正在搜索NOT unique字段:您将受益于添加
LIMIT 1
到查询。即使您在该字段上有索引。例如,如果您在
email
字段上没有唯一索引,速度也会更快
那么即使两个查询都返回一条记录,
。这是因为MySQL一旦找到一条记录就会停止搜索更多记录。
Short answer:
If you are searching against indexed unique field: there is no diffrence.
However note, that:
If you are searching against NOT unique field: you will benefit from adding
LIMIT 1
to query. Even if you have index on that field.For example if you don't have unique index on
email
fieldwill be faster than
even though both queries will return one record.
It's because MySQL will stop searching for more records, once it finds one record.