索引的本质
Mysql官方对索引（index）的定义是，用来高效获取数据的数据结构。说明了两个点，第一，索引是一种数据结构；第二，通过索引可以提高查询效率。
索引类型
按照数据结构类型分，大概有这样几种：
- hash索引
哈希表大家应该都比较熟，查询效率极高，O(1)，这是它的优点. 不过也有缺点，因为索引键（index key）经过hash之后变得无序，所以，没有办法保证查询的数据有序，也就不支持范围查询，而且也无法利用部分索引键查询。
- 全文索引（fulltext index）
在Mysql5.6之前，只有MyISAM引擎支持全文索引，之后InnoDB也支持了。它底层通过倒排索引来实现。什么意思呢，一般的索引，比如我们用文章的标题做index，然后检索出整篇文章；而倒排索引呢，则是利用文章里面的某些关键字来建立索引，这就叫做倒排索引，是各种搜索引擎的技术基础。目前在Mysql中，有char、varchar、text类型的字段可用于建全文索引。
底层是RTree，用于解决多维度空间的搜索问题，没用过，暂时还没仔细研究。。。
- BTree索引
这个是Mysql各种引擎里用的最多的索引结构，除了Mysql之外，像MongoDB的WiredTriger引擎也是用的Btree索引。到底是什么原因，让Btree索引这么受各种存储引擎的青睐呢？
Why BTree
在讨论BTree的特性之前，我们先看一下业务用的最多的查询场景：
- 根据一个或多个字段检索数据
- 某个字段需要根据范围查询
- 检索的数据结果需要按照某个字段排好序
- 有时需要分页查询
- 最最重要的一点，检索速度要尽可能的快
了解二叉查找树的同学都知道，像AVL Tree，RBTree，查找时间复杂度都是O(log2N)，而且能完全满足上述的查询场景，但是文件系统和数据库系统却没有使用它们作为索引结构，这是为什么呢？
- 内存的访问速度要比磁盘I/O高几个数量级，读取磁盘一次需要耗费10ms左右
- 一般文件系统和数据库系统面临的数据量都非常大，导致索引也非常大，无法全部装进内存，只能存储在磁盘上
这样一来，内存的访问时间基本可以忽略，检索数据的速度就完全取决于读取磁盘的消耗了，很明显访问磁盘次数越少，整个检索数据的速度就越快。
不难发现，对于树形结构，时间复杂度取决于树的高度，而由于二叉树的每个父节点的子节点最多只有2个，在数据量大的时候会导致整棵树非常高，也就意味着读取磁盘的次数非常多，因此检索效率就会非常低了。
那怎么样降低树的高度呢？下面我们分析下BTree及其变种B+Tree的实现。
1. BTree
首先，回顾一下BTree的定义：我们定义一个二元组(key, data)，key是键值，data是数据记录，如果所有data对应的key都不同，称这样的键为唯一键。用度定义的BTree：
- B树中每个内节点能包含的key有上界和下界，这个下界可以作为B树的最小度，d>=2；
- 每个内节点由n-1个key和n个指针组成， 其中n >= d且n <= 2d；
- 如果一个节点恰好有2d-1个key，那么说这个节点是满的；
- 叶节点最少有1个key和2个指针，且指针为null
- 所有叶节点具有相同的深度，即为树的高度h
- key和指针互相间隔，节点两端是指针
- key从左到右按非递减排列
- 指针所指向节点的key的范围必定是在与之相邻两个key值之间
我们看一个d=2的BTree示意图
由于BTree的特性，在BTree中进行数据检索的算法非常直观：首先在根节点内部按照二分法进行查找，如果找到则返回data，否则对区间内的指针指向的子节点进行递归查找。
2. B+Tree
B+Tree是BTree的升级版，被广泛用作存储引擎的索引结构。其最主要的改变是：
- 内节点不存储data，只存储key
- 叶子节点增加了顺序访问指针
- 所有数据地址都存储在叶子节点
B+Tree的优点：
- 内节点有没有data域，这样可以存储更多的key，使得树的整体高度进一步降低
- 有了顺序访问的指针，可以方便获取某个范围内的数据，只需先找到两个端点，然后利用指针在节点间遍历即可；
- 查询任意一个key的数据都需要到叶子节点，因此查询效率更稳定
B+Tree示意图
当然BTree在特定情况下也有一定优势，由于任一个内节点都包含了data域，因此如果查询的数据比较靠近根节点的话，效率会比较高，不过这个在实际中可操作性不强。
3. 局部性原理
根据空间局部性，靠近当前被访问数据位置的数据，很快也会被访问。由于磁盘访问的时间主要消耗在寻道和第一次旋转定位，而顺序读取的效率很高，读取1个字节的耗时，和读取1kB的耗时相差无几，所以磁盘会采取预读的策略，预读的长度一般为页的整数倍。
在虚拟存储器中，将主存视为磁盘的高速缓存，主存和磁盘之间以页为单位交换数据，通常一个页的大小为4KB。
InnoDB存储引擎也是以页为单位进行管理，默认大小16KB。
MyISAM引擎中的实现
MyISAM使用B+Tree作为索引结构，叶节点存储data的地址，也就是说，MyISAM存储引擎将数据和索引是分开存储的，这种索引方式称之为非聚集索引。
- 主键索引（Primary Key）示意图：
- 辅助索引（Secondary Key）示意图：
在MyISAM中，主键索引和辅助索引的存储结构上没有任何区别，只是主键要求key唯一，所以通过主键和辅助索引都可以直接检索到数据。检索数据的流程是：先按B+Tree搜索算法找到索引所在叶子节点，然后在节点内部用二分查找，找到对应的key，再根据data域指向的地址取出数据记录。
InnoDB引擎中的实现
InnoDB也使用B+Tree作为索引结构，不过与MyISAM有所不同的是，数据文件本身就是索引文件，叶子节点的data域完整的存储了数据记录，index key就是主键，所以这个数据文件本身就是主键索引。这也就解释了，为什么InnoDB要求必须有主键（如果没有显示指定，InnoDB会自动选择一个可唯一标识数据记录的列作为主键，如果没有这样的列，会隐式创建一个自增字段作为主键）。这种索引方式，称之为聚集索引。
- 主键索引示意图
那么问题来了，数据和主键索引存储在一起，那辅助索引怎么存？data域是再存一份数据呢？还是存数据的地址呢？
- 辅助索引示意图
InnoDB的辅助索引，data域选择的是存储主键，这样的话，通过辅助索引检索数据的时候，需要先找到主键，然后再通过主键索引才可以检索到数据记录，过程略显曲折。既然是这样的话，为什么不存数据的地址呢？大家可以想一下。
联合索引的实现
相比较单个索引，其实在实际业务情景中，基于多个字段创建的联合索引（复合索引）使用的更频繁，而且也往往是数据库出问题最多的地方。所以了解联合所以的实现原理是很有必要的。
可以看到，联合索引的key是按照字段从左往右的顺序依次进行排序。准确的说是这样，整个索引树按照联合key的第一个字段进行排列，当第一个字段值相等时，再按照第二个字段排列，以此类推。也就是说，从全局来看，只有第一个字段是有序的，其它所有字段只是在局部范围有序。
这种存储结构就引发了联合索引查询的一个最最重要的原则，即最左前缀匹配原则。怎么来解释这个原则呢，还是举例子吧。假设有个联合索引key(firstname, lastname, age，date)，分别看一下以下查询场景：
- select * from table where firstname = "Tom" and lastname = "Parker";
命中的索引长度为"firstname_lastname"
- select * from table where lastname = "Parker" and firstname = "Tom" ;
命中的索引长度为"firstname_lastname"，与顺序无关，引擎会优化
- select * from table where firstname = "Tom" and age = 20;
命中的索引字段为"firstname"，这样的话lastname和age两列都需要遍历
- select * from table where lastname = "Parker" and age = 20;
没有命中索引，需要全表扫描，说明查询语句必须包含联合索引的第一个字段，否则无法使用索引。
- select * from table where firstname = "Tom" and lastname = "Parker" and age > 20 and date = '2020-01-01'
命中的索引字段为“firstname_lastname_age”，说明索引有效性只能用到第一个范围列。
索引设计及优化策略
索引键不宜过大
在前面分析B+Tree的原理时就发现，树的高度决定了索引查询的效率，而在索引键数量一定的情况下，树的高度是与每个内节点所能存储key的数量成反比的，而节点的大小又是固定的，因此key越大，则索引树就会越高，查询效率相对就越低。
索引键最好保持单调递增
这个主要与插入索引的操作有关，由于每个节点所能存储的key的数量是有限的，一旦超过上限就会导致节点分裂，而且这个分裂会进一步往父节点传导。
分裂会涉及到key的拷贝和移动，而且会产生磁盘IO，影响数据库写操作的性能。如果是聚合索引，情况会更严重，因为叶节点不仅存储了key，还有数据记录，这样拷贝和移动的量就更大了。
如果每次插入的键能保证单调递增的话，那么当节点满的时候，会创建一个新的节点了，而不是从中间某个节点位置插入了，也就不会导致节点分裂了。
区分度小的字段不宜作为索引键
计算区分度 = (select count(distinct col)) / (select count(*))
为什么这么说呢，索引之所以能提高查询效率，是因为可以最大限度缩小数据的查询范围，比如主键索引，不管key的总量是多少，可能是单表千万级别的量，可是通过一次主键索引查询后，最多只有一条数据，这才是索引存在的意义。举个反例，比如说性别字段，取值就只有男、女两个值，如果之前是1000w条数据，经过索引查询后还有500w，量级并没有发生质的变化。而我们知道，索引也是需要存储空间的，既要磁盘空间，也需要消耗宝贵的内存资源，因此不建议做性价比很低的事情。
尽量使用联合索引
- 减少开销
联合索引key（a，b，c）等于同时拥有(a)，（a，b），（a，b，c）三个索引。每增加一个索引，除了增加存储空间，还会增加写操作的开销。
- 效率高
在复合查询场景下，联合索引能过滤掉更多的数据。比如，在同时用a和b作为查询条件时，索引key（a，b）可以先用a缩小查找范围，然后用b进一步缩小范围；而任一单个索引只能过滤一次。
- 覆盖索引
很多时候，业务进程在检索数据时，只需要返回固定的列，不需要返回整行数据，此时如果查询的列也在索引字段中，那么完全可以在索引树完成查询；相反，如果查询的字段不在索引字段中，那么必须要找到数据记录存储区域，这大大减少了随机I/O的次数。
最左前缀匹配原则
业务代码在写SQL语句时必须结合这个原理，最大限度利用索引查询的优势。
查询条件有表达式的字段无法使用索引
索引字段的排序方式尽量与业务逻辑保持一致
比如说，time作为联合索引的一个key，默认是按升序排列，而业务逻辑总是需要按time倒序排列，这样的话，存储引擎检索完数据之后还需要在内存重新进行排序，这是比较消耗CPU的；而如果创建联合索引的时候，将time设置为倒序排列的key，此时，检索完的数据就不需要重新进行排序了。
结语
Mysql索引原理及优化覆盖的范围非常广泛，如有描述错误的地方请及时指出，谢谢。
参考资料
《Mysql技术内幕》
