【MySQL】深入理解Mysql索引底层数据结构与算法

索引

Mysql数据表中的数据在磁盘中分布位置可能是不连续的，在读取数据时，每读取一条数据就进行一次磁盘IO效率是很低的，为了减少IO次数，索引就诞生了，通过索引，我们可以快速定位到数据位置，增加查询效率。

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

正常查询和添加索引的查询对比，如下图所示
二叉树会把数据分成两组，值小的数据放左边，大的放右边，同样是找89，如果用链表的方式查，需要查6次，而二叉树只需要查两次，极大的提升了效率。

索引数据结构

索引数据结构（数据结构学习网站）

• 二叉树
• 红黑树
• Hash表
• B-Tree

二叉树

Mysql底层没有采用这种数据结构，比如Col1的值是顺序增长的，这种单边增长的值在二叉树中和链表没有什么区别，也是去做顺序查找，对查询效率没什么帮助。

RBTree-红黑树

大数据量情况下，高度不可控

又叫自平衡二叉树，它会自动优化二叉树的结构，下图中，二叉树查询需要6次，红黑树只要3次，但是数据量大的时候，它的树的高度会很高，从根节点查询到叶子结点也会经历多次IO，效率也很慢，所以也不是Mysql的数据结构。

B Tree B树-平衡多路查找树

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

B+Tree

Mysql底层使用这种数据结构，可以说是对B-Tree的优化，节点中的索引也是从左到右递增排列，data数据都存储在叶子节点，多个叶子节点组成一个磁盘页，叶子节点之间通过指针链接Mysql中把B+Tree优化成了双向指针，指针存储相邻磁盘页的地址。非叶子节点只存储引，并且节点中的每一个元素值就是它指向的下一层节点的磁盘页的第一个值。在构建B+Tree的时候，每个磁盘页的第一个元素会存储在非叶子索引中，通过二分查找法，可以快速定位数据行在磁盘中的地址。

Mysql中一个节点的大小为16kb，例如一个索引类型是bigint，占8字节，加上下一个磁盘的文件地址6字节是14字节，一个非叶子节点就可以放1170个元素。叶子节点不太一样，因为存储引擎不同，data在Innodb中存储整行数据信息，在Mylsam中存储磁盘地址信息，如果是Innodb，每行1kb也能存储16个元素。假设树的高度为三，一个B+Tree能存储的数据量就是：1170 X 1170 X 16 大概2200万，得出结论就是，同样的数据量情况下，每个节点中能存储的索引元素越多，树的高度就会越小，查询效率就越快。

总结：
MySQL索引没有用B Tree，而是用B+Tree——B树的变种，有比B树更高的查询效率
其对B树进行改造，数据全部存叶子节点：

• 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
  • 非叶子节点从叶子节点取中间某些索引，冗余存储
• 叶子节点包含所有索引字段，且数据索引从左到右递增排列
• 叶子节点用双向指针连接，提高区间访问的性能
  • 叶子节点间，可变相理解是双向链表
  • 指针：存储节点在磁盘的位置——自己的和前面节点的，见上图

B-Tree和B+Tree的区别：
1、B-Tree的非叶子节点也存储Data，B+Tree只用叶子节点存储Data，非叶子节点只用来存储索引。同样的数据量情况下，B+Tree每个节点中能存储的索引元素就更多，树的高度就更小，查询速度更快。
2、B-Tree的叶子节点没有指针，B+Tree的叶子节点用指针连接，由于B-Tree没有指针链接，所以做范围查找的时候就会很慢，因为跨区间查找的时候，会重新从根节点向下定位，所以B+Tree区间的访问性能更好。

提问
为什么不把所有的节点放到一个叶子上，省去I/O时长？

• 数据过多，内存会崩，而且都加载到内存速度不会很快

为什么MySQL选择B+树做索引？

• 树的高度，影响索引查找效率
• B+树将data放到叶子节点，而非叶子节点只存储索引，存放同样数量的索引，其高度更小，查找效率更高

Hash索引

