需要注意的是聚簇索引和非聚簇索引只是在逻辑关系上分的类,本文就从先整体分析两大索引的区别,之后再谈Innodb和Myisam两大执行引擎中各索引的区别。
对Innodb和Myisam不了解的可以看看之前的文章:
Innodb和Myisam的区别_迷茫的羔羊羊的博客-CSDN博客
1.聚簇索引和非聚簇索引的区别
-
聚簇索引在叶子节点上存储的是整个完整的数据值,而非聚簇索引在叶子结点上存储的是主键值或者(主键值)+数据地址偏移量。
-
聚簇索引每张表中只能有一个,而非聚簇索引每张表可以有多个。
-
聚簇索引中的排序顺序与表中数据的顺序一致,而非聚簇索引中不一致。
2.Innodb中的聚簇索引和非聚簇索引
2.1聚簇索引
Innodb中一定是有主键的,如果没有手动创建主键,那么也会使用隐藏的主键,在Innodb引擎中主键一定是聚簇索引。
2.2非聚簇索引
在聚簇索引上建立的索引也称为辅助索引,非聚簇索引都是辅助索引,非聚簇索引上叶子节点存储的是主键id,访问实际数据时如果需要二次查找,就拿着主键id再去查找完整的数据,这个过程也称为叫做回表,像唯一索引、复合索引都采用的是这种方式。
如果在非聚簇索引中直接能查询到所需的列的值,则不需要回表(这种也成为索引覆盖)如下情况:
select id from table where num = "110" #此处假设id为主键,num为二级索引
但是这种情况就必须先拿到id,然后回表去查询相关的score数据:
select id,num,score from table where num="110" #num为二级索引
3.Myisam中的聚簇索引和非聚簇索引
Myisam采用的是非聚簇索引的设计,不支持聚簇索引。
而且Myisam引擎中表可以没有主键,如果有主键的话:Myisam会为表的主键单独创建一个索引,只不过在索引的叶子节点中存储的不是完整的用户记录,而是主键值和数据地址偏移量的组合。因为Myisam本身存储的索引和数据不在一个文件中,其叶子节点存储的是数据的物理地址(维护方便,所以适合查询),拿到数据地址偏移量直接去数据文件获取即可,所以查找效率很高。
Myisam对于其他的列还可以创建索引,其叶子结点上存的是数据的偏移地址。