使用包含列或不使用创建索引的区别

2022-01-16 00:00:00 indexing sql tsql sql-server sql-server-2008-r2

我想在 SQL Server 2008 R2 的 Column1 和 Column2 中创建索引以下查询有什么区别:

I Want To Create Index In SQL Server 2008 R2 in Column1 and Column2 What is the difference of below query:

  1. 不包括

CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[MyTable] 
(
    [Column1] ASC,
    [Column2] ASC
) ON [PRIMARY]

  • 或包括:

  • Or include:

    CREATE NONCLUSTERED INDEX [IX_2] ON [dbo].[MyTable] 
(
    [Column1] ASC
)
INCLUDE ([Column2]) ON [PRIMARY]

    • 推荐答案

    在第一个 Column2 被添加到索引键.在第二种情况下，它可能不会 (*) 被添加到键中，在这种情况下它只会出现在索引叶页面中.这可以允许通过 Column1 搜索索引，但避免需要返回基表 (书签查找/键查找) 以检索 Column2 的值.

    In the first one Column2 gets added to the index key. In the second one it might not (*) get added to the key in which case it will only appear in the index leaf pages. This can allow the index to be searched by Column1 but avoid the need to go back to the base table (bookmark lookup/key lookup) to retrieve the value for Column2.

    即它使 index2 覆盖"诸如

    i.e. it makes index2 "covering" for queries such as

    SELECT Column1,Column2
FROM [dbo].[MyTable] 
WHERE Column1 = 'X'

    而且它还涵盖了诸如

    SELECT Column1,Column2
FROM [dbo].[MyTable] 
WHERE Column1 = 'X' AND Column2 = 'Y'

    但是 index1 对于第二个查询可能会更好地执行，因为它可以直接在两列上查找(而不是只能在 Column1 上进行搜索，然后需要评估索引处的所有匹配行叶级别查看它们是否满足 Column2 谓词).如果 Column2 从未用作针对该索引的搜索谓词，并且您对该索引的查询不会因 Column2 排序而受益，则应将其添加为 INCLUDE-d 列以保持键的大小并减少索引中的页数.

    But index1 may well perform better for the second query as it can seek on the two columns directly (as opposed to only being able to search on Column1 then needing to evaluate all matching rows at the index leaf level to see if they meet the Column2 predicate). If Column2 is never used as a search predicate against that index and your queries against that index wouldn't benefit from having Column2 ordered then it should be added as an INCLUDE-d column to keep the size of the key down and reduce the number of pages in the index.

    (*) 我上面说可能不是"的原因是因为如果 Column2 是聚集索引键的(一部分)它 UNIQUE 选项.

    (*) The reason I say "might not" above is because if Column2 is (part of) the clustered index key it will still be added there anyway for a non clustered index not created with the UNIQUE option.

    相关文章