MySQL索引数据结构与算法

MySQL索引数据结构与算法

1、索引

索引是帮助MySQL高效获取数据的排好序的数据结构（容易忽略的点：排好序）（形象点就是教科书的目录）

索引存储在文件里（也就是说有IO操作）

MyISAM 表的磁盘文件有三个：

• .frm （保存表结构 FoRM）
• .MYI （保存索引 MYisam Index）
• .MYD（保存数据 MYisam Data）

InnoDB 表的磁盘文件有两个：

• .frm （保存表结构 FoRM）
• .ibd （保存索引和数据 InnodB Data）

2、索引结构

这里说说在几种数据结构中，MySQL为什么选择 hash，B+ tree

1. 二叉树
2. 红黑树
3. hash
4. BTree

首先，如果数据没有索引，那我们读取数据是这样的

这里有一篇很好的硬盘存取原理文章，看完更容易理解： https://www.cnblogs.com/leezhxing/p/4420988.html

图片来自 https://blog.csdn.net/caijunsen

上面我们发现读取数据特别耗时，那有没有比较节时的数据结构，我们可以看看二叉树

上面虽然优化了，但是mysql为什么选择 B+Tree

这里介绍一个动态演示数据结构的网址：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

二叉树与红黑树的比较

从上图我们可以看出， 红黑树相比较于二叉树又进步了一些，但红黑树还是有些问题：那就是数据量大的话，【红黑树】的深度会很深，也就是说【深度不可控】，这样一来查找数据还是会很耗时

HASH

从上面我们发现，相比较于红黑树，hash可以固定“深度”，且映射到磁盘存储引用，这样查找数据直接告诉磁盘数据在哪，查找数据也挺快的，但是 【hash】 还是有些不足：那就是【不能范围查找】，比如我们查找Col1>1的数据，当然如果我们查询操作很少的话，我们也可以选择hash数据结构，因为它查找数据挺快的，这也是mysql的索引方法除了B+Tree还有hash

B-Tree

从上图看出，B-Tree又进不了一些，查询速度提高，储存容量也没影响。可能有人会这么想，那我们为什么不把数据全部存在一个节点上，这样深度不就为1了吗？

这是不行的！因为Java拿去数据一般是这样：Java程序 --> CPU --> 内存 --> 硬盘，而内存与硬盘的交互大小是有限制的，是一页数据4K左右，所以不能把所有数据都放在一个节点来获取，一般节点会尽量预存4K容量。

看到这里，我们知道（4K=节点；节点=小节点*小节点的容量）一个节点是4K，而节点内有几个小节点，那么也就是说，只要我们每个的小节点的data容量越小，那么可以存的节点也就可以更多。

B+Tree

B+Tree通过吧data不放在非叶子节点来增加度（小节点），一般会一百个以上是的深度是3~5，从而减少查询次数。并且，叶子节点之间有指针，数据又是递增的，这使得我们范围查找可以通过指针连接查找，而不再从上面节点往下一个找。

结论：B+Tree即减少查询次数，又提供了很好的查询范围。

MyISAM索引实现（非聚集）

MyISAM的索引文件和数据文件是分离的，如开头所介绍，MyISAM 表的磁盘文件有三个：

• .frm （保存表结构 FoRM）
• .MYI （保存索引 MYisam Index）
• .MYD（保存数据 MYisam Data）

MyISAM的索引具体如图：

InnoDB索引实现（聚集）

InnoDB 表的磁盘文件有两个：

• .frm （保存表结构 FoRM）
• .ibd （保存索引和数据 InnodB Data）

• 数据文件本身就是索引文件
• 聚集索引-叶节点包含了完整的数据记录

• 为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

因为InnoDB的数据文件本身要按【主键聚集】，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，MySQL会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

关于为何选择【整型主键】，因为相比于整型的比较，字符串类型的大小比较还要进行ASCII码的转换。

因为表使用【自增主键】，每当插入一条新的记录时，记录就会顺序添加到当前索引节点的后续位置，当页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页。

如果使用【非自增主键】（如学号或身份证号等），由于每次插入的主键值近似于随机，那么不得不为新记录插入到合适位置而移动数据，这增加了很多开销。

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

【节省空间】：指向主键的节点，不用再储存一份相同数据。

【一致性】：如果我们修改索引的数据，那只要修改主键的数据；而如果非主键也存一分数据的话，那得修改两份，所以非主键的叶子节点存主键值就可以保持数据一致性。

参考：

https://blog.csdn.net/caijunsen/article/details/83045985

http://blog.codinglabs.org/articles/theory-of-mysql-index.html