都知道mysql  InnoDb和MyISAM 索引的最大区别是在存储上。一个是主键对应数据行,一个是索引指向数据指针地址。

聚簇索引是一种数据存储方式,它实际上是在同一个结构中保存了B+树索引和数据行,InnoDB表是按照聚簇索引组织的(类似于Oracle的索引组织表)。

InnoDB通过主键聚簇数据,如果没有定义主键,会选择一个唯一的非空索引代替,如果没有这样的索引,会隐式定义个主键作为聚簇索引。

下图形象说明了聚簇索引表(InnoDB)和非聚簇索引(MyISAM)的区别:

对于非聚簇索引表来说(右图),表数据和索引是分成存储的,主键索引和二级索引存储上没有任何区别。

而对于聚簇索引表来说(左图),表数据是和主键一起存储的,主键索引的叶结点存储行数据二级索引的叶结点存储行的主键值

聚簇索引表最大限度地提高了I/O密集型应用的性能,但它也有以下几个限制:

1)插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键。

2)更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义主键为不可更新。

3)二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据

二级索引的叶节点存储的是主键值,而不是行指针(非聚簇索引存储的是指针或者说是地址),这是为了减少当出现行移动或数据页分裂时二级索引的维护工作,但会让二级索引占用更多的空间。

聚簇索引的叶节点就是数据节点非聚簇索引的页节点仍然是索引检点,并保留一个链接指向对应数据块
聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。
相比之下,聚簇索引适合排序非聚簇索引不适合用在排序的场合因为聚簇索引本身已经是按照物理顺序放置的,排序很快。非聚簇索引则没有按序存放,需要额外消耗资源来排序
当你需要取出一定范围内的数据时,用聚簇索引也比用非聚簇索引好。
Tagged on: