MySQL MIN/MAX 与 ORDER BY 和 LIMIT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/426731/
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
MIN/MAX vs ORDER BY and LIMIT
提问by nickf
Out of the following queries, which method would you consider the better one? What are your reasons (code efficiency, better maintainability, less WTFery)...
在以下查询中，您认为哪种方法更好？你的原因是什么（代码效率，更好的可维护性，更少的WTFery）...
SELECT MIN(`field`)
FROM `tbl`;
SELECT `field`
FROM `tbl`
ORDER BY `field`
LIMIT 1;
回答by Sean McSomething
In the worst case, where you're looking at an unindexed field, using MIN()requires a single full pass of the table. Using SORTand LIMITrequires a filesort. If run against a large table, there would likely be a significant difference in percieved performance. As a meaningless data point, MIN()took .36s while SORTand LIMITtook .84s against a 106,000 row table on my dev server.
在最坏的情况下，您正在查看未索引的字段，使用MIN()需要对表进行一次完整的传递。使用SORT和LIMIT需要文件排序。如果针对大表运行，感知性能可能会有显着差异。作为一个毫无意义的数据点，我的开发服务器上的 106,000 行表MIN()花费了 0.36 秒，SORT而LIMIT花费了 0.84 秒。
If, however, you're looking at an indexed column, the difference is harder to notice (meaningless data point is 0.00s in both cases). Looking at the output of explain, however, it looks like MIN()is able to simply pluck the smallest value from the index ('Select tables optimized away' and 'NULL' rows) whereas the SORTand LIMITstill needs needs to do an ordered traversal of the index (106,000 rows). The actual performance impact is probably negligible.
但是，如果您正在查看索引列，则很难注意到差异（在这两种情况下，无意义的数据点都是 0.00s）。综观解释的输出，但是，它看起来就像MIN()是能够简单地从指数（“选择表优化掉”和“NULL”行），而采摘的最小值SORT和LIMIT仍然需要需要做索引的有序遍历（106,000 行）。实际的性能影响可能可以忽略不计。
It looks like MIN()is the way to go - it's faster in the worst case, indistinguishable in the best case, is standard SQL and most clearly expresses the value you're trying to get. The only case where it seems that using SORTand LIMITwould be desirable would be, as msonmentioned, where you're writing a general operation that finds the top or bottom N values from arbitrary columns and it's not worth writing out the special-case operation.
看起来MIN()是要走的路 - 在最坏的情况下更快，在最好的情况下无法区分，是标准 SQL，并且最清楚地表达了您想要获得的价值。正如mson 所提到的，似乎唯一需要使用SORTand 的LIMIT情况是，您正在编写一个通用操作，从任意列中查找顶部或底部 N 个值，并且不值得写出特殊情况操作。
回答by Otávio Décio
SELECT MIN(`field`)
FROM `tbl`;
Simply because it is ANSI compatible. Limit 1 is particular to MySql as TOP is to SQL Server.
仅仅是因为它与 ANSI 兼容。限制 1 是 MySql 特有的，就像 TOP 是 SQL Server 一样。
回答by user650654
As msonand Sean McSomethinghave pointed out, MIN is preferable.
如MSON和肖恩McSomething指出，MIN是优选的。
One other reason where ORDER BY + LIMIT is useful is if you want to get the value of a different column than the MIN column.
ORDER BY + LIMIT 有用的另一个原因是，如果您想获取与 MIN 列不同的列的值。
Example:
例子：
SELECT some_other_field, field
FROM tbl
ORDER BY field
LIMIT 1
回答by mson
I think the answers depends on what you are doing.
我认为答案取决于你在做什么。
If you have a 1 off query and the intent is as simple as you specified, select min(field) is preferable.
如果您有一个 1 off 查询并且意图与您指定的一样简单，则最好选择 min(field)。
However, it is common to have these types of requirements change into - grab top n results, grab nth - mth results, etc.
但是，通常将这些类型的需求更改为 - 获取前 n 个结果、获取第 n 个 - 第 m 个结果等。
I don't think it's too terrible an idea to commit to your chosen database. Changing dbs should not be made lightly and have to revise is the price you pay when you make this move.
我不认为提交到您选择的数据库是一个太糟糕的想法。更改dbs 不应该掉以轻心，并且必须修改您在执行此操作时所付出的代价。
Why limit yourself now, for pain you may or may not feel later on?
为什么现在限制自己，因为以后可能会或可能不会感到疼痛？
I do think it's good to stay ANSI as much as possible, but that's just a guideline...
我确实认为尽可能保持 ANSI 是好的，但这只是一个指导方针......
回答by Charles Bretana
Given acceptable performance I would use the first one because it is semantically closer to the intent.
If the performance was an issue, (Most modern optimizers will probalbly optimize both to the same query plan, although you have to test to verify that) then of course I would use the faster one.
鉴于可接受的性能，我会使用第一个，因为它在语义上更接近意图。
如果性能是一个问题（大多数现代优化器可能会将两者优化为相同的查询计划，尽管您必须进行测试以验证），那么我当然会使用更快的。
