MySql索引详解
一、介绍
索引是的字面意思就是我们平常经常见的目录，是帮助快速找到书中某块内容的设计，而要具体去弄懂 MySql 索引的原理，需要先清楚 MySql 是如何存储数据的
二、InnoDB存储方式
MySql现在使用比较多的就是 InnoDb 引擎，本文讲述的就是此引擎。
我们有一个主观的印象就是 MySql 存储的就是一条一条的对应关系，数据是分条存储进入的，而对于这样每一条数据，InnoDB又将它们存储在页中，页是其管理存储空间的基本单位，一个页的大小通常为 16 k，页同样可以分为很多种，用来存储不同类型的数据，总的来说，我们存储一个数据的基本单位就是页，页中可以包含多条数据。如下图所示：
根据图片我们可以看到页中其实还有一个最大记录和最小记录，我们知道一个页中对应的数据是有多条的，而：
最大记录：大于页中多条记录中主键最大值的值
最小记录：小于页中多条记录中主键最小值的值
至于这两个值的作用后面会讲
我们还要知道的就是页中每条数据的排列方式类似一个单向链表，即每一条数据都会存储其下一条数据的地址，而他们的排列顺序则是严格按照主键（或者其他）的大小来进行排序
那我们思考一个问题：当我们执行一个查询语句时，如：SELECR * FROM page_demo WHERE C1 = 3 ？
我们假定我们找到了这个记录所在的页（这个在下面写），那我们剩下的工作即从页中找到这个记录，现在最直观的一个找法就是，遍历这个链表，直至找到对应的主键，但一个页面通常的大小为 16KB，即 16384 个字节，也就意味着一个页中的数据条数不会少，而我们利用遍历的方法去找数据的效率就不会高。
所以在页中又引入了槽的概念：
一个槽将某几个连续的记录分为一组，然后抛出其中槽中记录间最大的最大记录（这个前面有介绍），一个页中的记录又被细分为很多组，每个组都有最大记录，所以我们在查找的时候就可以先利用二分法找到数据对应的槽，然后在槽中进行遍历寻找，这样就会快很多（类似于归并排序）
那么单个页的故事讲完了
还需要想一个问题，就是我们所要查找的数据可能会涉及到多个页，所以页与页之间肯定也是需要某种联系。所以 InnoDB 中页是通过双向链表互相链接的，这样我们就可能在跨越页的时候快速找到下一个页，当然得强调，页与页之间的联系只是逻辑联系，反映在磁盘上就不一定是连续的空间。
那最重要的问题来了，怎么找到我们想查询数据所在的页呢？
想法: 我们参考页中数据的存储方式，我们可以为页设置一个目录，做目录需要完成两件事情：
1、下一个页的主键值必须全部大于本页的主键值：这样毫无疑问可以优化查询，但对于插入来说可能会变的繁琐一点：
比如 页10 和 页 28 连在一起，注意是逻辑相连，物理地址不一定相连，页 10 最大主键值为 6，已经存满了，现在来了一个键值为 5 的数据，这里为了保证顺序性，我们就要将键值为6的数据放入下一页也就是 28 页中，再把新来的键值为 5 的数据存入页 10，注意我们的操作是前面的页面必须知道后面页面的地址，而后面页面也必须要知道前面页面的地址，这就是为什么页与页之间要用双向链表的方式相连。
2、建立目录：这个目录中存储这 一个页的页号及其对应的最大键值数
建立了这个目录后，我们就可以采用二分查找来去找目的数据所在的页号，这个目录的另一个名字就叫做索引
三、InnoDB的索引方案
前面所介绍只是一个简单的索引方案，我们通过建立确定目录的方式利用二分查找确实可以大大提高查询的效率，但会存在两个问题：
1、由于数据的量是不确定的，如果存储的数据越来越多，则目录表可能会非常大，这得需要一个很大的连续空间去记录，这样对于一个很大体量的数据库来说是不现实的
2、如果此时 页 34 中的数据被全部删除，那页目录也没有存在的必要，所以这里需要删除页 34 的目录，而删除之后就需要将之前的目录表进行调整，显然不是一个明智的选择。
所以针对这两点，InnoDB的解决方案是将索引的记录也存在页里面，因为从本质上讲，索引的数据同样是数据，也可以存在数据页中，那可以形成这样一种索引：
注意：
前面我们讲 InnoDB 分配空间的最小单位是页，所以图中一个磁块代表一个页，并且同一层的磁块见用双向链表相连
根据这个索引，我们可以模拟一下我们找主键为 10 的数据的步骤：
1、先找最上层，发现其最大为28，10 < 28,故数据是存在的
2、到第二层 发现 10 < 17, 则锁定在 磁块2（一般用二分查找），在磁块2里找到 10 < 17,故在磁块5中
3、到磁块5，也就是真正存储数据的地方，找到了主键为10的块
可以看到在查找中，通过一层一层的范围确定（二分查找），很快速的锁定了查询对象所在点页。这个就是 InnoDB 所采用的存储结构 B+树，我们总结一下其特点：
1、单个页里面的记录按照主键的大小顺序排成一个单向链表，页内的记录被分为若干组（每个组对应一个槽），每个组的最大值会被记录在槽里，我们可以根据二分查找来快速定位某个值
2、存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
3、存放目录的也分为不同的层级，在同一层级中的页也是根据页中目录项纪录的主键大小排序成一个双向链表
4、B+树的叶子节点存储的是完整的用户记录，而非叶子节点则是存的目录
而这种将用户记录都存储在叶子结点的方式就称为 聚簇索引，就是所谓的 “索引即数据，数据即索引”
三、继续深入
1、二级索引
前面的例子我们都是找主键，那可以不可以根据非主键来建立一个 B+树呢？ 可以 ，这样是 B+ 树建立的索引就是二级索引，结构与前面讲的 B+ 树一样，只不过二级索引中叶子结点存储的不在是完整的用户数据，而是存储的主键的值，为什么存储主键的值？
我们知道主键是唯一确定一条数据的指标，系统默认为主键建立了一个 B+ 树，同时将真实的数据已经存储在这个 B+ 树里面，但如果我们要再建立一颗 B+ 树就要复制一份这个真实的数据，但显然这会浪费空间，所以二级索引建立的 B+ 树，叶子节点存储的是 所要查询键对应的主键。再通过这个主键在主键所建立的 B+ 树查找真正的用户数据，这个过程称为 回表。
那考虑一个问题，如果二级索引（不是主键，可能重复）相等的情况下怎么排序？
答案是重复的部分通过主键来排序，二级索引其实相当于建立了一个 （主键，非主键）对联合索引（见下）
2、联合索引
我们同样可以以多个列的指标作为排序规则来建立一个 B+ 树，那两个指标在具体的页中如何排序呢？如 C2 和 C3 两个指标，首先按左边 C2 进行排序，如果C2出现重复，则重复的数据按 C3 进行排序。同样的，联合索引的叶子结点也是 主键 。
3、索引的代价
- 空间上的代价：空间上的代价显然是我们建立树的每一个非叶子结点都需要分配一个页，这会占用相当大的一片空间
- 时间上的代价：每当对表中数据进行增删改查时，都要修改 B+ 树的索引，而 B+ 树中的每一层都是按顺序排列的双向链表，而页中又是按照大小排列的单向链表，如果改一个，看你就要涉及到页面的分裂和页面的回收，肯定会影响系统效率。所以并不是索引越多越好，建索引要按照需求来合理的建立。
4、回表的代价
我们上面讲过，B+ 树的每层节点都是按双向链表连接，上一个节点在物理上没有必要和下一个节点相邻，但 InnoDB还是尽量让其在物理上也相邻，这样可以减少内存页换入换出的 I/O操作。所以对于一个 B+ 树来说，I/O操作代价是相对较少的。
但如果是二级索引，我们通过 非主键 查出的主键的顺序可能是毫无规律的，这样我们拿这些主键去 B+ 树查询真正的用户数据的时候，就会产生大量的内存页交换的操作，产生高昂的代价。有时候甚至不如不用索引。
那什么时候用索引？什么时候全表扫描？这就是查询优化器该做的工作
查询优化器会根据表中数据来计算需要回表操作的记录数，如果需要回表的操作越多，就越倾向于全表扫描，反正倾向与二级索引 + 回表
5、覆盖索引
为了彻底告别回表所带来，如无硬性的业务需求，建议在查询列表中只包含索引的列，这样就可以避免回表的操作，这种索引中已经存在需要读取的所有列的方式为覆盖索引。
