Mysql基本原理：索引

Mysql基本原理：索引

参考资料

一步步分析为什么B+树适合作为索引的结构 以及索引原理 (阿里面试)

彻底搞懂系列B-树、B+树、B-树、B*树

MySQL索引原理

MySQL索引实现原理分析

mysql中的聚集索引、非聚集索引、聚簇索引、稀疏索引、稠密索引

一、基本概念

1.1 为什么要有索引

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

1.2 什么是索引

定义： 索引是帮助 MySQL 高效获取数据的数据结构。

快速定位数据，并查询出来，这是索引干的事。

索引的文件存储形式与存储引擎有关，InnoDB 引擎的索引文件后缀是 .ibd ; MyISAM 引擎则是 .MYI。

索引在MySQL中也叫是一种“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。

索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。

索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

1.3 索引的原理

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段…这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？按照搜索树的模型，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

思考：索引能快速定位数据，那它不是在内存中嘛？为啥保存在硬盘中呢？

因为硬盘相当于永久存储介质，可以保证意外断电或者发生故障重启不会造成索引数据丢失，而内存，它是RAM，断电数据会丢失的。
索引存储在硬盘中，但是MySQL服务启动，它会将整个索引文件加载到内存中，这样就可以快速地找到某个key ( 数据 )，再根据数据结构去硬盘中读取对应的数据。

当我们在硬盘上进行查询时，也就产生了硬盘的 I/O (读写)操作。相比于内存的存取来说，硬盘的 I/O 存取消耗的时间要高很多。当磁盘 I/O 次数越多，所消耗的时间也就越大。

所以，索引的数据结构设计目的是为了快速定位数据，并且去硬盘找数据时能少走 ‘弯路’。

补充：

磁盘IO与预读：磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS（Million Instructions Per Second）的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行约450万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

1.4 索引的数据结构

1. hash

把key转换为 int 数据，取模运算，将key存储到hash表中，数据都会加载到内存中，数据表小，没啥问题，数据大，就会耗费大量内存空间，MySQL中采用的是“自适应Hash索引”的方式。

2. 树

树状图是一种数据结构，它是由n（n>=1）个有限结点组成一个具有层次关系的集合。把它叫做“树”是因为它看起来像一棵倒挂的树，也就是说它是根朝上，而叶朝下的。

它具有以下的特点：每个结点有零个或多个子结点；没有父结点的结点称为根结点；每一个非根结点有且只有一个父结点；除了根结点外，每个子结点可以分为多个不相交的子树

根结点 : A

父节点 : A是B,C的父节点

叶子节点：D,E是叶子节点

树的深度/树的高度：高度为3

3. B-tree

Balance tree（平衡多路搜索树），在二叉树的基础上采用多叉树，这样可以降低树的深度，提高查询数据。

如下图，深度为3 ，所以查找数据最多需要对磁盘3次 I/O 操作。

缺点：

每个节点都有data（整条记录） , 节点空间有限，通常是16KB，如果data较大，会导致节点存储的数据更少，往往3层的深度存储的数据远远不能满足需求。
那就需要4层或更深的树，于是通过磁盘查找数据多了几次 I/O 操作，严重影响数据存取速度，而影响服务器性能。

于是，就有了B+tree ,读作 ’ B plus 树 ’ , 它在B树的基础上做了一些优化。

4. B+树

任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生（B+树是通过二叉查找树，再由平衡二叉树，B树演化而来）。B+树是B树的一个升级版，相对于B树来说B+树更充分的利用了节点的空间，让查询速度更加稳定，其速度完全接近于二分法查找。

思考：为什么redis中用跳表做索引，不用B+树？

因为B+树的原理是 叶子节点存储数据，非叶子节点存储索引，B+树的每个节点可以存储多个关键字，它将节点大小设置为磁盘页的大小，充分利用了磁盘预读的功能。每次读取磁盘页时就会读取一整个节点,每个叶子节点还有指向前后节点的指针，为的是最大限度的降低磁盘的IO；

而Redis是内存中读取数据，不涉及IO，因此使用了跳表；

（1）B+树结点组成

B+树包含两种结点：

