SQL 为什么对我的聚集索引进行扫描？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1161303/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why is there a scan on my clustered index?
提问by user142253
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:
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
Why is there an INDEX SCAN on the SIGN table for this query? What would cause an index scan on a clustered index? Thanks
为什么此查询的 SIGN 表上有一个 INDEX SCAN？什么会导致对聚集索引进行索引扫描？谢谢
采纳答案by SqlRyan
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:
这是一篇关于 SQL Server 何时达到“临界点”并从索引搜索切换到索引/表扫描的很好的博客文章：
http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Tipping-Point-Query-Answers.aspx
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.
您可能需要查看查询的过滤方式，因为临界点通常比人们预期的行少得多。
回答by zinglon
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.
聚集索引扫描是 SQL Server 指定对具有聚集索引的表进行全表扫描的方式。这是因为 SIGN 表上没有足够的索引来满足 WHERE 子句，或者因为它决定 SIGN 表足够小（或索引不够选择性），表扫描会更有效。
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.
仅通过检查查询，您可能必须索引 DeptID 列以及 StartTime、EndTime 和 NEDStatusID 的某些组合以避免表扫描。如果您询问的原因是因为您遇到了性能问题，您还可以运行索引优化向导（现在是 SQL2005+ 客户端工具中的数据库引擎优化顾问），并让它就创建哪些索引以加快速度提供一些建议提出您的查询。
回答by MyItchyChin
Because your WHERE clause isn't against indexed columns.
因为您的 WHERE 子句不针对索引列。
回答by marc_s
You have several restrictions on the SIGN A table, if I read this correctly:
如果我没看错，您对 SIGN A 表有几个限制：
WHERE
(A.DeptID = @DeptID OR
S.DeptID = @DeptID
AND
A.[EndTime] > @StartDateTime AND A.[StartTime] < @EndDateTime
AND
A.NEDStatusID = 2
Are any of those restrictions (like DeptID, StartTime, EndTime, NEDStatusID) indexed? How well are those field selecting from your set of data?
这些限制（如 DeptID、StartTime、EndTime、NEDStatusID）是否已编入索引？这些字段从您的数据集中选择的程度如何？
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.).
如果你有10个mio。行和 NEDStatusID 只有 10 个可能的值，那么对该字段的任何限制总是会产生大约。1 米欧。行 - 在这种情况下，SQL Server 执行全表扫描（聚集索引扫描）可能更容易（且成本更低），特别是如果它还需要检查同一表上未编入索引的其他 WHERE 子句，或者（开始时间、结束时间等）。
Marc
马克
