本文介绍 mysql 在写入数据的时候对于索引更新的原理,mysql 采用 B+ 树的数据结构作为索引。
索引介绍见:【数据库】数据库索引介绍和使用
2. 索引写入的几种情况
mysql 的索引 B+ 树的每个节点对应一个磁盘页,在写入的时候区分以下几种情况
序号 | 叶子节点是否满 | 其他条件 | 行为 |
1 | 没有满 | / | 直接写入叶子节点 |
2 | 满 | 相邻节点未满 | 左旋或者右旋,把数据移到相邻的节点 |
3 | 满 | 父节点未满 | 1. 拆分叶子节点,把叶子节点当中的值放到父级 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边 |
4 | 满 | 父节点满 | 1. 拆分叶子节点 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边4. 拆分父节点,把叶子节点当中的值放到父级5. 父节点拆分之后小于中间值的放左变,大于中间值的放右边6. 中间节点放入再上一层的父节点 |
序号 | 叶子节点是否满 | 其他条件 | 行为 |
1 | 没有满 | / | 直接写入叶子节点 |
2 | 满 | 相邻节点未满 | 左旋或者右旋,把数据移到相邻的节点 |
3 | 满 | 父节点未满 | 1. 拆分叶子节点,把叶子节点当中的值放到父级 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边 |
4 | 满 | 父节点满 | 1. 拆分叶子节点 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边4. 拆分父节点,把叶子节点当中的值放到父级5. 父节点拆分之后小于中间值的放左变,大于中间值的放右边6. 中间节点放入再上一层的父节点 |
序号 | 叶子节点是否满 | 其他条件 | 行为 |
1 | 没有满 | / | 直接写入叶子节点 |
2 | 满 | 相邻节点未满 | 左旋或者右旋,把数据移到相邻的节点 |
3 | 满 | 父节点未满 | 1. 拆分叶子节点,把叶子节点当中的值放到父级 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边 |
4 | 满 | 父节点满 | 1. 拆分叶子节点 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边4. 拆分父节点,把叶子节点当中的值放到父级5. 父节点拆分之后小于中间值的放左变,大于中间值的放右边6. 中间节点放入再上一层的父节点 |
序号 | 叶子节点是否满 | 其他条件 | 行为 |
1 | 没有满 | / | 直接写入叶子节点 |
2 | 满 | 相邻节点未满 | 左旋或者右旋,把数据移到相邻的节点 |
3 | 满 | 父节点未满 | 1. 拆分叶子节点,把叶子节点当中的值放到父级 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边 |
4 | 满 | 父节点满 | 1. 拆分叶子节点 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边4. 拆分父节点,把叶子节点当中的值放到父级5. 父节点拆分之后小于中间值的放左变,大于中间值的放右边6. 中间节点放入再上一层的父节点 |
序号 | 叶子节点是否满 | 其他条件 | 行为 |
1 | 没有满 | / | 直接写入叶子节点 |
2 | 满 | 相邻节点未满 | 左旋或者右旋,把数据移到相邻的节点 |
3 | 满 | 父节点未满 | 1. 拆分叶子节点,把叶子节点当中的值放到父级 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边 |
4 | 满 | 父节点满 | 1. 拆分叶子节点 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边4. 拆分父节点,把叶子节点当中的值放到父级5. 父节点拆分之后小于中间值的放左变,大于中间值的放右边6. 中间节点放入再上一层的父节点 |
序号 | 叶子节点是否满 | 其他条件 | 行为 |
1 | 没有满 | / | 直接写入叶子节点 |
2 | 满 | 相邻节点未满 | 左旋或者右旋,把数据移到相邻的节点 |
3 | 满 | 父节点未满 | 1. 拆分叶子节点,把叶子节点当中的值放到父级 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边 |
4 | 满 | 父节点满 | 1. 拆分叶子节点 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边4. 拆分父节点,把叶子节点当中的值放到父级5. 父节点拆分之后小于中间值的放左变,大于中间值的放右边6. 中间节点放入再上一层的父节点 |
序号 | 叶子节点是否满 | 其他条件 | 行为 |
1 | 没有满 | / | 直接写入叶子节点 |
2 | 满 | 相邻节点未满 | 左旋或者右旋,把数据移到相邻的节点 |
3 | 满 | 父节点未满 | 1. 拆分叶子节点,把叶子节点当中的值放到父级 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边 |
4 | 满 | 父节点满 | 1. 拆分叶子节点 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边4. 拆分父节点,把叶子节点当中的值放到父级5. 父节点拆分之后小于中间值的放左变,大于中间值的放右边6. 中间节点放入再上一层的父节点 |
序号 | 叶子节点是否满 | 其他条件 | 行为 |
1 | 没有满 | / | 直接写入叶子节点 |
2 | 满 | 相邻节点未满 | 左旋或者右旋,把数据移到相邻的节点 |
3 | 满 | 父节点未满 | 1. 拆分叶子节点,把叶子节点当中的值放到父级 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边 |
4 | 满 | 父节点满 | 1. 拆分叶子节点 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边4. 拆分父节点,把叶子节点当中的值放到父级5. 父节点拆分之后小于中间值的放左变,大于中间值的放右边6. 中间节点放入再上一层的父节点 |
序号 | 叶子节点是否满 | 其他条件 | 行为 |
1 | 没有满 | / | 直接写入叶子节点 |
2 | 满 | 相邻节点未满 | 左旋或者右旋,把数据移到相邻的节点 |
3 | 满 | 父节点未满 | 1. 拆分叶子节点,把叶子节点当中的值放到父级 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边 |
4 | 满 | 父节点满 | 1. 拆分叶子节点 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边4. 拆分父节点,把叶子节点当中的值放到父级5. 父节点拆分之后小于中间值的放左变,大于中间值的放右边6. 中间节点放入再上一层的父节点 |
序号 | 叶子节点是否满 | 其他条件 | 行为 |
1 | 没有满 | / | 直接写入叶子节点 |
2 | 满 | 相邻节点未满 | 左旋或者右旋,把数据移到相邻的节点 |
3 | 满 | 父节点未满 | 1. 拆分叶子节点,把叶子节点当中的值放到父级 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边 |
4 | 满 | 父节点满 | 1. 拆分叶子节点 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边4. 拆分父节点,把叶子节点当中的值放到父级5. 父节点拆分之后小于中间值的放左变,大于中间值的放右边6. 中间节点放入再上一层的父节点 |
序号 | 叶子节点是否满 | 其他条件 | 行为 |
1 | 没有满 | / | 直接写入叶子节点 |
2 | 满 | 相邻节点未满 | 左旋或者右旋,把数据移到相邻的节点 |
3 | 满 | 父节点未满 | 1. 拆分叶子节点,把叶子节点当中的值放到父级 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边 |
4 | 满 | 父节点满 | 1. 拆分叶子节点 2. 写入叶子节点3. 拆分之后小于中间值的放左变,大于中间值的放右边4. 拆分父节点,把叶子节点当中的值放到父级5. 父节点拆分之后小于中间值的放左变,大于中间值的放右边6. 中间节点放入再上一层的父节点 |
3. 场景推演
3.1. 场景1:叶子节点未满
以上述B+树为例,当写入 49 时,符合上述的第一个情况,直接写入磁盘块5即可,会获得如下B+树
3.2 场景2:叶子节点满,相邻节点未满
假设上述 B+树写入值是 50 的记录时,符合上述的第二个条件,因为左侧兄弟节点的磁盘块未满,会进行左旋操作,左旋右旋见:https://yuque.antfin-inc.com/docs/share/ae9c4fe3-ac81-4ea8-9ac8-287544edd9c6#isDvY
左旋之后会得到如下 B+ 树
3.3. 场景3:叶子节点满、兄弟节点满、父节点未满
假设上述 B+ 树继续写入值是 51 的记录,因为叶子节点已经满了,并且相邻的兄弟节点也已经满了,则 B+ 树会进行分裂,分类会从插入的数据块的中间分裂(如果是随机写入,顺序写入会决策分裂点,后文讨论),分裂成磁盘块5和磁盘块7,对应的磁盘数据也要做拷贝,并且把中间的值复制到父节点的非叶子索引中,最终会得到如下的图:
3.4. 场景4:叶子节点满、兄弟节点满、父节点满
这个场景和场景3类似,在叶子节点做好分裂之后,父节点需要再做一次分裂。
4. 分裂
4.1. 分裂的问题
以聚簇索引为例,假设索引的写入一直是有序的写入,比如 123456,之后的写入是 789 以此类推,假设写入8的时候页已经满了,则需要进行分裂,分裂成 12345 和 678,因为是顺序写入,所以在分裂前的磁盘上,是不会有数据写入的,就导致了磁盘块1 的空间浪费,如下图
4.2. 解决方案
InnoDB存储引擎的Page Header中有以下几个部分用来保存插入的顺序信息:
- PAGE_LAST_INSERT :最后一次插入数据的位置
- PAGE_DIRECTION :最后插入数据的方向
- PAGE_N_DIRECTION:同一个方向连续插入的次数
4.2.1. 场景1 顺序写入 在插入的地方分裂
4.2.2. 场景2 顺序写入,定位的地方后面还有3条记录,在3条记录后分裂
4.2.3. 场景3 随机写入,中间分裂
假设下图是随机写入的 123456,则在中间进行分裂
编辑于 2022-03-19 03:49