MySql索引底层数据结构小记

MySql索引底层数据结构小记

前言：我们在向数据库中查询自己想要的数据时，由于mysql数据库中数据表中的数据位于磁盘中的某个点，但这些点时分布不均匀或不连续的，所以在每读一条记录就进行一次磁盘IO效率是非常低的，为了减少IO次数，就有了索引。

作用：帮助我们快速定位数据在磁盘中的分布位置，提高读取效率。

理解：索引其实就是一种排好序的数据结构。

索引的数据结构：

二叉排序树、红黑树（二叉平衡树）、B-Tree（B树）、B+Tree（B树的优化）、Hash(哈希)。

• 二叉排序树：

特点：右节点永远大于根节点，左节点永远小于根节点；

例如上述表中，我需要查询col1的值为22的记录。在链表的形式下，我需要顺序查询5次，同样为二叉排序树的方式查询，只需要从根节点34出发，查询2次即可查询到。因此提高了查询效率。

但是为什么mysql不选用二叉排序库为索引的数据结构呢，例如如下例子：

如果我对col1这样的递增字段进行索引，采用二叉排序树存储，那我需要查询col1字段为5的记录，同样需要顺序查询5次，因此，对于

col1这样的递增字段，二叉排序树并不能提高查询效率。

不选用二叉排序树的原因：无法控制树的高度会很大，查询时效率会很慢，例如递增的索引值

• 红黑树（平衡二叉树）：

特点：是一种自平衡二叉查找树，都是在进行插入和删除操作时通过特定操作保持二叉查找树的平衡，从而获得较高的查找性能。

如上图，同样我需要查询col1的值为5的记录，需要查询4次，相比二叉树、链表是效率有所提高的，但是同样存在一个树的高度不可控问题，当数据量达到非常大时，整个红黑树的高度也就会变高，再去查询同样效率也不是有很明显的提高。

不选用二叉排序树的原因：数据量大时，同样无法控制树的高度会很大，查询时效率会很慢。

• B-Tree（B树）:

特点：

1. B树的叶子节点具有相同的深度，而且叶子节点的指针为空。

2. 所有的索引元素不重复

3. 节点中的数据索引从左到右递增排列（排好序）

4. 两个索引元素之间的空间存储的是位于此两个索引元素之间的数据页的磁盘文件地址

5. 每个索引存储对应的data存储的是索引所在行的磁盘文件地址。

   如下图，
   

对比B树和红黑树，其通过在每个数据页节点可以存储多个索引元素，来控制了树的高度，同时由于每个数据页上的索引元素是递增（排好序）的，可以一批一批查询数据在内存（RAM）中做查找（二分等），从而提高了查询效率。

• B+Tree（B树的优化）:

特点：

1. 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
2. data数据都存在叶子节点,非叶子节点中的每一个元素值都是其指针指向下一层的节点的第一个元素值。每个数据页的第一个索引元素会存储在非叶子节点上，通过二分查找，可快速定位数据行所在的磁盘文件地址。
3. 叶子节点包含所有索引字段
4. 叶子节点用双向指针连接，提高区间访问的性能
5. 节点中的数据索引从左到右递增排列（排好序）
6. 聚集索引在MyISAM引擎中，data存储的是索引所在行的内存地址。InnoDB引擎中，data存储的是所在行的字段值。

B+树在继承了B树的可控树高度的特点下，又添加了叶子节点之间用双指针连接，提高了范围查找的效率。相较于B树，B+树只在叶子节点存储数据，节省了每个非叶子节点的索引存储数量，从而达到了减少树的高度，极大提高了查询的效率。

• Hash(哈希)：

mysql中还有一中hash索引数据结构，其原理是添加一个元素，首先会对索引元素hash算法得到hash值，然后放入hash散列通中，如果hash值发生碰撞，则同样hash值的元素会以链表的形式存储。查询时，首先也会对查找的值进行hash运算得到hash值，然后遍历链表，寻找对应的索引元素，然后取出该索引所在行的磁盘文件地址。

这种数据结构，虽然查询效率快，但他仅限于“=”、“in”；范围查询的话会导致全表扫描，所以应用场景比较少。

综上总结：

mysql在针对B树和B+树肯定选择了B+树，其原因有以下：

1. B+树的数据只存储在叶子节点，极大的降低了树的高度，从而提高了查找效率，
2. B+树叶子节点的双指针，为范围查找的性能得到大幅度提高，B树只能每次从根节点向下查找。

Mysql的存储引擎：

MyLASM:

他的索引文件和数据文件是分开存储的。

frm文件：存储表结构信息；MYD：存储数据信息、MYI：存储索引信息

索引信息采用B+树数据结构存储，叶子节点存储的data是索引所在行的磁盘文件地址

查找数据时，如果查找条件有索引，则先从MYI文件中查找到对应索引元素的索引所在行的磁盘文件地址，再通过磁盘地址去MYD文件中查找数据记录。

InnoDB:

他的索引文件和数据文件是一起存储的。

frm文件：存储表结构信息；ibd：存储索引和数据信息

叶子节点存储的data是索引所在行的记录值。

InnoDB的主键索引就是聚集索引。MyLASM是非聚集索引。

Question：为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

为什么建主键：InnoDB表被创建时采用B+树结构存储索引和数据，主键自带主键索引，直接可以采用主键组织B+树存储。如果不主动建主键的话，mysql会默认选择一列元素不重复的字段作为主键，去组织B+树。 如果不存在这样的列，那么mysql会自动生成隐藏列（类似row_id），这个列是唯一的，来组织B+树。所以为了不浪费不必要的资源，所以建议建主键。

为什么使用整型：假如使用UUID来做主键，在查询阶段，会使用UUID的ASCII码去比对大小，来进行组织B+树，整型占用的空间较小，比较起来也简单快速，数据量大的话，可以节省磁盘空间。

为什么用自增：假如用UUID做主键，在插入新数据时，由于叶子结点是有序排列的，UUID不是有序的，可能会导致节点分裂，树会发生自平衡。但是采用自增主键的话， 每次只需要往后新增节点即可。效率会提高很多。

Question：为什么非主键索引结构叶子节点存储的是主键值？

为了保证数据的一致性，同时也为了节省磁盘空间。

联合索引的数据结构：

联合索引是由多个字段组成的一个索引，他具有先后顺序之分，在组织B+树是，顺序已经按照建立联合索引的字段顺序进行排序。如上 他会按照name、age、position依次去排好序，如果创建的是联合非主键索引，那么会存在所有值相同的情况，这种情况会根据叶子节点的主键索引去排序。

字段组成的一个索引，他具有先后顺序之分，在组织B+树是，顺序已经按照建立联合索引的字段顺序进行排序。如上 他会按照name、age、position依次去排好序，如果创建的是联合非主键索引，那么会存在所有值相同的情况，这种情况会根据叶子节点的主键索引去排序。

因此联合索引在建立的时候就是按照顺序排好序的，使用的时候也要按照顺序去使用，这就是最左前缀原则。