是否有任何硬性和快速性的规则来决定应将哪些列和以什么顺序放置在包括在非聚集索引中。我只是在阅读这篇文章https://stackoverflow.com/questions/1307990/why-use-the-include-clause-when-creating-an-index
,我发现对于以下查询:

SELECT EmployeeID, DepartmentID, LastName
FROM Employee
WHERE DepartmentID = 5


张贴者建议制作这样的索引:

CREATE NONCLUSTERED INDEX NC_EmpDep 
  ON Employee(EmployeeID, DepartmentID)
  INCLUDE (Lastname)


我的问题是为什么我们不能制作这样的索引

CREATE NONCLUSTERED INDEX NC_EmpDep 
      ON Employee( EmployeeID, DepartmentID, LastName)




    CREATE NONCLUSTERED INDEX NC_EmpDep 
          ON Employee( EmployeeID, LastName)
INCLUDE (DepartmentID)


是什么促使海报发布者决定保留包括姓氏列。为什么不另列?以及如何决定应以什么顺序将列保留在那里?

评论

在找到记录后,INCLUDE通常应该具有您需要的字段,这样可以节省往返行程以获取更多数据。 INCLUDE中字段的顺序并不重要。

Ryk,我个人认为这篇文章很有帮助。

我觉得这个问题也有帮助。让我们专注于好的问题和好的答案,而不是跟踪个人....

#1 楼

marc_s的索引建议是错误的。我添加了一条评论。 (这也是我接受的答案!)

此查询的索引将是

CREATE NONCLUSTERED INDEX NC_EmpDep 
  ON Employee(DepartmentID)
  INCLUDE (Lastname, EmployeeID)


索引通常是

CREATE INDEX <name> ON <table> (KeyColList) INCLUDE (NonKeyColList)


其中:


KeyColList =键列=用于行限制和处理
WHERE,JOIN,ORDER BY,GROUP BY etc
NonKeyColList =非键列=在选择/限制后用于SELECT和聚合(例如SUM(col))


评论


+1-我同意(请参阅我的回答),OP中的样本索引对于查询毫无价值!

– JNK
2011年5月31日13:10

大!仅一件事决定了KeyColList和NonKeyColList的顺序。你能用我的例子解释一下吗?假设现在我的查询是SELECT EmployeeID,DepartmentID,LastName FROM EmployeeWHERE DepartmentID = 5,StateID = 4现在索引如何了?

– Rocky Singh
2011年5月31日13:18

@Rocky-NonKeyColList顺序无关紧要。 KeyColList的顺序应该是您希望它们在查询中使用的频率顺序。请在下面的答案中查看我的注释,但这就像电话簿中的“姓氏”,“名字”,“ Middile Initial”一样。您需要第一个字段才能找到第二个字段。

– JNK
2011年5月31日13:27

@gbn我们是否真的需要在包含列表中使用EmployeeID?就像如果我们在EmployeeID列上有一个聚集索引,并且如果在DeptId列上创建非聚集索引一样,那么非聚集索引已经引用了包含在非聚集索引结构中的聚集键,包括INCLUDE列表中的聚集键不存在。增加任何好处。

– Vishwanath Dalvi
17年8月6日在6:01

@ViswanathanIyer不会将它两次添加到实际的磁盘存储中:SQL Server会检测到它。因此它不是必需的,但是它使事情变得更清晰。但是,我们不知道该问题中是否存在任何聚集索引,因此假设它们都不安全。

– gbn
17年8月7日在6:32

#2 楼

