mysql - JOIN 以奇怪的顺序完成；搞乱 ORDER BY？

Question

假设我有三个表 - 用户、服务器和付款。每个用户可以拥有多个服务器，每个服务器可以进行多次支付。假设我想查找最近的付款并获取有关这些付款所附加的服务器/客户的信息。这是一个可以执行此操作的查询:

SELECT *
FROM payments p
JOIN customers c ON p.custID = c.custID
JOIN servers s ON s.serverID = p.serverID
WHERE c.hold = 0
    AND c.archive = 0
ORDER BY p.paymentID DESC
LIMIT 10;

问题是，当我对此查询运行 EXPLAIN 时，我得到以下信息:

id   select_type   table   type   possible_keys            key                key_len   ref                 rows     Extra
1    SIMPLE        c       ref    PRIMARY,hold_archive     hold_archive       3         const,const         28728    Using where; Using index; Using temporary; Using filesort
1    SIMPLE        p       ref    custID                   custID             5         customers.custID    3        Using where
1    SIMPLE        s       eq_ref PRIMARY                  PRIMARY            4         payments.serverID   1        Using index

问题是查询需要一段时间才能运行。如果我删除 ORDER BY，速度会提高 10 倍。但我需要 ORDER BY。这是我删除 ORDER BY 时的解释:

id   select_type   table   type   possible_keys            key                key_len   ref                 rows     Extra
1    SIMPLE        c       ref    PRIMARY,hold_archive     hold_archive       3         const,const         28728    Using where; Using index
1    SIMPLE        p       ref    custID                   custID             5         customers.custID    3        Using where
1    SIMPLE        s       eq_ref PRIMARY                  PRIMARY            4         payments.serverID   1        Using index

所以这里最大的区别是“额外”列中缺少“使用临时”和“使用文件排序”。

在这种情况下，原因似乎是我正在执行 ORDER BY 的列不是 EXPLAIN 中的第一列。

另一个观察结果。如果我删除其中一个 WHERE 子句(同时保留 ORDER BY)，它的速度也会类似，但我需要两个 WHERE 子句。这是一个解释的例子:

id   select_type   table   type   possible_keys            key                key_len   ref                 rows     Extra
1    SIMPLE        p       index  custID,serverID          PRIMARY            4         NULL                10       Using where
1    SIMPLE        c       eq_ref PRIMARY,hold_archive     PRIMARY            4         payments.custID     1        Using where
1    SIMPLE        s       eq_ref PRIMARY                  PRIMARY            4         payments.serverID   1        Using index

此处，ORDER BY 列正在 EXPLAIN 的第一列上完成。但是，为什么 MySQL 会重新排列表的 JOINed 顺序，我怎样才能做到这一点，这样它就不会这样做呢？您可以在 MySQL 中强制建立索引，但这似乎没有帮助..

有什么想法吗？

Answer 1

速度快 10 倍 - 它可以比“查找所有可能的行，对它们进行排序，然后交付 10 行”更快地查找“任意 10 行”。

让 WHERE 和 ORDER BY 命中不同的列很难优化。

hold=0 和 archive=0 的付款比例是多少？听起来比例很小？每个表有多少行？

还有什么需要INDEX(hold, archive)吗？如果没有，就摆脱它。它似乎只是在这里造成麻烦。

如果 hold=0 和 archive=0 很常见，那么您希望像第三个 EXPLAIN 一样执行——即扫描 付款 按降序排列。由于其中大多数与 WHERE 匹配，因此通常需要命中不超过 10 行才能找到 10 个匹配行。

另一个解决方案(除了删除索引之外)是在查询中将 JOIN 更改为 STRAIGHT_JOIN。这告诉优化器您更了解，应该首先扫描付款，然后扫描客户。如果我之前的段落适用，那就很有效。

但是，如果您查找 archive=1，查询就会搞砸(因为速度很慢)。