Mysql 索引底层数据结构，到底是二叉树、红黑树还是B-Tree、还是B+Tree呢？

目录

前言

Pre1 索引的数据结构选型

二叉树 ?

红黑树 ?

B-Tree ？

B+Tree

Hash表

Pre2 MySQL不同存储引擎下索引的实现

一、MyISAM索引实现

二、InnoDB索引实现

Final：常见面试题

1、为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

2、为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)

---

前言

什么是索引？

通俗的说就是为了提高查询效率专门设计的一种排好序的数据结构。

上边是数据，假设有个sql

select * from col2 where id = "5"

如果没有索引的话，需要进行全表扫描，走磁盘IO…

加上一个合适的索引，比如二叉树

---

Pre1 索引的数据结构选型

二叉树 ?

在线演示地址：

Binary Search Tree Visualization

Mysql 一般有主键自增、id之类的字段，演示二叉树存储自增数据时：

 当进行查询时，

select * from t where id = 7

 

自增主键时，二叉树已经变为一个链表了。。。

想下，如果百万条数据，一个一个的进行对比，可想IO效率而知，（数据保存在磁盘中，从磁盘中捞呀！！）。

二叉树 pass，试试带有平衡功能的树。二叉平衡树（红黑树）

红黑树 ?

这样就不是一个链表了。

select * from t where id = 7

 

三次磁盘I/O即可找到， 比刚才二叉树的七次是少了些哈 ，自然查找效率也比二叉树高了

但是问题来了，如果百万条数据，那么这棵树异常庞大。

数据量大， 树高问题 - pass

那既然树高不好， 是不是如果可以控制树的高度（比如 3 到4层的高度，这样查询起来还能接受），让每一层能存储更多的数据，然后再分裂，这样的话数据量相乘起来，也是不少了对吧，这样就能存储更多的数据，这样会不会好一点？ ----> B-Tree

B-Tree ？

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

 

叶子节点之间的没有指针，区别于B+树。

data存储的是数据对应的磁盘地址， k-v结构。

我们来看下B-Tree的插入 （Max.Degree 设置为3 即 元素到了3个就分裂 ）

 查找一下

 

3次

MySQL也没有使用B-Tree , 因为

除了存储索引以外，还存储了data(数据对应的磁盘地址) ， 为了更多的存储数据，MySQL对B-Tree进行了很多改造

由此演进出了 B+Tree ，将data部分仅保留在叶子节点上，这样的话同等的页可以存储更多而索引数据。

B+Tree

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

 

数据仅存储在叶子节点， data可能是磁盘地址也可能是其他的列数据，这个和存储引擎有关系。

叶子节点之间有指针相连。

我们来算下 3层高的B+Tree能存储多少数据结构

假设是BigInt类型的数据

 

BigInt 占 8个字节 ，同时还是用6个字节存储了它指向的数据的物理地址

MySQL在使用innodb引擎的时候页大小默认是16K ,查询如下

mysql> SHOW GLOBAL STATUS like 'Innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

mysql>

 假设 树高为3 ， 这样的话，第一层即可以存储 16KB * 1024 / (8B + 6B) = 1170

同样的第二层也是1170 （第二层不是叶子结点，不存储数据）

第三层，存储数据，一般情况下一行数据的大小肯定不会超过1KB，那我们就按照1KB算吧

3层高的B+Tree , 存储BitInt可以存储 1170 * 1170 * 16 = 2千1 百万。。。。这效率还是可以的哈

想一想 如果是4层高的数 1170 * 1170 * 1170 * 16 = 250多亿数据。.。。。

当然了 都是估算， 如果换成其他类型的数据，每个表的行数据的大小都是相关的，这也就是我们通常说的 MySQL的表到千万级别就要分库分表的理论依据了。

我们看下B+Tree的插入和查找

 

 

Hash表

• 对索引的key进行一次hash计算就可以定位出数据存储的位置
• 很多时候Hash索引要比B+ 树索引更高效
• 仅能满足 “=”，“IN”，不支持范围查询
• hash冲突问题
  

对索引字段进行hash以后， 还存储了数据对应的磁盘地址。

一般情况下，hash 比 b+tree的效率要高 ，但工作中绝大部分还是使用的B+Tree , 因为hash对范围查找不是很友好，还要全表扫描。

为啥B+Tree 支持范围查找？

我们知道B+Tree的叶子节点 有指针相连，从根节点找到对应的叶子节点后， 加上节点本身就是排好序的，所以范围查找就恨轻松了。

B-Tree 没有指针相连，所以要想范围查找，还得从根节点重新找，效率肯定比B+树低 。

---

Pre2 MySQL不同存储引擎下索引的实现

主要讨论InnoDB和MyISAM两个存储引擎的索引实现方式。

一、MyISAM索引实现

非聚簇（非聚集）索引

我们建立一个myIsam存储引擎的表，看磁盘上的文件存储如下

 

我这个是8.0的MYSQL， 5.7版本 不是sdi结尾的文件，而是frm (framework)

可以看到MyISAM存储引擎的索引文件 MYI 和数据文件 MYD 是分离的(非聚集)

这就是非聚簇索引的含义， MYI 和 MYD 分开存储 ，同样的 InnoDB都存在.idb文件中，所以InnoDB存储引擎的索引就是聚簇索引。

索引原理图

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

 

上图就是 MyISAM索引的原理图。

上图一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。

 因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，去另外一个文件中MYD读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

二、InnoDB索引实现

聚簇（聚集）索引

建立一个innodb存储引擎的表，看磁盘上的数据文件如下

 这个ibd就是 数据和索引，这两个存储在一个文件中。

第一个重大区别是InnoDB的数据文件本身就是索引文件 ，因为就只有一个ibd文件啊。

• MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

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

InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM 不同。

索引原理图

上图就是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。

---

 第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域

上图为 定义在Col3上的一个辅助索引 观察叶子节点 ： data域存储相应记录主键的值而不是地址

Col3字段上的索引，以英文字符的ASCII码作为比较准则。

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

---

Final：常见面试题

1、为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

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

至于是整型，主要是构建B+Tree的时候，从左到右递增的属性，你如果用过UUID，不仅占用空间，还要转换成assic码进行比较，效率自然不行。

2、为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)

知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大，占用空间。

再比如用非单调（可重复）的字段作为主键在InnoDB中是不推荐的，因为InnoDB数据文件本身是一颗B+Tree，可重复的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，所以推荐使用自增主键。
 

---

这是我看过的最清晰易懂的文章了，赶紧记录保存一下。