Mysql Innodb B+树索引

1、前言：

Mysql 有9种存储引擎，可以通过show engines进行查看，如下图（演示版本为5.6.40）；可以看到InnoDB作为默认存储引擎（支持事务、行级别锁定、支持外键）；

2、InnoDB引擎的特点：

（1）事务类数据表的首选引擎，支持事务安全表，支持行级别锁定和外键，从MySQL-5.5版本开始的默认引擎；

（2）具有提交、回滚和崩溃恢复能力的事务安全存储引擎，能处理巨大数据量，性能及效率高；

（3）具有非常高效的缓存特性，既能缓存索引，也能缓存数据，对硬件要求比较高；

 

3、MySQL官方对索引的定义为：

索引（Index）是帮助MySQL高效获取数据的数据结构。

4、B+树的概念：

B+树是一种树数据结构，通常用于数据库和操作系统的文件系统中。B+树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+树元素自底向上插入，这与二叉树恰好相反。

一个m阶的B+树具有如下几个特征：

1).根结点至少有两个子女。

2).每个中间节点都至少包含ceil(m / 2)个孩子，最多有m个孩子。

3).每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m。

4).所有的叶子结点都位于同一层，所有的数据都存储在叶子节点。

下图是一个3阶的B+树，可以通过以下网址尝试：https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

 

 

那么一棵树可以存储多少数据呢？

在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是512字节，而文件系统（例如XFS/EXT4）的最小单元是块，一个块的大小是4k，而对于我们的InnoDB存储引擎也有自己的最小储存单元——页（Page），一个页的大小是16K。

 

数据库表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢？假设一行数据的大小是1k，那么一个页可以存放16行这样的数据。

这里我们先假设B+树高为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数*单个叶子节点记录行数。上文我们已经说明单个叶子节点（页）中的记录数=16K/1K=16。（这里假设一行记录的数据大小为1k）。那么现在我们需要计算出非叶子节点能存放多少指针，我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。

那么可以算出一棵高度为2的B+树，能存放1170*16=18720条这样的数据记录。

同理可以算出一个高度为3的B+树可以存放：1170*1170*16=21902400条这样的记录。

所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

 

5、InnoDB索引实现

InnoDB使用B+Tree作为索引结构。

InnoDB的数据文件本身就是索引文件。在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

聚集索引：

索引与数据存储在一起叫聚集索引，如上图中叶节点包含了主键key和数据记录。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

辅助索引（非聚集索引）：

辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域（如下图所示）。

 

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引（即所谓的回表）：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

 

 

----------------------------------------------------------------------------------------------------------------------

一道面试题：

为什么MySQL的索引要使用B+树而不是其它数据结构?比如B树？

 

我们可以这样回答：

因为在B树中不管叶子节点还是非叶子节点，都会保存数据，这样导致非叶子节点保存的指针数量变少，指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低。

而B+树的数据都保存在叶子节点，这样在同样数据量的情况下就降低了树的高度，减少了IO操作次数，从而提升了性能。

 

参考链接：

https://blog.csdn.net/weixin_45563049/article/details/104914021

https://www.cnblogs.com/leefreeman/p/8315844.html

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