MIN和MAX的MySQL索引 [英] MySQL index for MIN and MAX

问题描述

任何人都可以从官方的MySQL 文档中澄清这一点

Could anyone clarify this point from the official MySQL documentation

使用索引...查找特定索引列key_col的MIN（）或MAX（）值。这是由预处理器优化的，该预处理器检查您是否在索引中的key_col之前出现的所有关键部分上使用WHERE key_part_N =常量。在这种情况下，MySQL对每个MIN（）或MAX（）表达式执行单个键查找，并用常量替换它。如果所有表达式都替换为常量，则查询立即返回。例如： SELECT MIN（key_part2），MAX（key_part2）FROM tbl_name WHERE key_part1 = 10;

所以在他们的例子中，他们试图找到名为key_col的col的最小值和最大值。他们使用复合索引key_part1_key_part2吗？ key_part2部分的索引是否代表key_col列？

So in their exampe they're trying to find the min and max values of the col called key_col. Are they using a composite index key_part1_key_part2? Does key_part2 part of the index represent key_col column?

我绝对不明白他们试图用常量替换表达式。你能帮我解决这个问题吗？

I absolutely don't understand what they're trying to say of replacing the expressions by the constant. Could you help me to work out this point?

更新：问题不在于如何 WHERE 语句或索引在最高级别上工作。我很困惑这个处理器的表达式在这一点上替换MIN和MAX。

UPDATE: The question is not about how the WHERE statement or indexes work on the "highest levels", if I can say that. I was confused by this processor's expression replacing thing in this certain point about MIN and MAX.

推荐答案

SELECT MIN(b), MAX(b) FROM tbl WHERE a = 12;

loves

INDEX(a, b)

两列，按此顺序。

查询在 a = 12 的索引中查找，获取第一个（a，b）配对获得 MIN（b）并获取最后一对获得 MAX（b） 。

The query looks in the index for a = 12, grabs the first (a,b) pair to get MIN(b) and grabs the last pair to get MAX(b).

关于用常量替换的陈述令人困惑，因为它太深入了解它如何首先弄清楚如何执行查询的细节（发生了这种情况）获取最小值和最大值），然后继续执行查询剩余的内容（没有剩下的内容）。

The statement about "replacing with a constant" is confusing because it is going too deep into the details of how it first figures out how to perform the query (which happens to get the min and max), then proceeds to execute what is left of the query (nothing is left).

更一般地说，最佳索引通常是 一个以所有 WHERE 列开头的列与 = 的常量相比较。之后它变得复杂，所以让我给出另一个提示：

More generally, the optimal index is usually one that starts with all the WHERE columns compared to constants with =. After that it gets complex, so let me give another tip:

覆盖索引是包含 SELECT中提到的所有列的索引（在我的示例中， a 和 b ）。

A "covering" index is one that has all the columns mentioned in the SELECT (a and b in my example).

对不起，我似乎没有比手册更清楚。

Sorry, I don't seem to be clearer than the manual.

这篇关于MIN和MAX的MySQL索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！