MIN/MAX与ORDER BY和LIMIT [英] MIN/MAX vs ORDER BY and LIMIT

问题描述

在以下查询中，您认为哪种方法更好?您的原因是什么(代码效率，更好的可维护性，更少的WTFery)...

Out of the following queries, which method would you consider the better one? What are your reasons (code efficiency, better maintainability, less WTFery)...

SELECT MIN(`field`)
FROM `tbl`;

SELECT `field`
FROM `tbl`
ORDER BY `field`
LIMIT 1;

推荐答案

在最坏的情况下，当您查看未索引字段时，使用MIN()要求对表进行一次完整的传递.使用SORT和LIMIT需要文件排序.如果针对大表运行，则预期的性能可能会存在显着差异.作为无意义的数据点，在我的开发服务器上，相对于106,000行表，MIN()花费了0.36s，而SORT和LIMIT花费了0.84s.

In the worst case, where you're looking at an unindexed field, using MIN() requires a single full pass of the table. Using SORT and LIMIT requires 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 SORT and LIMIT took .84s against a 106,000 row table on my dev server.

但是，如果您正在查看索引列，则很难注意到该差异(两种情况下无意义的数据点均为0.00s).然而，查看一下explain的输出，看来MIN()能够简单地从索引中选取最小值(选择经过优化的表"和"NULL"行)，而SORT和LIMIT仍然需要需要对索引进行有序遍历(106,000行).实际的性能影响可能微不足道.

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 SORT and LIMIT still needs needs to do an ordered traversal of the index (106,000 rows). The actual performance impact is probably negligible.

看起来是MIN()的解决之道-在最坏的情况下它更快，在最好的情况下没有区别，是标准SQL，并且最清楚地表达了您想要获得的价值.如 mson 所述，似乎唯一需要使用SORT和LIMIT的情况是，其中您正在编写一个从任意列中查找前N个值或后N个值的常规操作，因此不值得写出特殊情况的操作.

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 SORT and LIMIT would be desirable would be, as mson mentioned, 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/MAX与ORDER BY和LIMIT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！