牡丹江市网站建设_网站建设公司_测试上线_seo优化

前言

在学习MySQL过程中，阅读到这样一段话：在 MySQL 中，B+ 树索引按照存储方式的不同分为聚集索引和非聚集索引。我就在想为什么要分为这两种，下面我就详细介绍这两者的联系、优缺点。

一、聚集索引和非聚集索引的本质

1. 聚集索引
   核心是“数据即索引，索引即数据”：
   • nnoDB 中，表的存储结构本身就是聚集索引的结构，表的所有行数据，都会按照聚集索引的键值（默认是主键）进行排序，存储在聚集索引的叶子节点中。
   • 可以把它理解成：一本按页码（主键）排序的书，书的每一页（叶子节点）就是完整的内容（整行数据）。
2. 非聚集索引
   是“单独的索引结构，指向数据的指针”：
   • 非聚集索引有自己独立的索引树结构，叶子节点中只存储「索引列的值 + 聚集索引的键值（主键）」，并不存储整行数据。
   • 可以把它理解成：这本书的目录，目录里只记录章节名（索引列）和对应的页码（主键），不记录章节的具体内容。

二、聚集索引和非聚集索引的联系

二者是强绑定的，InnoDB 的设计逻辑里，非聚集索引完全依赖聚集索引：

1. 非聚集索引的叶子节点，必须存储聚集索引的键值
   InnoDB 的非聚集索引，叶子节点不会直接存储数据的物理地址，而是存储聚集索引的主键值 —— 这是为了保证数据的稳定性（如果数据发生页分裂，物理地址会变化，但主键不会变）
2. 非聚集索引的回表操作，本质是依赖聚集索引
   当非聚集索引无法覆盖查询（查询字段不在非聚集索引中）时，会先通过非聚集索引找到主键值，再用主键值去聚集索引中查询完整数据，这个过程就是「回表」
3. 二者的索引树结构逻辑一致
   不管是聚集还是非聚集索引，都采用 B + 树的结构：非叶子节点存储索引键值用于导航，叶子节点存储实际的内容（聚集索引存数据，非聚集索引存索引列 + 主键）

三、优缺点对比

四、实战案例

我们用一个电商的订单表（order_info）来举例，表结构如下：

CREATETABLE`order_info`(`order_id`BIGINTNOTNULLCOMMENT'订单ID（主键，聚集索引）',`user_id`BIGINTNOTNULLCOMMENT'用户ID',`order_time`DATETIMENOTNULLCOMMENT'下单时间',`order_amount`DECIMAL(10,2)NOTNULLCOMMENT'订单金额',`order_status`TINYINTNOTNULLCOMMENT'订单状态',PRIMARYKEY(`order_id`),-- 聚集索引KEY`idx_user_time`(`user_id`,`order_time`)-- 非聚集联合索引)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;

场景 1：适合用聚集索引的场景
如果运营需要查询「订单 ID 为 100001 的订单的所有信息」，SQL 如下：

SELECT*FROMorder_infoWHEREorder_id=100001;

• 此时用聚集索引（order_id）是最优选择：直接通过主键定位到叶子节点的整行数据，不需要任何额外操作，效率最高

场景 2：适合用非聚集索引（覆盖索引）的场景
如果运营需要查询「用户 ID 为 1001 的所有订单的下单时间和订单金额」，SQL 如下：

SELECTorder_time,order_amountFROMorder_infoWHEREuser_id=1001;

• 此时我们创建的非聚集联合索引idx_user_time (user_id, order_time)，加上 InnoDB 默认存储的主键order_id，但我们的查询字段是order_time和order_amount—— 这里可以优化索引，改成idx_user_time_amount (user_id, order_time, order_amount)
• 优化后，这个索引就覆盖了查询的所有字段，MySQL 直接从非聚集索引中返回数据，不需要回表，效率远高于用聚集索引查询

场景 3：适合用非聚集索引（需要回表）的场景
如果运营需要查询「用户 ID 为 1001 的所有订单的完整信息」，SQL 如下：

SELECT*FROMorder_infoWHEREuser_id=1001;

• 此时非聚集索引idx_user_time只能提供user_id、order_time和order_id，无法覆盖所有字段，需要回表：
  1.先通过idx_user_time找到所有user_id=1001的行，拿到对应的order_id
  2.再用order_id去聚集索引中查询整行数据
• 但这个效率依然远高于用聚集索引全表扫描（聚集索引只能按 order_id 排序，无法快速找到 user_id=1001 的行）

五、实战建议

1. 聚集索引的选择：尽量用自增主键
   自增主键的插入是顺序的，不会导致聚集索引的页分裂，插入效率更高；如果用 UUID 作为主键，插入时会随机定位位置，导致大量页分裂，性能下降
2. 非聚集索引的设计：优先考虑覆盖索引
   对于高频查询，尽量创建包含查询所需所有字段的联合索引，避免回表
3. 不要滥用非聚集索引
   非聚集索引会占用额外的磁盘空间，同时会降低插入 / 更新 / 删除的效率（因为需要维护索引树），只给高频查询的字段创建索引