复合索引是如何工作的？

Question

我曾经创建过表上的复合索引（对数学人来说是指标），并且有一个假设它们的工作方式。我只是好奇我的假设是否正确。

我假设当你列出索引的列顺序时，你同时也在指定这些索引将如何分组。例如，如果你有列a、b和c，并按照同样的顺序指定索引a ASC、b ASC和c ASC，那么结果索引将本质上成为每个“组”中a的多个索引。

这个假设正确吗？如果不正确，结果索引会是什么样子？

- Joe Phillips

---

请参考此处：SQL Server 覆盖索引 以获取良好的解释。 - SQLMenace

这对我来说看起来像是一个复合索引 CREATE NONCLUSTERED INDEX idx_PeopleTest_Name_Id_FavoriteColor ON PeopleTest(Name, Id, FavoriteColor) - SQLMenace

Answer 1

复合索引的工作方式与常规索引类似，只是它们具有多值键。

如果您在字段(a,b,c)上定义了一个索引，则记录首先按a排序，然后按b排序，最后按c排序。

示例：

| A | B | C |
-------------
| 1 | 2 | 3 |
| 1 | 4 | 2 |
| 1 | 4 | 4 |
| 2 | 3 | 5 |
| 2 | 4 | 4 |
| 2 | 4 | 5 |

- Rik

---

请注意，索引以B树形式存储，因此(a,b,c)索引将有助于搜索(a)和(a, b)，但不会对其他搜索(如(b)或(b,c))产生帮助。 - geek-merlin

Answer 2

复合索引就像字典中的普通字母索引，但它覆盖了两个或更多字母，就像这样:

AA - page 1
AB - page 12

表格行首先按照索引中第一列的顺序排序，然后按照第二列等等。

当您通过两列或第一列进行搜索时，这是有用的。如果您的索引如下所示：

AA - page 1
AB - page 12
…
AZ - page 245
BA - page 246
…

你可以将它用于搜索2个字母（= 2列在一个表中），或者像一个单独的字母索引：

A - page 1
B - page 246
…

请注意，对于字典而言，页面本身是按字母顺序排列的。这是聚集索引的一个例子。

在普通的、非聚集索引中，页面的引用是有序的，就像历史书一样：

Gaul, Alesia: pages 12, 56, 78
Gaul, Augustodonum Aeduorum: page 145
…
Gaul, Vellaunodunum: page 24
Egypt, Alexandria: pages 56, 194, 213, 234, 267

当你需要按照两个或更多列进行排序时，复合索引也可以被使用。在这种情况下，DESC子句可能会很有用。

请参阅我博客中有关在复合索引中使用DESC子句的文章：

• 降序索引

- Quassnoi

Answer 3

最常见的索引实现方法使用B树来允许较快的查找，以及相对快速的范围扫描。这里无法详细解释，但是这是关于B树的维基百科文章。点击这里。并且你是正确的，在创建索引时声明的第一列将成为最终B树的高位列。

在高位列上进行搜索相当于进行范围扫描，B树索引对于这样的搜索非常有用。最容易通过类比于旧的卡片目录来理解，这些目录仍然存在于未转换为在线目录的图书馆中。

如果您要查找所有姓氏为“Clemens”的作者的卡片，则只需打开作者目录，很快就会找到一个前面写着“CLE-CLI”的抽屉。那就是正确的抽屉。现在您可以在该抽屉中进行某种非正式的二进制搜索，以快速找到所有带有“ Clemens，Roger”或“ Clemens，Samuel”字样的卡片。

但是假设您想要查找所有名字是“Samuel”的作者的卡片。现在你有麻烦了，因为这些卡片没有在作者目录中聚集在一起。数据库中的复合索引也会出现类似的现象。

不同的数据库管理系统在检测索引范围扫描以及准确估计其成本方面的优化器智能程度有所不同。并非所有索引都是B树。您需要阅读您特定DBMS的文档以获取真实信息。

- Walter Mitty

---

