查询执行所涉及的步骤

Question

考虑下表：

name     salary
-----   --------
a       100
b       200
c       300
d       300   -- note the duplicate max

现在，我们希望使用以下查询计算第二个最高工资：

SELECT max(salary)
FROM emptable
WHERE salary < (SELECT max(salary)
                FROM emptable);

那么，MySQL将如何确定此查询的结果？例如，我假设首先它将执行where子句中的子查询并确定最大工资，将其称为M1 (将是400)，并将其存储在某个变量中。然后，它将从原始表中找到最大工资，即400，但不满足where子句中的约束(即，将其与M1进行比较)。现在，它将制作包含此(400)值的元组的原始表的副本，因为它不满足约束，并重复这个过程，直到找到所需的结果。

很可能我错了，但我写上面的过程只是为了澄清我的问题。

Answer 1

WHERE子句中出现的子查询与外部查询不相关，因此优化器很可能只计算一次，然后缓存结果。在此之后，最大值将用于筛选具有该最大值的一个或多个记录。然后它只是一个典型的max查询。为了确认这一点，或者找到我遗漏的东西，您可以在查询中运行EXPLAIN。

顺便说一句，您还可以使用LIMIT和OFFSET编写这个查询。

SELECT DISTINCT salary
FROM emptable
ORDER BY salary DESC
LIMIT 1
OFFSET 1;

这还应返回排名第二的薪资(即，对于同一薪资中有多个薪资而言是稳健的)。但是，这种方法可以很容易地找到任何级别的工资，而子查询方法并不能很好地扩展。

Answer 2

查询优化器可以自由地进行任何优化(基于索引/数据分发/.)。您应该始终检查实际执行计划：

explain
SELECT max(salary)
FROM emptable
WHERE salary < (SELECT max(salary)
                FROM emptable);

输出：

┌────┬─────────────┬──────────┬──────┬───────────────┬──────┬─────────┬──────┬──────┬─────────────┐
│ id │ select_type │  table   │ type │ possible_keys │ key  │ key_len │ ref  │ rows │    Extra    │
├────┼─────────────┼──────────┼──────┼───────────────┼──────┼─────────┼──────┼──────┼─────────────┤
│  1 │ PRIMARY     │ emptable │ ALL  │ null          │ null │ null    │ null │    4 │ Using where │
│  2 │ SUBQUERY    │ emptable │ ALL  │ null          │ null │ null    │ null │    4 │             │
└────┴─────────────┴──────────┴──────┴───────────────┴──────┴─────────┴──────┴──────┴─────────────┘

加上INDEX的薪水：

create index idx ON emptable(salary);

┌────┬─────────────┬───────┬──────┬───────────────┬──────┬─────────┬──────┬──────┬──────────────────────────────┐
│ id │ select_type │ table │ type │ possible_keys │ key  │ key_len │ ref  │ rows │            Extra             │
├────┼─────────────┼───────┼──────┼───────────────┼──────┼─────────┼──────┼──────┼──────────────────────────────┤
│  1 │ PRIMARY     │ null  │ null │ null          │ null │ null    │ null │ null │ Select tables optimized away │
│  2 │ SUBQUERY    │ null  │ null │ null          │ null │ null    │ null │ null │ Select tables optimized away │
└────┴─────────────┴───────┴──────┴───────────────┴──────┴─────────┴──────┴──────┴──────────────────────────────┘

DBFiddle演示

Answer 3

你想要“密集排名”。

如果您有一个大表，下面的公式可能会得到更好的优化，因此更快：

SELECT salary
    FROM emptable
    WHERE salary < (SELECT max(salary)
                    FROM emptable)
    ORDER BY salary DESC
    LIMIT 1,1;

它需要

INDEX(salary)