• 根结点：(一般区分为两种，将根节点单独拿出来先讨论说明一下，因为根节点非常特殊而且唯一)

  若树只有一层：仅有根结点，此根结点也是叶结点，根结点中索引值个数无最少限制。

  若树为两层以及以上：最上面那一层为根结点，根结点属于内部结点，根节点中指针最少为2个。

• 叶结点：叶节点在B+树的最底层（所有叶节点都在一层），叶结点中存放索引值、指向记录的指针、指向下一个叶结点的指针。叶结点内的索引值是记录中键的拷贝，这些索引值以排好序的形式，从左到右分布在叶节点中。

• 内部结点：所有非叶结点都属于内部结点，所有内部结点上的索引值都是叶结点的副本。内部结点中存放索引值、指向某一个结点（可以是叶结点也可以是内部节点）的指针。

这两种结点的构成直观图如下： （可以看到两者的存储空间是一样的）

（2）B+树的结构特征

B+树必然是平衡的，从树根到树叶的所有路径都是等长的。

不同格式的B+树的区别仅有唯一参数n，n为树中所有结点（无论叶结点还是内部结点）包含的指针数目。在上图中我们可以看出，n为4（或称为4阶B+树）。

一旦n确定下来，在整个操作过程中都不会变，在整个操作过程中，任何结点的占用空间均为：

n-1个索引值空间 + n个指针空间。

事实上，在文件系统中，每个物理块都代表一个结点（可能有未被利用的空间，但要保证能放下一个结点，且n尽可能大）。

在树高度大于等于2层时，若结点是合法的，则有如下：

1）对于根节点：存放指针的空间>= 2 ，即根节点至少有两个孩子；

2）对于内部结点：存放指针空间的装满量必须 >= 一半，即每个内部节点至少包含ceil(n/2)个孩子，最多有n个孩子，在上图中，n=4因此必须存放2~4个指针才算合法；

3）对于叶结点：存放指向记录的指针的装满量必须 >= 一半。注意！存放记录的指针量是n-1，有1个是指向下一个叶结点的指针，每个叶子节点都包含k-1个元素，n/2 <= k <= n；当n=4时，存放记录的指针个数为2或3时都是合法的；

4）叶节点每一个索引值对应一个记录指针。内部结点每一个索引值划分两个区间，并把区间对应到指针上，因此若内部结点有a个索引，指针需要a+1个；

5）索引值不重复（这里不讨论索引值重复的变体）；

6）所有叶子节点位于同一层；

7）只有叶子节点才有data，其他都是索引；

对于高度仅为1的树，没有上述要求（因为最开始单个节点中肯定没有东西）。

一颗棵合法的B+树可能有如下形状：

（3）B+树的查询

在单元查询的时候，B+树会自定向下逐层查找，最终找到匹配的叶子节点。例如我们查找3 。

而B+树中间节点没有Data数据，所以同样大小的磁盘页可以容纳更多的节点元素。所以数据量相同的情况下，B+树比B树更加“矮胖“，因此使用的IO查询次数更少。

（4）B+树的插入

1）若为空树，创建一个叶子结点，然后将记录插入其中，此时这个叶子结点也是根结点，插入操作结束。

2）针对叶子类型结点：根据key值找到叶子结点，向这个叶子结点插入记录。插入后，若当前结点key的个数小于等于m-1，则插入结束。否则将这个叶子结点分裂成左右两个叶子结点，左叶子结点包含前m/2个记录，右结点包含剩下的记录，将第m/2+1个记录的key进位到父结点中（父结点一定是索引类型结点），进位到父结点的key左孩子指针向左结点,右孩子指针向右结点。将当前结点的指针指向父结点，然后执行第3步。

3）针对索引类型结点：若当前结点key的个数小于等于m-1，则插入结束。否则，将这个索引类型结点分裂成两个索引结点，左索引结点包含前(m-1)/2个key，右结点包含m-(m-1)/2个key，将第m/2个key进位到父结点中，进位到父结点的key左孩子指向左结点, 进位到父结点的key右孩子指向右结点。将当前结点的指针指向父结点，然后重复第3步。

下面是一颗5阶B树的插入过程，5阶B数的结点最少2个key，最多4个key。

