聊聊MySQL的聚簇索引和非聚簇索引

MySQL的聚簇索引和非聚簇索引翻译为中文也叫聚集索引，非聚集索引。英文有两种叫法 Clustered index、 non-Clustered index 。MySQL官方对 非聚集索引称之为 Secondary Indexes。所以遇到 Secondary Indexes 和 non-Clustered index等价。
MySQL官方文档介绍在 《15.6.2.1 Clustered and Secondary Indexes》https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

1. 索引的分类

在了解聚簇索引和非聚簇索引之前，我们先对数据库索引的分类进行一个了解，不然繁杂的概念和分类会使得搞混，通常我们可以听到B-Tree索引、全文索引 、复合索引、 聚簇索引 、静态索引，其实这些描述都是站在不同维度的描述，B-Tree索引是站在存储结构的维度，全文索引是站在功能维度描述，所以我们先了解一下不同维度的索引描述。
以下是常见的一些维度：

1. 存储结构维度

索引类型	描述	适用场景
B-Tree索引	基于平衡多路搜索树的索引结构，维护数据有序性	范围查询、排序操作
Hash索引	基于哈希表的索引结构，适合等值查询	等值查询
R-Tree索引	适用于存储和查询空间数据的树形结构	地理空间数据的查询
Bitmap索引	适用于低基数列的索引，占用空间小且查询效率高	低基数列（不同值的数目较少）的索引

2. 功能维度

索引类型	描述	适用场景
普通索引	基本的索引类型，无任何限制	通用
唯一索引	类似于普通索引，要求索引列的值必须唯一，通常用于主键	确保索引列的唯一性
全文索引	用于全文搜索	全文搜索
空间索引	用于地理空间数据的索引	地理空间数据的查询

3. 列数维度

索引类型	描述
单列索引	仅包含一个字段的索引
复合索引	包含多个字段的索引，可以是普通索引、唯一索引等

这些索引类型的选择取决于需要索引的列的组合和查询需求。复合索引可以更好地支持涉及多个列的查询，但需要权衡索引的大小和维护成本。

4. 存储方式维度

聚簇索引将数据行直接存储在索引中，因此范围查询和排序操作效率较高，而非聚簇索引则需要通过指针访问数据行。选择适当的索引类型取决于数据访问模式和查询需求。

索引类型	描述
聚簇索引	数据行存储在索引中，数据行的物理顺序与索引中的键值顺序一致
非聚簇索引	索引中的键值顺序与数据行的物理顺序不一致，索引中包含指向数据行的指针

5. 更新方式维度

索引类型	描述
静态索引	仅在数据被插入、删除或更新时更新索引
动态索引	在查询时实时更新索引

静态索引在数据被修改时才更新，因此可能存在索引与实际数据不一致的情况。它适用于数据变动较少的场景，可以提高插入、删除和更新操作的性能。动态索引则在查询时实时更新，确保索引与实际数据保持一致，适用于频繁变动的数据环境，但可能对写入操作的性能有一定影响。选择适当的索引类型应考虑数据变动频率和查询性能需求。

本章我们着重了解聚簇索引和非聚簇索引的工作原理

2. 聚簇索引

2.1 什么是聚簇索引

聚簇索引是一种特殊类型的索引，在存储引擎中，数据记录实际的存放方式会根据聚簇索引来组织。一个表中只能有一个聚簇索引，但可以有多个非聚簇索引。

在许多数据库系统中，聚簇索引通常就是主键索引。例如：

1. 在MySQL的InnoDB引擎中，聚簇索引默认是主键，如果没有定义主键，MySQL会选择一个非空唯一索引代替，如果没有非空唯一索引，MySQL会自动创建一个隐藏的聚簇索引。

2. 在SQL Server中，也可以选择用哪个列作为聚簇索引，但一般推荐使用主键。

3. 在Oracle中，可以明确指定创建聚簇索引。

虽然聚簇索引在很多情况下被设置为主键，但并不意味着聚簇索引一定是主键。聚簇索引应该选择最能代表数据存储特征的那一列或几列，例如，如果一个表的数据经常按照某一列的顺序进行查找，那么这一列就非常适合做聚簇索引。

2.2 聚簇索引的工作原理

MySQL 8中的聚簇索引原理与之前版本类似，主要的工作原理是基于B+树数据结构进行排序和检索操作。

对于MySQL的InnoDB存储引擎，聚簇索引是按照主键的顺序来存储数据的。这意味着每个表只能有一个聚簇索引，如果没有明确指定主键，InnoDB会自动选择一个能作为主键的列或者自行生成一个。

以下是聚簇索引的工作原理：

1. 查询操作：当执行查询操作时，InnoDB引擎会利用B+树的特性，从根节点开始，通过比较索引的键值找到对应的叶子节点（数据页），从而快速找到需要的数据。因为索引的键值和数据是在一起的，所以查询效率非常高。

2. 插入和删除操作：当进行插入或删除操作时，InnoDB引擎需要找到对应的索引键值，然后在对应的位置插入新的数据或删除旧的数据。因为数据是按照键的顺序存储的，所以插入和删除操作可能会引发数据的移动，尤其是在插入时如果插入的数据键值在当前键值范围内则可能会触发数据页的分裂。

3. 更新操作：当进行更新操作时，如果更新的是非索引列，那么只需定位到数据页并进行更新即可；但是如果更新的是索引列，那么可能会引发数据的移动，因为要保持数据的有序性。

