MySQL索引相关知识学习心得

你知道的越多，你不知道的也就越多 —— 芝诺曾

一、MySQL索引学习

MySQl主要有两种类型的索引：哈希索引、B+树索引

1、哈希索引

哈希索引可以以O（1）的时间复杂度进行查找，但是这样查找导致其失去了有序性，无法用于排序和分组、只支持精确查找，无法用于部分查找和范围查找

自适应哈希索引：

该概念为InnoDB存储引擎的特殊功能，当某个索引值被频繁使用的时候，就会在B+树索引之上再创建一个哈希索引，这样就可以让B+树索引拥有一些哈希索引的优点，比如快速的哈希查找。

2、B+树索引

是大多数 MySQL 存储引擎的默认索引类型。

因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。

因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组。

可以指定多个列作为索引列，多个索引列共同组成键。

适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

B+树索引可以分为聚簇索引和非聚簇索引。

聚簇索引：主索引的叶子结点上保存着完整的数据记录

非聚簇索引：索引的叶子结点上记录的是主键的值，在使用其进行查找的时候，需要先找到主键的值，然后再到主索引中进行查找，这个过程被称为回表。

聚簇索引和非聚簇索引的特点及区别：

聚簇索引有唯一性：因为其是把数据和索引结构放到一块的，因此一个表中只有一个聚簇索引
聚簇索引适合用在排序的场合，非聚簇索引不适合
取出一定范围的数据的时候，比较适合使用聚簇索引
可以把相关的数据保存到一起，在实现电子邮箱的时候，可以根据用户ID来聚集数据，这样只需要从磁盘中读取少数的数据页就可以获取某个用户的全部邮件
聚簇索引的劣势：维护索引很昂贵，特别是插入新行或者主键被更新导致需要进行分页的时候

二、常见的索引相关数据结构

1、B+树

B+树包含2种类型的节点：内部结点（又叫做索引结点）和叶子结点。其与B树的最大的区别为内部节点不保存数据，只用于索引，所有的数据以及记录都保存在叶子结点当中。

其内部结点的key都按照从小到大的顺序进行排列，对于内部结点中的一个key，左树中的所有的key都小于它，右树中的所有的key都大于它，叶子结点中的记录也是按照key的大小进行排列的。

更多内容请见：https://zhuanlan.zhihu.com/p/54102723

B+树的优点

B+树的磁盘IO更低；
B+树的查询也更加稳定（因为数据存储在叶子结点中，因此所有关键字的查询都必须是从根结点到叶子结点这样一条完整的路，所有的关键字查询的路径是相同的，因此每一个数据的查询效率都是一致的）；
B+树的元素遍历效率更高，B+树只要遍历叶子结点就可以实现整棵树的遍历。

三、特殊类型的索引

1、覆盖索引

覆盖索引即从非主键索引中就能查到的记录，而不需要去查询主键索引中的记录，避免了回表的产生进而减少了树的搜索速度，可以显著提升性能。

个人理解：从非主键数据中查询主键的值（可以这样理解，但是要注意聚簇索引中存储的索引不一定是主键）

2、联合索引

这个和覆盖索引很像，还是一个上面的学生表为例，我们建立了name和age的联合索引，通过name可以直接查询到age，不需要回表操作。

要说联合索引和覆盖索引的区别呢？可以说覆盖索引是联合索引的最优解。

联合索引遵循最左侧匹配原则，比如(name,age)，索引可以是name或者name和age的组合，看，后面这种情况就是覆盖索引，所以说，覆盖索引是联合索引的最优解。

3、最左前缀原则

在mysql建立联合索引的时候会遵循最左前缀匹配的原则，即最左侧的优先，在检索数据的时候从联合索引的最左边开始匹配。

很多时候我们就可以根据最左前缀原则判断当前的查询能不能命中我们设定的索引。

4、索引下推

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件。

在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

索引下推在非主键索引上的优化，可以有效的减少回表的次数，大大提升了查询的效率。

5、前缀索引

前缀索引顾名思义，定义字符串的一部分内容当作索引，而不是把整个字符串都当作索引。默认的，如果我们创建索引的语句不指定前缀的长度的时候，那么这个索引就会包含整个字符串。

四、原文链接

https://blog.csdn.net/SeekN/article/details/118524933?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522166297664516782391822775%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=166297664516782391822775&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_alltop_click~default-2-118524933-null-null.142^v47pc_rank_34_2,201^v3control_1&utm_term=MySQL%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B4%A2%E5%BC%95&spm=1018.2226.3001.4187

巴特西