MySQL索引相关知识点

问题引出：索引是什么？使用索引有什么好处？为什么要使用索引？
答：索引是一种能够帮助我们快速查找到数据记录的一种数据结构；
  假设我们现在有一张用户表user，包含（id，name，age）三个字段，现在需要执行以下SQL语句进行查询：

select * from user where name = "zhangsan"

  如果我们不使用用索引会发生什么呢？MySQL会从表中的第一行数据开始找name = “zhangsan”的记录，直到表中所有匹配的记录被找到。这种全表扫描的方式当然不可取，因为如果user表中的数据量很大的话，这种方式太费时间了，这是我们不能容忍的。
  所以我们聪明的程序员选择使用索引来加快查找速度。

思考1：为什么从查询数据的角度来决定是否要使用索引？而不是从修改数据、删除数据或者添加数据的角度？
答：因为在实际开发中，查询相关了业务占据了百分之八九十，而增删改只占了百分之一二十。

一、索引的底层实现

  有多种数据结构能够实现索引，为我们的查询提供便利。索引进化史
  索引到底怎么帮助我们来提升查找速度的呢？

（1）哈希索引

  MySQL底层的存储引擎支持哈希索引。首先什么是哈希索引，它通过一个哈希函数（或者叫散列函数），将索引列的值与此值所对应的地址相对应。如果在用户表的name字段创建哈希索引，则表中每一行用户记录的名字与这行记录的物理地址存在一个关系映射，如下图所示。

思考2：哈希索引存在哪些局限性？哈希索引是否能够满足我们的需求？
答：（1）哈希索引只支持等值比较的查询。例如=、in()等条件。
而对于范围查询无可奈何，例如查询用户表中所有名字以‘A’开头的的用户，
因为在用户名哈希索引对应的哈希函数中，没有这种映射规则，其他范围查询类似。
（2）既然有哈希函数，那么当表中的数量足够大时，一定会出现哈希冲突。处理哈希冲突也需要耗费很多资源。
当然哈希索引还有其他很多的局限性，但是就这两点就可以说明它并不能够满足我们的查询需求。

（2）查找树（排序树）

  我们是否可以使用树这种数据结构将表中的数据组织起来呢？答案当然是肯定的。

二叉查找树

  二叉查找树是一种特殊的二叉树，我们可以利用这棵树快速定位到想要查询的数据。二叉查找树的百度定义
  如下图是一个二叉查找树，它具有以下特性，例如节点62，它左边的节点都比它小，右边的节点都比它大。其他的节点也是如此。
  我们就可以利用二叉查找树来当做我们索引的底层存储结构。假如我们为user表中的id字段创建了索引，那么user表中的用户数据就会像下图一样被组织起来（省略了name和age列）。
  如果我们要执行以下的SQL查询语句：

select * from user where id = 51

那么具体的流程是什么样的？
62>51，向当前对比节点的左子树寻找；
58>51，向当前对比节点的左子树寻找；
47<51，向当前对比节点的右子树中寻找；
51=51，bingo，找到。
一共只需要对比4次即可找到正确结果，这可比全表扫描快。

普通二叉查找树

  上述二叉查找树即为普通的二叉查找树。

思考3：将表中的数据像普通二叉查找树这样组织起来能够满足我们的需求吗？有什么弊端？
答：普通二叉查找树在一定程度上减少了磁盘IO的次数，加快了查找的速度，但是还是不能满足我们的需求，主要有以下两点原因；
（1）当表中的数据足够多时，这种树的高度会很高，而树的高度log(n)代表着一次查询要对磁盘的的平均IO次数，磁盘IO次数越少则查找效率越高；
（2）这种树在一定概率上会退化成链表，使查找的时间复杂度变成O(n)；
综上，普通的二叉查找树并不能满足我们的需求。

平衡二叉树和红黑树

  平衡二叉树以及红黑树解决了普通二叉查找树会退化成链表的问题；但是没有解决当表中数据足够多时，树的高度依然很高的问题。所以它俩仍然不能满足我们的需求。

多叉查找树

  顾名思义，多叉查找树就是在树的一个节点内部放多个数据。这样做的好处就是我们可以将查找树变的更加扁平化，进而降低了树的高度，以及减少了每次查询的平均磁盘IO次数。B树以及B+树是两种典型的多叉查找树。

B树

假设表中有以下数据，并且在id字段建立了B树索引，则表中的数据会像下图那样被组织起来。
B树的特点：（1）每个节点可以存储多个数据；（2）每个节点内的数据按照索引字段有序排列。
  如果我们查询id为5的用户是怎么样一个过程：首先找到B树的根节点，在节点内部使用二分查找算法定位到根节点的最右子树，然后在最右子树中再使用二分查找算法定位到id为5的用户。显而易见，B树在一定程度上减少了磁盘IO次数，使得查找效率相对于上述二叉查找树又有了提升。

  B树详解

