我对DB的接触有限,只是作为应用程序程序员使用过DB。我想知道关于聚集和非聚集索引。
我在谷歌上搜索了一下,我发现:
A clustered index is a special type of index that reorders the way
records in the table are physically
stored. Therefore table can have only
one clustered index. The leaf nodes
of a clustered index contain the data
pages. A nonclustered index is a
special type of index in which the
logical order of the index does not
match the physical stored order of
the rows on disk. The leaf node of a
nonclustered index does not consist of
the data pages. Instead, the leaf
nodes contain index rows.
我在SO中发现的是聚集索引和非聚集索引之间的区别是什么?
有人能用通俗易懂的语言解释一下吗?
聚集索引
聚集索引根据表或视图中的键值对数据行进行排序和存储。这些是包含在索引定义中的列。每个表只能有一个聚集索引,因为数据行本身只能按一种顺序排序。
只有当表中包含聚集索引时,表中的数据行才会按排序顺序存储。当一个表具有聚集索引时,这个表称为聚集表。如果表没有聚集索引,则其数据行存储在称为堆的无序结构中。
非聚集
Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
可以将非键列添加到非聚集索引的叶级,以绕过现有的索引键限制,并执行完全覆盖的索引查询。有关更多信息,请参见创建包含列的索引。有关索引键限制的详细信息,请参见SQL Server最大容量规格。
参考:https://learn.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described
聚集索引意味着您告诉数据库在磁盘上存储实际上彼此接近的接近值。这样做的好处是可以快速扫描/检索某些聚集索引值范围内的记录。
例如,你有两个表,Customer和Order:
Customer
----------
ID
Name
Address
Order
----------
ID
CustomerID
Price
如果希望快速检索某个特定客户的所有订单,则可能希望在订单表的“CustomerID”列上创建聚集索引。这样,具有相同CustomerID的记录将在物理上彼此靠近地存储在磁盘上(集群),从而加快了它们的检索速度。
附注:CustomerID上的索引显然不是唯一的,因此您要么需要添加第二个字段来“唯一”索引,要么让数据库为您处理,但这是另一回事。
Regarding multiple indexes. You can have only one clustered index per table because this defines how the data is physically arranged. If you wish an analogy, imagine a big room with many tables in it. You can either put these tables to form several rows or pull them all together to form a big conference table, but not both ways at the same time. A table can have other indexes, they will then point to the entries in the clustered index which in its turn will finally say where to find the actual data.