两个列上的单个非聚集索引或每个列上的单独索引

Question

假设我有以下表格:

Table: RelationshipType
============================================================
|  ID (PK)  |  ParentID  |  ChildID  |  RelationshipType   |
============================================================

大多数情况下，ParentID和ChildID被单独选中:

... WHERE ParentID = @SomeID
... WHERE ChildID  = @SomeID

有时两者都被选中:

... WHERE ParentID = @SomeID AND ChildID  = @SomeOtherID

我想提高这些查询的性能，但最值得注意的是前两个。我应该在ParentID + ChildID上创建一个非聚集索引，还是在ParentID上创建一个索引，在ChildID上创建另一个索引?

EDIT:所有这些查询都是高度选择性的(返回1或2条记录)。

Answer 1

可以去掉代理键ID吗?

如果是，请考虑创建以下内容:

• {ParentID, ChildID}上的主键和集群键。
• {ChildID, ParentID}上的二级索引，但也包括RelationshipType(使用include关键字)。

这样，您在所有3种情况下都有一个覆盖索引，因此您不必支付双重查找的代价(这通常是集群表中的二级索引所需要的):

• ... WHERE ParentID = @SomeID可以通过在索引{ParentID, ChildID}的b树中进行简单的查找来满足。ChildID和RelationshipType ¹的值可以直接从该b树的查找叶中获取。
• ... WHERE ChildID = @SomeID可以通过在索引:{ChildID, ParentID}的b树中简单查找来满足。ParentID和RelationshipType ²的值可以直接从该b树的查找叶中获取。
• ... WHERE ParentID = @SomeID AND ChildID = @SomeOtherID均可满足。

¹集群键是表的"主"b树，包括所有列，而不仅仅是那些唯一的。

² Thanks to INCLUDE (RelationshipType) .

---

用ID做类似的事情是可能的，但是需要3个索引而不是2个，并且所有索引都将更胖以实现覆盖。你必须衡量以确保，但我的感觉是这样做会带来更多的麻烦。

否则，根本不要使用集群。在

上创建普通索引

• {ID} -常规的，非集群的主索引(使用NONCLUSTERED关键字)。
• {ParentID} -常规二级索引。
• {ChildID} -常规二级索引。

您将有一个普通的堆表，因此每次访问都需要索引查找+(通常)表堆访问，但是您的索引将保持精简，从而提高缓存效率。

... WHERE ParentID = @SomeID AND ChildID = @SomeOtherID将需要两次索引查找(或者可能在{ParentID}或{ChildID}索引+表堆访问上进行一次查找)，但这仍然非常快，并且不太频繁(如您所说)。

---

Answer 2

正如你所说，通过ParentId或ChildId查找是高度选择性的，我只是选择两个单独的索引。

SQL Server可以为WHERE ParentID = @SomeID AND ChildID = @SomeOtherID使用索引，并计算匹配的一行或两行上的残差谓词。

我想一个例外是，如果表或多或少是只读的，整个数据库适合内存，那么有额外的索引没有缺点，它避免了查找来检索缺失的列。

Answer 3

在某种程度上，如果不知道这些选择的频率以及对表的插入/更新发生的频率，就不可能说什么会给数据库带来最佳性能，但以下是我的最佳猜测:

听起来像ParentID, ChildID可能是你的主键，根据定义，这是一个聚集索引。

这里的偷懒方法是在ParentID和ChildID上再创建两个非聚集索引。但是…特别是ParentID列，或者主键/集群索引中最先出现的任何列——如果创建另一个非集群ParentID索引，我真的不确定您是否会获得任何选择好处。非聚集索引将存储按该索引排序的表的副本，但是在这种情况下，主键决定了表的排序，并且它已经按ParentID首先排序了。

总之，我将为ParentID和ChildID创建一个主键并在ChildID上创建一个非聚集索引，我认为您可以这样做

Answer 4

我将在每一列上创建一个单一的非聚集索引，但包括另一列和RelationshipType列(我假设RelationshipType是您正在检索的数据):

CREATE NONCLUSTERED INDEX IX_RelationshipType_ParentID
ON
    RelationshipType(ParentID)
INCLUDE
    (ChildID, RelationshipType)
GO
CREATE NONCLUSTERED INDEX IX_RelationshipType_ChildID
ON
    RelationshipType(ChildID)
INCLUDE
    (ChildID, RelationshipType)
GO

这将导致引擎一旦找到条目就能够从索引中获得所需的数据，而不需要在索引中定位条目后返回到表中。