思考4：B树是否已经可以满足我们的需求？它存在哪些不足？
答：虽然B树在二叉查找树的基础上变的更加扁平化，在查找时可以大大减少磁盘IO次数，但是仍然存在以下不足之处：
（1）B树的查找效率不稳定。当要查找的数据距离根节点较近时，则速度很快；
但是当目标数据距离根节点较远的时候，则速度会很慢；
（2）对于范围查找，B树则变的力不从心了。
例如上图中，我们要查找id大于等于3，小于等于6的用户，B树首先找到id为3的用户，然后再回到根节点，以此类推继续找到剩余的用户。
[参考链接](https://zhuanlan.zhihu.com/p/602957325)
（3）B的高度还有下降的空间。

B+树

  虽然B树已经在很大程度上减少了磁盘IO次数，但是还是存在上述的三个问题。B+树的出现就是为了解决这三个问题的。
  B+树相对于B树的特点：（1）非叶子节点只存储索引列的值；（2）叶子节点通过双向链表的数据结构相连；
它是如何解决B树遗留下来的问题的呢？
（1）对于每次查询，B+树都要求其走到叶子节点才能拿到数据，因为非叶子节点不存储真实的数据，只存储索引列数据。这样就解决了B树遗留的查询效率不稳定的问题；
（2）对于范围查找，B+树也能顺利解决，因为其叶子节点通过双向链表进行相连。假设还是同样的问题，要查找id大于等于3，小于等于6的用户，则B+树首先到根节点，根据二分查找算法定位到id等于3的用户所在的节点；然后在该节点内找到了id等于3的用户数据，由于叶子节点通过双向链表相连，则此时不再需要再向上寻求根节点的帮助，而是直接通过双向链表来找到下一个id大于等于3的用户，直到找到最后一个符合范围的用户；
（3）对于高度问题，其实已经被我们无形中解决了。因为B+树的非叶子节点中已经不在存储真实的数据，所以这部分腾出来的空间， 我们可以存储更多的索引列值，进而将这棵树变的更加扁平化，减少磁盘IO次数。

B+树详解

思考5：B+树是完美的吗？
答：我们可以看到由于B+树的特点，索引列的值在非叶子节点进行了冗余；（以空间换时间）
    B+树复杂的存储结构，使得我们在进行数据的增删改时，提高了不少代价；
个人理解：B+树是我们在真实应用中，为了更好的满足开发需求而选择的一种折中的索引数据结构。

二、索引类型区分

主键索引

MySQL会自动为一张表创建一个主键索引，索引列为主键值。

普通索引

以非主键列的列创建的索引。

唯一索引

索引列的值必须具有唯一性，主键索引就是唯一索引。

聚簇索引

MySQL为每一张表创建的主键索引就是一种聚簇索引。它的特点就是在B+树的叶子节点存储真实的数据。

非聚簇索引（二级索引）

非聚簇索引在叶子节点存储的并不是真实的数据，而是每个数据的主键值。在使用非聚簇索引时的步骤是这样的：先通过非聚簇索引B+树查找到数据的主键值，然后再到聚簇索引B+树上通过主键值查找到真实的数据，这个过程就叫回表。

单列索引

以表中的单个字段值创建的索引为单列索引

联合索引

用表的多个字段创建的索引称为联合索引。联合索引的B+树底层实现详解
在使用联合索引时要格外注意，要不然很容易出现索引失效的后果。

覆盖索引

假设我们在表的name字段创建了索引，则执行以下语句会触发覆盖索引：

select name from user where name = "lisi"

这种不需要回表的查询操作，叫做覆盖索引。

三、索引失效

索引的底层是B+树，在进行查询时，我们需要利用这课B+树来缩小查找的范围。所以一句话概括：任何没有使用这颗B+树来帮助我们缩小查找范围的操作，都将导致索引失效。
例子一：假设你在id字段有一个主键索引，而你的查询条件中没有限制id字段：

select * from user where age = 18

例子二：假设你在name字段建立了索引，但是你的查询条件以 %或者_开头。这颗B+树对name字段的排序规则肯定是这样的，比如先按照首字母排序，再按照第二个字母排序，以此类推。你用_通配符开头必然导致索引失效，因为你没有按照他的排序规则合理的使用name索引，它不知道你的第一个字符到底是a、b还是c，不能按照已经生成的B+树来给你缩小查找范围。

select * from user where = "_aaa"

例子三：假设你在（name，age）字段创建了联合索引，不满足最左匹配原则的查询会导致索引失效，例如以下查询：

select * from user where age>18

思考6：查询语句 slect * from user where age=18 and name="chao" 
会导致索引失效吗？
答：不会，MySQL底层会把我们的sql语句优化成 
slect * from user where name="chao" and age=18

在实际应用中，可以使用explain关键字来查看查询语句是否走了索引。
还有太多导致索引失效的例子，这里不一一举例了，具体请查看参考链接。

参考视频链接：
索引失效
索引底层原理

四、SQL优化

（1）避免使用select *
（2）避免多次回表
（3）避免多表联查
（4）在经常在查询条件中使用的列上创建索引
（5）正确并且高效的使用索引
参考视频链接：
SQL优化
参考书籍：《高性能MySQL 第三版》