【MySQL】索引篇

---

前言

整理一下MySQL索引相关的知识，以便构成自己的知识体系

---

一、索引是什么？

索引是一种加速查询的的数据结构，可以帮助我们优化数据的查询速度。
既然索引是数据结构，那么什么样的数据结构适合作为索引。
首先需要明确索引提供了哪些功能：等值查询，范围查询，排序等，还需考虑维护索引以及磁盘IO的花销

二、数据结构

1、最常见的数据结构数组作为索引时，可以使用有序数组加二分查找来实现索引的功能。这时等值查询的时间复杂度为O(logn)，范围查询也很方便。作为连续线性的数据结构，数据也可以一整块存到磁盘的某个数据页中以减少磁盘IO。但是增删改数据的时间复杂度为O(n)
2、使用哈希表作为索引时，增删改查的时间复杂度都是O(1)，但是不适用于范围查询和排序等功能
3、使用红黑树作为索引时，增删改的时间复杂度是O(logn)，对范围查询支持不是很好，且树的高度较高，查询数据所需IO次数较多
4、B-树相比红黑树的层数减少，IO次数减少，但对范围查询同样支持不是很好
5、B+树的数据存在叶子节点，支持等值查询和范围查询，且查询数据所需IO次数较少，个人感觉IO次数是MySQL使用B+树的最主要原因
6、跳表，跳表的增删改查时间复杂度都是O(logn)，且数据都能在跳表的最底层得到，因此也支持范围查询。但是B+树的层数在3层左右，而跳表的层数会比3大一些，所有跳表的IO次数是比B+树要高。但是跳表在插入和删除数据时不需要B+树的旋转来维持平衡，因此写入性能会比B+树好。

三、InnoDB中的索引

在InnoDB存储引擎中，使用了聚簇索引来存储数据。聚簇索引是指索引和行数据存储在一个文件中，而非聚簇索引则会把索引和数据分开存储，索引树的叶子节点会执行行记录所在的地址。

InnoDB中每个表都会有一个聚簇索引，一般是以主键为key，如果没有定义主键会生成一个隐式的主键作为索引树的key。
除了聚簇索引，在表的一些字段上创建的索引都是辅助索引，这些索引会在叶子节点存储那些建立索引的字段外加主键。
在辅助索引上查找数据时，1.会先在辅助索引找到符合条件的记录的主键；2.再回到聚簇索引查找整行记录，这个过程就是回表。

四、索引的查找算法

有一句话叫做“数据结构加算法就是一个计算机程序”，MySQL也是一个程序，也离不开数据结构和算法，而很多情况下，数据结构确定了，算法也基本确定了。
接下来我们看看怎么利用B+树实现上文所说的索引功能。以下算法不一定准确，更多的是个人理解。

等值查询
B+树是平衡搜索树，且节点内数据有序，可以根据key在节点内找到该key所在的范围，然后进入子节点继续查找，直到找到叶子节点就能判断该key是否在索引中。

范围查询
所谓范围就是说这个查询条件是一个区间，那么我们可以对这个区间的左端点做一个等值查询，查找到第一个符合条件的数据，然后就可以利用B+树叶子节点的链表一直遍历到不符合条件的数据为止。

join

• MySQL的join操作使用的是Nested-loop算法，即嵌套循环。假设将A表join B表，那么就需要在A表的聚簇索引上遍历整个表，每遍历一行数据，就拿出这行数据要join的字段。去B表扫描整表，找出能join到一起的B表行记录。
• MySQL对这个算法进行优化，不会每读一行A表就遍历整个B表，而是先读n行A表数据到内存，再遍历B表到内存中做join，这个算法叫Block Nested-Loop Join。
• 如果B表中要join的字段建立了索引，就可以利用这个索引优化内循环，把内循环变成O(logn)，该算法叫Index Nested-Loop Join

索引覆盖
前面提到在辅助索引上查找记录需要回表获取完整数据，但如果select的字段在辅助索引的叶子节点就能全部得到，就不用回表了，这个就叫做索引覆盖。

索引下推
假设t表在a,b字段上建立联合索引，查找条件是where a<10 and b=1，因为不符合最左匹配b字段的索引失效，只能根据a字段查找，MySQL会把辅助索引中所有a<10的记录扫出来，回到聚簇索引得到完整记录，然后交给server层判断where条件。而索引下推就是把在辅助索引得到的记录，提前通过b字段过滤一下，因为辅助索引也有b字段，从而增加效率。（感觉是一个很普通的东西，正常人都能想到，但是起了个吓唬人的名字- -。。。）

五、索引失效

多做索引查找的算法分析后，我们会发现，使用索引时我们依赖的是B+树的搜索树特性和叶子节点是链表的特性。
搜索树特性帮我们定位到符合条件的第一个记录，叶子节点的链表帮我们直接从第一个符合条件的记录遍历出所有符合条件的记录，不用我们每查一个记录都走一遍搜索树。
所有当符合条件的记录能通过这两个特性找出来的时候，我们能利用索引快速找出所有记录。

那索引失效的情况是怎样的？
1、搜索树特性失效，即我们拿到想查找的key，无法通过搜索树查找。例如在a,b字段建立索引，但是我们那c,d字段来查找。或者用b,a的组合去查找，这时你会发现我们没办法用这棵树来找到这个key。
2、链表特性失效，即符合我们查询条件的记录在叶子节点上不是连续的。例如在a,b字段建立索引，使用a<3 and b=1查询。假设有(1,1), (1,2), (1,3), (2,1), (2,2)这几条数据，我们发现(1,1)和(2,1)符合查询条件，但是不是连续的。因此索引会失效，只能拿a<3这个条件来找。

一般的文章谈索引失效都会提到一堆原则，但其实只要清楚你的数据在索引树上是怎么分布的，就可以分析索引是否会失效，也就不用去记那些最左匹配原则了。

---

总结

总结MySQL索引知识点