一文搞懂聚簇索引和非聚簇索引

关注公众号 不爱总结的麦穗 将不定期推送技术好文

聚簇索引（Clustered Index）和非聚簇索引（Non-clustered Index）是数据库中常见的两种索引类型。

聚簇索引

  聚簇索引决定了数据行的物理存储顺序。一个表只能有一个聚簇索引，因为数据行的存储顺序只能有一种。聚簇索引指向的是实际存储的数据行，其索引键值是唯一的。

非聚簇索引

  非聚簇索引将索引键值与实际数据行的存储物理位置分开。非聚簇索引指向实际数据行的指针，其索引键值可以是唯一的，也可以是非唯一的。

  在我们工作中，相信大部分小伙伴使用的关系型数据是MySql，接下来主要通过MySql的存储引擎InnoDB、MyISAM来看看聚簇索引和非聚簇索引。

InnoDB存储引擎

在 InnoDB 中，主键索引就是聚簇索引，而非主键索引则是非聚簇索引（辅助索引）。

主键索引

聚簇索引结构如下：

  可以看到叶节点包含了完整的数据记录，InnoDB的索引文件和数据文件是同一个。所以InnoDB要求表必须有主键，在 InnoDB 中，如果表结构中没有定义主键，数据库会自动为每行记录添加一个隐藏的主键，通常称为 db_row_id 字段。这个隐藏主键会确保每行记录都有一个唯一的标识符。

当我们通过主键索引查询数据时，只用到主索引中检索获得记录，就能找到具体的数据。

辅助索引

InnoDB的辅助索引data域存储相应记录主键的值。

辅助索引结构如下：

  当我们通过辅助索引查询数据时，首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。这一过程叫做回表(回表操作是在索引查询时，通过主键索引再次访问以获取完整数据记录的过程)。

MyISAM存储引擎

在MySQL的MyISAM存储引擎中，所有的索引都是非聚簇索引。

结构如下：

  MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址，叶节点的data域存放的是数据记录的地址，而不是主键。

当我们通过索引查询数据时，首先检索索引获得数据地址，然后用数据地址找到具体的数据。

如何避免回表

  回表操作通常需要访问两次索引，增加了IO开销和CPU消耗，对查询性能有一定的影响。

索引覆盖

索引覆盖，即在索引中包含查询所需要的所有列。

假设table_A 创建了一个联合索引(a,b),当我们执行以下语句时，就不需要进行回表查询

select a,b from table_A where a='XXX';

索引下推

索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。

假设table_A 创建了一个联合索引(a,b),当我们执行以下语句时

select a,b from table_A where a like '麦%' and b=10;

  在MySQL 会根据索引找到5.6之前，MySQL 会根据索引找到 like ‘麦%’ 的所有主键Id,然后再依次进行回表查询,得到具体的数据，再过滤 b=10 的数据。

  而MySQL 5.6 以后，联合索引(a,b)中包含列，所以存储引擎直接再联合索引里按照b=10过滤。按照过滤后的数据再一一进行回表扫描，减少回表次数。

总结

  聚簇索引在范围查询和按照主键的查询效率上有很大优势，而非聚簇索引对于等值查询和覆盖查询效果明显，可以快速定位到符合条件的数据行。对于两种索引在实际应用中，根据具体的查询需求和数据访问模式选择合适的索引策略非常重要，可以有效地提升数据库的性能和响应速度。