深入理解mysql 底层索引数据结构

 索引的定义

        官网定义：索引是帮助MySQL高效获取数据的数据结构。说明其本质也是一种数据结构。（个人理解：可以把索引看成一本书的目录。把书中的内容按照某种方式排列，方便查找书中内容）注意，索引本身就是按某种方式（数据结构）排好序的

        为什么要建立索引？

首先理解一下为什么需要索引：mysql存储数据最终是存放再磁盘中，写入时间不一样，在磁盘中内存地址也各不相同，可能两条连着的数据，最后存放的位置相差特别远。正常去查询的时候，需要去磁盘中找出该数据，没有顺序，所以会一次次做磁盘IO，一般而言内存访问的时间约为 50 ns，而磁盘在 10 ms 左右。速度相差了近 5 个数量级(mysql很多优化就是要尽量减少磁盘的IO次数) ，效率极低。如何解决？

               1.有没有可能将磁盘顺序存储？（基本没可能，mysql不止一张表，各个表数据增删改都会影响顺序问题）

                2.加一个中间层（书的目录），把存放的数据排列好，再查询（索引）

索引既然要用来排列数据，那么可以思考下一本书如何去排列书中的内容？

索引的数据结构

        mysql InnerDB支持的数据结构有：

                        二叉树，红黑树，hash表，B树

如果我们现在有一张表，有一些数据如图：

        最左边是磁盘地址，后面是表的两个字段，和一些数据，我们要对它进行排列

        二叉树

             这些数据如果我们从中间断开，把2作为一个树的根，那么可以分为两半，一边是1一边是3。查找的时候从2开始查询一次IO就可以找到1或者3,比没有加索引情况减少了一次IO。但是这个大家很快能发现问题。如果我加一个4或者5，6等等，结构如何呢？如图：

        

可以看到这个树结构如果除掉1,就和线性结构一样，那么查询会快么？

而且现在的数据没有重复的内容，如果有两个2两个3又如何排列？

            红黑树（二叉平衡树）

Data Structure Visualization (usfca.edu)https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

这个网址可以模拟各种数据结构，可自行模拟

 当单边的节点大于3，那么自动调整结构,这样可以解决二叉树的弊端，如上图

                 红黑树查询从根节点出发最多三次就能查找到6的数据，相毕竟线性和二叉树，效率高很多，可是也有问题：如果数据更多，那么下面节点也就越多，树也就越高，查询的IO也更多,效率也不会高。

        

        BTree

        这里的 B 表示 balance( 平衡的意思),B-树是一种多路自平衡的搜索树（B树是一颗多路平衡查找树）
它类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点

如图，B树结构一般不会超过3层（减少IO,一层就会有一次IO），添加节点时，会根据数据内容，判断添加位置如：

        再加一个10，从根节点判断大于4 ，大于8，再大于9，直接放到9得后面成为一组

        现在加一个14又怎么样呢？

 可以看到，当它将14放入10后面得时候，超过了树得高度，则将8放入了根节点，10作为了9和14得中间节点，提到了第二层节点，再将9和14用二分的方式存放

        如果现在在里面再插入一个11结果会如何呢？

         就不过多赘述了，自行脑补如何存放

总结一下BTree:

         可以看到B树的高度不高，相当于磁盘IO不会特别多，入查找11，从根节点出发，11>8 在8的右边再找  11>10  在10的右边再找，第三次就能找到11了。所以B树的效率还是蛮高了

  B树特点

1. 所有键值分布在整颗树中（索引值和具体data都在每个节点里）；
2. 任何一个关键字出现且只出现在一个结点中；
3. 搜索有可能在非叶子结点结束（最好情况O(1)就能找到数据）；
4. 在关键字全集内做一次查找,性能逼近二分查找；

 从特点中可以看出，B树中每条索引的值都在树节点中，想象一下，如果索引字段内容特别大，那么查询时间可能不一定，有的快，有的慢。还有一个问题，索引也是需要占内存空间的，当一个索引里面放太多的内容，是不是会导致索引更少呢？比如我给书籍的目录就两页，这本书也很大，如果我们在目录中把标题内容写得很长，或者直接把一部分内容放到目录中，那可想而知这个目录也不会太好用。

        B+Tree（B树变种）

