mysql 索引的原理揭秘

索引就是通过通过最少的IO次数，从磁盘中检索到目标数据！所以索引的实现本质上就是一个查找算法。

那么，我们需要找到一个最合适的数据结构来实现快速查找功能，最基本的

查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数

据量很大时显然是不合适的，除了顺序查找，在数据结构中还有很多选择，

比如：

1. 二叉查找树

　　不平衡 在特定场景下可以变成链表。

2. 平衡二叉查找树

　　通过 自旋  可以稳定生成一个平衡二叉树。但是路数（树的分支） 为2。这样的树就很高。查找深度也就很高。速度也就很慢。

3. 红黑树

多路平衡查找树（B Tree）

B Tree有一个特点：分叉数（路数）永远比关键字数多1。比如我们画的这

棵树，每个节点存储两个关键字，那么就会有三个指针指向三个子节点（当

然肯定不只存3个这么少）！

分叉数越多，树的深度就会更少，这样一来，原本高高瘦瘦的二叉树就变成

了矮矮胖胖的样子，如下图所示。

 

 节点的排列规则需要满足平衡二叉树的基本规则（左子节点小于父节点，右

子节点大于父节点）

我们上面描述的B Tree中的节点，在Mysql中代表的是Page页。

前面我们说过，Mysql为了更好的利用磁盘的预读能力，把一个Page页的大

小设置为16k，也就是一个节点（磁盘块）的大小是16K。也就是说在进行一

次磁盘IO时，会把一个节点（16K）的索引加载到内存中。

B树 特性：

1.B树每个节点都保存了对应的数据。

2.分叉数（路数）永远比关键字数多1

3. 通过指针指向下一个叶子节点。

4. 查找时，查到了就直接返回。不管是根节点还是子节点。

2. B树优点：

相比二叉树。 B树 是多路查找树。保存的数据会更多。

B树缺点：

B Tree在做检索时，检索效率非常高，但是在做数据插入和删除时，会破坏

B Three本身的行知，所以为了保持B Tree的平衡，需要对节点进行分裂、

合并、转移等操作，而这个操作在节点数量较多的情况下性能影响较大。

所以这也是为什么我们在做索引的创建和索引更改时，性能较慢的原因！

B+Tree(加强版多路平衡查找

树)

B+Tree(加强版多路平衡查找树)

在Mysql的InnoDB引擎中，并没有使用B Tree作为索引的存储结构，而是使

用B+Tree！它的存储结构如下图所示。

 

 

B+ Tree相比B Tree，做了以下几个方面的优化：

1. B树的路数和关键字的个数的关系不再成立了，数据检索规则采用的是

左闭合区间，路数和关键个数关系为1比1

2. B+Tree的根节点和枝节点中都不会存储数据，只有叶子节点才存储数

据，并且每个叶子节点都会增加一个指针指向响铃的叶子节点，形成一

个有序链表结构。

B TREE和B+TREE区别是什么？

1. B+Tree 关键字的搜索采用的是左闭合区间，之所以采用左闭合区间是

因为他要最好的去支持自增id，这也是mysql的设计初衷。即，如果id =

1命中，会继续往下查找，直到找到叶子节点中的1。

2. B+Tree 根节点和支节点没有数据区，关键字对应的数据只保存在叶子

节点中。即只有叶子节点中的关键字数据区才会保存真正的数据内容或

者是内容的地址。而在B树种，如果根节点命中，则会直接返回数据。

3. 在B+Tree中，叶子节点不会去保存子节点的引用。

4. B+Tree叶子节点是顺序排列的，并且相邻的节点具有顺序引用的关系，

如上图中叶子节点之间有指针相连接

 

 

MySQL为什么最终要去选择B+Tree？

总结一下，InnoDB中的B+Tree特性带来的优势：

1. 它是B Tree的变种，B Tree能解决的问题，它都能解决。B Tree解决的

两大问题是什么？（每个节点存储更多关键字；路数更多）

2. 扫库、扫表能力更强（如果我们要对表进行全表扫描，只需要遍历叶子

节点就可以了，不需要遍历整棵B+Tree拿到所有的数据）

