为什么SQL Server不使用我的索引？ [英] Why isn't SQL Server using my index?

问题描述

在我们的数据库中，我们有200.000行的这个表

In our database we have this table with 200.000 rows

CREATE TABLE dbo.UserTask (
    UserTask_ID int NOT NULL IDENTITY (1, 1),
    UserTask_SequenceNumber int NOT NULL DEFAULT 0,
    UserTask_IdEntitat uniqueidentifier NOT NULL,
    UserTask_Subject varchar(100) NOT NULL,
    UserTask_Description varchar(500) NOT NULL,
            .....
            .....
    CONSTRAINT [PK_UserTask] PRIMARY KEY CLUSTERED 
    (
        [UserTask_ID] ASC
    ) ON [PRIMARY]
) ON [PRIMARY]

我在<$ c上创建了一个索引$ c> UserTask_IdEntitat 列

CREATE NONCLUSTERED INDEX IX_UserTask_IDEntitat ON dbo.UserTask 
(
    UserTask_IDEntitat
)

执行以下查询，执行计划向我们显示该索引在 UserTask_IDEntitat 用于执行查询：

Executing the following query, execution plan shows us that index on UserTask_IDEntitat is used to do the query:

SELECT UserTask_ID
  FROM UserTask   
 WHERE UserTask_IdEntitat = @IdEntitat 
 ORDER BY UserTask_LastSendSystemDateTime desc

但是如果我们在选择列表中添加另一列，则不使用索引

But If we add another column in the Select list, then the index is not used

SELECT UserTask_ID, UserTask_SequenceNumber, UserTask_IDEntitat, ....., UserTask_Subject
  FROM UserTask   
 WHERE UserTask_IdEntitat = @IdEntitat 
 ORDER BY UserTask_LastSendSystemDateTime desc

为什么添加与主键不同的列会导致SQL Server执行计划不使用<$ c上的索引$ c> UserTask_IDEntitat 列？

Why adding a column different from the primary key makes that the SQL Server execution plan doesn't use the index on the UserTask_IDEntitat column?

点击此链接 http://bytes.com/topic/sql-server/answers/144592-sqlsever-not-using-index 似乎那个次数在列上重复过滤的值，它可以使索引成为可能没有使用，但我尝试使用重复60.000次的@IdEntitat值进行查询，其他只重复175次且结果相同， IDEntitat 列被忽略。

Following this link http://bytes.com/topic/sql-server/answers/144592-sqlsever-not-using-index it seems that the number of times that the filtered value is repeated on the column, It can make that the index is not used, but I have tried doing the query with an @IdEntitat value that is repeated 60.000 times and other that is repeated only 175 times and the results are the same, the index on IDEntitat column is ignored.

这让我抓狂!!!

感谢您的帮助。

推荐答案

好的 - 只要您选择索引中的列，或者群集键中的某些内容（通常，这个是主键），然后将使用索引，因为SQL Server可以找到它需要的所有信息（ UserTask_IDEntitat 列和聚簇索引列）在索引导航结构的叶级别。因此，它可以直接从索引的叶级页面返回 SELECT 查询所需的数据。

OK - as long as you select only the column that's in the index, or something from the clustering key (usually, this is the primary key), then the index will be used, since SQL Server can find all the information it needs (the UserTask_IDEntitat column, and the clustered index column(s) ) in the leaf level of the index navigation structure. So it can return the data needed for that SELECT query directly from the index's leaf level pages.

但是：如果您需要选择第二列，即既不在索引定义中也不是部分的群集密钥，那么SQL Server将不得不在实际数据页面中执行所谓的书签查找。

However: if you need to select a second column, that is neither in the index definition, nor part of the clustering key, then SQL Server would have to do a so-called bookmark lookup into the actual data pages.

对于找到的每一行，

So for every single row it finds in your nonclustered index, it would have to take the clustering index value, search the clustered index to find the actual data page at the leaf level of that clustered index, and then pick out that one column that you want.

书签查找非常适合少量点击 - 如果你选择了数千行，它们对于性能来说是非常具有破坏性的。在这种情况下，SQL Server查询优化器正确使用聚簇索引扫描 - 因为在聚集索引中，在叶级别上，它立即可用所有行 。

Bookmark lookups are great for small numbers of hits - they are totally devastating for performance if you're selecting thousands of rows. In that case, the SQL Server query optimizer correctly uses a clustered index scan instead - since in the clustered index, on the leaf level, it has all the rows available right away.

所以：如果你有 UserTask_IDEntitat 的索引，你有时需要第二列 UserTask_SequenceNumber - 那么你可以包括那个非你聚集索引中的那一列：

So: if you have an index on UserTask_IDEntitat and you sometimes need a second column UserTask_SequenceNumber too - then you could include that column in that nonclustered index of yours:

CREATE NONCLUSTERED INDEX IX_UserTask_IDEntitat 
ON dbo.UserTask(UserTask_IDEntitat)
INCLUDE(UserTask_SequenceNumber)

这样，该附加列仅出现在该非聚集索引的叶级中（它不能用于 WHERE 子句 - 它不是索引导航结构的一部分！） - 并且可以从非聚簇索引的叶级节点再次满足您的第二个 SELECT - >没有e需要进行低价书签查询 - >您的索引将再次使用。

With this, that additional column is present in the leaf level of that non-clustered index only (it cannot be used in a WHERE clause - it's not part of the navigation structure of the index!) - and your second SELECT can again be satisfied from the leaf-level nodes of the nonclustered index -> no expensive bookmark lookups are needed -> your index will be used again.

长话短说，除非您的非聚集索引是 高度选择性 （例如返回1％或更少的行），除非你的非聚集索引是覆盖索引（包含满足特定查询所需的所有列的索引），那么SQL Server的更改相当高将不使用您的非聚集索引。

Long story short: unless your nonclustered index is highly selective (e.g. returns 1% of your rows or less), and unless your nonclustered index is a covering index (an index that contains all the columns needed to satisfy a particular query), then changes are pretty high that SQL Server will NOT use your nonclustered index.

有关详细信息：

• SQL Server索引基础知识


• SQL Server - 学习SQL Server性能：索引基础 - 视频

• SQL Server Indexing Basics
• SQL Server – Learning SQL Server Performance: Indexing Basics – Video

这篇关于为什么SQL Server不使用我的索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！