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

MySQL InnoDB 引擎中的聚簇索引和非聚簇索引的区别

在 MySQL 的 InnoDB 存储引擎中,聚簇索引和非聚簇索引是两种常见的索引类型,它们在数据存储结构和使用场景上有显著区别。


1. 聚簇索引(Clustered Index)

特点

  1. 数据和索引存储在一起

    • InnoDB 表的主键索引是聚簇索引,数据行存储在叶子节点中。
    • 索引按照主键值排序,数据行的存储顺序与索引顺序一致。
  2. 一个表只能有一个聚簇索引

    • 因为表的数据行只能存储一次,因此只能根据一个索引进行聚簇。
  3. 高效范围查询

    • 聚簇索引适合范围查询,因为数据是物理上连续存储的。
  4. 回表操作减少

    • 查询主键索引时,无需再次从其他位置读取数据行。

缺点

  1. 主键过大影响性能

    • 主键列较长会导致索引占用更多空间,降低性能。
  2. 插入速度受排序影响

    • 新数据插入时需要维护顺序,可能引发页分裂,导致性能下降。

存储结构

  • 以 B+ 树为基础构建,叶子节点存储的是完整的数据行。

2. 非聚簇索引(Secondary Index)

特点

  1. 数据和索引分开存储

    • 索引的叶子节点存储的是主键值而非完整的数据行。
  2. 支持多个非聚簇索引

    • 一个表可以有多个非聚簇索引。
  3. 查询时需要回表

    • 查询非聚簇索引时,通过主键值回到聚簇索引查找完整数据行。

优点

  1. 支持更多的查询优化

    • 可以为需要频繁查询的非主键列建立索引。
  2. 灵活性高

    • 多个非聚簇索引可以覆盖不同的查询场景。

缺点

  1. 查询可能较慢

    • 需要通过主键值回表查找完整数据行,增加查询时间。
  2. 占用更多存储空间

    • 每个非聚簇索引都需要单独的存储空间。

存储结构

  • 以 B+ 树为基础构建,叶子节点存储的是主键值。

3. 聚簇索引与非聚簇索引的对比

对比项 聚簇索引 非聚簇索引
存储结构 数据行存储在叶子节点 叶子节点存储主键值
数据顺序 数据按主键顺序存储 数据存储与索引无关
支持数量 每个表只能有一个 一个表可以有多个
回表操作 不需要 查询完整数据时需要回表
空间占用 较少 较多
查询效率 主键查询和范围查询更高效 需要回表时效率较低
适用场景 主键查询、范围查询 查询非主键列

4. 总结

  • 聚簇索引

    • 将数据和索引存储在一起,查询主键效率高,但插入性能可能受影响。
    • 适合对主键频繁查询或排序的场景。
  • 非聚簇索引

    • 数据和索引分开存储,支持灵活的查询优化,但需要回表查找数据。
    • 适合对非主键列频繁查询的场景。

在设计表时,需要根据查询需求合理选择索引类型,以优化查询性能。

posted @   Eiffelzero  阅读(135)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示