十六、mysql索引的实现原理和数据结构

B-Tree

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

        就这样的一个结构。也就是说在一个节点上可以存储更多的元素，k-v，key就是索引字段，data就是索引字段所在的那一行的数据或是那一行数据坐在的的磁盘文件地址、指针，再去查找元素的时候一次性不是Load一个小元素，而是把一个大的节点的数据一次性全部load到内存，然后再在内存里再去比对，在内存里操作是比较快的。

        如果我们要查找49这个元素，实际上是从根节点开始查找的，它一次性将根节点这个大节点一次性load到内存里，然后用要查找的元素在这里去比对，49大于15小于56，在15和56之间有一个节点存储的是下一个节点的磁盘地址指向下一个节点（这个节点的索引都是大于15小于56的），然后再将这个节点一次性load到内存去找这个元素，然后比对就找到了。

        注意，一次load节点是一次磁盘IO，是非常慢的，但是我们把它load到内存中之后在你内存里随机的找某一个元素是非常快的，跟一次磁盘IO这个时间消耗去比对的话几乎可以忽略不计。

        那按这种说法树的高度越小越好，那按这种思路可不可以把一个表的数据都放到一个大的节点上？然后把这个节点一次性load到内存里，我再在内存里一个个去比对不行吗？不是说内存里去比较查找元素是非常的快嘛，跟一次磁盘IO去比对快的多。不可以这样吗？

        答案是否定的。

        凡事都有个度。你想想，假如我们有几千万数据，在磁盘上面全部放到一个节点上去是不可能的，你的数据表是一行行插入的，存在磁盘上面几百兆甚至几个G，一次性load到内存中合适吗？内存本来就有限，一次性load这么大的数据，而且如果你学过计算机组成原理你也知道，磁盘IO跟内存打交道的单位是4K，一次可能读取4K的数据，可能有时候有一些局部读取的原理可能会取几十K（4的整数倍），取个16K，24K也是可以的 。但是一次交互取这么大是搞不定的，这是计算机组成原理定的，一次磁盘IO取那么多数据，对内存也是非常的浪费，而且这一次磁盘IO也是非常慢的。所以这个节点的大小设置要合适，不能太大也不能太小，mysql对这个节点大小设置的是16K，用下面这个SQL就是可以查到 show clobal status like 'Innodb_page_size' 。

为啥设置16K？为什么不是更大的如16M呢，16K已经足够用了。等会儿会具体讲。

MySQL索引选择的不是原生的B-Tree，而是对他进行了改造，得到的是一种叫做B+Tree的数据结构。

B+Tree（B-Tree变种）

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

 （实际上叶子节点间的指针是双向的，图有问题）

 

        和B-Tree有啥区别？非叶子节点没有数据，数据都挪到叶子节点，叶子节点之间还有双向指针，非叶子节点之间跟原来一样没有指针。

        为啥data元素挪到叶子节点？非叶子节点只存储索引元素，叶子节点存储了一份完整表的所有行的索引字段，data元素是每个索引元素对应要查找的行记录的位置或行数据，这样非叶子节点的每个节点就可以存储更多的索引元素（等会会有一个大致的估算）。实际上非叶子节点存储的是一些冗余索引，看一下上图，15/20/49，选择的是整张表的哪些数据作为索引？选择的是处于中间位置的，因为它要用到B+Tree一些比大小去查找，B+Tree本质可以叫做多叉平衡树，单看B+Tree的某一小块他还是一个二叉树。

        还有一个特点，某一个节点的元素处于一个递增的顺序，会提取叶子节点的一些处于中间位置的数据作为冗余索引，查找的时候从根节点开始查找，先把根节点加载到内存里去，然后在内存里去比对。

 

        比如要查找索引为30的数据，先在根节点跟15去比较，大于15，然后小于56，然后从他俩中间的指针查找下一个节点把它load到内存，再在内存里去比对，大于15，大于20，然后小于49，就根据20和49之间的指针找到下一个节点，然后load到内存，去比对，不等于20下一个30，相等，OK了。

为什么把中间的元素提取出来做冗余元素，为的是查找效率更高。

        回到刚刚的问题，为啥要搞这些冗余索引，而且把这些冗余索引的data元素搞到叶子节点？也就是说B+Tree相对于B-Tree来说我的非叶子节点是不存储data元素的，叶子节点才存储data元素？

        你想一下，一个节点不能太大也不能太小，就是16K，把data元素挪走以后，是不是这个节点就能存更多的冗余索引了，意味着分叉就更多了，意味着叶子节点就能存储更多的数据了。

mysql为什么把节点大小设置为16K，而不是更大?

        假设索引字段类型是Bigint，8bit，每两个元素之间存的是下一个节点的地址，mysql分配的是6bit，也就是说一个索引后面配对一个节点地址，成对出现，可以算一下16K的节点可以存多少对也就是多少个索引，8b+6b=14b，16K /14b=1170个索引，叶子节点有索引有data元素，假设占1K，那一个节点就放16K/1K=16个元素，假设树高是3，所有节点都放满，能放多少数据？可以算一下，1170*1170*16=21902400，2千多万，mysql设置16K的大小，数据就可以存2千多万就已经足够了吧，既能保证一次磁盘IO不要Load太多的数据 又能保证一次load的性能，即便表的数据在几千万的数量也能保证树的高度在一个可控的范围。

        可以看一下几千万的数据表是不是加了索引几十毫秒几百毫秒就出结果了，所以就解释了几千万的表精确的使用索引后他的性能依旧比较高。

        树的高度只有3的情况下就能存储2千多万的数据，即便某一个索引在叶子节点，那也就2、3次磁盘IO就能查找到，当然很快了。而且mysql底层的索引他的根节点，是常驻内存的，直接就放到内存的，查找叶子节点，一个2千万的数据放到B+Tree上面，要查找叶子节点，就只需要2次磁盘IO就搞定了，在内存里比对的时间基本可以忽略。

MySQL是如何存储索引和数据的

索引和数据存放位置是哪？

首先问下mysql的表、数据、索引是放到那里的？

磁盘=》默认是安装目录的data文件里（不同版本可能有所不同），每个数据库对应data文件夹里的一个文件夹

我们打开一个walking_mybatis数据库看一下有一个user表，再打开对应的文件夹看一下，里面的文件名和表名有关系，然后有不同的后缀，这里面的不同的放法和mysql的存储引擎有关，和你选择的哪种存储引擎有关。

 

存储引擎是修饰什么的？

大家都知道，mysql常见的存储引擎有InnoDB存储引擎，MYISAM存储引擎，那存储引擎是形容mysql数据库的还是某一张表的？

是表，尽管数据库级别也有存储引擎选项，但最终还是以表的存储引擎为主的。

如果你用Navicat工具去建表，也许你最多就用了“字段”这一栏去增加字段，你可以点一下“选项”看一下，可以选择存储引擎。