什么是 Sql Server 中的书签查找? [英] What is a Bookmark Lookup in Sql Server?

问题描述

我正在尝试优化查找历史数据的查询.我正在使用查询分析器来查找执行计划，并发现我的大部分查询成本都在称为书签查找"的东西上.我以前从未在执行计划中看到过这个节点，不知道它是什么意思.

I'm in the process of trying to optimize a query that looks up historical data. I'm using the query analyzer to lookup the Execution Plan and have found that the majority of my query cost is on something called a "Bookmark Lookup". I've never seen this node in an execution plan before and don't know what it means.

这在查询中是好事还是坏事?

Is this a good thing or a bad thing in a query?

推荐答案

书签查找是根据在非聚集索引中找到的条目在 SQL 表中查找实际数据的过程.

A bookmark lookup is the process of finding the actual data in the SQL table, based on an entry found in a non-clustered index.

当你在非聚集索引中搜索一个值，并且你的查询需要比索引叶节点(所有索引字段，加上任何可能的 INCLUDE 列)的一部分更多的字段时，那么 SQL Server 需要去检索实际的数据页 - 这就是所谓的书签查找.

When you search for a value in a non-clustered index, and your query needs more fields than are part of the index leaf node (all the index fields, plus any possible INCLUDE columns), then SQL Server needs to go retrieve the actual data page(s) - that's what's called a bookmark lookup.

在某些情况下，这确实是唯一的方法 - 仅当您的查询仅需要一个字段(而不是一大堆)时，最好将该字段包含在非集群中指数.在这种情况下，非聚集索引的叶级节点将包含满足查询所需的所有字段(覆盖"索引)，因此不再需要书签查找.

In some cases, that's really the only way to go - only if your query would require just one more field (not a whole bunch of 'em), it might be a good idea to INCLUDE that field in the non-clustered index. In that case, the leaf-level node of the non-clustered index would contain all fields needed to satisfy your query (a "covering" index), and thus a bookmark lookup wouldn't be necessary anymore.

马克

这篇关于什么是 Sql Server 中的书签查找?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！