数据库索引:为什么要配对

Question

我有一个包含多个索引的表，其中几个索引重复相同的列：

Index 1 columns: X, B, C, D
Index 2 columns: Y, B, C, D
Index 3 columns: Z, B, C, D

我在实践中对索引不是很了解，所以我想知道是否有人可以解释为什么X，Y和Z与这些相同的列配对。B是生效日期。C是特定生效日期B的该表的半唯一密钥ID。D是标识标识符C的该记录的优先级的序列。

为什么不创建6个索引，分别对应于X、Y、Z、B、C、D？

我想向另一个列T添加一个索引，但在某些上下文中，我将仅在T上查询，而在其他上下文中，我还将指定B、C和D列……那么我是应该像上面那样只创建一个索引，还是应该为T和(T，B，C，D)分别创建一个索引呢？

当我在谷歌上搜索索引的全面报道时，我并没有像预期的那样幸运。有没有什么资源可以让我得到一个完整的解释和大量的B树索引的例子？

Answer 1

索引的规则是，索引可用于筛选构成用于该索引的列的前缀的任何列的列表。

换句话说，当我们过滤X和B，或者X，B和C，或者只过滤X，或者全部四个时，我们可以使用索引1。

但是，我们不能使用索引来过滤“在中间”。这是因为索引的工作原理与连接每一行的那些列的值并对结果进行排序并不完全相同。如果我们知道我们正在寻找的东西是从什么开始的，我们就可以找出在索引中的什么位置来查找-就像做二进制搜索一样。

这就是为什么单一的索引是不好的:如果我们需要过滤B，C，D，以及X，Y和Z中的一个，我们需要三个索引；X，Y不能作为只过滤Y的索引，因为我们要查找的值的前缀-X-是未知的。

正如Daniel提到的，覆盖索引是重复B、C和D的一种可能的解释:即使D从未被过滤，我们可能需要的正是您在索引中看到的列，然后我们可以只从索引中读取列，而不是仅仅使用索引来定位行。

Answer 2

在这些索引中包含B、C和D的一个原因可能是对频繁使用的查询有一个covering index。当索引本身包含特定查询所需的所有数据字段时，您将拥有一个覆盖索引。

覆盖索引可以极大地加快数据检索速度，因为只有索引页，而不是数据页将用于检索数据。

下面是一个示例查询，其中index 1将是一个覆盖索引：

SELECT B, C, D FROM table WHERE X = '10'

Answer 3

您应该在(T，B，C，D)中创建它。

假设您在表中有两个带有索引的字段:A和B。当您在每一列上创建单独的索引时，并具有如下查询：

SELECT * FROM table WHERE A = 10 AND B = 20

发生的情况要么是：

1) DB创建两个中间结果集，一个包含A= 10的行，另一个包含B= 20的行。然后，它必须将这两个结果集合并为一个(并检查重复行)。

2) DB创建一个包含A= 10的行的结果集。然后，它必须手动遍历此中间结果集中的所有行，并签入B= 10的每个行。

但是，当您知道索引B依赖于索引A，并且您的查询在B之前使用A时，您可以为这两列创建一个索引：(A，B)

这意味着现在DB将首先找到A= 10的所有行，但因为B是同一索引的一部分，所以它可以使用相同的索引信息将结果集过滤成B也是20的行。它不必创建两个中间结果集并合并它们，或者只使用一个索引并手动扫描另一个。

DB可能还有其他方法来处理这些情况，这在很大程度上取决于实现。