LIMIT MySQL php的最佳实践

I was wondering if there is any point of using LIMIT in a PHP PDO MySQL query, when the result would come back with 1 row anyway (Unless something horrible went wrong). For example, given a table:

我想知道在PHP PDO MySQL查询中是否有使用LIMIT的任何意义,当结果将返回1行时(除非出现可怕的错误)。例如,给出一个表:

CREATE TABLE users (
    ID Int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username varchar(25)
);

Would there be any performance difference between the following:

以下各项之间是否存在任何性能差异:

$stmt=$db->prepare("SELECT username FROM users WHERE ID=:ID");

$stmt=$db->prepare("SELECT username FROM users WHERE ID=:ID LIMIT 1");

EDIT: So I done my own little investigation, and it seems that the difference is in the nanoseconds Lol, and neither method being faster than the other. I just put each statement in a 10000x loop, and recorded a few runs ... On a laptop too, so not scientific or anything Lol ...

编辑:所以我做了我自己的小调查,似乎差异在纳秒Lol,并且两种方法都没有比另一种更快。我只是将每个语句放在10000x循环中,并记录了几次运行...在笔记本电脑上,所以不科学或任何东西大声笑...

3 个解决方案

#1

Not much for this query, since it is on a primary key.

这个查询并不多,因为它位于主键上。

But if you are doing a query that is less optimized, then yes there would be some small difference between a query with a limit and one without.

但是如果你正在进行一个不太优化的查询,那么是有一个限制的查询和一个没有限制的查询之间会有一些小的区别。

Also peace of mind knowing that your query will never return more rows than you expect.

也知道您的查询永远不会返回比预期更多的行,这也让您高枕无忧。

#2

Think about how many queries are being called per page and how many users are loading the page at any one time.

考虑每页调用多少查询以及一次加载页面的用户数。

Your may only save a few nanoseconds on a single query but they all add up.

您只能在单个查询中保存几纳秒,但它们都会加起来。

#3

With the clause LIMIT 1 (or more generic LIMIT offset, length) you are guaranteed to return results immediately once the LIMIT reached.

使用LIMIT 1(或更通用的LIMIT偏移,长度)子句,一旦达到LIMIT,您就可以立即返回结果。

Without such a limit the execution may continue even if no more matches found - so it is less performance effective. The more complex SQL query, the more apparent difference in execution time.

如果没有这样的限制,即使找不到更多匹配项,执行也可能继续 - 因此性能效果较差。 SQL查询越复杂,执行时间就越明显。

In general, it is always considered a good practice to apply reasonable LIMIT clause.

一般而言,应用合理的LIMIT条款始终被视为一种良好的做法。

#1