索引中列的顺序有多重要？

Question

我听说你应该在索引声明的开头放置最具选择性的列。例如：

CREATE NONCLUSTERED INDEX MyINDX on Table1
(
   MostSelective,
   SecondMost,
   Least
)

首先，我的说法正确吗？如果是，通过重新排列索引列的顺序，我是否会看到性能上的巨大差异，还是更多地是一种“好习惯”？

我之所以问这个问题，是因为在经过 DTA 查询后，它建议我创建一个索引，其中几乎所有列与现有索引相同，只是顺序不同。我正在考虑将缺少的列添加到现有索引中，然后就做完了。您有什么想法？

- Abe Miessler

Answer 1

看一个类似这样的索引：

Cols
  1   2   3
-------------
|   | 1 |   |
| A |---|   |
|   | 2 |   |
|---|---|   |
|   |   |   |
|   | 1 | 9 |
| B |   |   |
|   |---|   |
|   | 2 |   |
|   |---|   |
|   | 3 |   |
|---|---|   |

看看先限制 A 列，作为你的第一列，比先限制第二列要消除更多的结果？如果你能想象索引在使用时是如何遍历的——按照第一列、第二列等等的顺序——你就会发现，在第一次遍历中大部分结果都被排除了，这使得第二步变得更快。

另一个例子，如果你查询第三列，优化器甚至不会使用索引，因为它对缩小结果集没有任何帮助。无论何时你在查询中，缩小处理的结果数目，然后再进行下一步操作，都会带来更好的性能。

由于索引也是按照这种方式存储的，所以在查询该索引的第一列时，不需要在索引上回溯。

简而言之：不，这不只是为了好看，实际上有真正的性能优势。

- Nick Craver

---

请注意上面的图片中，只有在查询中指定了第一列时，索引才会有益处。如果您的查询仅在联接或搜索谓词中指定了第二列，则索引无效。因此，在这里顺序也很重要。也许这是显而易见的，但还是想提一下。 - CodeCowboyOrg

请注意，假设您的索引类似上面的图片，并且您的查询筛选了第一列和第二列，但第二列更为独特，实际上您想要过滤的是第二列，那么拥有第二列作为首位的索引会更加有益。这可能看起来反直觉，但请记住索引存储在多个页面上，是一个具有范围值的树形结构，尽管上面的第一列排除了一半的可能性，但索引已经知道要直接跳转到第二列的哪一页，它并不一定需要第一列来缩小集合。 - CodeCowboyOrg

这张图片并不准确地展示了索引是如何构建和导航的。我已经提交了一个答案来纠正这个问题https://dev59.com/zXE95IYBdhLWcg3wft1w#39080819 - Martin Smith

@MartinSmith我不同意它是不准确的。它确实被极度简化了，这是我的意图。感谢您就层级深入挖掘更多细节的答案，对于那些想要深入研究的人来说，这很有用。如果您看一下您的树形图像，您将会看到我在非常简单的方式上所阐述的内容。这并不是非常独特或仅适用于SQL；B-树索引在许多事物中都非常普遍。 - Nick Craver

拥有按不同顺序排序的多个索引是否有益？例如，A、B、C和B、A、C，以帮助处理不同的分组可能性？ - jwrightmail

Answer 2

列的顺序非常重要。现在哪种顺序是正确的取决于您将如何查询它。索引可用于执行精确查找或范围扫描。

精确查找是指指定索引中所有列的值，并且查询正好落在感兴趣的行上。对于查找，列的顺序无关紧要。范围扫描是指仅指定了一些列，在这种情况下，顺序变得很重要。只有左起第一列被指定，SQL Server才能使用索引进行范围扫描，然后只有左起第二列被指定，以此类推。

如果您在(A,B,C)上有一个索引，它可以用于A=@a、A=@a AND B=@b的范围扫描，但不能用于B=@b、C=@c或B=@b AND C=@c的范围扫描。当使用A=@a AND C=@c时，情况比较复杂，因为A=@a部分将使用索引，但C=@c不会（查询将扫描所有A=@a的B值，并不会“跳过”到C=@c）。其他数据库系统具有所谓的“跳过扫描”操作符，当未指定外部列时，可以利用索引中的内部列。

有了这些知识，你现在可以再次查看索引定义。对于 (MostSelective, SecondMost, Least) 这个索引，只有当指定MostSelective列时才有效。但是由于它是最具选择性的列，内部列的相关性会很快降低。通常情况下，更好的索引应该是 (MostSelective) include (SecondMost, Least) 或者是 (MostSelective, SecondMost) include (Least)。因为内部列的选择性较低，在索引中将它们放置在正确位置之后，它们对于搜索查询来说就只是噪音了，所以将它们移出中间页并仅保留在叶子页上，则更适合于查询覆盖的目的。换句话说，将它们移到 INCLUDE 中。随着 Least 列的大小增加，这点变得更加重要。这个索引只有在指定 MostSelective列时才能够提供帮助，而这一列已经限制了候选行的范围，无论是精确值还是范围值。

