,我发现对于以下查询:
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)
是什么促使海报发布者决定保留包括姓氏列。为什么不另列?以及如何决定应以什么顺序将列保留在那里?
#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
子句中同时具有EmployeeID
或LastName
和WHERE
时才有用。这几乎是此查询所需的条件。评论
@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
评论
在找到记录后,INCLUDE通常应该具有您需要的字段,这样可以节省往返行程以获取更多数据。 INCLUDE中字段的顺序并不重要。Ryk,我个人认为这篇文章很有帮助。
我觉得这个问题也有帮助。让我们专注于好的问题和好的答案,而不是跟踪个人....