了解群集和非群集索引 [英] Understanding Clustered and Non Clustered Index

问题描述

大家好，

任何人都可以帮助我清楚地了解索引，聚集和非聚集索引。我已经阅读了很多关于这个但却无法理解的文章。



谢谢。

解决方案

我认为这应该足够清楚了： MS SQL Server中的索引 [ ^ ]



重点：



- 表只能有一个聚簇索引

- 默认情况下，在表上创建主键时，将在表上创建聚簇索引

- 非聚集索引使用聚簇索引（如果已定义）或堆来构建自身。

聚簇索引也称为索引组织表（IOT）。这是一个更具描述性的名称。

主键索引是表。因此，要获得记录，您只需要搜索索引。数据库不需要获取对RowID的引用来获取数据。

因此使用主键索引时速度非常快。



但是，由于BTree索引的性质是它的自我平衡，你在记录上没有RowID。这意味着二级索引需要引用主索引来获取记录。因此使用二级索引比在普通表上慢。



另一个缺点是所有字段都在索引中，所以当你有多个字段时它不适合使用一个实际上没有在索引中使用的字段。

这也意味着当你有大量不同大小的字段（例如大varchar字段或二进制数据字段）时，IOT不适合使用。 />


因此使用非常有限，我或多或少只在用于一个方向的连接表中使用它们。



一些写得很好的信息这里 [ ^ ]。

Hi everyone,
Can anyone please help me to get understanding about index, Clustered and Non Clustered Index clearly. I have read many articles about this but not able to understand.

Thanks.

解决方案

I think this should be clear enough : Indexes in MS SQL Server[^]

Important points:

- Table can have only one clustered index
- Clustered index will be created on a table by default the moment a primary key is created on the table
- Non-clustered index uses a clustered index (if defined) or the heap to build itself.

Clustered indexes are also known as Index Organized Tables (IOT). Which is a more descriptive name.
The primary key index IS the table. So to get a record you only need to search the index. The DB doesn't need to get a reference to the RowID to fetch the data.
So it's very fast when using the primary key index.

But as the nature of a BTree index is that it's self balancing you don't have a RowID on the record. This means that secondary indexes needs to reference the primary index to get a record. So using secondary indexes is slower than on a normal table.

Another drawback is that ALL fields will be in the index, so it's unfit for using whenever you have more than one field that's not actually used in the index.
It also means that IOTs are unfit for using when you have fields with (largely) varying sizes, such as large varchar fields or binary data fields.

So usage is quite limited, I'm more or less only using them in junction tables that's used for one direction joins only.

Some well written info here[^].

这篇关于了解群集和非群集索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！