聚簇索引和非聚簇索引在mysql中有何区别
2024-04-19 16:19:12
在 MySQL 中，聚簇索引和非聚簇索引的核心区别体现在数据存储方式、索引结构、数量限制、查询性能及对写入操作的影响等方面，具体如下：
1. 数据存储方式聚簇索引
物理排序决定数据存储顺序：数据行按聚簇索引的键值在磁盘上连续存放，形成逻辑与物理顺序一致的结构。
叶子节点存储完整数据行：索引的叶子节点直接包含数据行的全部字段，无需额外查找。
依赖主键或唯一索引：InnoDB 默认使用主键作为聚簇索引；若无主键，则选择唯一的非空索引；若均不存在，会隐式生成一个 6 字节的隐藏主键（ROWID）。
非聚簇索引
不决定物理存储顺序：数据行的物理存储与索引键值无关，仅通过逻辑关联指向数据。
叶子节点存储主键值：索引的叶子节点仅包含对应数据行的主键值，而非完整数据。
需回表查询：通过非聚簇索引查找数据时，需先获取主键值，再通过聚簇索引定位完整数据行。
聚簇索引
每表仅一个：由于数据行的物理存储顺序由聚簇索引唯一决定，一张表只能有一个聚簇索引。
非聚簇索引
可创建多个：一张表可基于不同列创建多个非聚簇索引，以优化不同查询场景。
聚簇索引
高效直接访问：通过聚簇索引查找数据时，索引与数据在同一结构中，无需回表，查询速度极快。
范围查询与排序优化：数据连续存储的特性使范围查询（如 BETWEEN）和排序操作（ORDER BY）性能显著优于非聚簇索引。
覆盖索引优化：若查询字段全部包含在聚簇索引中，可避免回表，进一步提升效率。
非聚簇索引
需回表开销：每次通过非聚簇索引查询需两次索引查找（先查非聚簇索引获取主键，再查聚簇索引获取数据），增加 I/O 成本。
适合多条件查询：虽单次查询效率略低，但可为常用查询字段（如 WHERE、JOIN、ORDER BY）创建多个非聚簇索引，显著加速复杂查询。
覆盖索引优化：若查询字段全部包含在非聚簇索引中（如索引包含 (col1, col2) 且查询仅涉及这两列），可避免回表。
聚簇索引
插入顺序敏感：若主键无序（如随机 UUID），可能导致页分裂（Page Split），即数据频繁插入到磁盘页的中间位置，引发页分裂和碎片化，降低写入性能。
自增主键优化：使用自增主键（如 INT AUTO_INCREMENT）可保证数据按顺序插入，减少页分裂，提升写入效率。
非聚簇索引
维护成本高：数据更新时，需同步更新所有相关非聚簇索引的叶子节点（存储的主键值不变，但索引键值可能变化），增加写入开销。
索引选择性影响：高选择性（唯一值多）的列更适合建非聚簇索引，可减少回表次数；低选择性列（如性别）索引效果有限。
聚簇索引
仅 InnoDB 支持：MyISAM 等存储引擎不支持聚簇索引，数据按插入顺序存储，索引与数据分离。
非聚簇索引
InnoDB 和 MyISAM 均支持：但 MyISAM 的索引结构与 InnoDB 不同（MyISAM 的索引叶子节点存储数据文件指针，而非主键值）。
聚簇索引适用场景：
主键查询或范围查询频繁的表。
需高效排序或分组操作的场景。
建议：优先使用自增主键作为聚簇索引，避免无序主键导致的性能问题。
非聚簇索引适用场景：
常用查询条件（如 WHERE、JOIN）的列。
需覆盖索引优化的查询。
建议：避免过度创建非聚簇索引（每个索引增加写入开销），仅对高频查询字段建索引。
通过合理设计主键和索引策略（如选择自增主键、为常用查询字段添加非聚簇索引），可显著提升 MySQL 的查询性能和写入效率。
热门标签
