MySQL 索引机制_主键有多个 用一个查询 算索引吗

谈到 MYSQL 索引服务端的同学应该是熟悉的不能再熟悉，新建表的时候怎么着都知道先来个主键索引，对于经常查询的列也会加个索引加快查询速度。那么 MYSQL 索引都有哪些类型呢？索引结构是什么样的呢？有了索引是如何检索数据的呢？

你认为应该如何查询数据

谈到 InnoDB 引擎的时候聊过在 InnoDB 引擎是面向行存储的，数据都是存储在磁盘的数据页中，数据页里面按照固定的行格式存储着每一行数据。

InnoDB存储引擎是 B+ 树索引组织的，所以数据即索引，索引即数据。B+ 树的叶子节点存储的都是数据段的数据。InnoDB 引擎对数据的存储必须依赖于主键，主键对应的索引叫做聚集索引。如果不幸的是你建表没有建主键，InnoDB 会从表字段中寻找第一个非空的唯一索引作为聚集索引，如果还是不幸找不到，InnoDB 会生成一个不可见的名为 ROW_ID 的列，该列是一个 6 字节的自增数字，用来创建聚集索引。

小Tips:

对于 ROW_ID 列的自增实现其实是来自于一个全局自增序列，这意味着所有使用到 ROW_ID 作为聚集索引的表都共享该序列，如果在高并发的情况就有保证不了唯一性的可能。

大家都知道 MYSQL 中索引是使用 B+ 树的数据结构，在此也就不故弄玄虚。但是大家有没有想过除了 B+ 树还有什么数据结构也可以用于索引检索呢？我们不妨来看看。

二叉树

对于二叉树而言，每个节点只能有两个子节点，如果是一颗单边二叉树，查询某个节点的次数与节点所处的高度相同，时间复杂度为O(n)；如果是一颗平衡二叉树，查找效率高出一半，时间复杂度为O(Log2n)。

并且二叉树还有另一个坏处，二叉树上的每一个节点都是数据节点，那么对于一个比较高的数如果要获取最下面的数据遍历的节点数将会很消耗性能。

Hash 表

散列表的好处是散列查询单条数据比较快，但是坏处也比较多，比如 Hash 碰撞的解决，范围查找等等。

B 树

B树是二叉树的升级版，又叫平衡多路查找树。它和平衡二叉树的区别在于：

1. 平衡二叉树最多两个子树，而 B 树每个节点都可以有多个子树，M 阶 B 树表示每个节点最多有M个子树。

2. 平衡二叉树每个节点只有一个数据和两个指向孩子的指针，而 B 树每个中间节点有 k-1 个关键字（可以理解为数据）和 k 个子树（ k 介于阶数 M 和 M/2 之间，M/2 向上取整）。

3. 所有叶子节点均在同一层、叶子节点除了包含关键字和关键字记录的指针外也有指向其子节点的指针，只不过其指针地址都为 null 。

另外，它们相同的点是节点数据也是按照左小右大的顺序排列。我们用一张图来对比它们的区别：

B+ 树

说到 B 树就连着B+树一起说了。B+ 树是应文件系统所需而产生的一种 B 树的变形树（文件的目录一级一级索引，只有最底层的叶子节点（文件）保存数据）非叶子节点只保存索引，不保存实际的数据）。

这里有一个很重要的一点就是 B+ 树的非叶子节点不保存数据，只有索引。一棵 m 阶的 B+ 树和 m 阶的 B 树的异同点在于：

1. 节点的子树数和关键字数相同（B 树是关键字数比子树数少一）；

2. 所有的叶子结点中包含了全部关键字的信息，即指向含有这些关键字记录的指针（节点的关键字表示的是子树中的最大数，在子树中同样含有这个数据），且叶子结点本身依关键字的大小自小而大的顺序链接。 (而 B 树的叶子节点并没有包括全部需要查找的信息)；

3. 所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。 (而 B 树的非终节点也包含需要查找的有效信息)。

4. 叶子节点之间通过指针连接。

对于 B 树和 B+ 树来说，两种数据结构都是为了减少磁盘 I/O 读写过于频繁而生，本身节点的个数是有限的，采用多叉结构就是为了让每一层放尽可能多的节点以此来降低整棵树的高度。但是为什么 InnoDB 索引结构最终选择了 B+ 树而不是B 树呢？

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

B+ 树内部非叶子节点本身并不存储数据，所以非叶子节点的存储代价相比 B 树就小的多。存储容量减少同时也缩小了占用盘块的数量，那么数据的聚集程度直接也影响了查询磁盘的次数。

B+ 树查询效率更加稳定

树高确定的前提下所有的数据都在叶子节点，那么无论怎么查询所有关键字查询的路径长度是固定的。

B+ 树对范围查询的支持更好

B+ 树所有数据都在叶子节点，非叶子节点都是索引，那么做范围查询的时候只需要扫描一遍叶子节点即可；而 B 树因为非叶子节点也保存数据，范围查询的时候要找到具体数据还需要进行一次中序遍历。

MyISAM 和 InnoDB 索引组织的区别

在 MYSQL 中索引属于存储引级别的概念，存储引擎不同，索引的实现方式也不一样。我们分别看看看 MyISAM 和 InnoDB 中都是如何实现索引功能。

MyISAM 实现

MyISAM 也是使用 B+ 树作为索引存储结构，他的叶子节点 data 域存放的是数据的物理地址，即索引结构和真正的数据结构其实是分开存储的。

InnoDB 索引实现

MyISAM 索引和数据是分离的，但是在 InnoDB 中却大不相同，InnoDB 中采用主键索引的方式，所有的数据都保存在主键索索引中。

所以这也是为什么 InnoDB 要求每个表都必须要有主键的原因。本身就是基于主键来组织的数据存储。

索引类型

以下所有索引类型都是基于 InnoDB 引擎。

主键索引

主键索引也就是我们说的聚集索引。上面说过主键索引是基于主键来创建的 B+ 树索引结构，如果没有指定主键，也找不到任何一列不重复的列可以作为主键的情况下，InnoDB 会新增一个隐藏列 RowId 作为主键继而创建聚集索引。

二级索引(非主键索引)

二级索引就是指除了主键索引外的索引。主键索引和所有的二级索引都是各自维护各自的 B+ 树结构，但是有个不同的地方在于，二级索引的叶子节点存储的不是数据，而是主键索引对应的主键值。

即二级索引不再保存一份 data 数据，而是去主键索引中查数据。那么对于二级索引查找一条数据所要做的操作就是：

1. 首先在二级索引中找到叶子节点对应的数据主键值；

2. 根据这个主键值去聚集索引中找到真正对应的数据行。

所以这里需要两次 B+ Tree 查找。

覆盖索引