聚簇索引和非聚簇索引的根本区别在于数据存储方式和物理顺序。一、核心概念1. 聚簇索引Clustered Index聚簇索引是指索引的叶子节点直接存储了整行数据。在 InnoDB 中主键就是聚簇索引。2. 非聚簇索引Non-Clustered Index非聚簇索引是指索引的叶子节点存储的是主键值而不是完整数据。需要根据主键值回表查询完整数据。也叫二级索引。二、结构对比图1. 聚簇索引结构聚簇索引主键索引 ┌─────────────────────────────────┐ │ 根节点 │ │ [1-100] [101-200] [201-300] │ └────────────┬────────────────────┘ │ ┌───────┴───────┐ ▼ ▼ ┌─────────────┐ ┌─────────────┐ │ 叶子节点 │ │ 叶子节点 │ │ id1 完整行 │ │ id101 完整行│ │ id2 完整行 │ │ id102 完整行│ │ id3 完整行 │ │ id103 完整行│ │ ... │ │ ... │ └─────────────┘ └─────────────┘ 数据即索引 索引即数据2. 非聚簇索引结构非聚簇索引如 name 索引 ┌─────────────────────────────────┐ │ 根节点 │ │ [A-F] [G-M] [N-Z] │ └────────────┬────────────────────┘ │ ┌───────┴───────┐ ▼ ▼ ┌─────────────┐ ┌─────────────┐ │ 叶子节点 │ │ 叶子节点 │ │ Alice → 1 │ │ Bob → 3 │ │ Ann → 2 │ │ Ben → 4 │ └─────────────┘ └─────────────┘ 存储主键值 需要回表查询三、详细区别对比表维度聚簇索引非聚簇索引数据存储叶子节点存整行数据叶子节点存主键值每张表数量只能有一个可以有多个物理顺序数据按索引顺序存储数据独立存储查询速度极快一次查找需要回表两次查找占用空间无额外空间数据本身需要额外存储空间主键选择强烈建议使用自增主键任何字段都可以建插入性能顺序插入极快随机插入可能慢四、工作原理示例1. 建表和数据CREATETABLEusers(idINTPRIMARYKEY,-- 聚簇索引nameVARCHAR(50),ageINT,emailVARCHAR(100),INDEXidx_name(name),-- 非聚簇索引INDEXidx_age(age)-- 非聚簇索引);INSERTINTOusersVALUES(1,张三,25, ),(2,李四,30, ),(3,王五,28, ),(4,赵六,32, );2. 通过聚簇索引查询-- 通过主键查询一次查找SELECT*FROMusersWHEREid3;-- 执行过程-- 1. 在聚簇索引树中查找 id3-- 2. 直接在叶子节点找到完整数据-- 3. 返回结果不需要回表-- 性能极快O(log n)3. 通过非聚簇索引查询-- 通过 name 索引查询两次查找SELECT*FROMusersWHEREname王五;-- 执行过程-- 1. 在 idx_name 索引树中找到 王五-- 2. 叶子节点存的是主键值3-- 3. 拿着 id3 回聚簇索引查询完整数据-- 4. 返回结果-- 性能需要两次 BTree 查找-- 这叫回表查询五、回表查询的代价1. 什么是回表-- 场景查询所有字段EXPLAINSELECT*FROMusersWHEREname王五;-- Extra 字段可能显示Using where-- 执行计划显示需要回表2. 如何避免回表-- 创建覆盖索引索引包含所有需要的字段CREATEINDEXidx_name_ageONusers(name,age);-- 查询只返回索引中的字段SELECTname,ageFROMusersWHEREname王五;-- Extra: Using index不需要回表-- 这种叫做覆盖索引查询六、主键选择对性能的影响1. 使用自增主键推荐CREATETABLEusers_autoinc(idINTPRIMARYKEYAUTO_INCREMENT,-- 顺序插入nameVARCHAR(50));-- 插入数据INSERTINTOusers_autoinc(name)VALUES(张三),(李四),(王五);-- 数据物理存储顺序-- id: 1,2,3,4,5...连续有序-- 优点-- 1. 插入快只在最后追加-- 2. 页分裂少-- 3. 空间利用率高2. 使用 UUID 作主键不推荐CREATETABLEusers_uuid(idVARCHAR(36)PRIMARYKEY,-- UUID 无序nameVARCHAR(50));-- 插入数据INSERTINTOusers_uuidVALUES(UUID(),张三),(UUID(),李四);-- 数据物理存储顺序-- id: 随机分散-- 缺点-- 1. 插入慢需要不断调整位置-- 2. 频繁页分裂-- 3. 空间碎片多-- 4. 索引体积大3. 性能对比-- 自增主键插入100万条/分钟-- UUID主键插入30万条/分钟-- 差距3-5倍七、聚簇索引的其他特点1. 页合并和页分裂-- 页分裂场景非顺序插入-- 当页满时需要将一部分数据移到新页-- 影响插入性能-- 页合并场景删除数据-- 当页数据少于一半时可能合并-- 优化空间使用2. 辅助索引的叶子节点-- InnoDB 辅助索引的叶子节点-- 存储的是主键值不是行指针-- 优点-- 1. 主键更新时不需要改辅助索引但很少更新主键-- 2. 辅助索引大小固定-- 缺点-- 1. 需要回表查询-- 2. 占用更多空间八、实际优化案例案例1查询优化-- 原查询需要回表SELECTid,name,ageFROMusersWHEREageBETWEEN20AND30;-- 创建覆盖索引CREATEINDEXidx_ageONusers(age,name,id);-- 现在查询SELECTage,name,idFROMusersWHEREageBETWEEN20AND30;-- Extra: Using index不回表案例2分页优化-- 深分页问题SELECT*FROMusersORDERBYidLIMIT100000,10;-- 需要扫描 100010 行-- 优化先查主键再关联SELECT*FROMusers t1INNERJOIN(SELECTidFROMusersORDERBYidLIMIT100000,10)t2ONt1.idt2.id;-- 二级索引扫描主键减少回表九、总结核心区别维度聚簇索引非聚簇索引数量1个N个存储内容完整数据主键值查询次数1次2次可能回表物理顺序按索引顺序独立存储主键影响直接影响性能间接影响选择建议主键一定要用自增避免页分裂提高插入性能查询尽量用覆盖索引减少回表避免 SELECT *只查需要的字段复合索引设计考虑查询顺序一句话理解聚簇索引就像书的正文本身已经按页码排好非聚簇索引就像书的目录告诉你某个关键词在哪些页码要看到内容还得翻到对应页。