浅析Mysql索引Innodb及底层存储结构B+树

前言

最近博主在学习mysql索引相关知识，看了很多博客，公开课然后自己总结一下，最近的收获吧。本文从二叉树->平衡二叉树->B树（有同学读B减树，是不正确的） ->B+树 再到B+树在Myisam和Innodb中的体现形式，会提到索引失效的情况，以及创建索引时的一些注意事项及其原理。采用大量的图片+部分文字更加清晰的描述。其中部分图片来源于咕泡学院公开课。

准备

提到mysql索引的不由想到了mysql的优化，开头先来点mysql优化硬菜：数据库索引58军规

分享一个国外的树状结构运行过程（添加，删除）结构变化模型仿真器：树结构模拟器 使用如下：

下面开始上车：

数据结构

二叉树

要了解tree结构还是从二叉树谈起吧，二叉树是一个最简单的树状存储结构，其结构如下：

二叉树结构模拟器 连接

特点：

1）若左子树不空，则左子树上所有结点的值均小于它的根节点的值；

2）若右子树不空，则右子树上所有结点的值均大于它的根结点的值

3）左、右子树也分别为二叉排序树

4）没有键值相等的节点（？可能是因为不好处理键值相等的节点到底是左节点还是右节点吧）

总结：

   这是所有树形结构的基础，简单的二叉树容易导致失去平衡，高度差太大，甚至成为链装结构，搜索速度不稳定，可能要全部扫描才可以搜索到，不能满足mysql存储引擎要求的稳定的搜索速度，所以不适合作为数据库存储引擎。

平衡二叉树

高度差最多为1      结构动画模拟器 （你点我试试）

 

磁盘块：

由于索引都比较大，内存一般是存不下的，例如mysql 一个库的索引就可以达到几十上百G，内存这么昂贵的玩意是吃不消的，持久化也是问题。因此，都是存储在硬盘上的，而OS读取磁盘是以磁盘块为单位的，一个磁盘块可以存储一页的数据（1024*1024b=1k），磁盘块能存储节点个数=1K/（每个节点索占用的空间+子节点信息所占空间），另外还有OS系统读取还才用预读取，这个不在这里细讲。

特点：

在二叉树基础上引入了平衡二叉树，每个节点最多只能用两个子节点，各个叶子节点高度差最多为1，这样解决了因分布不均匀导致树失衡，查找效率不稳定。搜索原理：如图，采用磁盘块形式记录（存储）每个节点，每个节点记录左子节点，和右子节点的值和位置，当查询或者插入时候命中相应节点后再加载磁盘中对应子节点数据到内存中（tree的数据是存在硬盘中的），进行比较命中后依次进行查找。总结：谈到这里也许你会觉得这就可以了啊，搜索也平衡了，那mysql为啥不采用在这样的存储引擎呢？试想一下mysql的索引可能有几个G，对应的一张表就可能用近千万条数据，如果采用这样的结构树的高度依然很高很高，并且，每个节点独占一个磁盘块，造成严重资源浪费，需要OS不断的读取硬盘，效率比较低，所以不适合作为mysql的索引。

B-Tree

多路，节点个数=路数-1，节点差为0（图示为三路） 结构动画模拟器

特点：

每个磁盘块中存储的节点个数=路数-1，节点差为0，如图，拿三路的来举例：

数据存储在各个节点上，小于最左侧的节点，就找对应的左子节点，大于右边的找右子节点，介于之间的找中间节点，类似于区间化对比搜索。插入或删除的时候通过旋转节点来达到绝对平衡。上面提到了磁盘块的概念，在这里可以通过增加多路来充分利用每个磁盘块的空间，而需要多少路的概念也是由于创建的索引大小来确定的，如果创建了很多索引每个节点就需要更多的内存空间，导致每个磁盘块能存储的节点数量减少，索引要求在创建索引时候一定不要创建无用的索引，这样可以有更多的路可以实现，以此来降低树的高度，提高搜索效率

不足：每次为了维护绝对平衡需要做节点的旋转，维护节点相对耗时点。

总结：

         这保证了效率的近似稳定，通过充分利用磁盘块的空间，减少IO，提高效率，最快的查找就是命中就直接返回，最慢就是搜索的深度为树的高度，看到这里是不是觉得这个树很完美了啊，但是！！！，由于数据存储在各个节点上，导致需要在一定范围内遍历时候需要扫描很多节点，例如查找  id >3 and id <15, 需要很多次遍历。

B+Tree

数据存储在叶子节点   结构动画模拟器    

特点：

数据存储在叶子节点，其他节点只存储索引。叶子节点有序，遍历方便，不存储下一个节点磁盘位置信息，节约空间，能有更多的路，降低高度，由于数据都在叶子节点所以搜索次数恒定速度稳定

总结：

由于数据只在叶子节点存储，非叶子节点只存储索引信息，存储单个节点的信息少，这样一个磁盘块就可以存储更多的非叶子节点，从而再次降低树的高度，降低树的高度就意味着搜索深度降低了，对索引是个优化。B+tree还有一个出色的亮点，就是所有叶子节点是有序的且有索引指向，这样再搜索一个范围的值得时候只需要找到两个端点然后把中间数据全部返回即可。相比于B-tree，每次搜索遍历的深度都是树的高度，速度稳定了，但是没有B tree可能出现的遍历一两次命中就可以返回的情况，所以没有完美的tree，只有更合适的tree。

mysql存储引擎

mysql5.5之前都采用myisan作为默认引擎，5.6开始采用Innodb

B+Tree在Myisam中的体现形式

特点：

Myisam中B+Tree 的叶子节点存储数据库中对应数据的地址，通过地址索引形式，在命中后查找对应地址存放的数据并返回；  主键索引与辅助索引为同级别索引； 

 

特点 ：

Innodb中B+Tree，主键索引叶子节点存储的不再是数据对应的存储位置，而是数据本身；辅助索引叶子节点存储的是数据对应的主键索引，命中后拿到对应的主键索值再去主键索引中搜索到具体的值返回。

PK：

 

总结：

由于Innodb 中主键索引下直接存放的是数据，无需再通过磁盘地址查找，速度会更快，以主键索引为主。在索引变化是只需要更改主键索引，和辅助索引中对应的主键索引值，维护量相比于Myisam中同时维护所有索引，效率更高。

索引的选择（离散性）

选择离散性大的增加搜索效率

最左匹配原则

 

联合索引

 

覆盖索引

 

总结：

在B+中，节点命中就可以直接返回，无需等扫描到最后一行才返回，这就是覆盖索引；

 

博主第一次写博客哈，写的不好还希望可以给多多指导，相互学习！！！