JNK和gbn给出了很好的答案,但是也有必要考虑一下大局-而不是仅仅关注单个查询。尽管此特定查询可能会受益于索引(#1):

Employee(DepartmentID) INCLUDE (Lastname, EmployeeID)


如果查询稍有变化,该索引将根本无济于事,例如:

SELECT EmployeeID, DepartmentID, LastName
FROM Employee
WHERE DepartmentID = 5 AND LastName = 'Smith'


这将需要索引(#2):使用索引#1来查找所有Smiths,您需要遍历Department 5中的所有1,000行,因为包含的列不是键的一部分。使用索引#2,您可以直接查找部门5,LastName Smith。

因此,索引#2在服务于更大范围的查询时更有用-但代价是索引键更加膨胀,这将使索引的非叶子页变大。每个系统都会有所不同,因此这里没有经验法则。


作为补充说明,值得指出的是,如果EmployeeID是该表的集群键-假设聚集索引-则无需包含EmployeeID-它存在于所有非聚集索引中,这意味着索引#2可能只是

Employee(DepartmentID, LastName) INCLUDE (EmployeeID)


评论


+1获取更多有用的信息。对于您的最后一点,我进行了测试,如果EmployeeID是聚集索引,则实际上会忽略INCLUDE中EmployeeID的显式使用(基于索引的大小)。尽管我认为这更加明显,并且没有空间不足。

– gbn
2011年5月31日13:38

我完全同意-明确表示总是更好,特别是如果不花钱的话!

–吉姆·麦克劳德(Jim McLeod)
2011年5月31日13:43

以防万一...我的意思是我已经在INCLUDE中测试了集群键(不是显式的EmployeeID),并且没有增加任何空间。在关键列中。

– gbn
2011年6月1日下午4:57

@gbn是,集群键只需要驻留在索引的叶级别,即INCLUDE列所在的位置。将其移入索引键将意味着它也将存在于非叶子页中。这会导致一点点膨胀,但数量却不菲(在中间级页面上,假设为Integer,您将在每个叶级页面上再添加4个字节)。

–吉姆·麦克劳德(Jim McLeod)
2011年6月1日上午11:48

这是一个很好的答案,其中包括本文中所述的某些效果:sqlperformance.com/2014/07/sql-indexes / ...如果您的查询发生更改,则索引要求也将更改。吉姆的答案可能会更好,但@gbn答案可能会更好。

– John K. N.
16年11月1日在14:43

#3 楼

我不确定你是怎么得到第一个的。对于我来说,对于该查询,我将使用:

CREATE NONCLUSTERED INDEX NC_EmpDep 
  ON Employee(DepartmentID)
  INCLUDE (EmployeeID, Lastname)


对于SQL中的几乎所有内容,都没有“硬性规定”。 />但是,在您的示例中,索引将使用的唯一字段是DepartmentID,因为它位于WHERE子句中。您基于DepartmentID选择,然后INCLUDE在索引的叶节点处具有这些字段。

您不想使用其他示例,因为它们不适用于该索引。 >
像电话簿这样的索引。大多数电话簿按姓,名,中间名首字母排序。如果您知道某人的名字,但不知道他们的姓氏,则电话簿对您不利,因为您无法根据电话簿的索引顺序搜索名字。

INCLUDE字段就像书中每个条目的电话号码,地址等其他信息。

编辑:

为进一步阐明为什么不使用的原因:

CREATE NONCLUSTERED INDEX NC_EmpDep 
          ON Employee( EmployeeID, LastName)
INCLUDE (DepartmentID)


此索引仅在EmployeeID子句中同时具有EmployeeIDLastNameWHERE时才有用。这几乎是此查询所需的条件。

评论


@ajbeaven是正确的,这就是为什么我在编辑中添加注释说您需要EITHER employeeID或同时使用两列的原因。

– JNK
18年6月7日在19:37

杜尔抱歉读错了:(

–ajajaven
18年6月7日在23:31

#4 楼

我认为您也许仍然可以使用(employee_id,department_id)索引,但是您必须在where短语中包含'dummy'行,例如:
“ employee_id = employee_id)


在(employee_id,departemnent_id)上具有索引,
仅在Department_id上进行搜索/限制
知道由于错误的顺序(或发生了变化,它不会使用该索引)到现在为止,不再需要下面的“技巧”。我是“老”吗?)。

使用“老” tricK吗?

选择* from员工emp
其中emp.employee_id = emp.employee_id
和emp.department_id = 5


(因此,我不在这里关注姓氏的包含部分,但按是/否使用键。)

问候,

Miguell

评论


不,那是没有用的,效率不高。

–超立方体ᵀᴹ
15年5月27日在21:51

具体来说,它仍然必须进行索引扫描以搜索每个员工ID,以查找department_id 5的所有实例。如果有1000个员工和5个部门,SQL必须遍历所有1000名员工以查找特定部门的所有行。

– Mark Sowul
19年9月30日在14:26

现在考虑相反的情况(索引位于department_id,employee_id上​​)。显然,现在查找特定部门很容易,但是还要注意,要查找特定雇员,SQL只需要扫描5个部门以查找特定雇员的所有行。

– Mark Sowul
19-09-30在14:26