另一方面，对于 (Least, SecondMost, MostSelective) 这个索引，可能看起来是一个错误，但实际上它是一个非常强大的索引。因为它将 Least 列作为最外层查询，所以它可以用于在低选择性列上聚合结果的查询。这样的查询在 OLAP 和分析数据仓库中非常普遍，这正是这种索引的一个很好的适用场景。这样的索引实际上是优秀的 聚簇索引，因为它们在相关行的大块物理布局上进行组织（相同的 Least 值通常表示某种类别或类型），并且它们有利于分析查询。

因此，不幸的是，并没有“正确”的顺序。你不应该按照任何标准配方进行操作，而是应该分析你要对这些表执行的查询模式，并决定哪个索引列顺序是正确的。

- Remus Rusanu

---

像往常一样，Remus回复得很棒。我要再读几遍你的第三段话并且跟进一下。我猜那可能正是我需要做的。 - Abe Miessler

仅当指定最左边的列，然后只有在指定下一个最左边的列时，SQL Server 才能仅使用索引进行范围扫描。这正是我理解不足之处，谢谢！我不知道范围扫描只能在最右侧使用的索引列上进行，但现在我知道了，这使得很多事情都变得合理。 - Allon Guralnek

@Roizpi 是的，基本上任何带有索引的关系型数据库都是以相同或非常相似的方式工作。 - Tatranskymedved

在上面的例子中，如果你在 (A,B,C) 上有一个索引, 那么它可以用来扫描 A=@a 的范围, 也可以用来扫描 A=@a AND B=@b。那么在 WHERE 语句中，如果我写成 B=@b AND A=@a，会有影响吗？ 索引能够处理 A=@a AND B=@b 和 B=@b AND A=@a 吗？ - Sam

@Sam 表达式 A=@a AND B=@b 和 B=@b AND A=@a 在执行过程中基本上无法区分，因此它们之间的差异并不重要。 - Remus Rusanu

Answer 3

正如Remus所说，这取决于你的工作量。

然而，我想解决被接受答案中一个误导性的方面。

对于在索引的所有列上执行等值搜索的查询，没有显着的差异。

下面创建了两个表，并使用相同的数据填充它们。唯一的区别是其中一个的键按最具选择性到最不具选择性的顺序排序，而另一个则相反。

CREATE TABLE Table1(MostSelective char(800), SecondMost TINYINT, Least  CHAR(1), Filler CHAR(4000) null);
CREATE TABLE Table2(MostSelective char(800), SecondMost TINYINT, Least  CHAR(1), Filler CHAR(4000) null);

CREATE NONCLUSTERED INDEX MyINDX on Table1(MostSelective,SecondMost,Least);
CREATE NONCLUSTERED INDEX MyINDX2 on Table2(Least,SecondMost,MostSelective);

INSERT INTO Table1 (MostSelective, SecondMost, Least)
output inserted.* into Table2
SELECT TOP 26 REPLICATE(CHAR(number + 65),800), number/5, '~'
FROM master..spt_values
WHERE type = 'P' AND number >= 0
ORDER BY number;

现在对这两个表进行查询...

SELECT *
FROM   Table1
WHERE  MostSelective = REPLICATE('P', 800)
       AND SecondMost = 3
       AND Least = '~';

SELECT *
FROM   Table2
WHERE  MostSelective = REPLICATE('P', 800)
       AND SecondMost = 3
       AND Least = '~'; 

......两个查询都使用一个细节索引，并且它们的成本完全相同。

所接受答案中的ASCII艺术实际上并不是索引结构。下面是Table1的索引页（单击图像以全尺寸打开）。

索引页面包含包含整个键的行（在这种情况下，实际上会添加一个额外的键列作为行标识符，因为索引未声明为唯一，但可以忽略此处可以找到更多信息）。

对于上面的查询，SQL Server不关心列的选择性。它对根页面进行二进制搜索，并发现Key (PPP...,3,~ )大于等于>=(JJJ...,1,~ )和< (SSS...,3,~ )，因此应读取页面1:118。然后，在该页面上执行关键字条目的二进制搜索并定位要导航到的叶页面。

按选择性顺序修改索引不会影响二进制搜索的预期关键字比较次数或需要导航以执行索引查找的页面数。充其量，它可能会稍微加快关键字比较本身的速度。

但有时，将选择性最高的索引排在第一位对于负载中的其他查询可能是有意义的。

例如，如果工作负载包含以下两种形式的查询。

SELECT * ... WHERE  MostSelective = 'P'

