数据库-索引分类（单列索引、复合索引、聚簇索引、二级索引）

数据库-索引分类（单列索引、复合索引、聚簇索引、二级索引）

引用

CSDN

1.

https://blog.csdn.net/Aaaaaaatwl/article/details/138958112

单列索引与复合索引？

单列索引

在MySQL数据库中，索引的具体类型主要分为以下几类：主键索引、唯一索引、普通索引、全文索引。

• 主键索引（Primary Key Index）

• 主键索引是数据库表中的一个特殊索引，它包含一个唯一的标识符（或称为键），用于唯一地标识表中的每一行。

• 主键索引不允许有空值（NULL），并且表中的每一行都必须具有一个主键值。

• 由于主键索引的唯一性和非空性，数据库系统可以快速地通过主键来检索数据。

• 大多数数据库系统都会自动为主键创建一个唯一索引，以加速查询性能。

• 唯一索引（Unique Index）

• 唯一索引确保索引列中的每个值都是唯一的。但是，与主键索引不同，唯一索引允许有空值（除非列被定义为NOT NULL）。

• 一个表可以有多个唯一索引，但只能有一个主键索引。

• 使用唯一索引可以确保数据的唯一性，并且可以通过该索引快速地检索数据。

• 普通索引（Regular Index 或 Non-unique Index）

• 普通索引是最常见的索引类型，它仅仅是为了提高查询性能而创建的。

• 普通索引允许在索引列中存在重复的值和空值。

• 与主键索引和唯一索引相比，普通索引的创建和维护成本通常较低。

• 在选择使用哪种索引时，需要根据具体的查询需求和性能考虑来权衡。

• 全文索引（Full-text Index）

• 全文索引是一种特殊类型的索引，用于在文本字段（如VARCHAR、TEXT等）上执行全文搜索。

• 全文索引可以针对表中的一个或多个列创建，并允许用户根据文本内容（如单词、短语等）来检索数据。

• 全文索引通常使用倒排索引技术来实现，该技术将文本内容分解为单词或短语，并将它们与包含这些单词或短语的文档相关联。

• 全文索引在需要执行复杂的文本搜索查询时非常有用，例如在搜索引擎或内容管理系统中。

复合索引

在MySQL中，可以在多列上创建的索引称为复合索引（组合索引）。设计复合索引的一个重要原则是能用窄索引不用宽索引，因为窄索引（索引列为1-2列的索引）往往比宽索引（索引列超过2列的索引）更有效。同时，创建和维护索引也需要消耗一定的时间和存储空间，因此在使用复合索引时，需要根据实际的需求和数据的特点来选择合适的索引列和索引类型。

聚集索引（聚簇索引）&二级索引？

在InnoDB存储引擎中，根据索引的存储形式，可以分为以下两种：

• 聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引。

• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

• 聚集索引和二级索引的具体结构：
  

• 聚集索引的叶子节点下挂的是这一行的数据。

• 二级索引的叶子节点下挂的是该字段值对应的主键值。

接下来，我们来分析一下，当我们执行如下的SQL语句时，具体的查找过程是什么样子的：

具体过程如下：

1. 由于是根据name字段进行查询，所以先根据name='Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
2. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。
3. 最终拿到这一行的数据，直接返回即可

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

单列索引 跟聚集索引 还有二级索引的关系是三大类吗？

1. 不是三大类关系

• 单列索引、聚集索引和二级索引并不是同一层面划分的三大类索引。它们之间的关系是相互交叉的，是从不同维度对索引进行的分类和描述。

2. 单列索引

• 定义：
• 单列索引是基于表中的单个列构建的索引。例如，在一个员工表中，为员工姓名列单独建立的索引就是单列索引。它的主要目的是提高在该列上进行查询操作（如WHERE子句中根据员工姓名进行筛选）的效率。
• 与其他索引的关系：
• 单列索引可以是聚集索引也可以是二级索引。如果这个单列索引恰好是基于主键列构建的，并且存储引擎将表数据按照这个主键索引的顺序进行存储（如InnoDB存储引擎中的主键索引），那么它就是聚集索引。如果是基于非主键列构建的单列索引，在有聚集索引的情况下，它就是二级索引。

3. 聚集索引

• 定义：
• 聚集索引决定了表中数据的物理存储顺序。对于InnoDB存储引擎，主键索引就是聚集索引。表中的数据行是按照主键值的大小顺序存储在磁盘上的。这样，当通过主键进行查询时，可以直接定位到数据行的物理存储位置，从而快速获取数据。
• 与其他索引的关系：
• 聚集索引和二级索引相互关联。二级索引的叶子节点通常存储着聚集索引的键值（例如主键值）。当使用二级索引查询数据时，首先通过二级索引定位到聚集索引的键值，然后再通过聚集索引获取完整的数据行。这种结构使得在有聚集索引的情况下，二级索引能够借助聚集索引来获取最终的数据。

4. 二级索引（辅助索引）

• 定义：
• 二级索引是除聚集索引之外的其他索引。它的叶子节点不存储完整的数据行（除非没有聚集索引），而是存储一些指向数据行的指针或者聚集索引的键值。二级索引主要用于加速非主键列上的查询操作。例如，在员工表中，除了主键（员工编号）索引外，为员工部门列建立的索引就是二级索引。
• 与其他索引的关系：
• 二级索引的存在依赖于聚集索引（在有聚集索引的存储环境下），并且可以基于单列或者多列构建。它和单列索引有重叠部分，因为单列索引在不是聚集索引的情况下就属于二级索引的范畴。同时，它与聚集索引紧密配合，共同提高数据库查询的效率。

那如果没有聚集索引 的单列索引的叶子节点存的是id吗 然后去找对应的主键字段吗

1. 没有聚集索引时单列索引叶子节点存储情况

• 在没有聚集索引的情况下，单列索引的叶子节点存储的数据情况因数据库系统而异。有些数据库系统中，单列索引的叶子节点可能存储完整的行数据。
• 例如，在MyISAM存储引擎（MySQL的一种存储引擎）中，它没有像InnoDB那样的聚集索引概念来决定数据的物理存储顺序。当创建一个基于某列的单列索引时，这个索引的叶子节点会直接存储指向行数据的指针或者直接存储行数据本身，而不是像在有聚集索引的环境下存储主键值。

2. 查询过程中的数据获取方式

• 如果叶子节点存储的是行数据，那么在查询时，一旦通过单列索引定位到叶子节点，就可以直接获取需要的行数据，无需再通过主键字段来查找。
• 但如果叶子节点存储的是指向行数据的指针，那么可以通过这个指针直接找到对应的行数据，也不是通过先找主键字段的方式。只有在有聚集索引且二级索引（非聚集索引的单列索引）的情况下，二级索引叶子节点存储主键值，才会先通过二级索引找到主键值，再通过聚集索引（主键索引）查找完整的行数据。

本篇小结

有小伙伴对其他数据库内容感兴趣，可以通过以下链接进行查看
数据库-索引（基础篇）
数据库-索引结构（B-Tree，B+Tree，Hash，二叉树）
数据库-索引语法（增删查）
数据库-索引分类（主键索引、唯一索引、普通索引、全文索引）
数据库-索引使用（验证索引效率、单列索引与联合索引、最左前缀法则）
索引失效情况