为什么内连接会产生索引扫描与索引搜索

I have two tables

我有两张桌子

Account:
    AccountUID bigint (Primary Key)
    AccountID bigint
    Version smallint
    CustomerName varchar(50)

Note: AccountID and CurrentVersion are also part of a key that is unique

注意：AccountID和CurrentVersion也是唯一的密钥的一部分

AccountStatus:
    AccountID bigint (Primary Key)
    CurrentVersion smallint
    Filter1 varchar(10)
    Filter2 varchar(10))

To keep things simple, I currently have 100 rows in each as each AccountID only has one version. I have values in the Filter1 and Filter2 tables such that when I do the following I get 10 records returned:

为了简单起见，我目前每行有100行，因为每个AccountID只有一个版本。我在Filter1和Filter2表中有值，这样当我执行以下操作时，我会返回10条记录：

SELECT *
FROM AccountStatus acs
WHERE acs.Filter1=SomeValue1 and acs.Filter2=SomeValue2

Due to the fact that I have an index that has the Filter1 and Filter2 in it, the actual execution plan shows an Index Seek with only the 10 selected rows in the Actual Rows value.

由于我有一个索引，其中包含Filter1和Filter2，实际执行计划显示一个Index Seek，其中只有Actual Rows值中的10个选定行。

When I join in the Account table as follows, I get the same 10 records:

当我按如下方式加入Account表时，我得到相同的10条记录：

SELECT acs.*
FROM AccountStatus acs
    INNER JOIN Account a ON acs.AccountID=a.AccountID
                            AND acs.CurrentVersion=a.Version
WHERE acs.Filter1=SomeValue1 and acs.Filter2=SomeValue2

However, when I look at the Actual Execution Plan, I still see the Index Seek on the AccountStatus table as before with 10 Actual Rows. However, above that I see an Index Scan on the index that involves the AccountID and Version of the Account table. Also, this "action" shows 100 in the Actual Rows.

但是，当我查看实际执行计划时，我仍然像以前一样在10个实际行上看到AccountStatus表上的索引查找。但是，在上面我看到索引上的索引扫描涉及帐户表的AccountID和版本。此外，此“操作”在实际行中显示100。

Here is the detail of the indexes involved:

以下是涉及的索引的详细信息：

CREATE NONCLUSTERED INDEX [IX_Find] ON [dbo].[AccountStatus] 
(
    [Filter1] ASC,
    [Filter2] ASC
)
INCLUDE ( [AccountID],
[CurrentVersion]
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [IX_Account_Status] ON [dbo].[Account] 
(
    [AccountID] ASC,
    [Version] ASC
)
INCLUDE ( [AccountUID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

While this is not too much of a performance hit with 100 rows, I am more concerned when I get to a million or several million rows. This will become very inefficient.

虽然这对100行没有太多的性能影响，但当我达到一百万或几百万行时，我更担心。这将变得非常低效。

Is there anyway to make this type of query not scan all of the rows in the Index on the Account table?

反正有没有让这种类型的查询不扫描Account表上的索引中的所有行？

Any help will be most appreciated.

任何帮助将非常感谢。

2 个解决方案

#1

Execution plans are based off the statistics of the tables and their indexed columns. With only 100 rows, it will have a different execution plan than when it has 10m rows. If you are worried about it not using the index you need to increase the amount of data and update your statistics before looking at the execution plan again. The engine is pretty smart and will very often choose the fastest version of the execution plan.

执行计划基于表及其索引列的统计信息。只有100行，它将具有与10m行不同的执行计划。如果您担心它不使用索引，则需要在再次查看执行计划之前增加数据量并更新统计信息。该引擎非常智能，并且通常会选择执行计划的最快版本。

I assume filter 1 matches for a fair amount of rows, regardless, it doesn't take the engine much longer to perform a scan over a seek with this few rows so the engine just uses the scan.

我假设过滤器1匹配相当数量的行，无论如何，使用这几行不会花费更长的时间来执行对搜索的扫描，因此引擎只使用扫描。

#2

The way I determine if an index is needed or not is one of two ways. The first is execute the query using Display Estimated Execution Plan. Then if the execution plan shows that an index is missing (in green) right click the green missing index message and select Missing Index Details... This will open a new window with code to insert the missing index that can help this query. Simply uncomment the lower section of the code and replace with the name for the new index and execute the code.

我确定是否需要索引的方式是两种方法之一。第一种是使用Display Estimated Execution Plan执行查询。然后，如果执行计划显示缺少索引（绿色），请右键单击绿色缺失索引消息并选择缺失索引详细信息...这将打开一个新窗口，其中包含代码以插入可帮助此查询的缺失索引。只需取消注释代码的下半部分，并替换为新索引的名称并执行代码。

You may need to repeat this process a few times for any missing indexes that may help this code.

对于可能有助于此代码的任何缺失索引，您可能需要重复此过程几次。

The second method is more of a long term approach to database performance.

第二种方法更多是数据库性能的长期方法。

PINAL DAVE has published a series of scripts on his blog at http://blog.sqlauthority.com/ that help identify missing indexes as well as unused and duplicate indexes. I run these on a regular basis to determine what indexes will help and which are actually hurting performance.

PINAL DAVE在他的博客http://blog.sqlauthority.com/上发布了一系列脚本，帮助识别缺失的索引以及未使用和重复的索引。我定期运行这些来确定哪些索引会有所帮助，哪些索引实际上会损害性能。

Add the missing indexes and remove any unused or duplicate indexes.

添加缺少的索引并删除任何未使用或重复的索引。

#1