带你了解什么是MySQL数据库（六）索引原理，如何建立与使用索引

前言

学习过某一门编程后相信我们对索引不会陌生，如Python内的列表通过索引取值，其目的也很简单，就是在众多数据中快速取到我们需要的内容。如果没有索引的话，我们只能一个一个值的去找，这无疑大幅度降低了效率。那么我们本章节来了解一下MySQL内的索引机制。

索引原理

---

介绍

什么是索引？

1. 索引在MySQL中又称为’键’，是存储引擎快速找到记录的一种数据结构、或者说数据的组织方式。
2. 建立索引会消耗空间和时间，但是好处是索引可以快速找到我们想要的内容。

抽象理解：

• 表 > 书
• 记录 > 每一页的内容
• 索引 > 目录（用于快速查询哪一页的内容）

为何要用索引？

主要为了优化查询效率
ps：创建完索引以后会降低增、删、改的效率，但是通常大部分时间都在查询，而增、删、改的使用频率较低。
如：我们浏览页面，注册使用次数很少，大部分时间都在网页查询我们想要的内容。

如何正确看待索引？

开发人员最了解业务逻辑，任何一个软件都有吸引用户的亮点
亮点背后对应的就是热数据，这一点开发人员是最清楚的
开发人员最了解热数据对应的数据库字段有哪些
所以应该在开发软件的过程中就提前为相应的字段加上索引，而不是等软件上线后
让DBA发现慢查询SQL后再做处理，因为：

1. 一个软件慢会影响用户体验，但是慢的原因有很多，你不能立即确定是SQL的问题，所以等定位到SQL问题后，问题已经被拖了很久了
2. 因为大多数DBA都是管理型DBA而非开发型，所以即便是DBA从日志中看到了慢查询SQL，也会因为其不同业务而很难分析出慢的原因，最后的背锅的还是开发人员。

索引到底是一种怎样的数据结构？

• 二叉树
• 平衡二叉树
• B树
• B+树

首先我们索引在MySQL中分为三类：

1. B+树索引
2. HASH索引
3. 全文索引

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

查找二叉树、平衡二叉树、B树、B+树

---

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

二叉树

我们首先来一张图

图中的圆为查找二叉树的节点，节点中存储了键(key)和数据(data)

既然称之为"树"，那么它就有层次结构：

• 顶端节点称为：“根节点”
• 中间节点称为：“子节点”
• 末尾节点称为：“叶子节点”

二叉树的特点：

• 任何节点的左子节点的键值都小于当前节点的键值
• 右边节点的键值都大于当前节点键值

举例理解：

再来看User表，如果按正常逻辑，我们需要找到id=12字段的记录，那么就需要从上向下依次查找，经历6次以后才能找到。但是分成二叉树结构后的查询顺序：

1. 把根节点当做当前节点，将12与当前节点键值对比，很明显，12大于10，那么就把当前节点右边的子节点作为当前节点
2. 将12与当前节点键值对比，很明显，12小于13，那么就把当前节点左边的子节点作为当前节点
3. 将12与当前节点键值对比，发现12等于12，满足查询条件，那我们就从当前节点取出data：12,xm

利用二叉查找树我们只需要3次即可找到匹配的数据。

平衡二叉树

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

如果我们想要查询id=17的用户信息，我们需要查找7次，也就相当于全表扫描了。导致这个现象的原因其实是二叉查找树变得不平衡了，也就是高度太高了，从而导致查找效率不稳定。为了解决这个问题，我们需要保证二叉查找树一直保持平衡，就需要用到平衡二叉树了。平衡二叉树又称：“AVL树”，在满足二叉查找树的特性基础上，要求每个节点的左右子树的高度不能超过1。下面是平衡二叉树和非平衡二叉树的对比：

由于平衡二叉树的构造我们可以发现第一张图中的二叉树其实就是一颗"平衡二叉树"。平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致平衡二叉树变的不平衡时，而平衡二叉树会进行调整树上的节点来保持平衡。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

二叉树潜在问题：

因为内存的易失性。一般情况下，我们都会选择将表中的数据和索引存储在磁盘这种外围设备中。但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以，我们应当尽量减少从磁盘中读取数据的次数。 另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。 如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。 如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。那说明什么？说明每个磁盘块仅仅存储一个键值和数据！那如果我们要存储海量的数据呢？可以想象到二叉树的节点将会非常多，高度也会及其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低！

归根结底就是说：让磁盘块能尽量多存一些内容，因为每次硬盘进行IO时间为：平均寻道时间 + 平均延迟时间(一般为9ms)，如果能在一个磁盘块内尽量取出内容是最好的，因为每次为了获取一个节点内容就浪费9ms实属不值。

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

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)。

特点：

• 单一节点存储更多元素，减少了IO次数
• 树的高度比之二叉树更为缩短了，查找次数也随之减少

B+树(innodb存储引擎默认索引方式)

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+树索引真正的实现方式，准确的说应该是聚集索引（聚集索引和非聚集索引下面会讲到&