MySQL索引-B+树(讲得通透)_mysql索引b+树

    该文章摘自“安静的boy”发表在微信公众号“Hollis”上的原创文章：再有人问你为什么MySQL用B+树做索引，就把这篇文章发给她

    在这里我也做了一些个人的总结和补充，希望大家一起共同努力、共同进步，加油！

目录

二叉查找树

平衡二叉树

B树

B+树

聚集索引 VS 非聚集索引

利用聚集索引和非聚集索引查找数据

利用非聚集索引查找数据

总结

个人总结

---

索引这个词，相信大多数人已经相当熟悉了，很多人都知道MySQL的索引主要以B+树为主，但是要问到为什么用B+树，恐怕很少有人能把前因后果讲述的很完整。本文就来从头到尾介绍下数据库的索引。

索引是一种数据结构，用于帮助我们在大量数据中快速定位到我们想要查找的数据。 索引最形象的比喻就是图书的目录了。注意这里的大量，数据量大了索引才显得有意义，如果我想要在[1,2,3,4]中找到4这个数据，直接对全数据检索也很快，没有必要费力气建索引再去查找。索引在mysql数据库中分三类： 

B+树索引、Hash索引、全文索引

我们今天要介绍的是工作开发中最常接触到innodb存储引擎中的的B+树索引。

要介绍B+树索引，就不得不提二叉查找树，平衡二叉树和B树这三种数据结构。B+树就是从他们仨演化来的。

二叉查找树

首先，让我们先看一张图

从图中可以看到，我们为user表（用户信息表）建立了一个二叉查找树的索引。图中的圆为二叉查找树的节点，节点中存储了键(key)和数据(data)。

键对应user表中的id，数据对应user表中的行数据。二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。 顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点。 

如果我们需要查找id=12的用户信息，利用我们创建的二叉查找树索引，查找流程如下： 

• 1. 将根节点作为当前节点，把12与当前节点的键值10比较，12大于10，接下来我们把当前节点>的右子节点作为当前节点。 

• 2. 继续把12和当前节点的键值13比较，发现12小于13，把当前节点的左子节点作为当前节点。 

• 3. 把12和当前节点的键值12对比，12等于12，满足条件，我们从当前节点中取出data，即id=12,name=xm。

利用二叉查找树我们只需要3次即可找到匹配的数据。如果在表中一条条的查找的话，我们需要6次才能找到。

平衡二叉树

上面我们讲解了利用二叉查找树可以快速的找到数据。但是，如果上面的二叉查找树是这样的构造：

这个时候可以看到我们的二叉查找树变成了一个链表。

如果我们需要查找id=17的用户信息，我们需要查找7次，也就相当于全表扫描了。 

导致这个现象的原因其实是二叉查找树变得不平衡了，也就是高度太高了，从而导致查找效率的不稳定。

为了解决这个问题，我们需要保证二叉查找树一直保持平衡，就需要用到平衡二叉树了。 

平衡二叉树又称AVL树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过1。 

下面是平衡二叉树和非平衡二叉树的对比：

由平衡二叉树的构造我们可以发现第一张图中的二叉树其实就是一棵平衡二叉树。

平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。具体的调整方式这里就不介绍了。

由于文章来自于转载，原文没有对平衡二叉树算法做介绍。这里做了相应补充。如有了解请点击链接查看《平衡二叉树详解》

平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

B树

因为内存的易失性。一般情况下，我们都会选择将user表中的数据和索引存储在磁盘这种外围设备中。

但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以，我们应当尽量减少从磁盘中读取数据的次数。 另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。 

如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。 

如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。

那说明什么？

说明每个磁盘块仅仅存储一个键值和数据！

那如果我们要存储海量的数据呢？

可以想象到二叉树的节点将会非常多，高度也会及其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低！

为了解决平衡二叉树的这个弊端，我们应该寻找一种单个节点可以存储多个键值和数据的平衡树。也就是我们接下来要说的B树。 

B树（Balance Tree）即为平衡树的意思，下图即是一颗B树。

