需要澄清SQL Server索引的选择

We have an existing table structure as below

我们有一个现有的表结构如下。

Table Name: Provider #

表名称:提供者#

Columns:

列:

Provid
EntityId
SpecialityCode
ProvType
FedId
FullName
Status

In this table, ProvId is the primary key. So we have a clustered index on ProvId.

在该表中，ProvId是主键。我们有一个关于ProvId的聚集索引。

Apart from this, we have two composite nonclustered indexes as below

除此之外，我们还有两个复合非聚集索引，如下所示

CREATE NONCLUSTERED INDEX [Provider0] 
ON [dbo].[provider] ([fedid] ASC, [provid] ASC, [entityid] ASC, [fullname] ASC, [status] ASC)

CREATE NONCLUSTERED INDEX [XIE3Provider] 
ON [dbo].[provider] ([fedid] ASC, [entityid] ASC, [provtype] ASC, [fullname] ASC, [provid] ASC, )

Query 1:

查询1:

SELECT provid 
FROM provider 
WHERE FedId = '123'

For query 1, SQL Server uses XIE3Provider index for seek operation.

对于查询1,SQL Server使用XIE3Provider索引进行查找操作。

Query 2:

查询2:

SELECT provid, status 
FROM provider 
WHERE FedId = '123'

For query 2, SQL Server uses the Provider0 index for seek operation.

对于查询2,SQL Server使用Provider0索引进行查找操作。

Could you please provide more details as to how SQL Server switches the index selection for this case?

您能否提供更多关于SQL Server如何切换索引选择的细节?

2 个解决方案

#1

SQL Server's query optimizer tries to utilise covering index:

SQL Server的查询优化器尝试使用覆盖索引:

A covering index is one which can satisfy all requested columns in a query without performing a further lookup into the clustered index.

覆盖索引可以满足查询中的所有请求列，而无需对聚集索引进行进一步查找。

Select provid, status from provider where FedId  = '123';

Filter: FedId

过滤器:FedId

Select: provid, status.

选择:提供、状态。

So it chose to use:

所以它选择了:

CREATE NONCLUSTERED INDEX [Provider0] ON [dbo].[provider]
(
    [fedid] ASC,
    [provid] ASC,
    [entityid] ASC,
    [fullname] ASC,
    [status] ASC
);

You could use INCLUDE clause:

你可以使用INCLUDE条款:

CREATE NONCLUSTERED INDEX [Provider0] ON [dbo].[provider]
(
    [fedid] ASC,
    [provid] ASC,
    [entityid] ASC,
    [fullname] ASC,
) INCLUDE (status);

Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Make all other columns that cover the query into nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.

重新设计具有较大索引键大小的非聚集索引，以便仅用于搜索和查找的列是键列。将覆盖查询的所有其他列都转换为非键列。通过这种方式，您将拥有覆盖查询所需的所有列，但是索引键本身很小而且高效。

#2

Where possible SQL will ignore the Table and retrieve all relevant data from the Index. Because the Index is smaller than the Table and reading the Index will require less IO

如果可能，SQL将忽略表并从索引中检索所有相关数据。因为索引比表小，读取索引需要更少的IO。

Status, provid and FedId are in the Provider0 Index so SQL uses this index to reduce IO.

状态、provid和FedId都在Provider0索引中，所以SQL使用这个索引来减少IO。

#1