SELECT * ...WHERE Least = '~'

上述索引对它们中的任何一个都不够覆盖。 MostSelective 具有足够的选择性，可以制定一个值得进行查找和查阅的计划，但针对 Least 的查询则不是这样。

然而，这种情况（在复合索引的前导列的子集上进行非覆盖索引查找）只是可以通过索引帮助的查询类别之一。如果您从未实际通过单独的 MostSelective 或 MostSelective, SecondMost 的组合搜索，而总是通过所有三列的组合进行搜索，则这种理论优势对您毫无用处。

相反，如下查询：

SELECT MostSelective,
       SecondMost,
       Least
FROM   Table2
WHERE  Least = '~'
ORDER  BY SecondMost,
          MostSelective 

如果将通常预设的顺序倒过来，会对查询有帮助 - 因为它可以覆盖查询，支持查找并以所需的顺序返回行。

因此，这是一个经常重复的建议，但最多只是关于潜在受益于其他查询的启发式规则 - 它不能替代实际查看你自己的工作负载。

- Martin Smith

---

这个答案似乎是所有答案中解释得最清楚的，但我对它的赞数很怀疑。我很好奇，您是否仍然同意这个解释？也就是说，您对这个过程的理解有没有改变？如果没有，您有没有任何想法，为什么这个答案在这里得到了如此多的证实？ - Ross Brasseaux

@Lopsided - 不，答案是正确的。它是在页面上其他答案发布6年后发布的，这在一定程度上解释了投票差异。 - Martin Smith

谢谢。只是为了更正一个打字错误，我本来想说“这里的答案为什么如此不确定？” - Ross Brasseaux

Answer 4

在索引声明中，应该将最具选择性的列放在前面。

正确。 索引可以是复合索引，由多个列组成，其顺序很重要，因为它遵循左前原则。也就是说，数据库从左到右检查列表，并且必须找到与定义顺序匹配的相应列引用。例如，在地址表上创建一个包含以下列的索引：

• Address
• City
• State

任何使用 address 列的查询都可以利用该索引，但是如果查询仅具有 city 和/或 state 引用，则无法使用索引。这是因为左侧列未被引用。查询性能应告诉您哪个是最佳的选择-单个索引还是具有不同顺序的多个复合索引。好文章推荐: The Tipping Point, by Kimberley Tripp

- OMG Ponies

---

如果只有最右边的列没有被使用怎么办？比如查询中用到了地址和城市，但没有用到州。那么索引会被使用吗？ - Abe Miessler

@Abe：不会使用最右边的索引 - 必须满足从左侧开始的索引顺序。如果错过一个，就不能使用它。 - OMG Ponies

如果你查询地址和城市，但未查询州 - 那么是的，索引将被使用。换句话说，只要数据库能够从一个索引的左侧开始，并在使用正在查询的字段时向右移动，它就能够利用部分索引来满足请求。然而，如果你使用地址和州进行查询，但未查询城市，它仍然可能使用索引，但效率会降低 - 因为现在它只能使用索引的地址部分（因为下一个是城市，而它在查询中没有被使用）。 - JaredC

Answer 5

选择性是一个非常小的因素，而“最左”是关键

当选择顺序时，组合索引中各个列的选择性并不重要。

思路很简单：实际上，索引就是涉及的列的串联。

基于这一原理，唯一的区别在于比较两个“字符串”，它们在字符串中的位置不同。这只是总成本中的一小部分。没有“第一次/第二次”之分，正如某个答案中所提到的一样。

那么，应该使用什么顺序？

1. 从使用=测试的列开始，按任意顺序。
2. 然后添加一个范围列。

例如，在此示例中，选择性非常低的列必须首先出现：

WHERE deleted = 0  AND  the_datetime > NOW() - INTERVAL 7 DAY
INDEX(deleted, the_datetime)

如果在索引中交换顺序，将完全忽略deleted。（有很多关于列排序的规则。）

- Rick James

---

负面评价是因为我错了吗？还是因为我有强烈的观点？或者其他原因？ - Rick James

@Greg - 我想那意味着“选择性低”--也就是说，使用deleted并不能很好地过滤掉不需要的行。你有更好的例子吗？（这是我写答案时想到的一个例子）。 - Rick James

我误解了。 - Greg

@ClickOk - 谢谢。我的食谱提供了一些基本信息：http://mysql.rjweb.org/doc.php/index_cookbook_mysql - Rick James

@wingerse - 范围测试列（the_datetime）后面的列将被忽略（除了“covering”）。可以这样理解：按last_name，然后first_name排序的名称列表，使用INDEX（last_name，first_name）。并且 WHERE last_name LIKE 'J%' AND first_name = 'Rick'。它必须扫描所有J; 在索引中具有first_name对性能影响很小。 - Rick James