B+ 树为了维护索引有序性,在插入新值的时候需要做必要的维护。如果插入的值比最大值id大,则只需要最后记录后面插入一个新记录。如果新插入的ID值在原先的有序中间,就相对麻烦了,需要逻辑上挪动后面的数据,空出位置。如果所在的数据页已经满了,根据 B+ 树的算法,这时候需要申请一个新的数据页,然后挪动部分数据过去。这个过程称为页分裂。在这种情况下,性能自然会受影响。
除了性能外,页分裂操作还影响数据页的利用率。原本放在一个页的数据,现在分到两个页中,整体空间利用率降低大约 50%。
当然有分裂就有合并。当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。合并的过程,可以认为是分裂过程的逆过程。
基于上面的索引维护过程说明,我们来讨论一个案例:
你可能在一些建表规范里面见到过类似的描述,要求建表语句里一定要有自增主键。当然事无绝对,我们来分析一下哪些场景下应该使用自增主键,而哪些场景下不应该。
自增主键是指自增列上定义的主键,在建表语句中一般是这么定义的: NOT NULL PRIMARY KEY AUTO_INCREMENT。
插入新记录的时候可以不指定 ID 的值,系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。
也就是说,自增主键的插入数据模式,正符合了递增插入的场景。每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。
而有业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高。
除了考虑性能外,我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段,比如字符串类型的身份证号,那应该用身份证号做主键,还是用自增字段做主键呢?
由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键,那么每个二级索引的叶子节点占用约 20 个字节,而如果用整型做主键,则只要 4 个字节,如果是长整型(bigint)则是 8 个字节。
显然,主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。
所以,从性能和存储空间方面考量,自增主键往往是更合理的选择。
有没有什么场景适合用业务字段直接做主键的呢?还是有的。比如,有些业务的场景需求是这样的:
- 只有一个索引;
- 该索引必须是唯一索引。
由于没有其他索引,所以也就不用考虑其他索引的叶子节点大小的问题。
这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则,直接将这个索引设置为主键,可以避免每次查询需要搜索两棵树。
主键索引又称聚簇索引,聚簇索引具备惟一性因为聚簇索引是将数据跟索引结构放到一块,所以一个表仅有一个聚簇索引。
InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用"where id = xxx"这样的条件查找主键,则按照B+树的检索算法便可查找到对应的叶节点,以后得到行数据。
若对其他字段列进行条件搜索,则须要两个步骤:第一步在辅助索引B+树中检索其他,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操做,最终到达叶子节点便可获取整行数据。(重点在于经过其余键须要创建辅助索引)
聚簇索引的优缺点排序
优势:
- 数据访问更快,由于聚簇索引将索引和数据保存在同一个B+树中,所以从聚簇索引中获取数据比非聚簇索引更快
- 聚簇索引对于主键的排序查找和范围查找速度很是快
缺点:
- 插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,不然将会出现页分裂,严重影响性能。所以,对于InnoDB表,咱们通常都会定义一个自增的ID列为主键
- 更新主键的代价很高,由于将会致使被更新的行移动。所以,对于InnoDB表,咱们通常定义主键为不可更新。
- 二级索引访问须要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。