MySQL索引原理

一、索引的概念

1.1 是什么

MySQL 官方对索引的定义为：索引（Index）是帮助MySQL 高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。可以简单理解为排好序的快速查找数据结构。

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql.如果没有索引，那么你可能需要a----Z，如果我想找到Java开头的单词呢?或者Oracle开头的单词呢?

是不是觉得如果没有索引，这个事情根本无法完成?

数据本身之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法。

这种数据结构，就是索引。

下图就是一种可能的索引方式示例：

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。

为了加快Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的)结构组织的索引。其中，聚合索引，二级索引(辅助索引)，覆盖索引，复合索引，前缀索引，唯一索引，默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引（Hash Index）等。

1.2 优缺点

1.2.1 优势

1.提高数据检索的效率，降低数据库的IO成本。

2.通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

1.2.2 劣势

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

2.实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

3.索引只是提高效率的一个因素，若MySQL有大数据量的表，那么就需要花时间建立最优秀的索引，或优化查询语句。

二、Mysql的索引

2.1 Btree索引

2.1.1 版本1

【初始化介绍】

一颗b 树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1 包含数据项17 和35，包含指针P1、P2、P3。

P1 表示小于17 的磁盘块，P2 表示在17 和35 之间的磁盘块，P3 表示大于35 的磁盘块。

【查找过程】

如果要查找数据项29，那么首先会把磁盘块1 由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17 和35 之间，锁定磁盘块1 的P2 指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，

通过磁盘块1的P2 指针的磁盘地址把磁盘块3 由磁盘加载到内存，发生第二次IO，29 在26 和30 之间，锁定磁盘块3 的P2 指针，通过指针加载磁盘块8 到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3 层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

2.1.2 版本二

B树

• 每个节点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。

• 所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同。

• 每个节点都存有索引和数据，也就是对应的key和value。

图中的 p 节点为指向子节点的指针，二叉查找树和平衡二叉树其实也有，因为图的美观性，被省略了。

图中的每个节点称为页，页就是我们上面说的磁盘块，在 MySQL 中数据读取的基本单位都是页，Innodb引擎默认容量为16KB，所以我们这里叫做页更符合 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，b，v）。

2.2 B+tree索引

2.2.1 版本1

B+Tree 与B-Tree 的区别

1）B树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。

2）在B树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。

从这个角度看B树的性能好像要比B+树好，而在实际应用中却是B+树的性能要好些。

因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B树多，树高比B树小，这样带来的好处是减少磁盘访问次数。

尽管B+树找到一个记录所需的比较次数要比B树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些，

而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用B+树的缘故。

思考：为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？

1) B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。

相对来说IO 读写次数也就降低了。

2) B+树的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

2.2.2 版本二

1.mysql之页

2.B+树

1.B+树有两种类型的节点：内部结点（也称索引结点）和叶子结点。内部节点就是非叶子节点，内部节点不存储数据，只存储索引，数据都存储在叶子节点。

2.内部结点中的key都按照从小到大的顺序排列，对于内部结点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它。叶子结点中的记录也按照key的大小排列。

3.每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序链接。

4.父节点存有右孩子的第一个元素的索引。

根据上图我们来看下 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+ 树索引的叶子节点并不存储数据，而是存储数据的文件地址。

2.3 Hash索引

假如，Col3为索引列。

Hash索引会对索引的key进行一次hash计算，就定位出数据存储的位置。

与HashMap类似，它用数组+链表来存储索引以及数据的磁盘空间地址，假如，遇到hash冲突，也是采用拉链法。

对于Hash索引的查询，假如我们要找“Tom”这个索引对应的数据，我们只需要那Tom这个值，做一下Hash运算，直接定位到Hash桶里面，然后，找到Tom所在位置，拿出对应数据磁盘空间地址，找出数据。

很多时候Hash索引要比B+树索引更高效，仅能满足“=”，“IN”，不支持范围查询，假想一下，我们要找>Tom的元素，这不一脸蒙圈嘛，怎么查找，所以，工作中很多时候，我们还是用B+树索引。

视频教程

2.4 小结

B树，所有关键字在整棵树中出现，且只出现一次，非叶子结点可以命中

B+树，在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；

B+树相对于B树有一些自己的优势，可以归结为下面几点

1.单一节点存储的元素更多，使得查询的IO次数更少，所以也就使得它更适合做为数据库MySQL的底层数据结构了。

2.所有的查询都要查找到叶子节点，查询性能是稳定的，而B树，每个节点都可以查找到数据，所以不稳定。

3.所有的叶子节点形成了一个有序链表，便于范围查找。

参考博客，参考博客，参考博客，参考博客