MySQL索引

目录

索引分类

优缺点

引用场景

索引的底层原理

B+树相比于B数优点

聚簇索引和非聚簇索引区别

回表查询与索引覆盖

索引失效

最左匹配原则

为什么使用最左匹配原则

列运算

函数方法

类型转化

使用 is not null

like查询是以%开头

---

索引是一种数据结构，使用索引可以快速查询和检索数据（相当于一本书的目录可以快速找到要查询的内容的位置）

索引分类

• 主键索引（primary key）：用于标识每一行数据的索引，每个表只能有一个主键索引
• 唯一索引（unique key）：唯一索引是用来保证列的唯一性的索引，一个表中可以有多个唯一索引
• 普通索引（index）：也叫非唯一索引，可以加快查询和排序操作
• 全文索引（full text）：是一种全文搜索的索引类型，能够对文本数据进行快速的模糊搜索和关键字搜索
• 复合索引：也叫多列索引或联合索引，能够加快多列查询和排列查询

优缺点

优点：

• 提高查询效率
• 避免全表扫描
• 增加数据的唯一性和完整性

缺点：

• 占据额外空间
• 降低写操作效率
• 出现索引失效

引用场景

• 频繁的用于条件查询的列
• 唯一性约束
• 经常用于排列的列

索引的底层原理

MySQL 中默认的是存储引擎 InnoDB 索引，底层原理使用 B+树 实现：B+树是一种N叉搜索树，非叶子节点存储的是索引字段的值，可以通过非叶子节点的索引值快速查定位到叶子节点的数据；叶子节点存储的是所有的数据信息，叶子节点之间使用指针链接，方便查询和排列操作

B+树相比于B数优点

• 查询效率：B+数 的非叶子节点不存放实际数据，仅存放索引，因此在数据量相同的情况下，相比于即存索引又存数据的 B数，B+数 的非叶子节点可以存放更多的索引，因此 B+数 可以比 B数 更矮胖，查询底层节点磁盘 I/O 次数回更少
• 删除效率：B+树 有冗余的节点，如果删除一个节点，可以直接从叶子节点删除，甚至不动非叶子节点，删除速率快；B树没有冗余节点，删除节点的时候非常复杂，比如删除根节点的数据，可能涉及复杂的树型变化
• 范围查询：B+树 中的所有叶子节点使用链表进行连接，对查询有很大帮助；B树 没有将所有叶子节点使用链表连接，查询只能通过树的遍历完成

聚簇索引和非聚簇索引区别

聚簇索引：简单来说就是指数据和索引放在一起，B+树 的叶子节点保存了完整数据。在 InnoDB 存储引擎中，每个表只能有一个聚簇索引，其余都是非聚簇索引；在 InnoDB 中，如果表定义了主键，则主键索引是聚簇索引；如果表中没有定义主键，则第一个唯一非空索引是聚簇索引；如果都没有，则 InnoDB 会隐式创建一个隐藏的聚簇索引

非聚簇索引：简单来说就是数据和索引分开存放，非聚簇索引叶子节点存储的并不是真正的数据，而是主键 ID，所以使用非聚簇索引进行查询，首先会得到一个主键 ID，然后通过主键 ID 去聚簇索引上找到真正的行数据。

叶子节点保存着索引字段和指向对应数据行的指针（相当于主键 ID），通过这个指针可以找到对应的数据行。在查询中使用的是非聚簇索引，则需要先根据索引查找对应的行指针，再通过指针查找数据行，这个过程叫做 回表查询

• 存储数据不同：聚簇索引是将数据存储在与索引相同的 B+ 树中；而非聚簇索引是将索引和主键 ID存储在 B+树 结构中
• 数量不同：一张表只能有一个聚簇索引；但可以有多个非聚簇索引
• 查询范围不同：聚簇索引中的数据与索引一一对应，更适合范围查询；而非聚簇索引需要济宁两次查找，首先查找索引，再查找数据

回表查询与索引覆盖

回表查询：通过非聚簇索引找到对应的主键值，然后再通过主键值找到聚簇索引中对应的整行数据，这个过程叫做回表查询

索引覆盖：select 查询语句使用了索引，在返回的列必须在索引中全部被能够找到，如果使用 id 查询，它会直接走聚簇索引查询，一次索引扫描直接返回数据，性能高；如果按照非聚簇索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用 select *，尽量在返回的列都包含添加索引的字段。简单来说就是把单列的非主键索引修改为多字段的联合索引，在一棵树索引上就找到了想要的数据，不需要去主键索引上再检索一遍。

索引失效

索引失效是指在 SQL 查询中，索引没有被使用到，这种情况下，MySQL 会进行全表扫描，查询效率会很低，甚至导致服务器负载过高。

索引失效原因：

• 未遵循最左匹配原则
• 使用列运算
• 使用函数方法
• 类型转换
• 使用 is nut null
• like查询是以%开头

最左匹配原则

最左匹配原则是指在联合索引中，由多个列组成的索引时，只能从索引的最左边的列开始进行查询。

例如，如果有一个联合索引为（A，B，C），那么只能按照顺序进行查询才能利用该索引：

• A
• A，B
• A，C
• A，B，C

不可以是：

• B，C
• C

例如：联合索引的顺序为：sex，age，name

SELECT * FROM user where age="4"; #未使用索引
SELECT * FROM user where name="2"; #未使用索引
SELECT * FROM user where sex="2" and age="3"; #使用索引
SELECT * FROM user where sex="2" and age="3" and name="4"; #使用索引
SELECT * FROM user where age="3" and name="4";  #未使用索引
SELECT * FROM user where sex="2" and name="4";  #使用索引

为什么使用最左匹配原则

例如，使用 （年龄，姓名，住址）为联合索引：

此时只有我们使用了最左匹配原则：(年龄)或(年龄,姓名)或(年龄,姓名,住址) 才能顺利的找到对应的数据，否则将无法使用上图中的联合索引进行高效的查询了

列运算

如果索引列使用了运算，那么索引也会失效：

将参与计算的数值先算好，再查询

select * from student where id = 2

函数方法

查询列如果使用任意 MySQL 提供的函数就会导致索引失效，比如以下列使用了 ifnull 函数之后的执行计划如下：

类型转化

如果索引列存在类型转换，那么也不会走索引，比如 address 为字符串类型，而查询的时候设置了 int 类型的值就会导致索引失效，如下图所示：

使用 is not null

当在查询中使用了 is not null 也会导致索引失效，而 is null 则会正常触发索引的，如下图所示：

like查询是以%开头

模糊查询 like 的常见用法有 3 种：

1. 模糊匹配后面任意字符：like '张%'
2. 模糊匹配前面任意字符：like '%张'
3. 模糊匹配前后任意字符：like '%张%'

而这 3 种模糊查询中只有第 1 种查询方式可以使用到索引，具体执行结果如下：