图中的p节点为指向子节点的指针，二叉查找树和平衡二叉树其实也有，因为图的美观性，被省略了。- 图中的每个节点称为页，页就是我们上面说的磁盘块，在mysql中数据读取的基本单位都是页，所以我们这里叫做页更符合mysql中索引的底层数据结构。

从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。 

基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。 

假如我们要查找id=28的用户信息，那么我们在上图B树中查找的流程如下： 

• 1. 先找到根节点也就是页1，判断28在键值17和35之间，我们那么我们根据页1中的指针p2找到页3。 

• 2. 将28和页3中的键值相比较，28在26和30之间，我们根据页3中的指针p2找到页8。 

• 3. 将28和页8中的键值相比较，发现有匹配的键值28，键值28对应的用户信息为(28,bv)。

B+树

B+树是对B树的进一步优化。让我们先来看下B+树的结构图：

根据上图我们来看下B+树和B树有什么不同。 

1. B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。之所以这么做是因为在数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。另外，B+树的阶数是等于键值的数量的，如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO。 

2. 因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。  

有心的读者可能还发现上图B+树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。

其实上面的B树我们也可以对各个节点加上链表。其实这些不是它们之前的区别，是因为在mysql的innodb存储引擎中，索引就是这样存储的。也就是说上图中的B+树索引就是innodb中B+树索引真正的实现方式，准确的说应该是聚集索引（聚集索引和非聚集索引下面会讲到）。

通过上图可以看到，在innodb中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

MyISAM中的B+树索引实现与innodb中的略有不同。在MyISAM中，B+树索引的叶子节点并不存储数据，而是存储数据的文件地址。

聚集索引 VS 非聚集索引

在上节介绍B+树索引的时候，我们提到了图中的索引其实是聚集索引的实现方式。那什么是聚集索引呢？

在MySQL中，B+树索引按照存储方式的不同分为聚集索引和非聚集索引。

这里我们着重介绍innodb中的聚集索引和非聚集索引。

1. 聚集索引（聚簇索引）：以innodb作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的，而B+树的键值就是主键，在B+树的叶子节点中，存储了表中所有的数据。这种以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引。 

2. 非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。  
明白了聚集索引和非聚集索引的定义，我们应该明白这样一句话：数据即索引，索引即数据。

利用聚集索引和非聚集索引查找数据

前面我们讲解B+树索引的时候并没有去说怎么在B+树中进行数据的查找，主要就是因为还没有引出聚集索引和非聚集索引的概念。下面我们通过讲解如何通过聚集索引以及非聚集索引查找数据表中数据的方式介绍一下B+树索引查找数据方法。

利用聚集索引查找数据

还是这张B+树索引图，现在我们应该知道这就是聚集索引，表中的数据存储在其中。现在假设我们要查找id>=18并且id<40的用户数据。对应的sql语句为select * from user where id>=18 and id <40，其中id为主键。具体的查找过程如下：

• 1. 一般根节点都是常驻内存的，也就是说页1已经在内存中了，此时不需要到磁盘中读取数据，直接从内存中读取即可。

  从内存中读取到页1，要查找这个id>=18 and id <40或者范围值，我们首先需要找到id=18的键值。

  从页1中我们可以找到键值18，此时我们需要根据指针p2，定位到页3。

• 2. 要从页3中查找数据，我们就需要拿着p2指针去磁盘中进行读取页3。

  从磁盘中读取页3后将页3放入内存中，然后进行查找，我们可以找到键值18，然后再拿到页3中的指针p1，定位到页8。

• 3. 同样的页8页不在内存中，我们需要再去磁盘中将页8读取到内存中。

  将页8读取到内存中后。

  因为页中的数据是链表进行连接的，而且键值是按照顺序存放的，此时可以根据二分查找法定位到键值18。

  此时因为已经到数据页了，此时我们已经找到一条满足条件的数据了，就是键值18对应的数据。

  因为是范围查找，而且此时所有的数据又都存在叶子节点，并且是有序排列的，那么我们就可以对页8中的键值依次进行遍历查找并匹配满足条件的数据。

  我们可以一直找到键值为22的数据，然后页8中就没有数据了，此时我们需要拿着页8中的p指针去读取页9中的数据。

