多指标和单指标

Question

自从我和MySQL一起工作多年以来，我有点惭愧地问这个问题，但是哦，好吧。

我有一张有两个字段的桌子，a和b。我将对它运行以下查询：

• SELECT * FROM ... WHERE A = 1;
• SELECT * FROM ... WHERE B = 1;
• SELECT * FROM ... WHERE A = 1 AND B = 1;

从性能的角度来看，对于至少一个查询，至少是下列索引配置中的一个较慢？如果是，请详细说明。

1. ALTER TABLE ... ADD INDEX (a); ALTER TABLE ... ADD INDEX (b);
2. ALTER TABLE ... ADD INDEX (a, b);
3. ALTER TABLE ... ADD INDEX (a); ALTER TABLE ... ADD INDEX (b); ALTER TABLE ... ADD INDEX (a, b);

谢谢(请注意，我们正在讨论的是非唯一索引)

Answer 1

是的，至少有一个病例要慢得多。如果只定义以下索引：

ALTER TABLE ... ADD INDEX (a, b);

..。然后查询SELECT * FROM ... WHERE B = 1;将不使用该索引。

使用复合键创建索引时，键列的顺序非常重要。建议尝试对键中的列进行排序，以增强选择性，其中最具选择性的列位于左侧--大部分键。如果不这样做，并将非选择性列作为键的第一部分，则可能根本不使用索引。(资料来源：优化Server复合索引的技巧)

Answer 2

一个索引的存在会减缓SELECT查询的速度，这是非常不可能的:它只是不会被使用。

理论上，优化器可以错误地在(a, b)上选择更长的索引，而不是在(a)上选择更长的索引来提供只搜索a的查询。

在实践中，我从未见过这样的情况：MySQL通常会犯相反的错误，当一个较长的索引存在时，使用一个更短的索引。

更新：

在您的示例中，下列任何一种配置都足以满足所有查询：

(a, b); (b)

或

(b, a); (a)

MySQL还可以在index_intersect中使用两个单独的索引，因此创建这些索引

(a); (b)

还将加快使用a = 1 AND b = 1的查询速度，尽管其程度低于上述任何一种解决方案。

你也可以在我的博客上读到这篇文章：

• 创建索引

更新2:

看来我终于明白你的问题了:)

ALTER TABLE ... ADD INDEX (a); ALTER TABLE ... ADD INDEX (b);

对a = 1和b = 1来说很好，对a = 1 AND b = 1来说也相当不错

ALTER TABLE ... ADD INDEX (a, b);

对a = 1 AND b = 1来说很好，对a = 1几乎很好，对于b = 1几乎很差

ALTER TABLE ... ADD INDEX (a); ALTER TABLE ... ADD INDEX (b); ALTER TABLE ... ADD INDEX (a, b);

非常适合所有三个查询。

Answer 3

SQL将选择最涵盖查询的索引。A，B上的索引将包括对案例1和3的查询，但不包括2(因为主索引列是A)。

因此，要涵盖所有三个查询，您需要两个索引：

ALTER TABLE ... ADD INDEX (a, b); ALTER TABLE ... ADD INDEX (b)