Mysql 索引的底层结构

https://www.modb.pro/db/64010

什么是树

下图就是一个典型的树结构，A 节点就是 B 节点的父节点，B 节点是 A 节点的子节点。B、C、D 这三个节点的父节点是同一个节点，所以它们之间互称为兄弟节点。我们把没有父节点的节点叫做根节点，也就是图中的节点 E。我们把没有子节点的节点叫做叶子节点或者叶节点，比如图中的 G、H、I、J、K、L 都是叶子节点。

三个基础概念
节点的高度：节点到叶子节点的最长路径；
节点的深度：根节点到这个节点所经历的边的个数；
节点的层数：节点的层数+1；
树的高度：根节点的高度；

二叉树

解析：我们为user 表建立一个二叉树索引，索引中的key对应user 表中的id， value 对应user 表中对应的数据。这个时候如果我们要查找一个id=12 的数据，我们从根节点开始，仅需要三次搜索就可以拿到我们想要的数据。

但是在某些情况，二叉树会出现链表结构（如图）。这个时候如果我们要查找一个id=12 的数据，需要查询5次。这个时候我们就需要用到平衡二叉树。

平衡二叉树

平衡二叉树又称 AVL 树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过1。当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。相比于二叉查找树来说，平衡二叉树查找效率更稳定，总体的查找速度也更快。

B-Tree

平衡二叉树每个节点只存储一个键值和数据的。当我们表数据量大的情况下，查找数据时会进行很多次磁盘 IO，查找数据的效率将会极低。为了解决平衡二叉树的这个弊端，我们希望单个节点可以存储多个键值和数据的平衡树，让每个节点可以容纳更多的数据，这就是B树。

B-Tree 的几个特点：
1、叶节点具有相同的深度，叶节点的指针为空
2、所有索引元素不重复
3、节点中的数据索引从左到右递增排列

B+Tree

B+Tree 的几个特点：
1、B+ 树非叶子节点上是不存储数据的，仅存储键值
2、B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的
3、B+ 树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。

Mysql 索引实现

MyISAM索引实现

解析：
1、MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
2、在MyISAM中，主索引和辅助索引在结构上没有区别。

InnoDB索引实现

示例1：聚集索引

解析：
1、InnoDB引擎使用B+Tree作为索引结构，叶节点包含了完整的数据记录。
2、聚集索引（Clustered index) 也叫聚簇索引、主键索引。他的显著特点是其叶子节点包含行数据（表中的一行）
3、InnoDB的页块大小默认为16kb。

示例2：非聚集索引

解析：
1、InnoDB非主键索引与主键索引结构有一定区别，叶节点用于记录主键ID，而不是完整的数据记录。
2、非聚集索引（Secondary Index)也叫辅助索引、二级索引、非主键索引。他的显著特点是叶子节点不包括完整的行数据，而是包含行记录对应的主键key。

示例3：联合索引

扩展

Q1：MySQL索引为什么选择B+树而不是B树？
解析：
1、B+树的IO更少：B+树的非叶子节点没有指向关键字具体信息的指针，只用作索引，因此B+树的非叶子节点比B树占用更少磁盘空间。当数据量大时，一次不能把整个索引全部加载到内存，只能逐个加载每一个磁盘块，而关键字所占空间更小可以使得一次性读入内存的索引也就越多，IO次数也就越少。
2、B+树更擅长范围查找：B+树的叶子节点是按顺序放置的双向链表，更适合MySQL的区间查询。B树因为其分支结点同样存储着数据，需要进行一次中序遍历按序。
3、B+树更擅长遍历：B+树只需要去遍历叶子节点就可以实现整棵树的遍历，更适合MySQL的扫表操作。B树则需要进行深度优先遍历或层序遍历。（类比2）
4、B+树的查询效率更加稳定：B+树叶子节点（数据）都在同一层，查找操作从根节点到叶子节点经历的路径长度相同，所以查询效率稳定。

Q2：一个表有1600万记录，假如有一列占8位字节的字段，根据这一列建索引的话索引树的高度是多少?
解析：
以BIGINT为例，存储大小为8个字节。INT存储大小为4个字节（32位）。索引树上每个节点除了存储KEY，还需要存储指针。所以每个节点保存的KEY的数量为pagesize/(keysize+pointsize)（如果是B-TREE索引结构，则是pagesize/(keysize+datasize+pointsize)）。
假设平均指针大小是4个字节，那么索引树的每个节点可以存储16k/((8+4)8)≈171。那么：一个拥有1600w数据，且主键是BIGINT类型的表的主键索引树的高度就是(log10^7)/log171 ≈ 24/7.4 ≈ 3.2。假设平均指针大小是8个字节，那么索引树的每个节点可以存储16k/((8+8)8)≈128。那么：一个拥有1600w数据，且主键是BIGINT类型的表的主键索引树的高度就是(log10^7)/log128 ≈ 24/7 ≈ 3.4。
由上面的计算可知：一个千万量级，且存储引擎是MyISAM或者InnoDB的表，其索引树的高度在3~5之间。