---

a）空树中插入5

---

b）依次插入8，10，15

---

c）插入16

插入16后超过了关键字的个数限制，所以要进行分裂。在叶子结点分裂时，分裂出来的左结点2个记录，右边3个记录，中间key成为索引结点中的key，分裂后当前结点指向了父结点（根结点）。结果如下图所示。

当然我们还有另一种分裂方式，给左结点3个记录，右结点2个记录，此时索引结点中的key就变为15。

---

d）插入17

---

e）插入18，插入后如下图所示

当前结点的关键字个数大于5，进行分裂。分裂成两个结点，左结点2个记录，右结点3个记录，关键字16进位到父结点（索引类型）中，将当前结点的指针指向父结点。

当前结点的关键字个数满足条件，插入结束。

---

f）插入若干数据后

---

g）在上图中插入7，结果如下图所示

当前结点的关键字个数超过4，需要分裂。左结点2个记录，右结点3个记录。分裂后关键字7进入到父结点中，将当前结点的指针指向父结点，结果如下图所示。

当前结点的关键字个数超过4，需要继续分裂。左结点2个关键字，右结点2个关键字，关键字16进入到父结点中，将当前结点指向父结点，结果如下图所示。

当前结点的关键字个数满足条件，插入结束。

（5）B+树的删除

如果叶子结点中没有相应的key，则删除失败。否则执行下面的步骤：

1）删除叶子结点中对应的key。删除后若结点的key的个数大于等于Math.ceil(m-1)/2 – 1，删除操作结束,否则执行第2步。

2）若兄弟结点key有富余（大于Math.ceil(m-1)/2 – 1），向兄弟结点借一个记录，同时用借到的key替换父结（指当前结点和兄弟结点共同的父结点）点中的key，删除结束。否则执行第3步。

3）若兄弟结点中没有富余的key,则当前结点和兄弟结点合并成一个新的叶子结点，并删除父结点中的key（父结点中的这个key两边的孩子指针就变成了一个指针，正好指向这个新的叶子结点），将当前结点指向父结点（必为索引结点），执行第4步（第4步以后的操作和B树就完全一样了，主要是为了更新索引结点）。

4）若索引结点的key的个数大于等于Math.ceil(m-1)/2 – 1，则删除操作结束。否则执行第5步

5）若兄弟结点有富余，父结点key下移，兄弟结点key上移，删除结束。否则执行第6步

6）当前结点和兄弟结点及父结点下移key合并成一个新的结点。将当前结点指向父结点，重复第4步。

注意，通过B+树的删除操作后，索引结点中存在的key，不一定在叶子结点中存在对应的记录。

下面是一颗5阶B树的删除过程，5阶B数的结点最少2个key，最多4个key。

---

a）初始状态

---

b）删除22,删除后结果如下图

删除后叶子结点中key的个数大于等于2，删除结束

---

c）删除15，删除后的结果如下图所示

删除后当前结点只有一个key,不满足条件，而兄弟结点有三个key，可以从兄弟结点借一个关键字为9的记录,同时更新将父结点中的关键字由10也变为9，删除结束。

---

d）删除7，删除后的结果如下图所示

当前结点关键字个数小于2，（左）兄弟结点中的也没有富余的关键字（当前结点还有个右兄弟，不过选择任意一个进行分析就可以了，这里我们选择了左边的），所以当前结点和兄弟结点合并，并删除父结点中的key，当前结点指向父结点。

此时当前结点的关键字个数小于2，兄弟结点的关键字也没有富余，所以父结点中的关键字下移，和两个孩子结点合并，结果如下图所示。

5. B*树

• B*树是B+树的变种，相对于B+树他们的不同之处如下：

（1）首先是关键字个数限制问题，B+树初始化的关键字初始化个数是ceil(m/2)，b*树的初始化个数为（ceil(2/3*m)）;

（2）B+树节点满时就会分裂，而B*树节点满时会检查兄弟节点是否满（因为每个节点都有指向兄弟的指针），如果兄弟节点未满则向兄弟节点转移关键字，如果兄弟节点已满，则从当前节点和兄弟节点各拿出1/3的数据创建一个新的节点出来；

