1.定义:
不是单独的索引类型,而是一种数据存储方式。
一般建表会用一个自增主键做聚簇索引,没有的话MySQL会默认创建。我们日常工作中,根据实际情况自行添加的索引都是辅助索引,辅助索引就是一个为了需找主键索引的二级索引,现在找到主键索引再通过主键索引找数据;
聚簇索引:将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据
非聚簇索引:将数据与索引分开存储,索引结构的叶子节点指向了数据对应的位置
1.InnoDB中
- InnoDB使用的是聚簇索引,表数据文件本身就是按B+Tree组织的一个索引结构。
索引文件和数据文件是放在一起的。
- 将主键组织到一棵B+树中,而行数据就存储在叶子节点上,若使用where id = 14这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据
- 若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树中,再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。(重点在于通过其他键需要建立辅助索引)
- 聚簇索引默认是主键,如果表中没有定义主键,InnoDB会选择一个唯一且非空的索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键(类似oracle中的Rowld)来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引,必须先删除主键,然后添加我们想要的聚簇索引,最后恢复设置主键即可。
- 主键索引:
- InnoDB的辅助索引
注意:在innoDB中,在聚簇索引之上创建的索引称之为辅助索引,非聚簇索引都是辅助索引,像复合索引,前缀索引(普通索引),唯一索引。辅助索引叶子节点存储的不再是行的物理位置,而是主键值,辅助索引访问数据总是需要二次查找。 如下图,辅助索引是非聚簇索引。
InnoDB的所有辅助索引都引用主键作为data域。
2.MYISAM
- MyISAM使用的是非聚簇索引
索引文件和数据文件是分开存放的。
- 主键索引:MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。
- 辅助索引(Secondary key):在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引,则此索引的结构如下图所示:
- MyISAM使用的是非聚簇索引,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两棵B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。
3.对比
为了更形象说明这两种索引的区别,我们假想一个表如下图存储了4行数据。其中Id作为主索引,Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。
举例:基于name去查,会先查到页目录。会找对应的id,拿着id去聚簇索引中找。
二次查找,先找到主键id,再去页目录中找。所以就是建立name的单值索引,就去建表后默认的主键索引(聚簇索引)上创建一个为name的辅助索引。
问题:主键索引是聚集索引还是非聚集索引?
在Innodb下主键索引是聚集索引,在Myisam下主键索引是非聚集索引
3.使用聚簇索引的优势
问题:每次使用辅助索引检索都要经过两次B+树查找,看上去聚簇索引的效率明显要低于非聚簇索引,这不是多次一举吗?聚簇索引的优势在哪?
1.由于行数据和聚簇索引的叶子节点存储在一起,同一页中会有多条行数据,访问同一数据页不同行记录时,已经把页加载到了Buffer中(缓存器),再次访问时,会在内存中完成访问,不必访问磁盘。这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了,如果按照主键Id来组织数据,获得数据更快。
2.辅助索引的叶子节点,存储主键值,而不是数据的存放地址。好处是当行数据发生变化时,索引树的节点也需要分裂变化;或者是我们需要查找的数据,在上一次IO读写的缓存中没有,需要发生一次新的IO操作时,可以避免对辅助索引的维护工作,只需要维护聚簇索引树就好了。另一个好处是,因为辅助索引存放的是主键值,减少了辅助索引占用的存储空间大小。
4.聚簇索引需要注意什么?
当使用主键为聚簇索引时,主键最好不要用uuid,因为uuid的值太过离散,不适合排序且可能出现新增加记录的uuid,会插在索引树中间的位置,导致索引树调整复杂度变大,消耗更多的时间和资源。
建议使用int类型的自增,方便排序并且默认会在索引树的末尾增加主键值,对索引树的结构影响最小。而且,主键值占用的存储空间越大,辅助索引中保存的主键值也会跟着变大,占用存储空间,也会影响到IO操作读取到的数据量。