一篇文章讲清楚MySQL的聚簇/联合/覆盖索引、回表、索引下推
2023-08-10 04:36:47
一、聚簇索引与非聚簇索引
聚簇索引：在MySQL的InnoDB引擎中，聚簇索引是指索引的叶子节点存储了完整的行数据。通常，主键索引就是聚簇索引。如果表中没有主键，MySQL会默认创建一个隐藏的主键来作为聚簇索引。聚簇索引的存储结构使得数据在物理上按照索引顺序存储，因此在进行范围查询时，能够显著提高查询效率。
非聚簇索引：与聚簇索引不同，非聚簇索引的叶子节点只存储了索引字段和主键ID，而不是完整的行数据。这样的设计可以节省存储空间，但在查询时需要先通过非聚簇索引找到主键ID，再通过主键ID回表查询完整的行数据。
二、联合索引
定义：联合索引是指由多个字段组成的索引。在创建联合索引时，需要指定字段的顺序，这个顺序决定了索引的排序方式和查询时的匹配原则。
优点：联合索引能够大大减少扫描行数，提高查询效率。特别是在进行多字段查询时，如果查询条件中的字段与联合索引中的字段顺序一致（或前缀一致），则可以直接使用联合索引进行查询，而无需进行多次单字段索引的查询和合并。
最左匹配原则：在创建联合索引时，需要遵循最左匹配原则。即查询条件中的字段顺序必须与联合索引中的字段顺序一致（或前缀一致），才能使用联合索引进行查询。例如，在(age, name)上创建联合索引后，查询条件为age或(age, name)时可以使用索引，但查询条件仅为name时则无法使用索引。
三、覆盖索引与回表查询
覆盖索引：当查询的字段完全包含在索引中时，可以直接通过索引获取查询结果，而无需回表查询完整的行数据。这种索引被称为覆盖索引。使用覆盖索引可以显著提高查询效率，因为避免了回表查询的开销。
回表查询：当查询的字段不完全包含在索引中时，需要先通过索引找到主键ID，再通过主键ID回表查询完整的行数据。这种查询方式被称为回表查询。回表查询会增加查询的开销，因为需要进行额外的磁盘I/O操作。
四、索引下推
定义：索引下推（Index Condition Pushdown）是MySQL 5.6引入的一个优化索引的特性。它允许在索引层面进行更多的条件过滤，从而减少回表查询的行数。
工作原理：在使用联合索引进行查询时，如果查询条件中包含了联合索引中的部分字段，并且这些字段能够过滤掉大部分数据，那么MySQL可以使用索引下推技术，在索引层面先对这些字段进行过滤，然后再回表查询满足剩余条件的行数据。这样可以显著减少回表查询的行数，提高查询效率。
优点：索引下推技术能够减少回表查询的行数，提高查询效率。特别是在进行多字段查询时，如果查询条件中的字段与联合索引中的字段顺序一致（或前缀一致），并且这些字段能够过滤掉大部分数据，那么索引下推技术能够带来显著的性能提升。
总结：
- 聚簇索引和非聚簇索引是MySQL中两种不同类型的索引，它们在存储结构和查询效率上有所不同。
- 联合索引由多个字段组成，能够减少扫描行数，提高查询效率。在创建联合索引时，需要遵循最左匹配原则。
- 覆盖索引可以避免回表查询的开销，提高查询效率。当查询的字段完全包含在索引中时，可以直接通过索引获取查询结果。
- 回表查询需要额外的磁盘I/O操作，会增加查询的开销。
- 索引下推是MySQL 5.6引入的一个优化索引的特性，它允许在索引层面进行更多的条件过滤，从而减少回表查询的行数，提高查询效率。
热门标签
