MySQL：索引知识点盘点

本篇内容包括：MySQL 索引的概述 、索引的数据结构、索引的分类、索引的失效以及索引的设计原则

一、MySQL 索引的概述

1、什么是索引

在关系数据库中，索引是一种数据结构，为存储引擎提高访问速度的数据结构，它一般是以包含索引键值和一个指向索引键值对应数据记录物理地址的指针的节点的集合的清单的形式存在。

索引的作用：数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树的高度一般在 2-4 层，最多只需要读取 2-4 次磁盘，查询速度大大提升。

2、索引的适用与不适用场景

索引的适用场景：

1. 经常用于查询的字段；
2. 经常用于连接的字段建立索引，可以加快连接的速度；
3. 经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度。

索引的不适用场景：

1. where 条件中用不到的字段不适合建立索引；
2. 表记录较少；
3. 需要经常增删改；
4. 参与列计算的列不适合建索引；
5. 要参与计算的列级区分度不高的列（如性别）。

二、MySQL 索引的数据结构

1、MySQL 索引的数据结构

MySQL 索引的实现采用的是 B+ 树，B+ 树是 B- 树的变体，也是一棵多路搜索树。

B+ 树相较于 B- 树最主要的特点是：

• 数据只出现在叶子节点
• 所有叶子节点增加了一个链指针

在 B+ Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的key值数量，降低 B+ 树的高度。

B+ 树的叶子节点上有指针进行相连，因此在做数据遍历的时候，只需要对叶子节点进行遍历即可，这个特性使得 B+ 树非常适合做范围查询。

2、其他类型的索引特点及其缺点

二叉树索引：左子节点小于父节点，右子节点大等于父节点；索引会受插入顺序影响，增加复杂度，比如插入的是已排序数据时，导致二叉树变成了链表；，查找复杂度为 N；

红黑树索引：自平衡二叉查找树，每次新增数据，都会进行大量的平衡判断，数据量特别大的时候，红黑树的深度也会很大，再进行搜索时会比较耗时；

HASH 索引：基于Hash表，在确定值的情况下，对于读写定位都很快；区间范围查找困难，比如大于、小于、between，order by 和 group by也很困难；

B-Tree 索引：一个节点包含多个索引，这样树的高度可控。我们知道 B+ 树就是 B- 树的变体，之所以不采用 B- 树，是因为B树每个节点中不仅包含数据的 key 值，还有 data 值。而每一个页的存储空间是有限的，如果 data 数据较大时将会导致每个节点能存储的 key 的数量很小，要保存同样多的 key，就需要增加树的高度。树的高度每增加一层，查询时的磁盘 I/O次 数就增加一次，进而影响查询效率。

三、索引的分类

1、聚簇索引与非聚簇索引

MySQL 的索引分类有多种方式，其中按存储方式来区分的话，就包括聚簇索引（clustered index）和非聚簇索引（secondary index，也称辅助索引或普通索引）

• 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
• 非聚簇索引：将数据存储于索引分开结构，索引结构在叶子节点指向了数据的对应行

此外 MySQL 索引的按照不同类型还可以分为：主键索引与辅助索引、联合索引、前缀索引、覆盖索引、全文索引之类的

2、主键索引与辅助索引

主键索引：叶子节点保存数据

辅助索引：叶子节点保存主键值

3、前缀索引

前缀索引也叫局部索引，比如给身份证的前 10 位添加索引，类似这种给某列部分信息添加索引的方式叫做前缀索引。

4、联合索引

联合索引：MySQL可以使用多个字段同时建立一个索引，叫做联合索引；

最左前缀匹配原则：在 MySQL 建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。比如我们配置了一个 A、B、C 三个字段的联合索引，我们用 A、AB、ABC 的方式都是可以走到联合索引的，但如果是 AC、BC、C 的这种情况则不会使用索引。

5、覆盖索引

覆盖索引就是一种特殊的联合索引

我们如果直接用主键查找，用的是聚集索引，能找到全部的数据。如果我们是用非聚集索引查找，如果索引里不包含全部要查找的字段，则需要根据索引叶子节点存的主键值，再到聚集索引里查找需要的字段，这个过程也叫做回表

覆盖索引指的是在⼀次查询中，如果⼀个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为覆盖索引，⽽不再需要回表查询。

6、全文索引

全文索引与普通的索引不是一回事，在查找上方面其效率是普通模糊（like）查询和 N 倍，是 MySQL专门提供用作搜索引擎的。

7、自适应哈希索引

自适应哈希索引是Innodb引擎的一个特殊功能，当它注意到某些索引值被使用的非常频繁时，会在内存中基于B-Tree所有之上再创建一个哈希索引，这就让B-Tree索引也具有哈希索引的一些优点，比如快速哈希查找。这是一个完全自动的内部行为，用户无法控制或配置，但如果有必要刻意关闭该功能。

四、相关知识点

1、索引的失效

常见的索引失效情况：

1. 列于列的对比：例如：某个表中，有两列 id 和 c_id 都建了单独索引，Where 条件后为 id=c_id，这种情况会被认为还不如走全表扫描；
2. 存在 Null 值条件：如果索引列是可空的，是不会给其建索引的；
3. 存在 Not 条件：当查询条件为非时，索引定位就困难了，执行计划此时可能更倾向于全表扫描;
4. Like 通配符：前匹配的情况下，执行计划会更倾向于选择全表扫描。后匹配可以走 INDEX RANGE SCAN。所以业务设计的时候，尽量考虑到模糊搜索的问题，要更多的使用后置通配符；
5. 条件上包括函数：查询条件上尽量不要对索引列使用函数，因为索引在建立时会和计算后可能不同，无法定位到索引。但如果查询条件不是对索引列进行计算，那么依然可以走索引；
6. 复合索引前导列区分大：当复合索引前导列区分小的时候，我们有 INDEX SKIP SCAN，当前导列区分度大，且查后导列的时候，前导列的分裂会非常耗资源，执行计划想，还不如全表扫描来的快，然后就索引失效了。
7. 数据类型的转换：当查询条件存在隐式转换时，索引会失效。比如在数据库里 id 存的 number 类型，但是在查询时，却用了下面的形式：select * from sunyang where id='123';

2、索引的设计原则

索引设计不合理或者缺少索引都会对数据库和应用程序的性能差生障碍，高效的索引对对获得良好的性能非常重要，设计索引是要考虑下下准则：

1. 索引并非越多越好，大量的索引不仅占用磁盘空间，而且还会影响 insert,delete,update 等语句的性能，因为当表中的数据更改时索引也会相应的调整和更新
2. 避免对经常更新的表做更多的索引，并且索引中的列尽可能少；对经常用于查询的字段创建索引，避免添加不必要的索引
3. 数据量少的表尽量不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果
4. 在条件表达式中经常用到不同值较多的列上创建索引，在不同值很少的列上不要建立索引。比如性别字段只有“男”、“女”俩个值，就无需建立索引。如果建立了索引不但不会提升效率，反而严重减低数据的更新速度。
5. 在频繁进行排序或者分组的列上建立索引，如果排序的列有多个，可以在这些列上建立联合索引。