每日大厂面试题分享

MySQLInnoDB 引擎中的聚簇索引和非聚簇索引有什么区别?

  • 后端
  • MySQL
大约 5 分钟全民制作人ikun

简短回答

提示

聚簇索引:

索引叶子节点存储的是数据行，可以直接访问完整数据。

每个表只能有一个聚簇索引，通常是主键索引，适合范围查询和排序。

非聚簇索引:

索引叶子节点存储的是数据行的主键和对应的索引列，需通过主键才能访问完整的数据行 。

一个表可以有多个非聚簇索引(称之为非主键索引、辅助索引、二级索引)，适用于快速查找特定列的数据。

详细回答

聚簇索引

概念

聚簇索引（Clustered Index）是一个特殊的索引，它将索引项和数据存储在一起，以减少磁盘读取次数。在InnoDB引擎中，聚簇索引是默认的索引类型，并且每个表都必须有一个聚簇索引。

特点

  1. 聚簇索引的数据和索引存储在一起，因此可以减少磁盘读取次数，提高查询效率。
  2. InnoDB中的主键默认是聚簇索引，如果表没有定义主键，InnoDB会选择第一个唯一且非空的索引作为聚簇索引。
  3. 如果表既没有主键也没有合适的唯一索引，InnoDB会自动生成一个隐藏的主键（row_id），作为聚簇索引。
  4. 数据按照聚簇索引的顺序物理存储在表中，所以一个表只能有一个聚簇索引。
  5. 聚簇索引的叶子节点保存的是数据行本身。
  6. 聚簇索引对按主键范围查询的操作非常高效，因为相邻的数据在物理存储上也是相邻的。
  7. 插入和更新操作可能导致页分裂，因此按主键顺序插入数据效率会更高。

非聚簇索引

概念

非聚簇索引（Non-Clustered Index），也称为二级索引（Secondary Index），是指索引结构和数据分开存储的索引。在InnoDB中，所有非主键索引都是非聚簇索引。

特点

  1. 非聚簇索引的叶子节点不包含完整的数据记录，而是存储了主键值。
  2. 使用非聚簇索引查询时，需要先通过索引找到主键值，然后再通过主键值到聚簇索引中查找完整的数据记录，这个过程被称为"回表"。
  3. 一个表可以有多个非聚簇索引，因为它们只存储索引列和主键值。
  4. 相比聚簇索引，非聚簇索引需要额外的存储空间。
  5. 当查询只涉及到索引列和主键时，可以通过"覆盖索引"避免回表操作，提高查询效率。
  6. 非聚簇索引的维护成本低于聚簇索引，因为页分裂主要发生在聚簇索引中。

实战分析

案例一：索引选择对查询性能的影响

假设有一个用户表，结构如下：

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100),
    created_at DATETIME,
    INDEX idx_email (email),
    INDEX idx_created_at (created_at)
);

在这个表中：

  • id是主键，对应聚簇索引
  • username、email和created_at上的索引是非聚簇索引

查询场景分析：

  1. SELECT * FROM users WHERE id = 1;

    • 直接使用聚簇索引，一次磁盘IO即可获取完整数据。

  2. SELECT * FROM users WHERE email = 'user@example.com';

    • 先通过非聚簇索引idx_email找到主键值
    • 再通过主键值查询聚簇索引获取完整数据（回表）
    • 需要两次索引查找

  3. SELECT id, email FROM users WHERE email = 'user@example.com';

    • 通过覆盖索引优化，只需要在idx_email索引中查找
    • 不需要回表，因为索引包含了查询需要的所有列

案例二：主键设计的影响

考虑两种不同的表设计：

设计A（自增主键）：

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(20) UNIQUE,
    customer_id INT,
    order_date DATETIME,
    INDEX idx_order_no (order_no),
    INDEX idx_customer_id (customer_id)
);

设计B（业务字段作主键）：

CREATE TABLE orders (
    order_no VARCHAR(20) PRIMARY KEY,
    customer_id INT,
    order_date DATETIME,
    INDEX idx_customer_id (customer_id)
);

分析：

  • 设计A使用自增主键，插入数据时主键值递增，新数据总是追加到索引末尾，减少了页分裂，提高了写入性能。
  • 设计B使用业务字段order_no作为主键，随机的主键值会导致频繁的页分裂，降低写入性能。
  • 设计A中查询order_no需要回表，设计B中直接使用聚簇索引。
  • 设计A的主键占用空间小，二级索引叶子节点存储的主键值也小，节省空间。

总结：聚簇索引与非聚簇索引的区别

特性聚簇索引非聚簇索引
数据存储索引和数据存储在一起索引和数据分开存储
叶子节点包含完整的数据记录包含主键值
数量限制一个表只能有一个一个表可以有多个
默认创建主键自动成为聚簇索引非主键索引都是非聚簇索引
查询路径直接获取数据需要回表（除非是覆盖索引）
维护成本较高（页分裂、数据移动）较低
适用场景主键查询、范围查询单列查询、复合条件查询
存储空间不需要额外空间存储主键需要额外空间存储主键
IO性能查询通常只需一次IO查询通常需要多次IO

【聚簇索引结构】

提示

待补充

欢迎您的参与
Copyright © 2025 全民制作人ikun