特点：

在B+树的基础上因其初始化的容量变大，使得节点空间使用率更高，而又存有兄弟节点的指针，可以向兄弟节点转移关键字的特性使得B*树额分解次数变得更少；

6. 总结

（1）MySQL主要用到两种结构：B+ Tree索引和Hash索引

• Inodb存储引擎 默认是 B+Tree索引

• MyISAM 存储引擎 默认是Fulltext索引；

• Memory 存储引擎 默认 Hash索引；

MySQL中，只有Memory(Memory表只存在内存中，断电会消失，适用于临时表)存储引擎显示支持Hash索引，是Memory表的默认索引类型，尽管Memory表也可以使用B+Tree索引。Hash索引把数据以hash形式组织起来，因此当查找某一条记录的时候，速度非常快。但是因为hash结构，每个键只对应一个值，而且是散列的方式分布。所以它并不支持范围查找和排序等功能。

B+Tree是mysql使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎模式的索引类型。相对Hash索引，B+Tree在查找单条记录的速度比不上Hash索引，但是因为更适合排序等操作，所以它更受欢迎。毕竟不可能只对数据库进行单条记录的操作。

对比：

• hash类型的索引：查询单条快，范围查询慢

• btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

（2）数据库中为什么不用链表结构索引？

数据库中实际使用的索引并不会是链表结构，因为效率太低了。
我们知道链表的查询效率是O(n)。就像上面的例子，遍历了501次才找到第一条符合条件的记录，这是很低效的。而我们知道，数组+二分查找的效率是O(lgn)，但是数组的插入元素以及删除元素的效率很低，因此使用数组做为索引结构并不合适。

另外，在选择数据库索引的结构的时候，要考虑到另一个问题。索引是存在于磁盘中，当索引非常大的时候，达到几个G的时候，无法一次加载到内存中。

考虑到上面两个因素，数据库中索引使用的是树形结构。

（3）平衡二叉树、B树、B+树作为索引的合理性

• 平衡二叉树：不适合作为索引

  索引是存在于索引文件中，是存在于磁盘中的。因为索引通常是很大的，因此无法一次将全部索引加载到内存当中，因此每次只能从磁盘中读取一个磁盘页的数据到内存中。而这个磁盘的读取的速度较内存中的读取速度而言是差了好几个级别。

  注意，我们说的平衡二叉树结构，指的是逻辑结构上的平衡二叉树，其物理实现是数组。然后由于在逻辑结构上相近的节点在物理结构上可能会差很远。因此，每次读取的磁盘页的数据中有许多是用不上的。因此，查找过程中要进行许多次的磁盘读取操作。

  而适合作为索引的结构应该是尽可能少的执行磁盘IO操作，因为执行磁盘IO操作非常的耗时。因此，平衡二叉树并不适合作为索引结构。

• B-Tree：适合作为索引

  平衡二叉树没能充分利用磁盘预读功能，而B树是为了充分利用磁盘预读功能来而创建的一种数据结构，也就是说B树就是为了作为索引才被发明出来的的。

  引申：局部性原理与磁盘预读

  由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理： 
  当一个数据被用到时，其附近的数据也通常会马上被使用。 
  程序运行期间所需要的数据通常比较集中。 
  由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。
  

  而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

  使用红黑树（平衡二叉树）结构的话，每次磁盘预读中的很多数据是用不上的数据。因此，它没能利用好磁盘预读的提供的数据。然后又由于深度大（较B树而言），所以进行的磁盘IO操作更多。

  B树的每个节点可以存储多个关键字，它将节点大小设置为磁盘页的大小，充分利用了磁盘预读的功能。每次读取磁盘页时就会读取一整个节点。也正因每个节点存储着非常多个关键字，树的深度就会非常的小。进而要执行的磁盘读取操作次数就会非常少，更多的是在内存中对读取进来的数据进行查找。

  B树的查询，主要发生在内存中，而平衡二叉树的查询，则是发生在磁盘读取中。因此，虽然B树查询查询的次数不比平衡二叉树的次数少，但是相比起磁盘IO速度，内存中比较的耗时就可以忽略不计了。因此，B树更适合作为索引。

