MySQL索引详解

---

前言

学习MySQL索引前，我们应该都了解一下B树，B+树，主要是B+树的原理和实现方式，因MySQL的索引存储就是基于B+树实现的
如果不了解，可以去看看这篇文章，先了解一下

【关于二叉树、二叉查找树、平衡二叉树、红黑树、B树、B+树的区别和特性】

---

一、索引是什么？

索引是数据库中的一种数据结构，占用物理空间。
索引是数据表数据的引用指针，可以快速访问数据库中的数据，相当于书中的目录，可以根据目录快速查询数据。

---

二、索引优缺点

1、索引优点

• 减少数据的全面扫描，只需通过索引即可快速锁定数据，极大提高了检索效率。
• 加速表与表之间的连接。对数据中的参照完整性的实现具有重要意义，而参照完整性规则定义了外键与被参照的主键之间的引用规则
• 根据索引分组和排序时，可以加快分组和排序所需时间

2、索引缺点

• 创建索引会占用磁盘空间
• 索引后期维护成本高，当对数据表中数据进行增加、删除和修改的时候，索引也需要动态的维护，加大了维护开销
• 创建索引和维护索引耗费时间，并且随着数据量的增加而逐渐增加

---

三、索引结构

1. B树

B树是一种自平衡多路查询树，对于树结构，树越高，从根结点向下I/O次数就更多，自然检索时间就更长，而B树多路查询可以存储更多的数据，减少树结构的深度。

特征如下：
1、所有非叶子结点最多M个儿子（M>2），即至多有M-1个关键字
2、除根结点以外的非叶子结点的儿子数为至少为M/2
3、任何一个关键字只出现在一个结点中
4、所有叶子节点都在同一层
5、自动层次的控制（B树的分裂机制，让B树在删除插入时，不会退化成线性结构）

2. B+树

1、B+树，可以说是B树的加强版本
2、B+树和B树不同的是，有如下几点：

• 非叶子结点都存储索引、所有关键字都在叶子结点出现，即叶子节点存储数据（这意味着B+树深度更低，可指向更多的数据，加快检索）
• 所有叶子结点有一个链指针，叶子结点中数据用链表把数据有序的链接在一起（更适合范围检索）
• 每个非叶子节点地址指针和关键字同样数量
• 非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树

3. Hash索引

1、Hash索引是基于内部定义的hash函数计算出该值的hash值来寻址。 所以hash索引非常适合等值检索，可以根据hash值快速定位到当前值。

2、hash索引是根据hash值寻址，当键值不一样，但是计算出的hash值是一样的，这就是所谓的hash冲突，所以我们明白，hash索引不支持范围检索，因为它不能保证计算后hash值的大小关系一致。
关于hash冲突在这不多解释，有空再给大家说说

四、索引分类

1. 按物理分类

MySLQ数据库现今默认使用InnoDB存储引擎

  物理分类：聚簇索引、非聚簇索引（非聚簇索引也叫二级索引或者辅助索引）

1.1、聚簇索引

• 聚簇索引是数据库中的一种存储数据方式，数据和索引存放在一起，使用B+树实现存储
• 数据库规定在一个表中只能有一个聚簇索引，通常默认主键就是聚簇索引。

如何理解数据和索引存放在一起？
你可以理解为创建了聚簇索引后，通常是主键，那么就能根据主键查询到该表的所以数据。

1.2、非聚簇索引

也是数据库一种数据存储方式，使用B+树实现存储，但行数据和索引是分开，不存放在一起。
假如我们建立一个表，以其中一个字段建立非聚簇索引，这列的数据包含A、B、C、D。
那么下图就很明显看出，非聚簇索引，只是包含了主键值，没有其他数据，而聚簇索引还包含其他完整的数据

1.3、非聚簇索引如何查询数据？

两种情况：
1、回表查询：先查询该字段数据，得到所需数据的主键值，再根据主键值依据聚簇索引查询其他数据
2、覆盖查询：把经常需要查询的数据创建联合索引，即可直接查询得到数据

1.3.1、回表查询

先查询该字段数据，得到所需数据的主键值，再根据主键值依据聚簇索引查询其他数据（查询R）

1.3.2、覆盖查询

覆盖查询通常会用到联合索引，只查询索引字段数据，它不用再去回表查询。当然，在建立联合索引时，我们需要了解是否合适创建联合索引，这个下文会讲到。

---

---

2. 按字段列数分类

  1、单列索引：主键分类、唯一索引、普通索引、前缀索引、全文索引
  2、联合索引：使用多列字段建立共同索引       

2.1、 单列索引

单列索引包含了主键索引、唯一索引、普通索引和前缀索引

2.1.1、主键索引

主键索引特点

1、一张表只可以有一个主键
2、不可以为null
3、不可以重复

通常在创建表之初，就用primary key定义了主键值

create table tableName (
    id int(4) not null,
    ....
    primary key(id)
);

2.1.2、唯一索引

唯一索引特点

1、该索引列值必须唯一
2、允许有空值
3、一张表可以多个唯一索引

create unique index 索引名 on 表名(列名);

扩展一下关于唯一键 unique key 和唯一索引的区别