设计聚簇索引时要尽可能选择稳定且不频繁变动的列作为主键，这样可以减少因为插入、删除和更新操作引发的数据移动，提高数据库的性能。

聚簇索引可以提高大多数查询操作的性能，因为它们为数据提供了更线性的访问路径，数据存储在页面中。此外，由于具有相似索引键值的行存储在一起，使用聚簇索引时，顺序检测预取更高效。

示例
假设我们有一个学生信息表，我们可以通过以下SQL语句创建这个表，并且设置id为主键，也就是聚簇索引：

CREATE TABLE students (id INT PRIMARY KEY,name VARCHAR(100),age INT
);

假设我们现在要查询id为100的学生的信息，我们可以通过以下SQL语句进行查询：

SELECT * FROM students WHERE id = 100;

在执行这个查询操作时，因为id是聚簇索引，所以MySQL会通过B+树的检索算法，从根节点开始，比较索引的键值，找到对应的叶子节点（数据页），然后读取该数据页，找到id为100的学生的信息，这个过程的效率非常高。

同样地，如果我们要更新id为100的学生的年龄，我们可以通过以下SQL语句进行更新：

UPDATE students SET age = 20 WHERE id = 100;

在执行这个更新操作时，MySQL也会先通过聚簇索引找到id为100的学生的信息，然后直接在数据页上进行更新操作。如果更新的是非索引列（在这个例子中，是age列），那么更新操作的效率也是非常高的。

如果更新的是索引列，那么可能会引发数据的移动，因为要保持数据的有序性。例如，如果我们要改变id为100的学生的id，那么可能就会引发数据的移动，因此，设计聚簇索引时，我们应该尽可能选择稳定且不频繁变动的列作为主键。

3. 非聚簇索引（MySQL官方文档称为Secondary Indexes）

MySQL官方文档介绍在《15.6.2.1 Clustered and Secondary Indexes》

3.1 什么是非聚簇索引

非聚簇索引，也被称为二级索引或辅助索引，它的工作方式与聚簇索引有所不同。在非聚簇索引中，索引的逻辑顺序与磁盘上行的物理存储顺序不同。换句话说，非聚簇索引的逻辑顺序是索引的键值顺序，但是这个顺序并不等于数据在磁盘上的物理存储顺序。

在非聚簇索引中，每一个索引条目都包含了键值和一个指向该键值对应的数据行的指针。这个指针通常是数据行的物理地址或者是一个指向数据行的其他种类的标识符。

一个表可以有多个非聚簇索引。当查询不包含聚簇索引的列时，数据库系统会使用非聚簇索引来提高查询性能。

比如在一个员工表中，聚簇索引可能会基于员工的ID进行设置，而非聚簇索引可能会基于员工的姓名或者部门来设置。这样当查询姓名或者部门时，数据库系统就可以直接利用非聚簇索引进行查找，而不需要扫描整张表，从而提高了查询效率。

3.2 非聚簇索引的工作原理

非聚簇索引的工作原理与聚簇索引有些不同。非聚簇索引（也称为二级索引或辅助索引）并不会影响表中数据的物理存储顺序，而是创建一个单独的数据结构（通常是B+树）来存储索引列的值和对应的行指针。如上图示例。

非聚簇索引的工作原理：

1. 查询操作：当执行查询操作时，数据库会从非聚簇索引的B+树的根节点开始，通过比较索引的键值找到对应的叶子节点。这个叶子节点包含了键值和一个指向该键值对应的数据行的指针。数据库通过这个指针找到实际的数据行。这个过程通常需要两次磁盘I/O操作，第一次是在索引上进行查找，第二次是通过找到的指针去数据文件中获取实际的数据行。

2. 插入和删除操作：当进行插入或删除操作时，数据库需要同时在索引结构和数据文件中进行操作。首先，数据库会在索引结构中插入或删除对应的键值和行指针，然后在数据文件中插入或删除实际的数据行。

3. 更新操作：当进行更新操作时，如果更新的是非索引列，那么数据库只需要在数据文件中更新对应的数据行即可；如果更新的是索引列，那么数据库需要同时在索引结构和数据文件中进行更新操作。

4. 聚簇索引与非聚簇索引的区别

	聚簇索引	非聚簇索引
查询速度	通常较快，因为可以直接定位到数据	较慢，因为需要先定位到索引，然后再通过索引找到数据
内存使用	使用的内存较少，因为数据和索引在一起	使用的内存较多，因为数据和索引是分开的
数据存储	聚簇索引就是主数据，数据按照索引排序	非聚簇索引是数据的一份索引，数据的物理排序与索引无关
索引数量	一个表只能有一个聚簇索引	一个表可以有多个非聚簇索引
数据存储能力	聚簇索引存储数据本身	非聚簇索引存储数据的指针，并不存储数据本身
存储内容	聚簇索引存储实际的数据行	非聚簇索引存储索引列和行指针
叶节点内容	在聚簇索引中，叶节点就是实际的数据	在非聚簇索引中，叶节点不是实际的数据，而只包含索引和行指针
数据顺序	在聚簇索引中，数据物理存储的顺序与索引的顺序一致	在非聚簇索引中，数据的物理存储顺序与索引顺序无关
索引类型	聚簇索引是一种将表记录物理排序以匹配索引的索引类型	非聚簇索引是一种索引的逻辑顺序与数据在磁盘上的物理存储顺序无关的索引类型
索引大小	主聚簇索引的大小一般较大	相对而言，非聚簇索引的大小较小