• B+ Tree：比B-Tree更适合作为索引

  B+树的关键字全部存放在叶子节点中，非叶子节点用来做索引，而叶子节点中有一个指针指向一下个叶子节点。做这个优化的目的是为了提高区间访问的性能。而正是这个特性决定了B+树更适合用来存储外部数据。

（4）B+树和B树的对比

B树：有序数组+平衡多叉树；

B+树：有序数组链表+平衡多叉树；

B+树的优点：

① B+树的层级更少，IO次数更少：相较于B树B+每个非叶子节点存储的关键字数更多，树的层级更少所以查询数据更快；

② B+树查询速度更稳定：B+所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比B树更稳定；B树的查找并不稳定（最好的情况是查询根节点，最坏查询叶子节点）；

③ B+树天然具备排序功能：B+树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高；
④ B+树全节点遍历更快，范围查询更简便：B+树遍历整棵树只需要遍历所有的叶子节点即可，而不需要像B树一样需要对每一层进行遍历，这有利于数据库做全表扫描。

B树的优点：

B树相对于B+树的优点是：如果经常访问的数据离根节点很近，而B树的非叶子节点本身存有关键字其数据的地址，这种数据检索的时候会要比B+树快。

（5）三种B树总结

• 相同思想和策略
  从平衡二叉树、B树、B+树、B*树总体来看它们的贯彻的思想是相同的，都是采用二分法和数据平衡策略来提升查找数据的速度；

• 不同的方式的磁盘空间利用
  不同点是他们一个一个在演变的过程中通过IO从磁盘读取数据的原理进行一步步的演变，每一次演变都是为了让节点的空间更合理的运用起来，从而使树的层级减少达到快速查找数据的目的。

1.5 聚集索引与辅助索引

数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。

• 相同点：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据；
• 不同点：叶子结点存放的是否是一整行的信息。

1. 聚集索引

InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。

而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。

如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。

聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录；

聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可；

2. 辅助索引

表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。

叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。

由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得只想主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

举例来说，如果在一棵高度为3的辅助索引树种查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问才能得到最终的一个数据页。

3. 聚集索引和非聚集索引的区别

聚集索引
1.纪录的索引顺序与物理顺序相同，因此更适合between and和order by操作；
2.叶子结点直接对应数据，从中间级的索引页的索引行直接对应数据页；
3.每张表只能创建一个聚集索引；

非聚集索引
1.索引顺序和物理顺序无关；
2.叶子结点不直接指向数据页；
3.每张表可以有多个非聚集索引，需要更多磁盘和内容，多个索引会影响insert和update的速度

1.6 聚集索引、非聚集索引、聚簇索引、稀疏索引、稠密索引

1. 聚集索引

指索引项的排序方式和表中数据记录排序方式一致的索引 。

也就是说聚集索引的顺序就是数据的物理存储顺序。它会根据聚集索引键的顺序来存储表中的数据，即对表的数据按索引键的顺序进行排序，然后重新存储到磁盘上。因为数据在物理存放时只能有一种排列方式，所以一个表只能有一个聚集索引。

比如字典中，用‘拼音’查汉字，就是聚集索引。因为正文中字都是按照拼音排序的。而用‘偏旁部首’查汉字，就是非聚集索引，因为正文中的字并不是按照偏旁部首排序的，我们通过检字表得到正文中的字在索引中的映射，然后通过映射找到所需要的字。

聚集索引的使用场合为：

a.查询命令的回传结果是以该字段为排序依据的；

b.查询的结果返回一个区间的值；

c.查询的结果返回某值相同的大量结果集。

聚集索引会降低 insert，和update操作的性能，所以，是否使用聚集索引要全面衡量。

2. 非聚集索引

索引顺序与物理存储顺序不同。

非聚集索引的使用场合为：

a.查询所获数据量较少时；

b.某字段中的数据的唯一性比较高时；

非聚集索引必须是稠密索引。

3. 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。术语“聚族”表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行放在两个不同的地方，所以一个表只能有一个聚族索引。

聚族索引的优点：

