【必会面试题】聚簇索引和非聚簇索引

前言

聚簇索引和非聚簇索引是两种不同类型的索引结构，主要应用于关系型数据库。

聚簇索引 (Clustered Index)

1. 数据存储与索引结合：在聚簇索引中，数据行的物理顺序与索引的顺序相匹配。这意味着索引项直接指向数据行的实际存储位置，叶子节点包含了实际的数据行。因此，对于一个表来说，只能有一个聚簇索引。

2. 主键关联：在许多数据库系统中，尤其是MySQL的InnoDB引擎，如果没有显式定义主键，聚簇索引通常会默认建立在表的主键上。如果表没有主键，InnoDB会选择一个唯一的非空索引来充当聚簇索引。

3. 查询效率：由于数据是按照索引顺序存储的，因此对于基于索引列的查询，特别是范围查询和等值查询，聚簇索引可以提供很高的效率，减少了磁盘I/O操作。

4. 更新成本：当插入、删除或更新导致行的位置变化时，需要调整索引，可能会导致额外的性能开销。

非聚簇索引 (Non-Clustered Index)

1. 独立存储：非聚簇索引不改变数据的物理存储顺序。它维护了一个指向数据行的指针（通常是聚簇索引的键值，通常是主键），索引的叶子节点存储的是指向数据行的指针或行ID，而不是实际的数据行。

2. 多个索引：一个表可以有多个非聚簇索引，它们可以基于不同的列创建，以满足不同查询需求。

3. 查询过程：使用非聚簇索引查询数据时，首先通过索引找到主键值，然后通过这个主键值去聚簇索引中查找实际的数据行，这个过程被称为“回表”操作，因此相比于聚簇索引，查询可能会稍微慢一些，尤其是当索引覆盖查询不适用时。

4. 适用场景：非聚簇索引适合于不需要经常变更且用于频繁查询的列，如姓名、分类等辅助查询列。

总结来说，聚簇索引优化了基于索引列的查询速度，但对数据更新敏感；而非聚簇索引提供了更多的灵活性，支持多个索引，但可能需要额外的回表操作来获取完整的数据行，适用于辅助查询。

聚簇索引与非聚簇索引在MySQL中的应用

假设我们有一个employees表，包含以下字段：employee_id(主键), first_name, last_name, department_id, 并且我们经常需要根据员工姓名和部门ID进行查询。

创建聚簇索引

在InnoDB存储引擎中，如果未特别指定，主键自动成为聚簇索引。

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    PRIMARY KEY (employee_id)
);

在这个例子中，employee_id作为主键，其对应的索引就是聚簇索引。数据行按照employee_id的顺序存储在表中。

创建非聚簇索引

为了加速基于last_name和department_id的查询，我们可以创建两个非聚簇索引。

CREATE INDEX idx_last_name ON employees(last_name);
CREATE INDEX idx_department_id ON employees(department_id);

• idx_last_name是非聚簇索引，存储了按last_name排序的员工记录的指针（实际上是主键employee_id）。
• idx_department_id同样是非聚簇索引，存储了按department_id排序的员工记录的指针。

查询示例

使用聚簇索引查询

SELECT * FROM employees WHERE employee_id = 100;

此查询直接通过聚簇索引定位到记录，效率较高。

使用非聚簇索引查询并回表

SELECT * FROM employees WHERE last_name = 'Smith';

此查询首先通过idx_last_name索引找到所有last_name为’Smith’的员工的employee_id，然后根据这些employee_id在聚簇索引中查找完整的记录信息，即进行了"回表"操作。

总结

• 聚簇索引直接决定了数据的物理排列，适合基于主键的快速查询和排序。
• 非聚簇索引提供了对其他列的快速访问路径，但可能需要额外的步骤来获取完整数据行，适用于辅助列的查询优化。
• 根据实际的查询需求和数据操作模式合理选择和创建索引，可以有效提升数据库的查询性能。