Hash索引原理：
如果把一列数据作为Hash索引，在插入一行记录时，会把插入值的Hash值定位到Hash桶中，桶中的Hash碰撞，会以链表的方式存储。查找的时候也会根据查找值的Hash值去Hash桶中定位，然后遍历链表，取出对应的索引元素，以及索引所在行的磁盘文件地址。
虽然这种数据结构查询快，但是没有数据大小的概念，所以这种数据结构仅能满足 “=”，“IN”，范围查询会导致全表扫描，所以也应用较少。

由于Mysql的B+Tree叶子节点有双向指针，并且是顺序排列的，就可以很好的支持范围查找。

存储引擎

存储引擎是针对数据库表的

MyiSAM

Mylsam索引文件和数据文件是分离的（非聚集），使用B+Tree作为索引结构，叶子节点data域存储的是数据记录的地址。

非聚集索引：索引文件和数据文件是分离的

查询一条数据的时候，如果查询条件增加了索引，会先从MYI文件中找到对应行的索引信息，在通过索引中data的磁盘地址值去MYD文件查询数据。

表存储在磁盘中，为以下三个文件：

• .frm
  • 存储表结构信息
• .MYD
  • 存储数据行
• .MYI
  • 存储索引
    

InnoDB

Innodb（聚集）表数据文件本身就是按B+Tree组织的一个索引结构文件
表存储在磁盘中，为以下两个文件：

• .frm
  • 存储表结构信息
• .idb
  • 存储索引和数据行
    

为什么建议Innodb表必须建主键，并且推荐使用整型的自增主键？
1、为什么建主键：Innodb表创建的时候一定会用B+Treel来组织一个索引结构，主键是自带主键索引的，所以说建了主键的话会直接用主键来组织索引结构。如果不建主键，它会从表中选择一列所有元素都不相等的列，把这个列来当成组织B+Tree的数据列，如果没有这样的列，Mysql会自动建一个隐藏列用来组织索引结构。
2、为什么用整型：如果使用不是整型的UUID做主键，在查询阶段会用AscII码去比对大小，效率就会降低，整型的主键对比大小就会快很多，并且整形占用空间也比较小，尤其数据量大时可以节约磁盘空间。
3、为什么用自增：如果使用非自增的UUID做主键，在插入新数据时，因为叶子节点是顺序排列的，如果不是有序的值，就会导致节点分裂，分裂还可能导致树做自平衡。但如果使用自增主键，每次插入都是往后增加新节点，就不会出现这些情况，效率会提高很多。

为什么非主键索引结构叶子节点存储的是主键值？
主要是为了节约存储空间，因为主键的叶子节点已经存储了数据，所以就没必要再存储数据。
非主键索引是非聚集索引，这样设计的缺点是非主键索引找到叶子节点的主键值后，会做回表操作（重新回到聚集索引中找到真正的数据行）。

索引类型

聚集索引： 索引结构和数据一起存放的索引。
非聚集索引： 索引结构和数据分开存放的索引。
比如字典中，用‘拼音’查汉字，就是聚集索引。因为正文中字都是按照拼音排序的。而用‘偏旁部首’查汉字，就是非聚集索引。

Innodb的主键索引就是聚集索引，Mylsam是非聚集索引

密集索引： 叶子节点保存的不只有索引键值，还有该行数据的其他列信息。一个表只有一个密集索引
稀疏索引： 叶子节点保存的只有索引键值，还有该行数据的地址值。

Mylsam：所有索引都是稀疏索引
Innodb：有且只有一个密集索引

Innodb稀疏索引选择规则：
1、有主键，主键是密集索引
2、没有主键，第一个唯一非空索引是密集索引
3、没有主键，没有唯一非空索引，会自动生成一个隐藏索引，把这个索引当作密集索引

联合索引

联合索引就是由多个字段组成的一个索引，上图示例是一个联合主键索引，联合索引也是B+Tree结构 ，会先根据第一个字段的值排序，如果值一样再根据第二个字段值排序，以此类推直到排序成功。如果是联合非主键索引，可能会出现所有值都相同的情况，这种情况就会根据叶子节点中主键索引去排序。如果是联合主键索引，叶子节点存储的就是数据（其他列的值），而非主键索引
所以，联合索引在建立的时候就是排好序的，我们用的时候也要根据建立索引时的字段顺序来用，这就是最左前缀原则。