• 索引是一种数据结构，用于加快数据库查询速度
• 唯一键用于作用于表中的某列或多列，确保列的数据唯一。
• 唯一键和索引的区别：唯一键是单个或多个列的组合，用于确保数据唯一性，而索引是提高检索速度和性能。

2.1.3、普通索引

普通索引和唯一索引差不多，但是普通索引的值可以重复、可以为空，一张表可以创建多个普通索引

create index 索引名 on 表名(列名);

2.1.4、前缀索引

前缀索引即设置数据开始的部分字符为索引。如果我们在在很长的字符列上创建索引，这会造成索引特别大且慢。所以可以以某列开始的部分字符作为索引，这样可以大大节约索引空间，从而提高索引效率。
比如txt、身份证号等此类数据，就可以设置前缀索引，以提高检索速度。

---

2.2、联合索引

使用两列以上字段建立共同索引，并遵循最左前缀原则，即从左往右依次执行。

create index 索引名 on 表名（列名1，列名2...）

---

五、索引失效

1、需要创建索引的时候

• 频繁需要查询且不易变动的字段
• 和其他表关联的字段。如外键建立索引
• 经常排序的字段（order by）
• 经常需要统计或者分组字段

2、不适合创建索引的时候

• 经常频繁改动的字段，比如经常增删改
• 没有辨识度的字段
• 很少使用的字段

3、索引失效的场景

失效场景通常指的是联合索引

3.1、违反最左原则

当我们建立联合索引时，因为B+树是有序排序存储的，根据最左原则，先根据a排序，然后才根据b排序
所以执行如下语句时，可以顺利执行，因为a，b都遵循最左原则

SELECT * FROM 表名 WHERE a = 1；
SELECT * FROM 表名 WHERE a = 1 and b = 2；

注意如果你写：b=1 and a=1 也可以执行，MySQL优化器会自动调整顺序
但是当你写成这样，就不能使用索引查询，失效了，直接走全表查询，因为你建立的索引B+树中，需要a才可以顺利查询，你a都没有，怎么查询

SELECT * FROM 表名 WHERE b = 1；

3.2、范围查询<>

为什么说范围查询了就失效了呢？
联合索引中第一个数据是有序的，可以查询，但第二个或者后几个是无序的，它只是基于第一个数据局部有序
以下图分析：

发现了以（a,b）建立联合索引时，首先是以第一个元素有序顺序来存储位置的，然后才根据b有序顺利存储。
当a=1时，b才会开始往后排，即b=1，b=2，得到（1，1）、（1，2）
当a=2时，a确定了，再看b，b=1，b=4，得到（2，1）、（2，4 ）
当a=3时，b=1、2 ，得出（3，1）、（3，2）
a整体是有序的（1、1、2、2、3、3），当是a确定了，b才是局部有序，整体而言，b是无序的

当我们执行如下语句时，先看a，a=(2,1)、（2、4）、（3，1）（3，2）得出 ：a整体有序，b=1、4、1、2是无序的，b整体是无序的
则b无序，所以B+树中不能直接按顺序得到b值，所以只能全表查询，导致索引失效

SELECT * FROM table WHERE a > 1 and b = 2;

当我们执行如下语句，则索引生效，因为a确定了，b是有序的

SELECT * FROM table WHERE a = 1 and b > 1;

3.3、模糊查询 like

还是回归最左原则，使用模糊查询时，大概语句如下，匹配首先遇到 %，第一个就不符合条件，自然不进行索引查询
记住这句话就行：以通配符%开头时，索引就会失效。但是如果你 % 在最后一位，则不影响，如 ”小牟%“

EXPLAIN SELECT * FROM employee WHERE username like '%n';   索引失效
EXPLAIN SELECT * FROM employee WHERE username like '%n%';   索引失效

EXPLAIN SELECT * FROM employee WHERE username like 'n%';   索引生效

3.4、字段使用函数

索引中保存的数据是表中的原始数据，而不是函数计算后的数据，对函数字段使用函数计算后，索引失效，
其中的索引字段隐式类型转换失效就是这个原理，在数据库中，MySQL在针对字符串和字符时，如果你不对数据加引号，它会自动把字符串转换为数字，这相当于对字段进行了先进行一次函数计算，然后才进行查询，效率低，MySQL优化器会选择全表扫描，所以此时索引会失效
如下图，此时字段 phone 为varchar类型，对这个字段加普通索引。

 EXPLAIN SELECT * FROM employee WHERE  password = '123457';   运行结果表示，索引生效

 EXPLAIN SELECT * FROM employee WHERE  password = 123457;     运行结果表示，索引失效

3.5、or连接

or 表示两者任意一条件符合即可。只要两个字段都单独设立索引时，索引才会生效，如果两个字段是联合索引，不会生效

3.6、order by 排序

当使用 order by 排序时，如果不是索引覆盖查询，那通常索引失效，因为它还需要回表查询，速度反而慢，MySQL优化器会选择全表扫描。如果是索引覆盖查询，数据是有序的，不再需要再次回表查询，这样索引就会生效。

---

#总结
以上内容如果有错误的地方，可以评论区指出，谢谢啦。