performance - Maria DB INDEX selection - 为什么 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 能够选择明显优于 PRIMARY 的 INDEX 2。

问题 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 倍），因此，我还不完全满意......

感谢您的帮助人们:)

标签： performanceoptimizationindexingmariadb

Answer 1

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

(A, C, B)