• 4. 因为页9不在内存中，就又会加载页9到内存中，并通过和页8中一样的方式进行数据的查找，直到将页12加载到内存中，发现41大于40，此时不满足条件。

  那么查找到此终止。

  最终我们找到满足条件的所有数据为：

  (18,kl),(19,kl),(22,hj),(24,io),(25,vg),(29,jk),(31,jk),(33,rt),(34,ty),(35,yu),(37,rt),(39,rt)。

  总共12条记录。

下面看下具体的查找流程图：

利用非聚集索引查找数据

读者看到这张图的时候可能会蒙，这是啥东西啊？怎么都是数字。

如果有这种感觉，请仔细看下图中红字的解释。什么？还看不懂？那我再来解释下吧。首先，这个非聚集索引表示的是用户幸运数字的索引（为什么是幸运数字？一时兴起想起来的:-)），此时表结构是这样的。

在叶子节点中，不在存储所有的数据了，存储的是键值和主键。

对于叶子节点中的x-y，比如1-1。左边的1表示的是索引的键值，右边的1表示的是主键值。如果我们要找到幸运数字为33的用户信息，对应的sql语句为select * from user where luckNum=33。

查找的流程跟聚集索引一样，这里就不详细介绍了。我们最终会找到主键值47，找到主键后我们需要再到聚集索引中查找具体对应的数据信息，此时又回到了聚集索引的查找流程。  

下面看下具体的查找流程图：

在MyISAM中，聚集索引和非聚集索引的叶子节点都会存储数据的文件地址。

总结

本篇文从二叉查找树，详细说明了为什么mysql用B+树作为数据的索引，以及在innodb中数据库如何通过B+树索引来存储数据以及查找数据。我们一定要记住这句话：数据即索引，索引即数据。

该文章摘自“安静的boy”发表在微信公众号“Hollis”上的原创文章：再有人问你为什么MySQL用B+树做索引，就把这篇文章发给她

---

个人总结

1、为什么innodb和MyISAM引擎中不使用B树呢？

    个人认为B树的页中即存储了指针也存储了数据，对于innodb和MyISAM引擎来说，每一页的存储文件都有大小限制的，这就造成了每页存储的信息大小也是受到限制的。

     在数据存储过程中，数据所占空间大于指针所占空间的数量，所存储的指针数量较少。如果一张表想存储更多的数据，就必须增加页（存储文件）数，相当于增加节点的深度。在查询时遍历的次数增加和磁盘IO（打开存储文件）数据增加。

    选用B+树的原因在于数据放在叶子节点上，子节点上有更多的空间存储指针数据，会很大程度上减少树的深度，从而减少对树的遍历次数和对磁盘的IO。例如：一个深度为5每个节点都有指针和数据的B树对比一个深度为2一层节点为指针一层节点为数据的B+树，从查询的的复杂度上来讲哪个更快？

2、什么是聚集索引、非聚集索引和回表？

    聚集索引和非聚集索引从数据结构上讲都是由B+树实现的。

    简单来说，聚集索引可以理解成主键索引，非聚集索引可以理解成除主键索引外所有自建的索引。那么问题来了，聚集索引和非聚集索引都是由B+树实现的，那为什么主键索引为什么比其它索引的查询速度要快呢？这里就要引出回表这个问题了。

    什么是回表呢？首先说一下聚集索引和非聚集索引的区别。聚集索引叶子节点存储的是数据，非聚集索引的叶子节点存储是的主键ID。通过非聚集索引查询出数据的主键，然后在使用聚集索引查询出最终的数据，这也就解释了什么是回表和为什么主键索引会比其它索引的查询速度要快。

延伸

1、联合索引及索引最左匹配原则

    联合索引相比于多个单独的索引，在一定程度上减少索引的存储空间和减少在查询索引时对磁盘的IO。

    在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。所以在联合索引查询时应该按照建立索引时的顺序书写查询条件。

