面试八股之MySQL篇2——索引篇

Ethan Yankang

已于 2024-05-31 09:36:59 修改

阅读量649

点赞数 31

分类专栏： # MySQL 面试书籍网课笔记文章标签：面试职场和发展 mysql

于 2024-05-17 22:55:41 首次发布

本文链接：https://blog.csdn.net/m0_68657832/article/details/139014000

版权

面试同时被 3 个专栏收录

160 篇文章 1 订阅

订阅专栏

书籍网课笔记

38 篇文章 0 订阅

订阅专栏

MySQL

24 篇文章 0 订阅

订阅专栏

🌈hello，你好鸭，我是Ethan，一名不断学习的码农，很高兴你能来阅读。

✔️目前博客主要更新Java系列、项目案例、计算机必学四件套等。
🏃人生之义，在于追求，不在成败，勤通大道。加油呀！

🔥个人主页：Ethan Yankang
🔥专栏：史上最强八股文||Java项目

🔥温馨提示：划到文末发现专栏彩蛋点击这里直接传送

🔥本篇概览：详细讲解了MySQL索引的知识，聚簇索引、覆盖索引、回表查询、索引创建规则、索引失效情况。面试必备。

（3）不要在索引列上进行运算操作，索引将失效

（4）字符串不加单引号，造成索引失效。(类型转换)

（5）以%开头的Like模糊查询，索引失效

总结

1、数据结构对比

（1）B tree

（2）B+ tree

B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构

（3）总结

2、聚簇索引与非聚簇索引

（1）什么是聚簇索引和非聚簇索引?

聚簇索引主要是指数据与索引放到一块，B+树的叶子节点保存了整行数据，主键在作为聚簇索引的有且只有一个。
非聚簇索引值指的是数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个，一般我们自己定义的索引都是非聚簇索引。

（2）回表查询

回表的意思就是通过二级索引找到对应的键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表。

3、覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到

（1）超大分页问题

超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决

先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了
因为查询id的时候，走的覆盖索引，所以效率可以提升很多。

以下是一个覆盖索引的具体例子：假设有一个表 `students` ，包含字段 `id`（主键）、`name`、`age`。创建了一个索引 `idx_name_age` 包含 `name` 和 `age` 字段。现在有一个查询： `SELECT name, age FROM students WHERE name = '张三'` 。在这个查询中，通过索引 `idx_name_age` 就可以直接获取到满足条件的 `name` 和 `age` 信息，而无需再去查找表中的数据行，这就是覆盖索引发挥作用了。因为查询所需要的列都在索引中直接找到了，避免了回表操作，提高了查询效率。

（2）创建覆盖索引

要创建覆盖索引，可以按照以下步骤进行：

1. **确定需要创建索引的表和字段**：

确定要在哪个表上创建索引，以及要包含在索引中的字段。

2. **使用`CREATE INDEX`语句**：

使用`CREATE INDEX`语句来创建索引。

例如，如果要在表`students`的`name`和`age`字段上创建索引，可以使用以下语句： ```sql CREATE INDEX idx_name_age ON students (name, age); ``` 在上述语句中，`idx_name_age`是索引的名称，可以根据需要自定义。`students`是要创建索引的表名，`(name, age)`是要包含在索引中的字段列表。创建覆盖索引后，在查询中使用到这些索引字段时，数据库可以直接从索引中获取数据，而无需再进行回表操作，从而提高查询效率。

（3）总结

覆盖索引是指select查询语句使用了索引，在返回的列，必须在索引中全部能够找到，如果我们使用id查询，它性能高。会直接走聚集索引查询，一次索引扫描，直接返回数据

如果按照二级索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select*。

——————————————————
尽量在返回的列中都包含添加索引的字段

4、索引创建原则

（1）总结

大频、查询、联合、控制

（2）补充：联合索引

5、什么情况下索引会失效

（1）违反最左前缀法则

在 MySQL 中，当使用联合索引时，如果查询条件没有遵循最左前缀法则，可能会导致索引无法被充分利用或完全不能使用索引。

最左前缀法则是指在查询中要按照联合索引中字段的顺序依次使用条件。

例如，有一个联合索引 `(col1, col2, col3)`，如果查询条件中只使用了 `col3` 而没有使用 `col1` 和 `col2` ，或者使用 `col2` 和 `col3` 而没有 `col1` ，就违反了最左前缀法则，此时索引可能就不能发挥最佳效果或无法使用索引。

以下是一些违反最左前缀法则的常见情况示例：

```sql

-- 只使用了 col3，违反最左前缀法则 SELECT * FROM table WHERE col3 = 'value';

-- 先使用 col2 再使用 col1，顺序错误，违反最左前缀法则 SELECT * FROM table WHERE col2 = 'value' AND col1 = 'value';

```