MySQL聚簇索引和非聚簇索引

1.聚簇索引

1.1什么是聚簇索引

聚簇索引也被称为主键索引，因为InnoDB存储引擎将数据的存储与主键绑定在一起。这意味着表中的数据行根据主键的值顺序存放在磁盘上，从而构成一种有序的结构。这种索引结构使得按主键顺序访问数据变得非常快速，因为数据已经是排序的，并且紧密地存储在一起，减少了磁盘I/O操作。聚簇索引通常只需要根据主键的搜索条件进行数据检索，可以快速定位到数据行的位置。

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

1. 在MySQL的InnoDB引擎中，聚簇索引默认是主键，如果没有定义主键，MySQL会选择一个非空唯一索引代替，如果没有非空唯一索引，MySQL会自动创建一个隐藏的聚簇索引。
2. 在SQL Server中，也可以选择用哪个列作为聚簇索引，但一般推荐使用主键。
3. 在Oracle中，可以明确指定创建聚簇索引。

1.2 聚簇索引的工作原理 

1. 查询操作：当执行查询操作时，InnoDB引擎会利用B+树的特性，从根节点开始，通过比较索引的键值找到对应的叶子节点（数据页），从而快速找到需要的数据。因为索引的键值和数据是在一起的，所以查询效率非常高。
2. 插入和删除操作：当进行插入或删除操作时，InnoDB引擎需要找到对应的索引键值，然后在对应的位置插入新的数据或删除旧的数据。因为数据是按照键的顺序存储的，所以插入和删除操作可能会引发数据的移动，尤其是在插入时如果插入的数据键值在当前键值范围内则可能会触发数据页的分裂。
3. 更新操作：当进行更新操作时，如果更新的是非索引列，那么只需定位到数据页并进行更新即可；但是如果更新的是索引列，那么可能会引发数据的移动，因为要保持数据的有序性。

2.非聚簇索引 

2.1 什么是非聚簇索引

非聚簇索引，也叫二级索引，它的存储与数据行是分离的。在非聚簇索引中，索引树的叶子节点包含相应数据行的指针，而不是行数据本身。

当使用二级索引进行查询时，首先需要通过索引找到相应的指针，然后再通过指针找到实际的数据行。这通常需要两次磁盘I/O操作：一次是读取索引，另一次是读取实际数据。

二级索引可以为表中的任何列创建，不仅限于主键。这使得用户可以针对非主键列的查询优化。一个表可以有多个非聚簇索引。当查询不包含聚簇索引的列时，数据库系统会使用非聚簇索引来提高查询性能。

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

 2.2非聚簇索引工作原理

1. 非聚簇索引是一种索引方式，MyISM采用的是非聚簇索引，其索引文件结构为B+Tree结构。索引文件和数据文件是分离的。索引文件存储B+Tree结构，数据文件存储表中的数据行。
2. 索引文件是按照索引键值和表数据内存地址构建的B+Tree，其结构的叶子节点存储了索引列的值和指向数据文件中记录的物理位置（通常磁盘地址）的指针。
3. 每个索引对应一个B+Tree结构的索引文件，索引文件是独立的。通过辅助索引检索时，无需访问主键索引树。
4. 执行查询时，会利用非聚簇索引中的索引列值对B+Tree从根节点逐层查找，找到叶子节点。从叶子节点中获取记录的物理位置（磁盘地址）找到数据文件，从数据文件中获取响应的记录。当索引覆盖扫描时，可以直接从索引文件中返回这些值，无需再访问数据文件。

3.选择哪种索引

1. 如果大多数查询都是基于主键进行的，聚簇索引可以提供最佳的性能。
2. 如果查询经常涉及到非主键的列，那么可能需要创建一些关键的二级索引来提高这些查询的性能。
3. 在某些情况下，如果一个表的查询模式非常多样化，可能需要同时使用聚簇索引和多个二级索引。

总的来说，了解并正确使用聚簇索引和二级索引对于优化MySQL数据库的性能至关重要。选择正确的索引类型可以帮助改善查询响应时间，减少数据检索所需的资源消耗。