相关文章:
【mysql】聚簇索引(聚集索引)和非聚簇索引(二级索引、辅助索引)的区别
【mysql】InnoDB二级索引(辅助索引)
注意:本篇是InnoDB的二级索引实现,MyISAM实现不同。
二级索引:叶子节点中存储主键值
,每次查找数据时,根据索引找到叶子节点中的主键值,根据主键值再到聚簇索引中得到完整的一行记录。
问题:
1.相比于叶子节点中存储行指针
,二级索引存储主键值
会占用更多的空间,那为什么要这样设计呢?
指针长度固定,比如4个字节而已,但是主键值是一个具体的值,那么就可能会很长了
InnoDB在移动行时,无需维护二级索引,因为叶子节点中存储的是主键值,而不是指针。
我们知道聚簇索引存储了完整的数据,当数据发生改动时,对应的叶子节点位置可能会变化,但是Key不会变化。二级索引依赖聚簇索引,因此,如果存储的是位置,那么当聚簇索引变化后,需要同步更新二级索引的的位置信息,难度大效率差。如果存的是key的值,永远能找到对应的叶子节点。
就像你有个朋友,记住手机号就行,随时能找到他;如果记得是门牌号,搬家后,你就必须重新记住新的地址,关键是别人还得通知到你他搬家了,麻烦死了。
2.那么InnoDB有了聚簇索引,为什么还要有二级索引呢?
聚簇索引的叶子节点存储了一行完整的数据,而二级索引只存储了主键值,相比于聚簇索引,占用的空间要少。当我们需要为表建立多个索引时,如果都是聚簇索引,那将占用大量内存空间,所以InnoDB中主键所建立的是聚簇索引,而唯一索引、普通索引、前缀索引等都是二级索引
。
3.为什么一般情况下,我们建表的时候都会使用一个自增的id来作为我们的主键?
InnoDB中表中的数据是直接存储在主键聚簇索引的叶子节点中的,每插入一条记录,其实都是增加一个叶子节点,如果主键是顺序的,只需要把新增的一条记录存储在上一条记录的后面,当页达到最大填充因子的时候,下一跳记录就会写入新的页中,这种情况下,主键页就会近似于被顺序的记录填满。
若表的主键不是顺序的id,而是无规律数据,比如字符串,InnoDB无法加单的把一行记录插入到索引的最后,而是需要找一个合适的位置(已有数据的中间位置),甚至产生大量的页分裂并且移动大量数据,在寻找合适位置进行插入时,目标页可能不在内存中,这就导致了大量的随机IO操作,影响插入效率。除此之外,大量的页分裂会导致大量的内存碎片。