代码之家  ›  专栏  ›  技术社区  ›  P.K

集群索引和非集群索引实际上意味着什么?

  •  899
  • P.K  · 技术社区  · 15 年前

    我对DB的接触有限,只作为应用程序程序员使用过DB。我想知道 Clustered Non clustered indexes . 我在谷歌上搜索发现:

    聚集索引是一种特殊类型的索引,它可以对路径进行重新排序 表中的记录是物理的 存储。因此表只能有 页。非聚集索引是 索引的逻辑顺序不正确 匹配的物理存储顺序 磁盘上的行。树的叶节 非聚集索引不包括 数据页。取而代之的是树叶 节点包含索引行。

    我所发现的是 What are the differences between a clustered and a non-clustered index? .

    有人能用简单的英语解释一下吗?

    10 回复  |  直到 3 年前
        1
  •  1217
  •   Alec    5 年前

    使用聚集索引,行以与索引相同的顺序物理存储在磁盘上。因此,只能有一个聚集索引。

    对于非聚集索引,还有第二个列表具有指向物理行的指针。您可以有许多非聚集索引,尽管每个新索引都会增加写入新记录所需的时间。

    如果需要重新排列数据,则使用聚集索引写入表的速度可能会较慢。

        2
  •  612
  •   csano    13 年前

    例如,您有两个表,Customer和Order:

    Customer
    ----------
    ID
    Name
    Address
    
    Order
    ----------
    ID
    CustomerID
    Price
    

    关于多个索引。每个表只能有一个聚集索引,因为这定义了数据的物理排列方式。如果你想要一个类比,想象一个大房间里有许多桌子。你可以把这些桌子排成几行,也可以把它们放在一起组成一个大会议桌,但不能同时两种方式。一个表可以有其他索引,然后它们会指向聚集索引中的条目,而聚集索引最终会指出在哪里可以找到实际数据。

        3
  •  337
  •   Mihir Ajmera Serial    4 年前

    enter image description here

    ( Image Source )

    桌子。这有两个含义。

    1. 聚集索引叶页上的行始终包含 某物
    2. 聚集索引是表的主副本。

    非聚集索引也可以使用 INCLUDE 子句(自SQL Server 2005起)显式包括所有非键列,但它们是辅助表示,并且周围总是有另一个数据副本(表本身)。

    CREATE TABLE T
    (
    A INT,
    B INT,
    C INT,
    D INT
    )
    
    CREATE UNIQUE CLUSTERED INDEX ci ON T(A, B)
    CREATE UNIQUE NONCLUSTERED INDEX nci ON T(A, B) INCLUDE (C, D)
    

    上述两个指标几乎相同。使用包含键列值的上层索引页 A, B A, B, C, D

    每个表只能有一个聚集索引,因为数据行 它们只能按一个顺序进行排序。

    每个表只能有一个聚集索引,因为聚集索引的叶级行 桌子上有两行。

    这本书的在线引用并没有错误,但您应该清楚,非聚集索引和聚集索引的“排序”都是逻辑的,而不是物理的。如果按照链表读取页级别的页,并按插槽数组顺序读取页上的行,则将按排序顺序读取索引行,但物理上可能无法对页进行排序。通常认为,使用聚集索引时,行总是以与索引相同的顺序物理存储在磁盘上 钥匙 这是错误的。

    必须在文件中复制2GB的数据,以便为新插入的行腾出空间。

    相反,会发生页面拆分。聚集索引和非聚集索引的叶级别的每个页面都有地址( File: Page

    e、 g.链接的页面链可能是 1:2000 <-> 1:157 <-> 1:7053

    逻辑顺序和连续性与理想化物理版本的不同程度是逻辑碎片化的程度。

    在一个新创建的带有单个文件的数据库中,我运行了以下命令。

    CREATE TABLE T
      (
         X TINYINT NOT NULL,
         Y CHAR(3000) NULL
      );
    
    CREATE CLUSTERED INDEX ix
      ON T(X);
    
    GO
    
    --Insert 100 rows with values 1 - 100 in random order
    DECLARE @C1 AS CURSOR,
            @X  AS INT
    
    SET @C1 = CURSOR FAST_FORWARD
    FOR SELECT number
        FROM   master..spt_values
        WHERE  type = 'P'
               AND number BETWEEN 1 AND 100
        ORDER  BY CRYPT_GEN_RANDOM(4)
    
    OPEN @C1;
    
    FETCH NEXT FROM @C1 INTO @X;
    
    WHILE @@FETCH_STATUS = 0
      BEGIN
          INSERT INTO T (X)
          VALUES        (@X);
    
          FETCH NEXT FROM @C1 INTO @X;
      END
    

    然后使用检查页面布局

    SELECT page_id,
           X,
           geometry::Point(page_id, X, 0).STBuffer(1)
    FROM   T
           CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
    ORDER  BY page_id
    

    结果到处都是。键顺序的第一行(值为1-下面用箭头突出显示)几乎位于最后一个物理页面上。

    enter image description here

    通过重建或重新组织索引以增加逻辑顺序和物理顺序之间的相关性,可以减少或删除碎片。

    ALTER INDEX ix ON T REBUILD;
    

    我得到了以下信息

    enter image description here

    如果表没有聚集索引,则称为堆。

    非聚集索引可以构建在堆或聚集索引上。它们始终包含返回到基表的行定位器。对于堆,这是一个物理行标识符(rid),由三个组件组成(文件:Page:Slot)。对于聚集索引,行定位器是逻辑的(聚集索引键)。

    对于后一种情况,如果非聚集索引已经自然包含CI键列作为NCI键列或 包括 -d列,则不添加任何内容。否则,丢失的CI键列将以静默方式添加到NCI。

    聚集索引得到一个 uniquifier 为具有与现有行重复的键值的任何行添加。这只是一个升序整数。

    对于未声明为唯一的非聚集索引,SQL Server会将行定位器静默添加到非聚集索引键中。这适用于所有行,而不仅仅是那些实际重复的行。

    Enhancements to SQL Server Column Stores

    尽管列存储数据在任何键上都不是真正的“集群”,但我们 决定保留引用SQL Server的传统惯例 将主索引作为聚集索引。

        4
  •  167
  •   Community noseratio    4 年前

    聚集索引

    如果你走进一家公共图书馆,你会发现所有的书都是按特定的顺序排列的(最有可能是杜威十进制,或DDS)。这与 “聚集索引” 一本书。如果你想要的书的DDS是 005.7565 F736s 001-099 或者类似的。(堆栈末尾的endcap符号对应于索引中的“中间节点”)。最终,您将深入到标记为的特定工具架 005.7450 - 005.7600 ,然后进行扫描,直到找到具有指定DDS#的书籍,然后 你找到你的书了。

    非聚集索引

    但是,如果你没有带着你书中的DDS记忆进入图书馆,那么你需要第二个索引来帮助你。在过去的日子里,你会发现在图书馆的前面有一个奇妙的抽屉柜,被称为“卡片目录”。里面有数千张3x5的卡片——每本书一张,按字母顺序排序(也许是按书名排序)。这与 “非聚集索引” . 这些卡片目录是按层次结构组织的,这样每个抽屉都会贴上它所包含的卡片范围的标签( Ka - Kl 例如i、 即“中间节点”)。再一次,你会钻进去直到找到你的书,但是 在这种情况下,一旦您找到它(即“叶子节点”),您就没有书本身,只有一张带有 指数 在聚集索引中可以找到实际图书的编号(DDS)。

    当然,没有什么能阻止图书管理员复印所有卡片,并将它们按不同的顺序分类到单独的卡片目录中。(通常至少有两个这样的目录:一个按作者姓名排序,另一个按标题排序。)原则上,您可以拥有任意数量的这些“非聚集”索引。

        5
  •  73
  •   Community noseratio    4 年前

    以下是聚集索引和非聚集索引的一些特征:

    聚集索引

    1. 聚集索引是唯一标识SQL表中的行的索引。
    2. 您可以创建覆盖多个列的聚集索引。例如: create Index index_name(col1, col2, col.....) .
    3. 默认情况下,具有主键的列已经具有聚集索引。

    非聚集索引

    1. 非聚集索引与简单索引类似。它们只是用于快速检索数据。不确定是否有唯一的数据。
        6
  •  53
  •   Dan Diplo    15 年前

    一个非常简单、非技术性的经验法则是,聚集索引通常用于主键(或者,至少是唯一列),非聚集索引用于其他情况(可能是外键)。实际上,SQL Server默认情况下会在主键列上创建聚集索引。正如您将了解到的,聚集索引与数据在磁盘上的物理排序方式有关,这意味着对于大多数情况,它是一个很好的全面选择。

        7
  •  53
  •   Konstantinos Nikoloutsos    4 年前

    聚集索引

    聚集索引确定表中数据的物理顺序。因此,表只有1个聚集索引。

    • " “不需要任何其他索引,它已经根据文字索引了。”

    非聚集索引

    非聚集索引类似于书中的索引。数据存储在一个地方。这个

    • " 化学
        8
  •  9
  •   Vlad Mihalcea    3 年前

    聚集索引

    聚集索引基本上是树组织的表。与将记录存储在未排序的堆表空间不同,聚集索引实际上是B+树索引,其叶节点(按集群键列值排序)存储实际的表记录,如下图所示。

    Clustered Index

    聚集索引是SQL Server和MySQL中的默认表结构。即使表没有主键,MySQL也会添加隐藏的聚集索引,而如果表有主键列,SQL Server总是构建聚集索引。否则,SQL Server将存储为堆表。

    聚集索引可以加速通过聚集索引键过滤记录的查询,就像通常的CRUD语句一样。由于记录位于叶节点中,因此在按主键值定位记录时,不需要额外查找额外的列值。

    SELECT PostId, Title
    FROM Post
    WHERE PostId = ? 
    

    您可以看到,执行计划使用聚集索引查找操作来定位包含 Post

    |StmtText                                                                             |
    |-------------------------------------------------------------------------------------|
    |SELECT PostId, Title FROM Post WHERE PostId = @P0                                    |
    |  |--Clustered Index Seek(OBJECT:([high_performance_sql].[dbo].[Post].[PK_Post_Id]), |
    |     SEEK:([high_performance_sql].[dbo].[Post].[PostID]=[@P0]) ORDERED FORWARD)      | 
    
    Table 'Post'. Scan count 0, logical reads 2, physical reads 0
    

    非聚集索引

    次要索引将主键值存储在其叶节点中,如下图所示:

    Non-Clustered Index

    Title 邮递 表:

    CREATE INDEX IDX_Post_Title on Post (Title)
    

    我们执行以下SQL查询:

    SELECT PostId, Title
    FROM Post
    WHERE Title = ? 
    

    我们可以看到,索引查找操作用于在 IDX_Post_Title

    |StmtText                                                                      |
    |------------------------------------------------------------------------------|
    |SELECT PostId, Title FROM Post WHERE Title = @P0                              |
    |  |--Index Seek(OBJECT:([high_performance_sql].[dbo].[Post].[IDX_Post_Title]),|
    |     SEEK:([high_performance_sql].[dbo].[Post].[Title]=[@P0]) ORDERED FORWARD)|
    
    Table 'Post'. Scan count 1, logical reads 2, physical reads 0
    

    自从联合国 PostId 主键列值存储在 IDX_职位名称 叶节点,此查询不需要额外的查找来定位 邮递 聚集索引中的行。

        9
  •  6
  •   marvelTracker    7 年前

    聚集索引根据数据行的键值在表或视图中排序和存储数据行。这些是索引定义中包含的列。每个表只能有一个聚集索引,因为数据行本身只能按一个顺序排序。

    表中的数据行仅在表包含聚集索引时按排序顺序存储。当表具有聚集索引时,该表称为聚集表。如果表没有聚集索引,则其数据行存储在称为堆的无序结构中。

    非聚集

    非聚集索引具有与数据行分离的结构。非聚集索引包含非聚集索引键值,每个键值项都有一个指向包含键值的数据行的指针。 从非聚集索引中的索引行到数据行的指针称为行定位器。行定位器的结构取决于数据页是存储在堆中还是存储在集群表中。对于堆,行定位器是指向该行的指针。对于聚集表,行定位器是聚集索引键。

    您可以将非键列添加到非聚集索引的叶级别,以绕过现有索引键限制,并执行完全覆盖、索引的查询。有关详细信息,请参见创建包含列的索引。有关索引键限制的详细信息,请参阅SQL Server的最大容量规范。

    参考: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described

        10
  •  4
  •   xxks-kkk    6 年前

    让我提供一个关于“聚类指数”的教科书定义,该定义取自 Database Systems: The Complete Book :

    我们也可以说 聚类索引 ,它们是一个或多个属性上的索引,因此该索引的搜索键具有固定值的所有元组都会显示在尽可能少的块上。

    亲戚 R(a,b) a 并储存在 打包成块的订单肯定是杂乱无章的。索引 A. 聚类索引,因为对于给定的 A. -值a1,所有具有 A. 是连续的。因此,他们似乎挤满了人 块,可能为包含 -值a1,如图15.14所示。然而,b上的索引是 不太可能是集群,因为元组具有固定的 b -价值观 将分布在整个文件中,除非 A. B 密切相关。

    Fig 15.14

    请注意,该定义不强制要求数据块必须在磁盘上连续;它只说带有搜索键的元组被压缩到尽可能少的数据块中。

    一个相关的概念是 . 如果一个关系的元组被压缩成可能容纳这些元组的尽可能少的块,那么它就是“集群”关系。换句话说,从磁盘块的角度来看,如果它包含来自不同关系的元组,那么这些关系就不能被聚集(即,有一种更紧凑的方式来存储这种关系,方法是将该关系的元组从其他磁盘块交换到不属于当前磁盘块中的关系的元组)。清晰地 在上面的示例中,是集群的。

    要将两个概念连接在一起,聚集关系可以有聚集索引和非聚集索引。然而,对于非聚集关系,除非索引构建在关系的主键之上,否则聚集索引是不可能的。

    “集群”作为一个词,在数据库存储端的所有抽象级别(三个抽象级别:元组、块、文件)上都被垃圾邮件发送。一个叫做 clustered file “,它描述一个文件(一组块(一个或多个磁盘块)的抽象)是否包含来自一个关系或不同关系的元组。它与文件级别的聚类索引概念无关。

    然而,一些 teaching material 喜欢基于聚集文件定义定义聚集索引。这两种类型的定义在集群关系级别上是相同的,无论它们是根据数据磁盘块还是文件定义集群关系。从本段中的链接,

    当属性值为A=A的所有元组顺序(=连续)存储在数据文件中时,文件中属性A上的索引即为聚类索引

    连续存储元组与“元组被压缩到尽可能少的块中(一个谈论文件,另一个谈论磁盘)是相同的。这是因为连续存储元组是实现“打包到尽可能少的块中以容纳这些元组”的方法。

        11
  •  3
  •   Abhishek Duppati    6 年前

    聚集索引:

    非聚集索引: 非聚集索引的实际数据不能直接在叶节点上找到,相反,它必须采取额外的步骤才能找到,因为它只有指向实际数据的行定位器的值。

        12
  •  2
  •   Santhoopa Jayawardhana    4 年前

    聚集索引 -聚集索引定义数据在表中的物理存储顺序。表数据只能以这种方式排序,因此,每个表只能有一个聚集索引。在SQL Server中,主键约束会自动在该特定列上创建聚集索引。

    -非聚集索引不会对表内的物理数据进行排序。事实上,非聚集索引存储在一个位置,表数据存储在另一个位置。这类似于教科书,书的内容放在一个地方,索引放在另一个地方。这允许每个表有一个以上的非聚集索引。这里需要指出的是,在表内,数据将按聚集索引排序。但是,在非聚集索引中,数据是按指定顺序存储的。索引包含创建索引的列值和列值所属记录的地址。当对创建索引的列发出查询时,数据库将首先转到索引并查找表中相应行的地址。然后它将转到该行地址并获取其他列值。正是由于这一附加步骤,非聚集索引比聚集索引慢

    聚集索引与非聚集索引的区别

    1. 每个表只能有一个聚集索引。但是,你可以 在单个表上创建多个非聚集索引。
    2. 聚集索引只对表进行排序。因此,他们不消费 额外存储。非聚集索引存储在单独的位置
    3. 聚集索引比非聚集索引更快,因为它们 不要涉及任何额外的查找步骤。

    有关更多信息,请参阅 this 文章