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 需要文件排序.如果针对大表运行，感知性能可能会有显着差异.作为一个轶事数据点，MIN() 花费了 0.36 秒，而 SORT 和 LIMIT 在我的开发服务器上的 106,000 行表中花费了 0.84 秒.

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 an anecdotal data point, MIN() took .36s while SORT and LIMIT took .84s against a 106,000 row table on my dev server.

但是，如果您正在查看索引列，则很难注意到差异(在这两种情况下，无意义的数据点都是 0.00 秒).然而，查看解释的输出，看起来 MIN() 能够简单地从索引中提取最小值(选择优化掉的表"和NULL"行)，而 MIN()>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 并且最清楚地表达了您想要获得的值.似乎使用 SORT 和 LIMIT 的唯一情况是，因为 mson 提到，您正在编写一个从任意列中查找顶部或底部 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屋！