数据库应用——MySQL索引的本质笔记

索引的本质

索引是帮助MySQL高效获取数据的排好序的数据结构

• 索引数据结构
• 二叉树
• 红黑树
• Hash表
• B-Tree

二叉搜索树、红黑树、B+树的简单介绍

二叉搜索树（Binary Search Tree）

链接: 参考大佬的文章
二叉搜索树是一种节点值之间具有一定数量级次序的二叉树，对于树中每个节点：

• 若其左子树存在，则其左子树中每个节点的值都不大于该节点值；
• 若其右子树存在，则其右子树中每个节点的值都不小于该节点值。

查询复杂度

观察二叉搜索树结构可知，查询每个节点需要的比较次数为节点深度加一。如深度为 0，节点值为 “100” 的根节点，只需要一次比较即可；深度为 1，节点值为 “80” 的节点，只需要两次比较。即二叉树节点个数确定的情况下，整颗树的高度越低，节点的查询复杂度越低。

当把主键作为索引时，查询次数和全表扫描差不多，因此二叉搜索树并不适合作为MySQL索引的数据结构

查找6 需要查询6次

红黑树（Red-Black Tree）

链接: 参考大佬的文章1

链接: 参考大佬的文章2

红黑树一种特殊的二叉查找树。红黑树的每个节点上都有存储位表示节点的颜色，可以是红(Red)或黑(Black)。

红黑树是一种含有红黑结点并能自动平衡的二叉查找树。它必须满足下面性质：

• 每个节点要么是黑色，要么是红色。
• 根节点是黑色。
• 每个叶子节点（NIL）是黑色。
• 每个红色结点的两个子结点一定都是黑色。
• 任意一结点到每个叶子结点的路径都包含数量相同的黑结点。
• 如果一个结点存在黑子结点，那么该结点肯定有两个子结点

查找6 只需要查询3次

弊端：如果是很大的数据，几百万条，红黑树的高度会特别高，数据量越大，树的高度则特别高

当数据量大的时候，树的高度是不可控的

B-树

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列
  

• 规则：

1. 排序方式：所有节点关键字是按递增次序排列，并遵循左小右大原则；

2. 子节点数：非叶节点的子节点数>1，且<=M ，且M>=2，空树除外（注：M阶代表一个树节点最多有多少个查找路径，M=M路,当M=2则是2叉树,M=3则是3叉）；

3. 关键字数：枝节点的关键字数量大于等于ceil(m/2)-1个且小于等于M-1个（注：ceil()是个朝正无穷方向取整的函数 如ceil(1.1)结果为2);

4. 所有叶子节点均在同一层、叶子节点除了包含了关键字和关键字记录的指针外也有指向其子节点的指针只不过其指针地址都为null对应下图最后一层节点的空格子;

如上图我要从上图中找到0019，查找流程如下

1. 获取根节点的关键字进行比较，当前根节点关键字为8，19>8，所以往找到指向右边的子节点（二分法规则，左小右大，左边放小于当前节点值的子节点、右边放大于当前节点值的子节点）

2. 拿到关键字12和16，19>16，所以直接找到右边节点

3. 拿到18和20，18<19<20 所以直接找到18和20中间的节点

4. 因为19=19 所以直接返回关键字和指针信息（如果树结构里面没有包含所要查找的节点则返回null）

B+树

B+树是B树的一个升级版，相对于B树来说B+树更充分的利用了节点的空间，让查询速度更加稳定，其速度完全接近于二分法查找。

• 非叶子节点不存储data，只存储索引（冗余），可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能

• 规则

1. B+跟B树不同B+树的非叶子节点不保存关键字记录的指针，只进行数据索引，这样使得B+树每个非叶子节点所能保存的关键字大大增加；

2. B+树叶子节点保存了父节点的所有关键字记录的指针，所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数都一样；

3. B+树叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针。

4. 非叶子节点的子节点数=关键字数（来源百度百科）（根据各种资料 这里有两种算法的实现方式，另一种为非叶节点的关键字数=子节点数-1（来源维基百科)，虽然他们数据排列结构不一样，但其原理还是一样的Mysql 的B+树是用第一种方式实现）;
   

• 特点

1. B+树的层级更少：相较于B树B+每个非叶子节点存储的关键字数更多，树的层级更少所以查询数据更快；

2. B+树查询速度更稳定：B+所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比B树更稳定;

3. B+树天然具备排序功能：B+树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高。

4. B+树全节点遍历更快：B+树遍历整棵树只需要遍历所有的叶子节点即可，，而不需要像B树一样需要对每一层进行遍历，这有利于数据库做全表扫描。

B树相对于B+树的优点是，如果经常访问的数据离根节点很近，而B树的非叶子节点本身存有关键字其数据的地址，所以这种数据检索的时候会要比B+树快。只需要经过一次磁盘I/O，性能更高！

hash表

链接: 参考大佬的文章

• 对索引的key进行一次hash计算就可以定位出数据存储的位置
• 很多时候Hash索引要比B+树索引更高效
• 仅能满足 “=”，“IN”，不支持范围查询
• hash冲突问题
  

mylsam索引

MyISAM引擎使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。下图为MyISAM表的主索引，Col1为主键。

frm文件 表结构信息

MYD文件 表所有行数据

MYI文件 索引文件数据

主键索引放在MYI文件内，结构是一颗B+树。表里面的数据存储在MYD文件内

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

innodb索引

InnoDB索引实现(聚集)

• 表数据文件本身就是按B+树组织的一个索引结构文件

• 聚集索引-叶节点包含了完整的数据记录

• 为什么建议InnoDB表必须建主键？

  • 如果不建主键，mysql会在表里找一列所有元素都不相等的列，在这上面建一个唯一索引，然后加入B+树索引里去组织整张表的所有数据
  • 如果找不到这样条件的一个列，mysql会在后台自动建一列隐藏数据（rowid，1,2,3,4,5…）。这列隐藏数据就相当于主键。因此我们必须自己建主键，不要让mysql去消耗资源

• 并且推荐使用整型的自增主键？

  • 需要保证索引是有序的，从左到右依次递增
  • 如果索引不是有序的，会导致树分裂树平衡

• 为什么非主键索引结构叶子节点存储的是主键值?(一致性和节省存储空间)

frm文件 表结构信息

ibd文件 数据和索引都存储在ibd文件