其实可以看出，B树已经能很好得解决我们大部分问题了，只要解决了索引内容不放太多，是不是基本就解决了我们得索引问题？B+树就是我们mysql中使用得索引结构了

        特点：

                 1. B+树内节点不存储数据 ，只做索引作用    

                 2. 叶子节点包含所有索引字段

                 3. 叶子节点中用指针相互连接

                 4. 查找过程中，B树在找到具体的数值以后就结束，而B+树则需要通过索引找到叶子结点中的数据才结束

可以看到因为树结构中间节点中只存放索引，没有存放其他得数据值，从而保证能够存放更多得索引字段

        常见面试问题：

                        1. InnoDB一棵B+树可以存放多少行数据？

                        答：约2千万行

• 在计算机中，磁盘存储数据最小单元是扇区，一个扇区的大小是512字节。
• 文件系统中，最小单位是块，一个块大小就是4k;
• InnoDB存储引擎最小储存单元是页，一页大小就是16k。

假设B+树的高度为2的话，即有一个根结点和若干个叶子结点。这棵B+树的存放总记录数为=根结点指针数*单个叶子节点记录行数。

• 如果一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16.
• 非叶子节点内存放多少指针呢?我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，所以就是8+6=14字节，16k/14B =16*1024B/14B = 1170

因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400，也就是说，可以存放两千万左右的记录。B+树高度一般为1-3层，已经满足千万级别的数据存储。

注意：这个是个预估值，根据实际存储内容变动》如果超过两千多万怎么办？？？   分表

                       2. 为什么索引结构默认使用B+树，而不是hash，二叉树，红黑树，B树??

        其实前面已经总结了（hash在后面，比较独特）

                 Hash哈希，只适合等值查询，不适合范围查询

                 一般二叉树，可能会特殊化为一个链表，相当于全表扫描

                红黑树，MySQL 数据量很大的时候，索引的体积也会很大，内存放不下的而从磁盘读取，树的层次太高的话，读取磁盘的次数就多了

                B-Tree，叶子节点和非叶子节点都保存数据，相同的数据量，B+树更矮壮，也是就说，相同的数据量，B+树数据结构，查询磁盘的次数会更少

                            

                       3. B-树和B+树的区别 ?

                        其实就是B树特点

                 1. B+树内节点不存储数据 ，只做索引作用    

                 2. 叶子节点包含所有索引字段

                 3. 叶子节点中用指针相互连接

                 4. 查找过程中，B树在找到具体的数值以后就结束，而B+树则需要通过索引找到叶子结点中的数据才结束

                 5. B树中任何一个关键字出现且只出现在一个结点中，而B+树可以出现多次。

        HASH索引

它的数据结构相对特殊，和树没关系:

hash对索引的key进行一次hash计算就可以定位出数据存储的位置

 

 很多时候hash的效率比B树更高，因为他只需要找到对应hash值得数据，一次IO搞定。但是他也有很多局限性：

        1.仅能满足“=”，“IN”，不支持范围查询

        2.hash值冲突问题

        

索引的优缺点

                优点

        1. 索引大大减小了磁盘IO扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因

        2. 索引可以帮助服务器避免排序和创建临时表(mysql查询如果没有索引其实也会自己加一个临时表来排序)

        3. 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性

        。。。。主要就是加快查询效率

                缺点

        1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加（要维护B+树）

        2. 对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度

        3. 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果

        4. 如果表数据不大，加索引可能还没有全表扫描快速

索引方便我们的查询，加快我们的效率，但是也不能滥用，根据情况决定使用，加不加，在哪儿加，或者什么时候加，都要视情况而定，后续我还会把数据库索引的使用，优化等等更新出来，共同学习共同进步

                                                                

一个想过得更好的码农---邋遢道人