MySQL——索引

索引（重点）

什么是索引？索引是提升查询速度的一种数据结构

索引的优缺点

优点：使用索引可以加快数据的检索速度，这是创建索引的本质原因，通过创建唯一性索引，可以保证数据库中每一行数据的唯一性

缺点：

1. 索引本身需要存储起来，消耗磁盘空间。
2. 在运行的时候，索引会被加载到内存里面，消耗内存空间。
3. 在增删改的时候，数据库还需要同步维护索引，引入额外的消耗。

索引数据结构

索引底层数据结构有很多种类型，主要的有：B+树、B-树、Hash。在 MySQL 中，无论是 Innodb 还是 MyIsam，都是用了 B+树作为索引结构

B+树

B+树是一种多叉树，一个 m 阶的 B+树定义如下

1. 每个节点最多有 m 个子女。
2. 除根节点外，每个节点至少有 [m/2] 个子女，根节点至少有两个子女。
3. 有 k 个子女的节点必有 k 个关键字

两个重要特征：

1. 叶子结点存储数据，非叶子结点只存放了关键字
2. 叶子结点被双向链表串联起来了

用于数据库索引的三大优势：

1. 高度更低，树的高度代表了 IO 的次数，查询性能更好（对比红黑树）
2. 叶子结点都被串联起来了，适合范围查询，（对比哈希）
3. 非叶子节点没有存储数据，更适合放入内存中（对比 B-树）

哈希索引

哈希索引能以 O(1)的时间复杂度去查找，但是失去了有序性，它具有如下限制：

• 无法用于排序和分组
• 只支持精确查找，不支持部分和范围查找

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

R-Tree

MyISAM 存储引擎支持空间数据索引，可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询

倒排索引（不重要）

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射

索引分类

聚簇索引和非聚簇索引

1. 聚簇索引的B+树叶子结点存放的是主键值 + 完整的记录
2. 非聚簇索引的B+树叶子结点存放的是索引值 + 主键值

优缺点对比

优点：

• 索引即数据，不需要回表，当然非聚簇也不一定回表，但是聚簇索引一定不会回表

缺点：

• 更新代价大，聚簇索引存储完整数据，而非聚簇索引只存储主键值。

非聚簇索引优缺点相反

覆盖索引和联合索引

如果一个索引覆盖了所有需要查询的字段的值，我们称它为覆盖索引

为什么要用联合索引？

• 减少开销。建一个联合索引 (col1,col2,col3)，实际相当于建了 (col1)，(col1,col2)，(col1,col2,col3) 三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
• 覆盖索引。对联合索引 (col1,col2,col3)，如果有如下的 SQL：select col1,col2,col3 from test where col1=1 and col2=2;。那么 MySQL 可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机 IO 操作。减少 IO 操作，特别的随机 IO 其实是 DBA 主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
• 效率高。索引列越多，通过索引筛选出的数据越少。有 1000W 条数据的表，有如下 SQL：select from table where col1=1 and col2=2 and col3=3，假设假设每个条件可以筛选出 10% 的数据，如果只有单值索引，那么通过该索引能筛选出 1000W10%=100w 条数据，然后再回表从 100w 条数据中找到符合 col2=2 and col3=3 的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出 1000w * 10% * 10% * 10% =1w，效率提升可想而知！

当使用 EXPLAIN 分析 MySQL 查询时，可以通过以下方式来确定是否使用了覆盖索引（Covering Index）：

1. 查看 type 列：如果查询使用了覆盖索引，type 列的值通常会是 index 或 range。这表示查询使用了索引进行检索。
2. 查看 key 列：在 key 列中，如果显示了使用的索引名，那么查询使用了该索引。如果 key 列显示为 NULL，则表示没有使用索引

索引下推

索引下推（Index Condition Pushdown） 是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数

考虑一个简单的例子，假设我们有一个表 students，其中有一个索引 (age, name)。现在，我们要查询所有年龄为 20 岁且名为 "John" 的学生。

传统的查询过程是这样的：

1. 使用索引找到所有年龄为 20 的学生。
2. 取出这些学生的名字。
3. 再次检查这些学生的名字是否为 "John"。

如果没有索引下推，即使在第一步中已经排除了大部分学生，仍然需要检查第二步和第三步中的所有学生名字。

但是，有了索引下推，当检查到某个学生的年龄不满足条件时，就可以直接跳过后续的检查，因为该学生肯定不是我们要找的。这样，可以减少需要访问的数据量，从而提高查询效率。

索引失效

索引失效的七个场景：

“模型数空运最快”

模糊查询、数据类型不匹配、函数、空值、运算、最左前缀匹配、全表更快

2023 年 12 月 字节后端实习面试 如果查询的字段即在联合索引又在单列索引，先走哪个？为什么？

2024 年 1 月 字节后端实习面试 以 a、b、c 三列建立联合索引，查询 select * from table where a = x , c = y;这个时候会动索引吗？为什么？

参考博客 MYSQL | 最左匹配原则的原理-腾讯云开发者社区-腾讯云

最左前缀匹配原则

联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段后面的字段无法用到联合索引。但是，对于 >=、<=、BETWEEN、like 前缀匹配这四种范围查询，并不会停止匹配

索引使用场景

• 对于非常小的表，大部分全表扫描比使用索引更高效
• 对于中大型的表，索引非常有效
• 特大型的表，建立和维护索引的代价比较大。这种情况应该使用直接区分需要查询的一组数据，例如分区技术。