关于聚簇索引和非聚簇索引我们这里是根据MySQL的InnoDB存储引擎来讲的,MyISam存储引擎没有聚簇索引。
假设我们有一个Student表,我们结合这个表来讲解
聚簇索引(也叫一级索引)
先上结构图,便于理解
聚簇索引的结构是一颗B+树,并且叶子节点存放的就是行数据,索引和数据紧密结合,所以叫聚簇索引。
聚簇索引不用我们手动创建,创建表的时候存储引擎会自动根据自增主键当做 key 来创建聚簇索引,如果没有主键,会选择非空唯一索引,如果没有唯一索引,会隐式的生成一个名为row_id的列作为聚簇索引。
聚簇索引的叶子节点存放的是行数据,这种结构使得数据行的物理顺序与索引的逻辑顺序一致,故一个表只能有一个聚簇索引。
优点:
- 数据访问超快:由于叶子节点存储的就是行数据,所以访问数据超快。
- 范围查询性能优越:由于数据行在物理上按照索引顺序排列,因此在进行范围查时,一旦找到范围中的第一个数据行,后续的数据行就会物理上毗连在一起,从而避免了大范围扫描,提高了查询效率。
缺点:
- 如果不是按照主键的顺序在表最后插入,可能会导致索引的分裂和重组,从而影响插入性能。
- 更新主键值会导致行移动,进而导致索引分裂和重组。
- 删除行也会导致索引分裂和重组,所以最好逻辑删除,不影响整个索引结构。
非聚簇索引(也叫二级索引)
同样先上结构图
非聚簇索引的结构也是B+树,叶子节点存的除索引值外,还有聚簇索引的索引值。
非聚簇索引查找行数据需要查找两次,第一次查找是根据值找到非聚簇索引叶子节点的聚簇索引的 key,第二次是根据该 key 查找聚簇索引叶子节点的行数据。第二次查找的过程叫做回表。
我们自己定义的索引都是非聚簇索引。
特点:
- 索引与数据分开存储:非聚簇索引将索引和数据分开存储。
- 查询效率相对较低:由于索引和数据分开存储,查询时需要先查找聚簇索引,再根据聚簇索引找到对应的数据行,因此查询效率相对较低。
- 数量灵活:一个表可以有多个非聚簇索引,以满足不同的查询需求。
总结
聚簇索引是根据主键构造的一颗B+树,叶子节点存储行数据,查找只需一次效率高;非聚簇索引叶子节点存的是主键的key,查询需要两次效率相对较低,但是可以创建多个。