performance - Maria DB INDEX 选择 - 为什么 maria 选择次优索引？

Question

我有一个相当大的表(数百万行)，在 MariaDB(InnoDB，5.5.48-MariaDB-1~precise-wsrep)上运行，假设我的表结构如下

[
ID,
Field A,
Field B,
Field C,
Field D
]

我在这个表上有 3 个索引:

- PRIMARY[ID]
- INDEX 1 -> [A,B,C]
- INDEX 2 -> [A, D]

我试图优化的查询如下

SELECT * FROM table
WHERE (a = val1) AND (B NOT IN ([val2, val3])) AND (C BETWEEN val4 AND val5)
ORDER BY ID ASC LIMIT 50 OFFSET 100

这个查询应该自然适合我的 INDEX 1 对吗？但是 Maria 更喜欢使用 PRIMARY INDEX，这基本上意味着全表扫描(导致 40 秒的查询......)。

当我从此查询中删除 ORDER 或 LIMIT(或两者)时，Maria DB 能够选择 INDEX 2，这显然优于 PRIMARY。

问题 1 -> 当 ORDER BY 和 LIMIT 结合使用时，为什么 Maria 会回退到 PRIMARY INDEX ？

我决定通过禁止使用 PRIMARY 来稍微调整我的查询。

SELECT * FROM table IGNORE INDEX(`PRIMARY`)
WHERE (a = val1) AND (B NOT IN ([val2, val3])) AND (C BETWEEN val4 AND val5)
ORDER BY ID ASC LIMIT 50 OFFSET 100

结果 -> 对我的第一次优化很满意，这个 40 秒的查询现在需要 1 秒，但仍然......

问题 2 -> 为什么 MariaDB 选择 INDEX 2 ？

当我强制 Maria 使用 INDEX 1 时，查询下降到 100 毫秒延迟(快 10 倍)，因此，我还没有完全满意......

感谢您的帮助乡亲:)

Answer 1

它是 B NOT IN (val2, vl3)不能使用索引的部分，或者没有你想象的那么有效。我建议你创建这个索引:

(A, C, B)