为什么对我的聚集索引进行扫描？
SQL 2000
NED 表有一个到 SIGN 表 NED.RowID 到 SIGN.RowID 的外键
SIGN 表有一个到 NED 表 SIGN.SignID 到 NED.SignID 的外键
RowID 和 SignID 是 GUID 的聚集主键（不是我的选择）
WHERE 子句是：
FROM
[SIGN] A
INNER JOIN NED N ON A.SIGNID = N.SIGNID
INNER JOIN Wizard S ON A.WizardID = S.WizardID
INNER JOIN [Level] SL ON N.LevelID = SL.LevelID
LEFT JOIN Driver DSL ON SL.LevelID = DSL.LevelID
AND DSL.fsDeptID = @fsDeptID
INNER JOIN [Character] ET ON S.CharacterID = ET.CharacterID
INNER JOIN Town DS ON A.TownID = DS.TownID
WHERE
(A.DeptID = @DeptID OR
S.DeptID = @DeptID
AND
A.[EndTime] > @StartDateTime AND A.[StartTime] < @EndDateTime
AND
A.NEDStatusID = 2
为什么此查询的 SIGN 表上有 INDEX SCAN？ 什么会导致聚集索引上的索引扫描？ 谢谢
SQL 2000
The NED table has a foreign key to the SIGN table NED.RowID to SIGN.RowID
The SIGN table has a foreign key to the NED table SIGN.SignID to NED.SignID
The RowID and SignID are clustered primary keys that are GUIDs (not my choice)
The WHERE clause is:
FROM
[SIGN] A
INNER JOIN NED N ON A.SIGNID = N.SIGNID
INNER JOIN Wizard S ON A.WizardID = S.WizardID
INNER JOIN [Level] SL ON N.LevelID = SL.LevelID
LEFT JOIN Driver DSL ON SL.LevelID = DSL.LevelID
AND DSL.fsDeptID = @fsDeptID
INNER JOIN [Character] ET ON S.CharacterID = ET.CharacterID
INNER JOIN Town DS ON A.TownID = DS.TownID
WHERE
(A.DeptID = @DeptID OR
S.DeptID = @DeptID
AND
A.[EndTime] > @StartDateTime AND A.[StartTime] < @EndDateTime
AND
A.NEDStatusID = 2
Why is there an INDEX SCAN on the SIGN table for this query? What would cause an index scan on a clustered index? Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
聚集索引扫描是 SQL Server 对具有聚集索引的表指定全表扫描的方式。 这是因为 SIGN 表上没有足够的索引来满足 WHERE 子句，或者因为它决定 SIGN 表足够小（或索引选择性不够），表扫描会更有效。
仅通过检查查询，您可能必须对 DeptID 列以及 StartTime、EndTime 和 NEDStatusID 的某种组合建立索引，以避免表扫描。 如果您询问的原因是遇到性能问题，您还可以运行索引调优向导（现在是 SQL2005+ 客户端工具中的数据库引擎调优顾问），并让它提供一些关于创建哪些索引以加快速度的建议提出您的查询。
A clustered index scan is how SQL Server designates a full table scan on a table with a clustered index. This is because you don't have enough indexes on the SIGN table to satisfy the WHERE clause, or because it decided that the SIGN table is small enough (or the indexes not selective enough) that a table scan would be more efficient.
Just by examining the query, you'd probably have to index the DeptID column as well as some combination of StartTime, EndTime and NEDStatusID to avoid the table scan. If the reason you're asking is because you're having performance problems, you can also run the Index Tuning Wizard (now the Database Engine Tuning Advisor in the SQL2005+ client tools) and have it give some advice on which indexes to create to speed up your query.
因为您的 WHERE 子句不针对索引列。
Because your WHERE clause isn't against indexed columns.
这是一篇关于 SQL Server 何时达到“临界点”并从索引查找切换到索引/表扫描的好博文：
http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Tipping-Point-Query-Answers.aspx
您可以想要查看查询的过滤方式，因为临界点通常比人们预期的行数少得多。
Here's a good blog post about when SQL Server reaches the "tipping point" and switches from an index seek to an index/table scan:
http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Tipping-Point-Query-Answers.aspx
You may want to look at the way your queries are filtering, as the tipping point is often much fewer rows than people expect.
如果我没读错的话，您对 SIGN A 表有几个限制：
这些限制（如 DeptID、StartTime、EndTime、NEDStatusID）是否已建立索引？ 这些字段从您的数据集中进行选择的效果如何？
如果你有10米奥。 rows 和 NEDStatusID 只有 10 个可能的值，那么对该字段的任何限制总是会产生大约。 1 米奥。 行 - 在这种情况下，SQL Server 执行全表扫描（聚集索引扫描）可能会更容易（且成本更低），特别是如果它还需要检查同一表上未建立索引的其他 WHERE 子句，任一（开始时间、结束时间等）。
马克
You have several restrictions on the SIGN A table, if I read this correctly:
Are any of those restrictions (like DeptID, StartTime, EndTime, NEDStatusID) indexed? How well are those field selecting from your set of data?
If you have 10 mio. rows and NEDStatusID has only 10 possible values, then any restriction on that field would always yield approx. 1 mio. rows - in that case, it might just be easier (and less costly) for SQL Server to do a full table scan (clustered index scan), especially if it also needs to check additional WHERE clauses on the same table that aren't indexed, either (StartTime, EndTIme etc.).
Marc