MySQL原理(三)：索引

前言

上一篇介绍了 MySQL 的逻辑架构和执行过程，这一篇将介绍索引相关的内容。

索引是用额外的数据结构，来实现快速检索目标数据的。就像字典当中的目录一样，用额外的空间来存储部分内容，从而加快检索速度。

MySQL 的逻辑架构分为 Server 层和存储引擎层，其中索引和数据就位于存储引擎中，而不同的存储引擎可能有不同的实现索引的方式，比如常见的 InnoDB 和 MyISAM 使用的都是 B+Tree，但是实现方式不同。

按照不同的分类方式，索引可以分为以下几类：

• 按「数据结构」分类：B+ 树索引、B 树索引、Hash 索引等。
• 按「物理存储」分类：聚簇索引（主键索引）、非聚簇索引（二级索引）。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引、全文索引、空间索引等。
• 按「字段个数」分类：单列索引、联合索引。

B+树索引

B+ 树是一种多叉平衡搜索树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都指向下一个叶子节点，形成一个链表。所有节点按照索引键大小排序，构成一个双向链表，便于范围查询。

虽然，InnoDB 和 MyISAM 都支持 B+ 树索引，但是它们数据的存储结构实现方式不同：

• InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身（数据页）；
• MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址；

由于物理空间地址是混乱无序的，MyISAM 只能先取数据，再排序；而 InnoDB 的物理存放顺序和索引顺序一致。

后续如果没有额外说明，则默认说的都是 InnoDB 中的 B+ 树索引的实现。

InnoDB 里的 B+ 树中的每个节点都是一个数据页。


B树和B+树

B 树又名平衡多路查找树，B 树中所有结点的孩子个数的最大值称为 B 树的阶，通常用 m 表示。

B 树和 B+ 树的区别在于，B 树的每个节点都存储了 key 和 data，而 B+ 树的 data 只存储在叶子节点上，这样单个节点可以存更多的索引键，树的高低就越小，查询时磁盘 IO 的次数就越小。且 B 树没有冗余节点，而 B+ 树有冗余节点。

B+ 树的冗余节点：不仅在叶节点中保存了所有的键，且部分键在非叶节点中也存在。

具体区别：

1、单点查询

B 树的查询效率可能更高，但波动较大；B+ 树相对更矮胖，查询底层节点的 I/O 次数更少。

B+ 树的非叶子节点不存放实际的记录数据，仅存放索引值，因此数据量相同的情况下，B+ 树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O 次数会更少。

2、范围查询

B 树的范围查询相当于树的遍历，效率很低；B+ 树的叶子节点间还有双链表连接，利于范围查询。

3、插入删除效率

B 树没有冗余节点，插入和删除都需要涉及树的变形；B+ 树存在冗余节点，插入和删除时不需要涉及树的变形，效率更高。

为什么选择 B+ 树而不是红黑树（或其他平衡二叉搜索树）？

红黑树（或其他平衡二叉搜索树）每个节点只能存储一个数据，所以在数据量大的情况下，树高很大，会导致多次磁盘 IO。

按物理存储分类

聚簇索引

Clustered Index，又称主键索引，叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+ 树的叶子节点里。

因为表的数据都是存放在聚簇索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚簇索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个。

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

1. 如果有主键，默认会使用主键作为聚簇索引的索引键；
2. 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；
3. 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键；

当执行一条查询语句，比如使用主键索引查询 id 号为 5 的商品时，B+ 树会自顶向下逐层进行查找，查询过程是这样的：

1. 将 5 与根节点的索引数据（1，10，20）比较，因为 5 在 1 和 10 之间，根据搜索逻辑，会找到第二层的索引数据（1，4，7）；
2. 在第二层的索引数据（1，4，7）中进行查找，因为 5 在 4 和 7 之间，所以找到第三层的索引数据（4，5，6）；
3. 在叶子节点的索引数据（4，5，6）中进行查找，然后找到了索引值为 5 的行数据，完成查找。

数据库的索引和数据都是存储在硬盘的，我们可以把读取一个节点当作一次磁盘 I/O 操作。那么上面的整个查询过程一共经历了 3 个节点，也就是进行了 3 次 I/O 操作。

以一个整数字段索引为例，每个节点大约能存储 1200 条索引数据。当这棵树高是 4 的时候，就可以存 1200^3≈17 亿条记录。所以在一个 10