mysql - mariadb 外键非选择性索引

Question

我正在对 mariaDB 上索引非常慢的问题进行故障排除。需要10多秒的时间。该表有超过 10M 行。where 子句有“where a=x and b=y”。b 列有一个选择性索引。列 a 有一个非选择性索引(只有 5 个不同的值)，但无法删除，因为 a 是外键。mariaDB 使用两个索引的交集，这使得它的性能比单独使用 b 上的索引差很多。我不知道如何解决这个问题:

• 我无法删除 a 上的索引，因为我想确保完整性
• 我不想取消整个数据库的相交索引优化
• 我不想使用索引提示，因为它不是标准的(尽管我不确定我还有什么其他选项)。

有什么想法吗？

Answer 1

a foreing key automatically adds an index...

嗯...在 MariaDB 中，是的。无论如何，这都不是标准功能；其他数据库(Oracle、DB2、PostgreSQL 等)不会自动创建索引来强制执行外键。

我无法完全理解您的问题，但在我看来您有两个索引，一个使用列 a ，另一个使用列 b 。而且......您正在尝试决定使用哪一个或如何组合它们。

使您提到的查询使用索引并不困难。如果 WHERE 条件为 where a=x and b=y，则这是通过创建索引 (b, a) 来大幅加速的完美情况，如下所示:

create index ux_mytable_b_a on my_table (b, a);

请注意，我使用了(b, a)，而不是(a, b)。这样，最具选择性的列被放置在最前面，并且查询运行哈希冲突和/或存储桶溢出的机会更少。

现在，关于您的观察:

mariaDB is using intersect of the 2 indices, which make it perform a lot worse...

请注意，使用组合的第三索引(包含两列)可以避免索引交叉。 MariaDB 将自动选择新索引，因为它比使用以前索引的任意组合要快。