MySQL中的聚簇索引与非聚簇索引

MySQL中的聚簇索引与非聚簇索引

01 概念

聚簇索引：

• 聚簇索引是一种数据库索引结构，可以让数据行物理上按照索引键值排序存储。
• 每张表只能有一个聚簇索引，它定义了数据行的物理存储顺序。具有相近索引键值的数据行会在磁盘上彼此相邻存储。
• 使用聚簇索引的表在范围查询和按照索引键值排序时性能更好，因为数据库系统可以更高效地处理数据行，不需要额外的磁盘访问。
• 在InnoDB存储引擎中，主键索引就是一种聚簇索引。

非聚簇索引：

• 非聚簇索引是数据库中的一种索引，它把索引和数据行的存储分开，索引的顺序与实际数据行的顺序无关。
• 每个表可以有多个非聚簇索引，用于提高特定列的查询速度。当使用非聚簇索引进行查询时，数据库首先根据索引找到对应的行位置，然后再去读取实际的数据行。
• 这可能需要额外的磁盘访问，性能可能略逊色于聚簇索引。

02 深入理解聚簇索引

• 当我们考虑一个简单的学生信息表时，我们可以使用学生的学号作为聚簇索引。假设我们有以下的学生信息表：

学号 (ID)	姓名 (Name)	年龄 (Age)	专业 (Major)
101	小明	20	计算机科学
102	小红	21	数学
103	小刚	19	物理

• 如果我们将学号设定为聚簇索引，那么这个表中的数据行将按照学号的顺序在磁盘上进行物理存储。

• 这意味着，根据学号进行查询或者范围查询的效率会很高，因为数据行在磁盘上是连续存储的。

• 假设我们要查询学号为102的学生信息，由于聚簇索引的存在，数据库系统可以更快速地定位并检索到这条数据，而不需要扫描整个表。

• 同样地，如果我们要按照学号对学生进行排序，也会因为聚簇索引的存在而获得更好的性能。

• 这样，聚簇索引通过将数据行按照索引键的顺序进行组织，提高了数据的检索和排序效率。

03 深入理解非聚簇索引

• 它指定了数据行的逻辑顺序，但实际上并不改变数据行的物理顺序。

• 这意味着索引键值的顺序与数据行的存储顺序无关。

• 举个简单例子来说，如果你有一本书，目录部分就是一个索引，它指导你去找到特定内容所在的页数，但这个目录并没有改变书中各页的实际排列顺序。

04 区别

• 聚簇索引和非聚簇索引的主要区别在于数据行的存储方式不同。

  • 聚簇索引：在聚簇索引中，索引本身就是数据行的物理存储顺序，数据行按照索引的顺序存储在一起。

    • 表只能有一个聚簇索引，通常是主键索引。

  • 非聚簇索引：在非聚簇索引中，索引本身和实际数据行的物理存储是分开的，索引键值的存储顺序与数据行的存储顺序无关。

    • 每张表可以有多个非聚簇索引，用于加快特定列的查询速度。

• 举个简单例子：

• 假设我们有一个学生表（Students）包含学生姓名（Name）和学号（StudentID）两列，我们在学号上创建了聚簇索引和非聚簇索引：

  • 聚簇索引：如果我们在学号列上创建了聚簇索引，那么数据行将按照学号的顺序进行存储，学号本身就是索引的顺序。

  • 非聚簇索引：如果我们在学号列上创建了非聚簇索引，额外使用空间创造。

    • 那么索引的存储顺序与数据行的存储顺序无关，即索引键值的顺序并不影响数据行的实际存储位置。

    • 非聚簇索引使用的额外空间通常由两部分组成：索引键值和指向实际数据行的指针。

      • 索引键值（Index Key）：索引键值是用来构建索引的值，它们通常是表中某一列或多列的值的组合。

      • 索引键值的存储方式取决于具体的数据库管理系统，但通常会占用一定的存储空间，特别是对于较长的键值或复合键值而言。

      • 指向实际数据行的指针（Pointer to Actual Data Row）：非聚簇索引需要存储指向实际数据行的指针或引用，以便在查询时能够快速地定位到实际数据行。

      • 这些指针通常是指向数据行在磁盘上的位置或者是数据行的主键值。

• 非聚簇索引的创建会导致数据库中额外的存储空间被使用，这是因为索引本身需要占用存储空间来存储索引键值和指向实际数据行的指针。

• 此外，非聚簇索引还会增加对数据库的维护开销，因为当数据行发生变化（如插入、删除、更新）时，索引也需要进行相应的更新操作，这会影响到数据库的性能。

• 另外，过多的非聚簇索引也可能导致查询优化器在决定使用哪个索引时出现困难，从而影响查询性能。

• 因此，在创建非聚簇索引时需要权衡索引的数量和具体的查询需求，避免创建过多不必要的索引。