SQL覆盖索引如何工作? [英] How does SQL covering index work?

问题描述

我知道，当我们使用覆盖索引时，SQL Server在执行计划中仅使用索引搜寻(非聚集)或索引扫描(非聚集)运算符，而不会通过查找运算符检索数据.但是为什么不能在聚簇索引中查找值呢?非聚集索引不会在叶级上存储数据，因此无论它包含的列数是多少，都必须要求聚集索引返回数据行，因此它应该是执行计划中的查找运算符.我对吗?我已阅读 https://www.red-gate.com/simple-talk/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/和其他文章但没有解释.

I know, when we use a covering index, sql server uses only index seek(nonclustered) or index scan (nonclustered) operator in execution plan without retrieving data by lookup operator. But why is it possible not to look up values in clustered index? Nonclustered index doesn't store data on leaf level so regardless the number of columns it contains it has to ask clustered index to return data rows so it should be lookup operator in execution plan. Am I right? I've read https://www.red-gate.com/simple-talk/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/ and other articles but there is no explanation.

推荐答案

任何索引在所有级别上都存储定义该索引的键的列的值.此外，在非聚集索引的叶级别，叶子存储了任何其他列的值，这些值是聚集索引键 ¹ 的一部分，而不是非聚集索引键的一部分.聚簇索引键，因为这是随后执行聚簇索引查找的方式.

Any index, at all levels, stores the values for the column(s) defining that index's key. In addition, at the leaf level of the non-clustered index, the leaves store the values for any additional column(s) which are part of the clustered index key¹ and were not part of the non-clustered index key, because that is how the clustered index lookup is then performed.

如果查询仅需要检索的列要么是非聚集索引键的一部分，要么是聚簇索引键的一部分，那么我们已经可以通过导航非列索引来获得所有这些列值.聚簇索引.

If the only column(s) that the query needs to retrieve are either part of the non-clustered index key or are part of the clustered index key then we've already obtained all of those column values by navigating the non-clustered index.

查询不尝试检索行，而仅尝试检索特定列中的行值.

Queries, in general, are not trying to retrieve rows, only row values from particular columns.

以此类推，请考虑您要对整个城镇进行人口普查，并且正在存储所有数据卡和物理卡.这些卡包含该人的姓名，地址，出生日期，当前职业等.进一步假设每个人都有一个唯一的地址，因此您决定将所有这些卡按地址顺序存储在一个大盒子文件中.这是您的聚集索引.

As an analogy, consider that you're running a census for an entire town, and are storing all of the data and physical cards. These cards contain the person's name, address, date of birth, current occupation, etc. Assume further that every individual has a unique address and so you decide to store all of these cards in address order, in a big box file. This is your clustered index.

您经常想根据他们的名字来查找他们.因此，您将创建另一组索引卡，告诉您姓氏和名字的任何特定组合，以告知具有该名字的人的所有地址.您将这些卡放在第二个文件夹文件中，并按姓，名值对它们进行排序.这是您的非索引索引.

You frequently want to locate people based on their names. So you create another set of index cards that tell you, for any particular combination of surname and firstname all of the addresses at which someone with that name resides. You put these cards in a second box file and sort them by surname, firstname values. This is your non-clusered index.

最后，假设您的任务是识别所有姓为Radish的人居住的街道.显然，您可以使用非聚集索引来识别所有姓为Radish的人员.但是请记住，该二级索引中的卡片为您提供了这些人的地址.如果我们唯一的任务是识别他们的街道，那么我们已经掌握了这些信息.不需要我们去查找所有原始人口普查卡，其中包含我们 不需要的所有信息，只需完成此查询即可.

Finally, suppose your task is to identify the street on which all people with the surname Radish live. You can obviously use your non-clustered index to identify all of the people with the surname Radish. But remember, the cards in this secondary index gives you the addresses for these people. If our only task is to identify their street, we already have that information at hand. There's no need for us to go and look up all of the original census cards, containing all kinds of information that we've not been asked for, just to complete this query.

¹ 自2012年以来，在INCLUDE子句中为索引定义标识的所有其他列.

¹And since 2012, any additional columns identified in an INCLUDE clause for the index definition.

这篇关于SQL覆盖索引如何工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！