【MySQL】一篇讲懂什么是聚簇索引和非聚簇索引(二级索引)以及什么是回表？
1.聚簇索引：
叶子节点直接存储了完整的数据行。
- 每个表只能有一个聚簇索引，通常是主键(Primary Key)。
- 如果没有定义主键，则MySQL会选择一个唯一且非空索引作为聚簇索引。
特点：
- 数据存储：叶子结点存储完整的数据行，因此数据和索引是存储在一起的。
- 查询效率：一次索引查询就能查到完整数据航。
- 排序：数据在物理上时按照聚簇索引的顺序存储的，因此范围查询非常(BETWEEN、>、<)高效。
举例：
2.非聚簇索引(也叫二级索引)：
叶子节点存储的是 索引字段的值+主键值，而不是完整的数据行。
- 一个表可以有多个非聚簇索引。
- 非聚簇索引需要通过主键值 回到聚簇索引中查找完整的数据行，这个过程称为回表(下面详细介绍)。
特点：
- 数据存储：叶子结点存储的是 索引字段值+主键值。而不是完整的数据行。
- 查询效率：通过非聚簇索引查找数据需要两次查找：1.通过非聚簇索引找到主键值。2.通过主键值回到聚簇索引中查找完整的数据行。
- 使用场景：适合查找条件中不包含主键的场景。
举例：
- 步骤1：通过非聚簇索引idx_name找到name='Bob’对应的主键值id=2。
- 步骤2：通过主键值id=2回到聚簇索引中查找完整的数据行，返回id=2,name=‘Bob’,age=30。
优缺点：
-
优点：
- 提高查询效率：加速基于非主键字段的查询。
- 支持多字段索引，可以创建联合索引。
-
缺点：
- 占用存储空间：每个二级索引都要额外的存储空间。
- 可能触发回表：如果查询字段不在二级索引中，需要回表，增加查询开销。
3.回表：
回表 是指 当用二级索引查询数据时，如果查询的字段不在二级索引中，MySQL需要通过二级索引找到主键值，然后再回到聚簇索引中查找完整的数据行。
4.为什么会出现回表？
通常发生在一下场景：
1.查询的字段不在二级索引中：
- 例如，select * 需要获取所有字段，但二级索引只包含部分字段(如name和id)，因此需要回表。
2.使用了ORDER BY或GROUP BY： - 如果排序或分组的字段不在二级索引中，可能需要回表来获取完整的数据行。
5.如何避免回表？
5.1使用覆盖索引：
覆盖索引：查询的字段都在二级索引中，可以直接从二级索引中获取数据，而不需要回表。
SELECT id, name FROM users WHERE name = 'Alice';
id 和 name 都在二级索引 idx_name 中，因此不需要回表。
5.2优化索引设计
确保常用的查询字段都在索引中。
CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(100),age INT,KEY idx_name_age (name, age) -- 联合索引
);
如果查询name和age，可以使用联合索引，避免回表。