在理解聚簇索引时我错过了什么？ [英] What do I miss in understanding the clustered index?
问题描述
没有任何索引时，表行通过IAM（索引分配映射）访问。
我可以使用IAM以编程方式直接访问行吗？
In absence of any index the table rows are accessed through IAM ((Index Allocation Map).
Can I directly access a row programmatically using IAM?
不存在索引意味着读取特定行的唯一方法是全表扫描读取所有表？
为什么IAM不能参与更具体的直接访问？
Does absence of index mean that the only way to read specific row is full table scan reading all table?
Why IAM cannot be engaged for more specific direct access?
如果表是堆（换句话说，它没有聚集索引），书签是一个行标识符，它是文件＃中的实际行定位器：Page＃：Slot＃[1a]
"If the table is a heap (in other words, it has no clustered index), the bookmark is a row identifier (RID), which is an actual row locator in the form File#:Page#:Slot#" [1a]
现在，介绍一下集群的数据库索引意味着不能直接访问数据，而只能通过最终聚簇索引查找或顺序遍历聚簇叶节点。
Now, introduction of clustered index means that no data can be directly accessed but only through eventually clustered index lookup or traversing clustered leaf nodes sequentially.
我能正确理解聚集索引的引入仅对选择连续的相邻（范围）行和仅通过聚簇索引键有用吗？
还有什么好处是聚类表？
Do I understand correctly that introduction of clustered indexes is beneficial only for selecting continuous adjacent (ranges of) rows and only through clustered index keys?
Which else benefits are in clustering a table?
我是否正确理解聚集索引引入会加重非精确匹配查询的非聚集索引参与的性能优势？没有直接访问，顺序访问不能并行化，非聚簇索引通过聚集索引键等增加，是否正确？
Do I understand correctly that clustered index introduction worsen the performance benefits of non-clustered indexes engagement for non-exact match queries? No direct access, sequential access cannot be parallelized, non-clustered indexes are increased by clustered index keys, etc., correct?
好吧，我看到，对一个表进行聚簇对于非常具体和很好理解的上下文是有意义的，而创建主键时，在聚簇表中总是默认。为什么？
Well, I see that clustering a table makes sense for quite specific and well understood contexts while creation of primary keys always default in clustering a table. Why is it?
在聚集索引理解中我错过了什么？
What do I miss in clustered indexes understanding?
[1]
内部Microsoft®SQL Server™2005：存储引擎
由Kalen Delaney - 学习）
......................................... ...
发布商：Microsoft Press
出版日期：2006年10月11日
打印ISBN-10：0-7356-2105-5
打印ISBN-13：978-0-7356-2105-3
页数：464
[1]
Inside Microsoft® SQL Server™ 2005: The Storage Engine
By Kalen Delaney - (Solid Quality Learning)
...............................................
Publisher: Microsoft Press
Pub Date: October 11, 2006
Print ISBN-10: 0-7356-2105-5
Print ISBN-13: 978-0-7356-2105-3
Pages: 464
[1a] p .250 Section Index Organisation from Chapter 7. Index Internals and Management
[1a] p.250 Section Index organization from Chapter 7. Index Internals and Management
这里有帮助的在线copypaste从它
http://sqlserverindexeorgnization.blogspot.com/
虽然没有任何来源来源
Here is helpful online copypaste from it
http://sqlserverindexeorgnization.blogspot.com/
though without any credits to source
相关问题：
- No direct access to data row in clustered table - why?
- Why/when/how is whole clustered index scan chosen rather than full table scan?
- Reasons not to have a clustered index in SQL Server 2005
更新： @PerformanceDBA，
Update: @PerformanceDBA,
- 请忘记您的参考文献并重新开始
- "please, forget the doco you reference and start again"
再次启动我的基础是什么？ >
任何引用，任何建议。技术如何重新开始？
Starting me again on the basis of what?
Any references, any advices. techniques how to start again?
- **群集索引总是更好
您能回答我的问题为什么/何时/如何选择整个聚簇索引扫描而不是全表扫描？全集群索引扫描的意义是什么？它不会比全表扫描多吗？
Can you answer my question Why/when/how is whole clustered index scan chosen rather than full table scan? The doubt is what is the meaning of Full Clustered Index Scan. Does not it read more than Full Table Scan?
- 如果有IAM，则有一个索引
那么，如果没有索引，那么没有IAM？
如果有CI，有IAM？
So, there is no IAM if there is no index at all?
There is IAM if there is CI?
我应该如何验证/研究它？
如果所有文档写的相反：
- 在非索引表上有IAM
- 如果有聚集索引，没有IAM。
How am I supposed to verify/study it?
if all docs write the opposite:
- there is IAM on non-indexed table
- there is no IAM if there is clustered index.
推荐答案
这是很多问题，是IAM用来查找不同的是，没有索引，没有办法知道要为任何给定的数据块检索什么页面。数据的SQL /关系模型的一个重要特征是查询仅通过数据值访问数据 -
That's a lot of questions. Yes the IAM is used to look up pages on a heap. The difference is that without an index there is no way to know what pages to retrieve for any given piece of data. An important feature of the SQL / relational model of data is that queries access data by data values only - never by using pointers or other structures directly.
一个槽号只是标识一个页面中的一行，即使在一个页面中，行数据在逻辑上是不排序的，即使在聚簇索引中。每个数据页包含一个指向页面中行的位置的行偏移表。
A slot number just identifies a row within a page. Row data is not logically ordered within a page, even in a clustered index. Each data page contains a row offset table that points to the position of rows within a page.
聚簇索引可以减慢非聚簇索引的数据访问，因为额外需要书签查找。这可以通过使用INCLUDE子句将列添加到NC索引来缓解。有时，在表上没有聚簇索引可能更有效。
A clustered index can slow down data access from nonclustered indexes because of the additional bookmark lookups required. This can be mitigated by using the INCLUDE clause to add columns to a NC index. Sometimes it may be more efficient not to have a clustered index on a table.
这篇关于在理解聚簇索引时我错过了什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