可以把相关数据保存在一起：就好像在操场上战队，一个院系一个院系的站在一起，这样要找到一个人，就先找到他的院系，然后在他的院系里找到他就行了，而不是把学校里的所有人都遍历一遍；

数据访问更快：聚族索引将索引和数据保存在同一个B-Tree中，因此从聚族索引中获取数据通常比在非聚族索引中查找更快。

4. 稠密索引

稠密索引：每个索引键值都对应有一个索引项

稠密索引能够比稀疏索引更快的定位一条记录。但是，稀疏索引相比于稠密索引的优点是：它所占空间更小，且插入和删除时的维护开销也小。

5. 稀疏索引

稀疏索引：相对于稠密索引，稀疏索引只为某些搜索码值建立索引记录；在搜索时，找到其最大的搜索码值小于或等于所查找记录的搜索码值的索引项，然后从该记录开始向后顺序查询直到找到为止。

二、MySQL索引实现

目前大部分数据库系统及文件系统都采用**B-Tree(B树)或其变种B+Tree(B+树)**作为索引结构。B+Tree是数据库系统实现索引的首选数据结构。在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的。

2.1 MyISAM索引实现

MyISAM引擎使用B+ Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

这里设表一共有三列，假设以Col1为主键，则上图是一个MyISAM表的主索引（primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。

辅助索引

在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果在Col2上建立一个辅助索引，则此索引的结构为：

同样也是一颗B+ Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为：

首先按照B+ Tree搜索算法搜索索引，如果指定的key存在，则去除其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM 的索引方式也叫做“非聚集索引”，之所以这么称呼是为了与 InnoDB的聚集索引区分。

2.2 InnoDB索引实现

InnoDB也使用 B+Tree 作为索引结构，但具体实现方式却与 MyISAM 截然不同：

1. 第一个重大区别就是InnoDB的数据文件本身就是索引文件，从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

而在InnoDB中，表数据本身就是按B+ Tree组织的一个索引结构，这颗树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是 InnoDB 主索引（同时也是数据文件）的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集。

InnoDB要求表必须有主键，MyISAM可以没有，如果没有显式指定，则MySQL系统会自动选择一个可以标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，类型为长整型（实际上是生成长度为六字节48bit的ROW_ID）。

同时，应尽量在InnoDB上采用自增字段作为表的主键。因为数据文件本身是一颗B+ Tree，非单调的主键会造成在插入新纪录时数据文件为了维持B+ Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。如果表使用自增的主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。

这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

**2. 第二个区别是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。**即，InnoDB的所有辅助索引都引用主键作为data域。

例如，下图为定义在Col3上的一个辅助索引：

聚集索引这种实现方式使得主键的搜索十分高效，但辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

思考：为什么不建议使用过长的字段作为主键？

因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

2.3 索引类型

• InnoDB使用的是聚簇索引，将主键组织到一颗B+树中，而行数据就存储在叶子节点上，若使用“where id = 14”这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

  若对Name进行条件搜索，则需要两个步骤：

  第一步在辅助索引B+ 树中检索Name，到达叶子节点，获取对应的主键；

  第二步使用主键在主索引B+ 树中再执行一次B+ 树检索操作，最终到达叶子节点即可获取证行数据。

• MyISAM使用的是非聚簇索引，非聚簇索引的两颗B+ 树看上去没什么不同，节点的结构完全一致，知识存储的内容不同而已，主索引B+ 树的节点存储了主键，辅助索引B+ 树存储了辅助键，表数据存储在独立的地方，这两棵B+ 树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个建没有任何差别。由于索引树是独立的，通过辅助建检索无需访问主键的索引树。

为了更形象说明这两种索引的区别，假想一个表如下图：存储了 4 行数据。其中Id作为主索引，Name 作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

2.4 联合索引及最左原则

联合索引是指对表上的多个列进行索引，联合索引也是一棵B+树，不同的是联合索引的键值数量不是1，而是大于等于2。

联合索引存储数据结构图：

最左匹配原则：

例如联合索引有三个索引字段（A,B,C）

查询条件：

（A，，）—会使用索引

（A，B，）—会使用索引

（A，B，C）—会使用索引

（，B，C）—不会使用索引

（，，C）—不会使用索引