索引是提高MySQL查询性能的一个重要途径，要了解数据库索引的底层原理，我们先得了解一种叫树的数据结构，二叉树是树种非常经典的一种数据结构。
不同的存储引擎可能使用不同的数据结构，但是我们熟知的InnoDB使用的是B+Tree的数据结构。所以，要理解索引的底层原理，我们需要从二叉搜索树、平衡二叉树、B-Tree和B+Tree一步步开始了解。
二叉树（Binary Search Trees）
二叉树是每个结点最多有两个子树的树结构。通常子树被称作“左子树”（Left Subtree）和“右子树”（Right Subtree）。二叉树常被用于实现二叉查找树和二叉堆。
二叉搜索树的特性：
- 每个结点都包含一个元素以及 n 个子树，这里 0≤n≤2。
- 左子树和右子树是有顺序的，次序不能任意颠倒。左子树的值要小于父结点，右子树的值要大于父结点。
假如我们有这样一个数组[6,3,7,2,5,8]
这就是一棵二叉搜索树。经过一系列的插入操作之后，原本无序的一组数已经变成一个有序的结构了，并且这个树满足了上面提到的两个二叉树的特性。
如上图，如果要在这课树中查找值为5的记录，其大致流程：先找到根，其值为6，大于5，所以查找左子树，找到3，而5大于3，接着找3的右子树，总共找了3次。同样的方法，如果查找值为8的记录，也需要查找3次。所以二叉查找树的平均查找次数为(3 + 3 + 3 + 2 + 2 + 1) / 6 = 2.3次。
如果同样是上面的数组，并且按升序排列，按照[2,3,5,6,7,8]的顺序插入，结果会是怎样？
由于是升序插入，新插入的数据总是比已存在的结点数据都要大，所以每次都会往结点的右边插入，最终导致这棵树严重倾斜。
上图就是最坏的情况，也就是一棵树退化为一个线性链表了，这样查找效率自然就低了，完全没有发挥树的优势。
如果查找值为2的记录，仅需要1次，但查找值为8的记录则需要6次，所以顺序查找的平均查找次数为：(1 + 2 + 3 + 4 + 5 + 6) / 6 = 3.3次。
由于二叉查找树可以任意构造，同样的值可能构造出高度不同的二叉树。若想二叉查找数的查询性能最高，需要这棵二叉查找树是平衡的，使其高度最低。
为了较大发挥二叉树的查找效率，让二叉树不再倾斜，保持各科平衡，所以有了平衡二叉树。
平衡二叉树 (AVL Trees)
平衡二叉树是一种特殊的二叉树，所以他也满足前面说到的二叉树的两个特性，同时还有一个特性：它的左右两个子树的高度差的绝对值不超过 1，并且左右两个子树都是一棵平衡二叉树。
平衡二叉树的查找性能是比较高的，查询性能越好，维护的成本就越大。
如果再想图1插入9
通过一次左旋操作就将插入后的树重新变为平衡二叉树是最简单的情况了。当然还有其他情况的操作，这里暂不讨论。
我们需要查找一个数的时候就能沿着树根一直往下找，这样的查找效率和二分法查找是一样的。
一棵平衡二叉树能容纳的节点个数跟这棵二叉树的高度有关。每一层最多能容纳的节点为
2
n
−
1
2^{n-1}
2n−1，那么一棵高度为h的平衡二叉树的能容纳的节点个数为
2
0
+
2
1
+
2
2
+
…
…
+
2
h
−
1
=
2
h
−
1
2^{0}+2^{1}+2^{2}+……+2^{h-1} = 2^{h}-1
20+21+22+……+2h−1=2h−1。按这样，100w个左右的数据，平衡二叉树的高度是20，也就是说，在100w条数据的平衡二叉树中查询一个数据，查找次数最多20次。
至此我们考虑一下，平衡二叉树的查找效率还不错，实现也非常简单，相应的维护成本还能接受。为什么MySQL索引不直接使用平衡二叉树？
如果实在内存中操作，那这种效率是很高的。但是随着数据库的数据不断增加，索引本身的也随着增大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级。每读取一个二叉树的结点就是一次磁盘 IO，这样在100w条数据中我们找一条数据要经过 20 次磁盘的 IO。
那么想要提高性能，能想到的一种方法就是减少树的高度。将树的高度压缩一下，使每层能容纳更多的节点，将二叉树变为m叉树（多路搜索树）。
B-Tree
B树就是这样一棵被压缩的树。中间的“-”是杠精的杠，不是减。所以不要读作B减树。要明白索引原理，心中要有B树。
一棵 m 阶的 B-Tree 有如下特性：
- 每个节点最多 m 个子节点。
- 除了根节点和叶子节点外，每个节点最少有 m/2（向上取整）个子节点。
- 如果根节点不是叶子节点，那根节点至少包含两个子节点。
- 所有的叶子节点都位于同一层。
- 每个节点都包含 k 个元素（关键字），这里 m/2≤k。
- 每个节点中的元素（关键字）从小到大排列。
- 每个元素（关键字）字左节点的值，都小于或等于该元素（关键字）。右节点的值都大于或等于该元素（关键字）。
根据上述特性，来看下[1,2,3,4,5,6,7]这个数组插入一个3阶的B-Tree是如何实现的。
上面是B-Tree的生成过程，每个结点都可能包含多个元素，并且非叶子结点在元素的左右都有指向子结点的指针。
如果要在B-Tree中查找一个元素，流程是怎样的？
看看在下面这棵B-Tree中查找27这个元素的流程。
从这个流程我们能看出，B-Tree 的查询效率好像也并不比平衡二叉树高。但是查询所经过的结点数量要少很多，也就意味着要少很多次的磁盘 IO，这对性能的提升是很大的。
从前面对 B-Tree 操作的图，我们能看出来，元素就是类似 1、2、3 这样的数值。
但是数据库的数据都是一条条的数据，如果某个数据库以 B-Tree 的数据结构存储数据。那数据怎么存放的？
看下示意图：
索引的服务对象是数据，通过索引可以使引擎很快的找到数据。在B-Tree中索引（元素）就是一个个数字，而上图中，元素拆分成key-value形式，其中key就是数据的索引，data就是具体的数据。
这样在查询数据的时候，只需要按照索引从根节点往下查询就可以了。
B+Tree
B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构。
B+Tree有几个自己的特性：
- 所有的非叶子节点只存储关键字信息。
- 所有卫星数据（具体数据）都存在叶子结点中。
- 所有的叶子结点中包含了全部元素的信息。
- 所有叶子节点之间都有一个链指针。
理解B+Tree时，只需要理解其最重要的两个特征即可：第一，所有的关键字（可以理解为数据）都存储在叶子节点（Leaf Page），非叶子节点（Index Page）并不存储真正的数据，所有记录节点都是按键值大小顺序存放在同一层叶子节点上。其次，所有的叶子节点由指针连接。
B-Tree or B+Tree
在讲这两种数据结构在数据库中的选择之前，我们还需要了解的一个知识点是操作系统从磁盘读取数据到内存是以磁盘块（Block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。
这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。
预读的长度一般为页（Page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页的大小通常为 4K）。
页是计算机管理存储器的逻辑块，硬件及OS往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（许多OS中，页的大小通常为4K）。主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。
B-Tree和B+Tree的优劣：
- B-Tree 因为非叶子结点也保存具体数据，所以在查找某个关键字的时候找到即可返回。
而 B+Tree 所有的数据都在叶子结点，每次查找都得到叶子结点。所以在同样高度的 B-Tree 和 B+Tree 中，B-Tree 查找某个关键字的效率更高。 - 由于 B+Tree 所有的数据都在叶子结点，并且结点之间有指针连接，在找大于某个关键字或者小于某个关键字的数据的时候，B+Tree 只需要找到该关键字然后沿着链表遍历就可以了，而 B-Tree 还需要遍历该关键字结点的根结点去搜索。
比如上图中，如果查找大于3而小于5的记录，只需要找到节点3，就可以遍历指针依次找到4、5。如果没有链接指针的话，就无法进行区间查找。 - 由于 B-Tree 的每个节点（这里的结点可以理解为一个数据页）都存储主键+实际数据，而 B+Tree 非叶子节点只存储关键字信息，而每个页的大小是有限的，所以同一页能存储的 B-Tree 的数据会比 B+Tree 存储的更少。
这样同样总量的数据，B-Tree 的深度会更大，增大查询时的磁盘 I/O 次数，进而影响查询效率。
综上比较，MySQL和其他关系型数据库都是选择B+Tree的存储结构来存储数据。
MySQL巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了读取一个节点只需一次I/O。假设B+Tree的高度为h，一次检索最多需要 h-1I/O（根节点常驻内存），复杂度O(h)=O(logMN)。实际应用场景中，M通常较大，常常超过100，因此树的高度一般都比较小，通常不超过3。
Mysql代码:
CREATE TABLE People(
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender enum(`m`,`f`) not null,
key(last_name,first_name,dob)
);
对于表中每一行数据，索引中包含了last_name、first_name、dob列的值，下图展示了索引是如何组织数据存储的。
可以看到，索引首先根据第一个字段来排列顺序，当名字相同时，则根据第三个字段，即出生日期来排序，正是因为这个原因，才有了索引的“最左原则”。
InnoDB 引擎数据存储
在 InnoDB 存储引擎中，也采用页的概念，默认每个页的大小为 16K，也就是每次读取数据时都是读取 4*4K 的大小。
假设，目前有一个如下图的联盟成员表，并且继续往里面写数据。
假设一页能容纳10条数据，目前数据一页能够容纳。所以只有一个根节点，主键和数据都保存在根节点（左边数字代表主键，右边是名字）。
如果再往数据库中插入一条数据，将会如何存放呢。
有一个新的成员加入联盟，page1已经放不下了，这时就需要分裂，产生一个新的page。
上述流程：
- 产生新的page2，将page1中的数据复制到page2中
- 产生新的page3，将“娜塔莎·罗曼诺夫”数据放入page3中
- 原来的page1依然当做根节点，但是变成了一个不存放数据只存放索引的页，并且有两个子结点 Page2、Page3
两个问题：
①为什么要复制 Page1 为 Page2 而不是创建一个新的页作为根结点，这样就少了一步复制的开销了？
如果是重新创建根结点，那根结点存储的物理地址可能经常会变，不利于查找。
在 InnoDB 中根结点是会预读到内存中的，所以结点的物理地址固定会比较好。
②原来page1有10条数据，插入第11条数据后开始裂变，B+Tree 特性的了解，裂变之后每个结点的元素至少为 11/2=5 个。为什么不选择，裂变后1-5的主键在原来的页，6-11在新的页，根节点存放主键6？
如果是这样的话，新的页空间利用率只有 50%，并且会导致更为频繁的页分裂。
所以 InnoDB 对这一点做了优化，新的数据放入新创建的页，不移动原有页面的任何记录。
随着数据不断插入：
每次新增数据，都是将一个页写满，然后新创建一个页继续写，这里其实是有个隐含条件的，那就是主键自增。
主键自增写入时新插入的数据不会影响到原有页，插入效率高，且页的利用率高。但是如果主键是无序的或者随机的，那每次的插入可能会导致原有页频繁的分裂，影响插入效率，降低页的利用率。这也是为什么在 InnoDB 中建议设置主键自增的原因。
在 InnoDB 中，如果一个表没有主键，那默认会找建了唯一索引的列，如果也没有，则会生成一个隐形的字段作为主键。
如果这个表频繁的插入和删除，那将会导致数据页产生大量的碎片，页的空间利用率被降低，还可能导致树的“虚高”，影响查询效率。可以通过重建索引来消除碎片。
优化建议
- 尽量使用唯一值作为索引
- 索引长度尽量短
- like 的模糊查询以 % 开头，会导致索引失效
- 更新非常频繁的数据不适宜建索引
- 一个表建的索引尽量不要超过 5 个
- <，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 则不行
- NULL会使索引的效果大打折扣
参考：
苏静 51CTO技术栈
CHEN川
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
