我对DB的了解有限,并且只使用DB作为应用程序员 . 我想知道 Clustered
和 Non clustered indexes
. 我用谷歌搜索,发现的是:
聚簇索引是一种特殊类型的索引,它重新排序表中记录的物理存储方式 . 因此,表只能有一个聚簇索引 . 聚簇索引的叶节点包含数据页 . 非聚簇索引是一种特殊类型的索引,其中索引的逻辑顺序与磁盘上行的物理存储顺序不匹配 . 非聚簇索引的叶节点不包含数据页 . 相反,叶节点包含索引行 .
我在SO中找到的是What are the differences between a clustered and a non-clustered index? .
有人可以用简单的英语解释这个吗?
10 回答
聚簇索引意味着您要告诉数据库在磁盘上存储实际上彼此接近的近似值 . 这具有快速扫描/检索落入某些聚集索引值范围的记录的益处 .
例如,您有两个表,Customer和Order:
如果您希望快速检索某个特定客户的所有订单,您可能希望在Order表的“CustomerID”列上创建聚簇索引 . 这样,具有相同CustomerID的记录将在磁盘(群集)上彼此靠近地物理存储,这加速了它们的检索 .
附: CustomerID上的索引显然不是唯一的,因此您需要添加第二个字段来“unquify”索引或让数据库为您处理,但这是另一个故事 .
关于多个索引 . 每个表只能有一个聚簇索引,因为它定义了数据的物理排列方式 . 如果你想要一个类比,想象一个有很多 table 的大房间 . 您可以将这些表格放在一起形成多行,也可以将它们全部拉到一起形成一个大型 Session 桌,但不能同时形成两种方式 . 一个表可以有其他索引,然后它们将指向聚集索引中的条目,而这些条目最终将说明在哪里找到实际数据 .
我意识到这是一个非常古老的问题,但我想我会提供一个类比来帮助说明上面的好答案 .
CLUSTERED INDEX
如果你走进一个公共图书馆,你会发现这些书都按照特定的顺序排列(很可能是杜威十进制系统,或DDS) . 这相当于"clustered index"的书籍 . 如果你想要的书的DDS#是
005.7565 F736s
,你首先要找到标有001-099
的书架行或类似的东西 . (堆栈末尾的此endcap符号对应于索引中的"intermediate node" . )最后,您将向下钻取到标记为005.7450 - 005.7600
的特定工具架,然后您将扫描,直到找到具有指定DDS#的书,并且此时你找到了自己的书 .非集群索引
但是如果你没有带着你记忆的书的DDS#进入图书馆,那么你需要第二个索引来帮助你 . 在过去的日子里,你会发现在图书馆前面有一个很棒的抽屉柜,叫做"Card Catalog" . 其中有数千张3x5卡片 - 每本书一张,按字母顺序排列(可能是 Headers ) . 这对应于"non-clustered index" . 这些卡片目录以分层结构组织,因此每个抽屉将标有其包含的卡片范围(例如,
Ka - Kl
,即"intermediate node") . 再一次,你会钻进去,直到找到你的书,但在这种情况下,一旦你找到它(即"leaf node"),你就没有这本书本身,而只是一张带有索引号的卡片(DDS) #),您可以使用它在聚集索引中找到实际的书 .当然,没有什么可以阻止图书管理员复印所有卡片并在单独的卡片目录中以不同的顺序对它们进行分类 . (通常至少有两个这样的目录:一个按作者名称排序,一个按 Headers 排序 . )原则上,您可以拥有所需数量的这些“非群集”索引 .
下面列出了聚簇索引和非聚簇索引的一些特征:
聚集索引
聚簇索引是唯一标识SQL表中的行的索引 .
每个表只能有一个聚簇索引 .
您可以创建一个涵盖多个列的聚簇索引 . 例如:
create Index index_name(col1, col2, col.....)
.默认情况下,具有主键的列已具有聚簇索引 .
非聚集索引
使用聚簇索引,行以与索引相同的顺序物理存储在磁盘上 . 因此,只能有一个聚簇索引 .
对于非聚集索引,还有第二个列表,其中包含指向物理行的指针 . 尽管每个新索引都有,但您可以拥有许多非聚簇索引将增加编写新记录所需的时间 .
如果要返回所有列,通常从聚簇索引中读取更快 . 您不必首先访问索引,然后再访问表 .
如果需要重新排列数据,则写入具有聚簇索引的表可能会更慢 .
让我在"clustering index"上提供一个教科书定义,该定义取自Database Systems: The Complete Book的15.6.1:
为了理解这个定义,让我们看一下教科书提供的例15.10:
请注意,该定义不强制数据块必须在磁盘上连续;它只是说带有搜索键的元组被打包成尽可能少的数据块 .
相关概念是聚类关系 . 一个关系是"clustered",如果它的元组被打包成大约几乎可以容纳那些元组的块 . 换句话说,从磁盘块的角度来看,如果它包含来自不同关系的元组,那么这些关系就不能被聚类(即,通过将该关系的元组与其他磁盘块交换,存储这种关系的方式更加紧凑 . 元组不属于当前磁盘块中的关系) . 显然,上面例子中的
R(a,b)
是聚集的 .要将两个概念连接在一起,聚簇关系可以具有聚簇索引和非聚簇索引 . 但是,对于非聚簇关系,除非索引构建在关系的主键之上,否则无法进行聚簇索引 .
"Cluster"作为一个单词在数据库存储端的所有抽象级别上发送垃圾邮件(三个抽象级别:元组,块,文件) . 一个名为“clustered file”的概念,它描述文件(一组块(一个或多个磁盘块)的抽象)是否包含来自一个关系或不同关系的元组 . 它与聚类索引概念无关,因为它在文件级别上 .
但是,有些teaching material喜欢根据集群文件定义定义集群索引 . 这两种类型的定义在集群关系级别上是相同的,无论它们是根据数据磁盘块还是文件定义集群关系 . 从本段链接,
连续存储元组与说“元组被包装成可能容纳那些元组的大约几个块”(在一个谈论文件,另一个谈论磁盘的细微差别)相同 . 这是因为连续存储元组是实现“封装成可能容纳那些元组的大约几个块”的方法 .
Clustered Index: 如果表中不存在聚簇索引,则主键约束会自动创建聚簇索引 . 聚集索引的实际数据可以存储在索引的叶级 .
Non Clustered Index: 非聚集索引的实际数据不是在叶节点处直接找到的,而是必须采取额外的步骤来查找,因为它只有行定位符的值指向实际数据 . 非聚集索引无法排序为聚簇索引 . 每个表可以有多个非聚集索引,实际上它取决于我们使用的sql server版本 . 基本上,Sql server 2005允许249个非群集索引,对于像2008年,2016这样的上述版本,它允许每个表999个非群集索引 .
在SQL Server面向行的存储中,聚簇索引和非聚簇索引都组织为B树 .
(Image Source)
聚簇索引和非聚簇索引之间的关键区别在于聚簇索引的叶级别 is 表 . 这有两个含义 .
聚簇索引叶子页上的行总是包含表中每个(非稀疏)列的内容(值或指向实际值的指针) .
聚簇索引是表的主副本 .
非聚簇索引也可以通过使用
INCLUDE
子句(自SQL Server 2005)来明确包含所有非键列,但它们是次要表示,并且总是有另一个数据副本(表格)本身) .上面的两个指数几乎相同 . 上层索引页面包含键列
A,B
的值和包含A,B,C,D
的叶级页面SQL Server在线书籍的上述引用引起了很多混乱
在我看来,它会更好地表达为 .
书籍在线报价并不正确,但您应该清楚,非聚集索引和聚簇索引的"sorting"是逻辑的而非物理的 . 如果按照链接列表读取叶级别的页面并按插槽数组顺序读取页面上的行,那么您将按排序顺序读取索引行,但物理上可能不会对页面进行排序 . 人们普遍认为,对于聚簇索引,行总是以与索引 key 相同的顺序物理存储在磁盘上 .
这将是一个荒谬的实施 . 例如,如果在4GB表的中间插入一行SQL Server,则必须在文件中复制2GB的数据,以便为新插入的行腾出空间 .
而是发生页面拆分 . 聚簇索引和非聚簇索引的叶级别的每个页面都具有逻辑键顺序中下一页和上一页的地址(
File:Page
) . 这些页面不必是连续的或按键顺序 .例如链接的页面链可能是
1:2000 <-> 1:157 <-> 1:7053
当页面拆分发生时,从文件组中的任何位置(从混合范围,对于小表,或属于该对象的非空均匀范围或新分配的统一范围)分配新页面 . 如果文件组包含多个文件,则可能甚至不在同一文件中 .
逻辑顺序和邻接与理想化物理版本的不同程度是逻辑分段的程度 .
在具有单个文件的新创建的数据库中,我运行了以下操作 .
然后检查页面布局
结果到处都是 . 按键顺序的第一行(值为1 - 用下面的箭头突出显示)几乎在最后一个物理页面上 .
可以通过重建或重新组织索引来减少或删除碎片,以增加逻辑顺序和物理顺序之间的相关性 .
跑完之后
我得到了以下内容
如果表没有聚集索引,则称为堆 .
可以在堆或聚簇索引上构建非聚簇索引 . 它们总是包含一个返回基表的行定位器 . 在堆的情况下,这是物理行标识符(rid),由三个组件组成(File:Page:Slot) . 对于聚簇索引,行定位器是逻辑的(聚簇索引键) .
对于后一种情况,如果非聚集索引已经自然地将CI密钥列包括为NCI密钥列或
INCLUDE
-d列,则不添加任何内容 . 否则,将丢失的CI密钥列静默地添加到NCI中 .SQL Server始终确保键列对于两种类型的索引都是唯一的 . 但是,对于未声明为唯一的索引强制执行此操作的机制在两种索引类型之间有所不同 .
对于具有复制现有行的键值的任何行,聚簇索引都会添加
uniquifier
. 这只是一个递增的整数 .对于未声明为唯一SQL Server的非聚簇索引,将行定位器静默添加到非聚簇索引键中 . 这适用于所有行,而不仅仅是那些实际重复的行 .
聚簇与非聚集命名法也用于列存储索引 . 论文Enhancements to SQL Server Column Stores陈述
Clustered Index
聚簇索引确定表中DATA的物理顺序 . 因此,表只有1个聚簇索引 .
喜欢“字典”不需要任何其他索引,它已根据单词索引
Nonclustered Index
非聚集索引类似于Book中的索引 . 数据存储在一个位置 . 索引存储在另一个地方,索引具有指向数据存储位置的指针 . 因此,一个表具有多个非聚簇索引 .
比如“化学书”,在凝视时有一个单独的索引指向章节位置,在“结束”有另一个索引指向常见的词位置
一个非常简单的,非技术性的经验法则是聚簇索引通常用于您的主键(或者,至少是一个唯一列),而非聚簇索引用于其他情况(可能是外键) . 实际上,SQL Server默认会在主键列上创建聚簇索引 . 正如您将了解到的那样,聚集索引与数据在磁盘上物理排序的方式有关,这意味着它在大多数情况下都是一个很好的全面选择 .
Clustered Index
聚簇索引根据键值对表或视图中的数据行进行排序和存储 . 这些是索引定义中包含的列 . 每个表只能有一个聚簇索引,因为数据行本身只能按一个顺序排序 .
表中的数据行以排序顺序存储的唯一时间是表包含聚簇索引 . 当表具有聚簇索引时,该表称为聚簇表 . 如果表没有聚簇索引,则其数据行存储在称为堆的无序结构中 .
Nonclustered
非聚簇索引具有与数据行分开的结构 . 非聚簇索引包含非聚簇索引键值,每个键值条目都有一个指向包含键值的数据行的指针 . 从非聚簇索引中的索引行到数据行的指针称为行定位器 . 行定位器的结构取决于数据页是存储在堆还是聚簇表中 . 对于堆,行定位器是指向该行的指针 . 对于集群表,行定位器是聚簇索引键 .
您可以将非键列添加到非聚簇索引的叶级别,以绕过现有索引键限制,并执行完全覆盖的索引查询 . 有关更多信息,请参阅使用包含的列创建索引 . 有关索引键限制的详细信息,请参阅SQL Server的最大容量规范 .
参考:https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described