MySQL - 相关级联查询集的最佳索引

Question

这是针对带有 MySQL 5.7 的 InnoDB。

我有一组 4 个相关的级联查询:

SELECT DISTINCT A, COUNT(*) FROM MYTABLE
    WHERE D IN ? AND A > ?
    GROUP BY A ORDER BY A LIMIT 100

SELECT DISTINCT B, COUNT(*) FROM MYTABLE
    WHERE A = ? AND D IN ? AND B > ?
    GROUP BY B ORDER BY B LIMIT 100

SELECT DISTINCT C, COUNT(*) FROM MYTABLE
    WHERE A = ? AND B = ? AND D IN ? AND C > ?
    GROUP BY C ORDER BY C LIMIT 100

SELECT E, F, G, H FROM MYTABLE
    WHERE A = ? AND B = ? AND C = ? AND D IN ? AND ID > ?
    ORDER BY ID LIMIT 100

最小的索引集是多少，以便所有查询都可以使用其中一个索引来按每个 WHERE 子句进行修剪，并使用它/它们来加速 ORDER BY？

根据我对复合索引的理解，我需要:

CREATE INDEX INDEX01 ON MYTABLE (D, A)

CREATE INDEX INDEX02 ON MYTABLE (A, D, B)

CREATE INDEX INDEX03 ON MYTABLE (A, B, D, C)

CREATE INDEX INDEX04 ON MYTABLE (A, B, C, D)

(ID为主键列)

这是正确的吗？

我想如果我重新排序 WHERE 子句，我可能可以只使用一个复合索引:

SELECT DISTINCT A, COUNT(*) FROM MYTABLE
    WHERE D IN ? AND A > ?
    GROUP BY A ORDER BY A LIMIT 100

SELECT DISTINCT B, COUNT(*) FROM MYTABLE
    WHERE D IN ? AND A = ? AND B > ?
    GROUP BY B ORDER BY B LIMIT 100

SELECT DISTINCT C, COUNT(*) FROM MYTABLE
    WHERE D IN ? AND A = ? AND B = ? AND C > ?
    GROUP BY C ORDER BY C LIMIT 100

SELECT E, F, G, H FROM MYTABLE
    WHERE D IN ? AND A = ? AND B = ? AND C = ? AND ID > ?
    ORDER BY ID LIMIT 100

那么我只需要:

CREATE INDEX INDEX01 ON MYTABLE (D, A, B, C)

正确吗？

但是，我认为以这种方式排序 WHERE 子句并不是最佳选择。总是尝试将“IN”操作和“">”操作作为最后两个WHERE子句的原因是:

1. 与“=”相比，MySQL 需要为“IN”(与多个值进行比较)做更多的工作，并且可能(由于我的数据集和我正在过滤的内容)，将修剪更少的行依本条规定。

2. “>”操作主要用于分页目的。即在某些情况下，由于该子句，几乎不会进行修剪。

我的理解正确吗？

Answer 1

请勿在同一查询中同时执行DISTINCT 和GROUP BY。由于聚合 (COUNT)，您可能需要 GROUP BY，因此抛弃 DISTINCT。

对于GROUP BY x ORDER BY x LIMIT 100，以下可能有帮助:

INDEX(x)  -- or INDEX(x, ...)

所以，包括这一点，以防万一。我的意思是优化器可能选择使用索引来处理GROUP BY + ORDER BY + LIMIT，而不是查看WHERE 。如果它决定使用 WHERE，那么...

WHERE D IN ? AND A > ?
INDEX(D, A)

可以跳过(“MRR”)D 并扫描 A，但它不能消耗任何 GROUP BY 或 ORDER BY。

WHERE A = ? AND D IN ? AND B > ?
INDEX(A, D, B)

将所有“=”内容放在索引的前面。其余逻辑如上。

WHERE A = ? AND B = ? AND D IN ? AND C > ?
INDEX(A, B, D, C) or INDEX(B, A, D, C)

(相同的逻辑)

WHERE A = ? AND B = ? AND C = ? AND D IN ? AND ID > ?
INDEX(A,B,C, -- in any order, then
      D, ID) -- at end, in this order.

因此，对于这组 4 个语句，我建议按照给定的顺序使用 4 或 5 个索引:

INDEX(D, A)
INDEX(A, D, B)
INDEX(B, A, D, C)  -- I picked that one to get one starting with B
INDEX(c, B, A, D, ID)
INDEX(ID)  -- but don't add if you already have `PRIMARY KEY(ID)`

作为奖励，在这些索引中，前 3 个 SELECT 具有“覆盖”索引，这为您带来了额外的奖励。最后一个 SELECT 需要一个 9 列索引来“覆盖”；这太多了。

WHERE 中 AND 的顺序没有区别。所以，我想我可以忽略你剩下的问题。

(警告:在 5.6 左右之前，蛙跳并不存在，因此“最佳”索引集可能是其他东西。)