理解索引中的多列是什么意思？

Question

假设我有一个名为“table”的表，它有3个列，a、b和c。

在a、b列上有非聚集索引是什么意思？

在a、b列上的非聚集索引与在b、a列上的非聚集索引相同吗？（请注意顺序。）

此外，在列a上的非聚集索引是否与在a、c上的非聚集索引相同？

我看了sqlserver performance网站上的dmv脚本，它会告诉你是否有重叠的索引，我认为它是在说在a上有一个索引就等于a、b，因此它是多余的。索引是否是这样的？

最后一个问题是为什么聚集索引放在主键上。大多数情况下，主键不会被查询，那么聚集索引应该放在最常查询的列上。我可能错过了一些东西，比如将其放在主键上可以加快连接速度？

很好的解释。我应该将其转换为wiki并更改标题索引说明吗？

- Xaisoft

Answer 1

这篇文章开始是介绍索引的概念，但我认为你仍会发现它有用。尤其是前两段对你的问题有所涉及。

聚集索引 vs 非聚集索引

这指的是表在磁盘上的物理排列方式。聚集索引通过根据索引定义对表中的物理页面和行进行排序来工作。非聚集索引使用磁盘上的单独位置存储索引中的列的副本(仅这些列)，以及源记录的指针。因此，聚集索引通常更快，因为它们始终涵盖查询中需要的任何数据。但是，您只能获得其中一个，否则您将复制整个表。还要知道，向表添加非聚集索引实际上会减慢写入操作，如插入和更新，因为数据库必须重建索引或至少重建索引中的某些页面。

索引顺序

对于索引(A,B)与(B,A)不同。在第一种情况下，索引中的记录首先按列A排序，而只有当您具有A的重复值时，列B才影响索引顺序。仅使用列B值搜索索引是无用的，因为您仍然必须扫描索引中的每个记录以查找所有匹配的B值。在第二种情况下，相反发生:记录首先按列B排序，只有当您具有A的重复值时，列A才有帮助。仅使用列A值搜索该索引也无济于事。

覆盖索引

有时数据库可以完全通过索引满足查询的要求。在这种情况下，该索引被称为该查询的“覆盖”索引。这是有利的，因为索引通常缓存在内存中，因此数据库可能根本不必访问磁盘。为了理解这一点，请想象一个索引在 (A,B) 上，其中对于 A 很少有重复值。将 A 包含在索引中似乎很浪费，除非您经常运行查找特定 A 值并且还需要 B 的查询。现在，该索引将节省大量返回原始表以检索 B 的工作。

选择性

选择性是介于 0 和 1 之间的值（通常表示为百分比），它告诉您索引中每个值有多么独特。选择性为 1 或 100% 表示没有重复项。选择性为 0 表示列中只有一个值。通常，较高的选择性（接近 1）对索引更好。

为了说明这一点，考虑一下低选择性指数会发生什么。例如，您尝试通过向具有10000条记录的表中的位列添加索引来加快查询速度。在这种情况下（假设分布均匀），选择性为0.5。运行查询后，索引返回5000条记录。但是这些记录中的每一条仍然必须返回到原始表中，并且由于索引顺序与表顺序不匹配，因此它必须对表进行很多单独的查找。相反，直接扫描整个表以检索所需数据可能更快。

选择性解释了为什么您希望在主键上进行聚集。由于聚集索引告诉数据库如何对表进行排序，因此在这里选择任何小于100％的选择性意味着查询将不得不更频繁地扫描表。在主键上进行聚集可以获得完美的选择性。由于该主键通常用作其他索引中的记录指针，因此您希望将其保持尽可能小（即，整数标识列）。

这里有一篇关于选择性和索引的好文章：
http://www.akadia.com/services/ora_index_selectivity.html 可搜索

这指的是数据库是否能够使用特定的过滤器进行索引。

正如我们展示的那样，索引通常通过先将数据按特定顺序排序来工作，以便对该索引进行查找时可以使用像基于树的搜索这样高效的方法，而不是较慢的线性搜索。任何无法有效与排序数据进行比较的内容都不能与索引一起使用。一个很好的例子是LIKE操作符。这是可搜索的：

SELECT * FROM [Table] WHERE [Column] LIKE @Value + '%'

但这并不是可搜索的（sargable）：

SELECT * FROM [Table] WHERE [Column] LIKE '%' + @Value + '%'

一些使过滤器不可优化的因素是非确定性函数（比你想象的更多）。

按列索引

我经常看到的一个常见错误是为表中的每个列创建单独的索引。例如，有人会采取一个具有列（A，B，C，D）的表，并创建四个单独的索引，分别为A，B，C，D，认为他们现在已经对每个列进行了索引，所以每个查询都应该很快。实际上，出于我已经解释过的原因，这很少有帮助，而且通常会使情况变得更糟，因为数据库现在需要为每个数据更改更新这些索引。

- Joel Coehoorn

---

很好的解释。@Xaisoft - 确保现有的索引与您查询数据的方式匹配。查看表连接或where语句，并将它们与这些索引进行比较。 - Michael La Voie

Answer 2

在(a, b)上创建的非聚集索引是表的一部分“副本”，其行按照a，然后按照b排序，并包含对原始行的引用。

它有助于运行以下查询：

SELECT  *
FROM    mytable
WHERE   a = @A
        AND b = @B

, this:

SELECT  *
FROM    mytable
ORDER BY
        a, b

, this:

SELECT  *
FROM    mytable
WHERE   a = @A
ORDER BY
        b

例如，我们有这样的一个表格：

和许多其他的。

比如说，我们有这样一张表：

