SQL 哪个更好：书签/键查找或索引扫描
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1395275/
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
Which is better: Bookmark/Key Lookup or Index Scan
提问by OMG Ponies
I know that an index seekis better than an index scan, but which is preferable in SQL Server explain plans: Index seek or Key Lookup (Bookmark in SQL Server 2000)?
我知道索引查找比索引扫描更好，但是在 SQL Server 解释计划中哪个更可取：索引查找或键查找（SQL Server 2000 中的书签）？
Please tell me they didn't change the name again for SQL Server 2008...
请告诉我他们没有再次更改 SQL Server 2008 的名称...
回答by gbn
Index seek, every time.
索引查找，每次。
Lookups are expensive, so this is covering indexes and especially the INCLUDE clause was added to make them better.
查找是昂贵的，所以这涵盖了索引，特别是添加了 INCLUDE 子句以使它们更好。
Saying that if you expect exactly one row, for example, a seek followed a lookup can be better than trying to cover a query. We rely on this to avoid yet another index in certain situations.
比如说，如果您只期望一行，那么在查找之后进行查找可能比尝试覆盖查询更好。在某些情况下，我们依靠它来避免另一个索引。
Edit: Simple talk article: Using Covering Indexes to Improve Query Performance
编辑：简单的谈话文章：使用覆盖索引提高查询性能
Edit, Aug 2012
编辑，2012 年 8 月
Lookups happen per rowwhich is why they scale badly. Eventually, the optimiser will choose a clustered index scan instead of a seek+lookup because it's more efficient than many lookups.
每行都会进行查找，这就是为什么它们的伸缩性很差。最终，优化器将选择聚集索引扫描而不是搜索+查找，因为它比多次查找更有效。
回答by Remus Rusanu
Key lookupis verysimilar to a clustered index seek (pre 2005 SP2 was named 'seek with lookup'). I think the only difference is that the Key Lookup may specify an additional PRE-FETCH argument instructing the execution engine to prefetch more keys in the cluster (ie. do a clustered index seek followed by scan).
键查找是非常相似的一个聚集索引寻找（前2005 SP2被命名为“寻求与查找”）。我认为唯一的区别是 Key Lookup 可能会指定一个额外的 PRE-FETCH 参数，指示执行引擎在集群中预取更多的键（即，先进行聚集索引搜索，然后进行扫描）。
Seeing a Key Lookup should not scare you. Is the normal operator used in Nested Loops, and Nested Loops is the run-of-the-mill join operator. If you want to improve a plan, try improving on the join and see if it can use a merge join instead (ie. both sides of join can provide rows on the same key order, fastest join) or a hash-join (have enough memory for the QO to consider a hash join, or reduce the cardinality by filtering rows before the join rather than after).
看到 Key Lookup 应该不会吓到你。是嵌套循环中使用的普通运算符，嵌套循环是普通的连接运算符。如果你想改进一个计划，试着改进连接，看看它是否可以使用合并连接代替（即连接的两侧可以提供相同键顺序的行，最快的连接）或散列连接（有足够的QO 考虑散列连接的内存，或者通过在连接之前而不是之后过滤行来减少基数）。
回答by Dustin Venegas
This SO questionmentions that key lookups are something to avoid. An index seek is going to definitely be the better performing operation.
这个SO question提到关键查找是要避免的。索引查找肯定是性能更好的操作。