例：联合索引union_index(a,b,c)

select * from table where a=1 (使用索引)

select * from table where a=1 and b=1 (使用索引)

select * from table where a=1 and b=1 and c=1 (使用索引)

select * from table where c=1 and b=1 (索引失效)

如详细了解，可阅读知乎《面试前必须要掌握的MySQL索引最左前缀匹配原则》，原文链接：面试前必须要掌握的MySQL索引最左前缀匹配原则 - 知乎

2、字段和字段值上做计算和类型对索引的影响

    一、在索引字段上使用计算类函数，会将字段转换成临时字段，从而无法对应索引，致使索引失效。

    二、使用索引字段做查询条件时，‘=’左右两边值的类型保持一致，如果不一致什么导致索引字段类型转换变成临时字段。

    三、在使用like的问题上尽量使用右%（like 'abc%'），在索引匹配时mysql遵照即最左优先。

    四、索引字段不要使用NULL，因为NULL是一个特殊的值需要单独处理。单列索引不存储null值，复合索引不存储全为null的值。索引不能存储Null，所以对这列采用is null条件时，因为索引上根本没Null值，不能利用到索引，只能全表扫描。

3、为什么select字段往往使用索引字段比非索引字段要快

    一、select字段尽量不要使用'*',在使用*作为输入字段时，引擎在查询时会先从字典表中查找出所有字段然后在匹配输出。

    二、使用索引字段作为select输出字段速度快的主要原因在于，查询时引擎无需从磁上读取字段数据，直接从索引节点上返回数据。当然，如果列中存在非索引字段引擎不会从索引节点上返回数据，会从数据节点返回数据，这样索引列失效。

    三、非索引字段一定比索引字段要慢么？事实上，如果查询还需要拿别的字段(如sex)，那么光查询索引就不够了，必须扫表。注意！在这里 mysql 查询引擎就会对两种情况做判断： 1). 从索引索引拿到对应id=12的主键id，然后根据id去表中拿结果。 2).直接全表扫描。

    这里很多人有一个误区，认为1是好的，2是不好的。这是不对滴。全表扫描有时候会比先过索引在查表要快！

    先说走索引的情况，比如说满足dept_id=12的主键id有1w个，而且均匀分布在不同的page里，那么mysql需要一个page一个page的把结果读取出来(random io)。需要磁盘io 1w次。

    全表扫描的话，再比如全表有10w条记录，表文件为400m 大小(不少了吧)，那么因为是顺序读盘，一次最多读1m数据，那么只需要磁盘io 400次。

本节参考：

《mysql没有索引的字段_MySQL-mysql索引与select字段不是没关系吗？》

《MySQL在有索引列情况下select *的输出结果顺序》

4、为什么order by字段上使用索引字段更快

   B+树的索引都是按照顺序存储的，所以在对结果处理时无需做过多的排序操作。

5、多表关键查询时，为什么要小表驱动大表（小表在前，大表在后）

一、驱动表的定义
当进行多表连接查询时， [驱动表] 的定义为：

1）指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]

2）未指定联接条件时，行数少的表为[驱动表]（Important!）

忠告：如果你搞不清楚该让谁做驱动表、谁 join 谁，请让 MySQL 运行时自行判断

既然“未指定联接条件时，行数少的表为[驱动表]”了，而且你也对自己写出的复杂的 Nested Loop Join 不太有把握（如下面的实例所示），就别指定谁 left/right join 谁了，请交给 MySQL优化器 运行时决定吧。

如果您对自己特别有信心

二、mysql关联查询的概念:

MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。

例: user表10000条数据，class表20条数据

select * from user u left join class c u.userid=c.userid

这样则需要用user表循环10000次才能查询出来，而如果用class表驱动user表则只需要循环20次就能查询出来

例：

select * from class c left join user u c.userid=u.userid

本节摘自：程序人生dc 《了解MySQL联表查询中的驱动表，优化查询，以小表驱动大表》 原文链接：https://blog.csdn.net/dc2222333/article/details/78234649