#       col1    col2    col3
1       1       1       1
2       1       4       8
3       7       2       3
4       3       3       9
5       8       9       4
6       2       2       7
7       5       3       5
8       3       9       4

如果我们在 (col2, col3) 上创建索引，它将包含以下数据：

col2    col3    #
1       1       1
2       3       3
2       7       6
3       5       7
3       9       4
4       8       2
9       4       5
9       4       8

即首先按col2排序，其次按col3排序，最后按行引用排序。

很容易看出，这个索引也是col2的索引（按(col2, col3)排序意味着仅按col2排序）。

顺序很重要，因此如果我们在(col3, col2)上创建索引，则行将以不同的方式排序：

col2    col3    #
1       1       1
2       3       3
9       4       5
9       4       8
3       5       7
2       7       6
4       8       2
3       9       4

这个索引也是基于col3的。

如果我们想要在(col2, col3)范围内查找行，只需从有序数据中取出一个切片：

SELECT  col2, col3
FROM    mytable
WHERE   col2 BETWEEN 2 AND 3

col2    col3    #
1       1       1
----
2       3       3
2       7       6
3       5       7
3       9       4
----
4       8       2
9       4       5
9       4       8

很容易看出，我们无法使用此索引在 col3 上获取这个切片，因为 col3 本身并没有排序。

上面提到的“参考”是行的 RID（指向表空间中的位置的指针），如果表本身未集群化，则为其值；如果表已集群化，则为表的群集键的值。 聚集索引 不会创建值的阴影副本。相反，它重新排列表的行本身。

如果您在上述的 (col2, col3) 上创建一个聚集索引，则只会重新排列表的行：

#       col1    col2    col3
1       1       1       1
3       7       2       3
6       2       2       7
7       5       3       5
4       3       3       9
2       1       4       8
5       8       9       4
8       3       9       4

因此，集群或非集群是一种存储方法，而不是索引。

在 Oracle 中，这被称为索引组织表（行已排序），而不是堆组织表（行未排序）。

- Quassnoi

Answer 3

索引A、B与索引B、A不同

这是因为索引按照特定的排序方式进行组织。因此，请想象您需要使用以下WHERE子句进行搜索：

WHERE A='somecrit' AND B='SomepartialCrit%'  -- notice the wildcard

A,B指数在解决查询方面非常高效，但如果它

WHERE   A='SomepartialCrit%'  AND B='somecrit'

（A，B）索引只能部分帮助（可能比完全表扫描好，但不是最优的...），而使用（B，A）索引会更好。

对于同时包括A和B精确匹配查询的情况，两种索引都可以在效率上等效使用，但选择一个特定的索引可能会受到查询的其他部分（如ORDER BY子句等）的驱动。

A索引与A，C索引不同。一方面，A，C索引可用于解决涉及A和C标准的查询，另一方面，A，C索引可用于“覆盖”SELECT子句或其一部分，即：如果SELECT子句仅包括来自此特定表的列A和C，SQL可以仅通过从索引获取A和C值而不是从表中获取数据来提供结果。

“冗余”索引是否不好？如上所述，额外的索引可能有助于更有效地解决SELECT查询。缺点是它们a）使用存储空间，b）使INSERT、UPDATE和DELETE查询不那么高效（因为需要将新/更新/删除的值添加/更改/删除到更多位置）。因此，这是基于可用存储空间和用例寻找正确平衡的问题（一些主要是只读数据库可以拥有大量索引而毫不影响性能，具有频繁插入的数据库可以看到它们的性能因太多索引而严重下降）。

关于聚集索引，请参见Joel Coehoorn的解释。不，给定表的聚集索引不需要基于主键。选择一个好的聚集索引（或者决定不使用聚集索引）是一个部分科学、部分艺术的过程，其范围超出了这篇简短的回答。

- mjv

Answer 4

一个基于列a,b的非聚集索引和一个基于列b,a的非聚集索引不同（注意顺序）。如果你在WHERE子句中对a和b进行限制，或者只对a进行限制（但不是只对b进行检查），那么你可以使用(a,b)的非聚集索引。

一个基于列a的非聚集索引和一个基于列a,c的非聚集索引也不同，但是如果遇到仅限于"a"的WHERE子句的查询，SQL Server查询优化器将使用这个非聚集索引。

- marc_s

Answer 5

把索引看作电话簿。通常电话簿按姓、名、街道排序。所以，如果你想找到住在Main Street 101号的Joe Smith的电话号码，你会在S开头的地方打开电话簿，然后找到所有姓Smith的Joe，再找到住在101 Main Street的Joe Smith，并找到他的电话号码。

电话簿也可以按不同的方式排序，比如按街道、名字、姓氏排序。那么你会先查找Main Street，然后是Joe，最后是Smith。如果你只想找一个人的电话号码，这样做同样快。

当你想列出所有住在Main Street并且名字是Joe的人的电话号码时，差异变得重要起来。使用普通电话簿需要循环遍历所有姓氏，找到该姓氏下的Joes，以及他们是否住在Main Street上。为了做到这一点，你必须浏览整个电话簿。但是如果索引顺序是street、firstname、lastname，则任务几乎是微不足道的：查找Main Street、Joe，并复制所有姓氏和他们的电话号码。速度要快得多。

此外，电话簿列出街道的事实对于只关心名字的人来说是无关紧要的。如果你想找到所有Joe Smiths的电话号码，你需要按姓、名（或名、姓）排序的电话簿。你不在乎电话簿是否按街道排序所有的Joe Smiths。从这个意义上说，(lastname, firstname, street)上的索引包含了(lastname, firstname)上的索引。

所以：索引(a,b,c)不等于(c,a,b)，如果你有(a,c)，你就不需要另一个(a)。

- wallenborn