MySQL索引概述及为何InnoDB要使用B+树作为索引

​

索引(Index)是帮助MySQL高效获取数据的数据结构
索引的本质:数据结构
索引的目的在于提高查找效率
简单理解为:排好序的快速查找数据结构
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上
​

在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级的查找算法.这种数据结构就是索引
​

​

上图为B树(多路搜索树)索引
(聚集索引,复合索引,前缀索引,唯一索引默认都是使用B+数索引)
​

​

​

Q: 为什么业务层要求删除数据使用逻辑删除(is_deleted),而不是物理删除
​

如果数据删除真实删除了数据表中的行,那么就会导致索引失效(某个索引指向的数据表被物理删除)
​

​

索引优劣势分析

优势:

1.提高数据检索效率,降低数据库IO成本
2.通过索引对数据进行排序,降低数据排序成本,降低CPU消耗

劣势

1.实际上而言,索引也是一张表,该表保存了主键和索引字段,并指向实体表的记录,所以索引也需要占用空间
2.虽然索引提高了查询速度,但却降低了表的更新速度,因为更新表时,MySQL不仅要保存数据,还要保存索引文件,每次更新添加了索引列的字段,都会调整因更新所带来的键值变化后的索引信息(就是更新数据不仅要更新数据表,还要更新索引)
3.建立索引需要花费大量时间去完善出最佳索引
​

​

MySQL索引分类

单值索引(单列索引)

一个索引只包含单个列,一个表可以有多个单列索引

唯一索引

索引列的值必须唯一,但允许空值

复合索引

即一个索引包含多个列
​

​

​

基本语法

​

​

MySQL索引结构

B树

B树将数据存储在枝干中,数据无序,排序时需要回旋查找

B+树

B+树将所有元素转移到了叶子节点并链式连接在一起,保证了数据有序

那些情况需要创建索引

1. 主键自动生成索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段,外键关系建立索引
4. 在查询中需要排序的字段,排序字段若通过索引访问将大大提高排序速度
5. 查询中统计或分组字段

那些情况不需要创建索引

1. 频繁更新的字段不适合创建索引
2. Where条件里用不到的字段不创建索引
3. 表记录过少(百万级别下)
4. 经常增删改的表字段
5. 数据重复且分布平均的表字段,因为内容重复所以为它建立索引没有太大效果

为什么InnoDB使用b+树存储索引

1.索引加载过程:

索引数据和实际数据都存储在磁盘中,当进行查询的时候,需要将磁盘中的数据读取到内存中,
由于文件较大,需要进行分块(页)读取,(不能一次性将所有索引都load进内存)

2.确定了树形结构

使用树结构作为索引的原因就是每次load只一页进入内存,在被load进内存的那一页(节点)中获取到下一个需要被load进内存的页(节点)地址,直到load到需要的页(节点),每次io操作都只io了一小个树节点

3.确定了B树(多路查找树)结构(不是使用B树)

而其他数结构往往具有较高的深度,在数据量大的情况下需要进行多次io,而B树的结构通过多叉解决了树高度的问题

4.使用B+树

但B树虽然解决了,但是B树中非叶子节点中的data会占据大量内存,降低了节点的宽度,而且在范围查找时会遇到回旋查找的问题(下一级节点向上一级节点回溯), ,增加了io次数,B+树则是将所有元素都存储在了叶子节点上,避免了回溯操作
所以最终确定了使用B+树作为索引存储结构.

5对B+树的优化

mysql中对B+数进行了优化,在所有叶子节点中增加了指向相邻叶子节点的双向指针,加快了范围查找即排序操作的速度

不使用其他结构的原因

1.不使用哈希索引是因为首先哈希值是无序的,无法进行索引排序,其次hash表会导致hash冲突,形成拉链,降低查找效率.
2.不使用平衡二叉树是因为,平衡二叉树高度过高,数据量大后会进行大量io操作,其次在进行范围查询时会导致回旋查找.
3.不使用B树是因为,B树虽然解决了高度的问题,可还是会在范围查找时导致回旋查找.
为什么使用B+树索引
数据结构模型​