3. B+Tree的磁盘读写能力相对于B Tree来说更强（根节点和枝节点不保存

数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字

更多）

4. 排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链

表）

5. 效率更加稳定（B+Tree永远是在叶子节点拿到数据，所以IO次数是稳定

的）

B+Tree作为索引的数据结构带来的好处

由于在B+ Tree中，每个节点不存存储数据区，只需要存储键值+指针，使得

B+ Tree在每个节点存储的路数更多。

假设索引字段+指针大小一共是16个字节，那么一个Page页（一个节点）能

存储1000个这样的单元（键值+指针）。

假设一条记录是16bytes，一个叶子节点（一页）可以存储10条记录！

当数的深度是2的时候，就有1000^2个叶子节点，可存储的数据为1000 x

1000 x 10 =10000000（千万）

 

 

 

MyISAM引擎的索引实现

在MyISAM里面，另外有两个文件：

一个是.MYD文件，D代表Data，是MyISAM的数据文件，存放数据记录，比

如我们的user_myisam表的所有的表数据。

一个是.MYI文件，I代表Index，是MyISAM的索引文件，存放索引，比如我

们在id字段上面创建了一个主键索引，那么主键索引就是在这个索引文件里

面。一个索引就会有一棵B+Tree，所有的B+Tree都在这个MYI文件里面。。

也就是说，在MyISAM里面，索引和数据是两个独立的文件。

那我们怎么根据索引找到数据呢？实现机制如下图所示。

 

 

 

从MyISAM引擎中索引的实现来看，由于索引文件和数据文件是分离的，叶

子节点存储的是数据文件对应的磁盘地址，从索引文件.MYI中找到键值后，

会到数据文件.MYD中获取相应的数据记录。

在MyISAM引擎中，主键索引和辅助索引在结构上没有任何区别，只是主键

索引要求key是唯一的，而辅助索引的key允许重复！ 

InnoDB的索引实现

在InnoDB中，只有一个ibd文件，里面包含索引和数据。

同时，在B + Tree中的叶子节点存储了索引对应的数据行，所以我们称

InnoDB中索引即数据、数据即索引，它的整体结构如下图所示。

 

 

聚簇索引和非聚簇索引

在一个表中，我们可以建立很多中索引，如唯一索引、主键索引、辅助索引

等等，那如果是一个表中存在多个索引的情况下？

数据表应该保存到哪个索引的叶子节点呢？ 还是说每个索引的叶子节点都保

存一份？

很显然，即便是让我们自己设计，也不可能在每种索引上都冗余一份数据，

因为这会带来额外的存储空间浪费和计算消耗，所以在InnDB中，引入了聚

集索引（聚簇索引）和非聚集索引的概念。 

聚簇索引 

所谓的聚簇索引，就是只索引键值的逻辑顺序和表数据行的物理存储顺序一

致。只有聚簇索引才会在叶子节点缓存表中的数据。 

 

 

在InnoDB中，组织数据的方式就是用聚簇索引组织表（Clustered Index

Organize Table），所以一张表创建了主键索引，那么这个主键索引就是聚

集索引。

 

非聚簇索引

除了主键索引以外，其他索引均属于非聚簇索引，非聚簇索引的叶子节点不

会存储表数据，那么在这种情况下。

如果要查询一个非聚簇索引的字段，怎么去获取到数据的值呢？我们来看一

下非聚簇索引的存储结构。

、

 

 

从上面这个图可以看到，真正的数据仍然是保存到主键索引的叶子节点（这

也就是为什么InnoDB表必须要有主键的原因），而辅助索引的叶子节点的

数据区保存的是主键索引的关键字的值（非主键索引叶子节点的逻辑顺序和

磁盘顺序不一致）。

当我们要查询where name = '王五'时，先通过二级索引去B + Tree中找到

王五的叶子节点，拿到对应的主键值，也就是id=15。

接着在根据这个条件去主键索引中查找到叶子节点拿到数据（这里不能存储

磁盘地址，因为在数据insert和delete时，B+Tree的结构会发生变化）。

所以，从这个角度来说，因为主键索引比二级索引少扫描了一棵B+Tree（这

个动作叫做回表），它的速度相对会快一些