【面试题】MySQL的聚簇索引与非聚簇索引与主键索引：深入理解与应用

引言

聚簇索引、非聚簇索引和主键索引的有什么区别你知道吗

在数据库设计中，索引是提高查询性能的关键。MySQL中的聚簇索引和非聚簇索引是两种不同的索引类型，它们在数据存储和检索方面有着显著的差异。理解这些差异对于优化数据库性能至关重要。

基础知识

• 聚簇索引（Clustered Index）：数据行的物理顺序与索引顺序相同的索引。
• 非聚簇索引（Non-Clustered Index）：数据行的物理顺序与索引顺序不同的索引。

核心概念

• 数据存储：聚簇索引将数据存储与索引结构结合在一起，非聚簇索引则将数据存储与索引分开。
• 查询性能：聚簇索引通常在查询时提供更快的访问速度，非聚簇索引可能需要额外的查找步骤。

聚簇索引、非聚簇索引和主键索引的主要区别
在于它们的存储方式、数据组织结构以及查询效率。‌

• ‌聚簇索引‌（Clustered Index）：

• 聚簇索引实际上并不是一种单独的索引类型，而是一种数据存储方式。在InnoDB存储引擎中，聚簇索引的叶子节点包含了完整的记录行，这意味着表的数据行都存放在索引树的叶子页中。
• 由于聚簇索引决定了表中数据的物理存储顺序，因此一张表只能有一个聚簇索引。InnoDB的聚簇索引实际上是将索引和数据保存在同一个B-Tree中，如果没有定义主键，InnoDB会选择一个合适的列作为聚簇索引，如果找不到合适的列，会使用一列隐藏的列DB_ROW_ID作为聚簇索引‌.

• 非聚簇索引‌（ n-clustered Index）：

• 非聚簇索引，也称为二级索引或辅助索引，其叶子节点仅包含主键值，而不包含完整的记录信息。通过非聚簇索引查找记录时，需要先通过非聚簇索引找到主键值，然后再通过主键值到聚簇索引中找到对应的记录行，这个过程称为回表查询。由于非聚簇索引的逻辑顺序与磁盘上行的物理存储顺序不同，因此一个表中可以拥有多个非聚簇索引。非聚簇索引的叶子节点中保存的不是指向行的物理指针，而是行的主键值，这有助于减少移动数据或分裂时维护非聚簇索引的开销‌

• 主键索引‌：

主键索引是一种特殊的聚簇索引，它确保了数据的唯一性。在关系数据库中，主键是唯一标识表中每一行的列。当为表设置主键时，MySQL会自动为该主键创建一个聚簇索引。因此，主键索引既是聚簇索引的一种，也是数据库表中数据组织的核心‌

面试常问：聚簇索引不一定是主键索引，而主键索引一定是聚簇索引。

可以理解成在聚簇索引上建立的索引，都是非聚簇索引（也称为二级索引或辅助索引）。因为一个表中只能有一个聚簇索引，其他都是非聚簇索引。

引擎上的区别

InnoDB

使用的是聚簇索引，比如若使用“where id=5“的条件查找主键，则按照B+树的检索算法，即可查找到对应的叶子节点，之后获得行数据。

若对employee_name 列进行条件搜索，则需要两个步骤：
1.在辅助索引B+树中检索非主键列（eg：employee_name ），到达其叶子节点获取对应的主键。
2.用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点获取整行数据。（重点在于通过其他键需要建立辅助索引）

聚簇索引默认是主键，若表中无定义主键，InnoDB会选择一唯一且非空的索引代替。若无这样的索引，InnoDB会隐式定义一个主键（类似 oracle中的Rowld，使用一列隐藏的列DB_ROW_ID作为聚簇索引‌)作为聚簇索引。

若已设了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除原主键，然后添加，最后恢复设置主键即可。

MyISAM

便用的是非聚簇索引，只是存储的内容不同，主键索引B+树的节点存储了主键， 辅助键索引IB+树存储了辅助键。

表数据存储在独立的地方（MYN文件），这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。
由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

示例演示

1. 聚簇索引的使用：

   CREATE TABLE employees (
       employee_id INT PRIMARY KEY,
       employee_name VARCHAR(100),
       department_name VARCHAR(100)
   );
   

   在这个例子中，employee_id是聚簇索引，因为它直接存储了数据行。

2. 非聚簇索引的使用：

   CREATE INDEX idx_department ON employees (department_name);
   

   idx_department是非聚簇索引，因为它指向数据行的位置。

实际应用

• 查询优化：使用聚簇索引可以减少查询时的数据访问次数。
• 数据插入：聚簇索引可以减少数据插入时的页分裂。

深入与最佳实践

• 选择合适的索引类型：根据查询模式和数据访问模式选择合适的索引类型。
• 避免过度索引：过多的索引会降低数据修改的性能。

常见问题解答

• Q: 为什么聚簇索引通常比非聚簇索引更快？
  A: 聚簇索引的数据存储与索引结构结合在一起，减少了额外的数据查找步骤。

• Q: 我应该在什么情况下使用非聚簇索引？
  A: 当你需要基于非主键列进行频繁查询时，非聚簇索引是一个好的选择。

结语

聚簇索引和非聚簇索引在MySQL中有着不同的应用场景和性能特点。了解它们的差异和最佳实践，可以帮助你更好地设计数据库索引，提高查询性能。

学习资源

• MySQL官方文档：Indexes

互动环节

• 分享你在数据库索引设计和优化方面的经验。