谢谢，我一直在认真思考这个问题，但没有明确的答案。 "对高阶列的搜索相当于范围扫描"，但是如果索引涵盖2列，并且两列都在范围查询中指定，例如“ColumnA < threshold1 AND columnA > threshold 2 AND columnB < threshold3 AND columnB > threshold4”，那么似乎Oracle必须在B树上进行多次范围扫描，对吗？那么，如果我们在复合索引中有许多列，我们就必须进行许多范围扫描，索引的有效性就会大大降低。 - teddy teddy

在我的回答中，我想表达的是ColumnA = value等于一个范围扫描，因为可能有许多条目都具有ColumnA的正确值，但ColumnB的值不同。你所描述的情况则完全不同。它仍然可能是一个范围扫描，但范围可能涉及索引中大部分条目。范围越大，索引节省的越少。如果使用索引的价值降低得太低，优化器可能会选择不同的策略。 - Walter Mitty

Answer 4

结果索引将是单个索引，但具有复合键。

KeyX = A,B,C,D; KeyY = 1,2,3,4;

索引 KeyX，KeyY 实际上将是：A1,A2,A3,B1,B3,C3,C4,D2

因此，在需要通过 KeyX 和 KeyY 查找内容时，速度会很快，并且使用单个索引。例如 SELECT ... WHERE KeyX = "B" AND KeyY = 3。

但重要的是要理解：WHERE KeyX = ? 请求将使用该索引，而 WHERE KeyY = ? 将不会使用此类索引。

- Mash

---

最后一个断言在Oracle上不是正确的。请参见https://dev59.com/CnVD5IYBdhLWcg3wL4mM（忽略不正确的已接受答案）。 - Hobo

@Hobo：1. 在大多数关系型数据库管理系统中，跳过扫描不可用。2. 在大多数情况下，这将非常缓慢，仅比简单的表扫描快一点（有时甚至更慢），只有在非常罕见的情况下才会真正有所帮助。Oracle 中没有魔法。只需记住一个好规则-如果您的条件未仅使用索引的顶级列，则不要依赖复合索引（创建大型复合索引是非常常见的错误）。 - Mash

@Mash 理解了。绝对不是说跳过扫描就是万能的解决方案，只是在某些情况下 KeyY = ? 会 使用索引。认为最好提供尽可能完整的信息。至于速度，希望优化器能选择适当的方法（虽然，如果有疑问，始终要进行测量而不是假设）。 - Hobo

@Hobo。我认为，既然这是一个初学者的问题，最好不要一开始就给出完整的图片，而是先给出较小的图片。至于优化器，你知道，许多研究表明，Oracle优化器的人工智能比它应该的更聪明，实际上，Oracle 10在大多数情况下比Oracle 9慢，只是因为它在理论上太聪明了。 - Mash

Answer 5

哪些查询可以通过复合索引加速，哪些不能

一般来说，复合索引只能显著加速最后一列的不等式。

例如，一个x-y复合B树索引可以：

• 高效加速：
  • x = 1 and y = 2：两列都相等
  • x = 1 and y > 2：第一列相等，第二列不等
• 速度提升有限：
  • x > 1 and y > 2：两列都不等，包括第一列
  • x > 1 and y = 2：第一列不等
  • y > 2：这相当于x > -无穷大 and y > 2，所以对于复合B树索引来说，这是最糟糕的情况。然而，这种情况可以通过B树索引高效解决。

如果您需要在两列上使用不等式，那么您应该了解一些空间索引，例如R树。我已经提供了更多详细信息，链接在这里：什么是空间索引，何时应该使用它？

例如，考虑以下索引：

x|y

1|1
1|2
1|3
1|4
1|5
1|6

2|2
2|2
2|2
2|3
2|3
2|3
2|4
2|4
2|4

4|2
4|2
4|2
4|3
4|3
4|3
4|4
4|4
4|4

5|3
5|4
5|5
5|6
5|7
5|8

只有当索引中的所有行都是相邻的时，索引才能显著加快查询速度。

所以，例如，如果我们想要：

x = 5 and y > 4

我们得到了连续的内容：

5|5
5|6
5|7
5|8

但是如果我们想要：

x > 0 and y > 4

结果集将不是连续的，这意味着一堆无用的扫描。

1|5
1|6
5|5
5|6
5|7
5|8

- Ciro Santilli OurBigBook.com