一文整理14道MySQL索引相关面试题

精心整理14道MySQL索引相关面试题（珍藏版）

如果仅仅是死记硬背MySQL索引相关面试题一定是相当枯燥的，不容易记却容易忘，这里循序渐进的讲解有关索引有关知识点，让大家在理解的基础上记住一些面试常问的点。

• 什么是索引？
• 索引的优缺点？
• 什么情况下需要建索引？
• 什么情况下不需要建索引？
• 索引有哪几种分类？
• 索引的数据结构有哪些？
• Hash索引和B+树索引的区别？
• 为什么B+树比B树更适合实现数据库索引？
• 聚集索引相对于非聚集索引的区别？
• 什么是回表查询？
• 什么是覆盖索引？
• 什么是最左匹配原则？
• 索引失效场景有哪些？
• 索引的设计原则？

什么是索引

索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据。

索引的优缺点

优点

• 提高数据检索的效率，降低数据库IO成本。
• 通过索引对数据进行排序，降低数据的排序成本，降低CPU的消耗。

缺点

• 建立索引需要占用物理空间
• 会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长

什么情况下需要建索引？

1. 主键自动创建唯一索引
2. 较频繁的作为查询条件的字段
3. 查询中排序的字段，查询中统计或者分组的字段

什么情况下不建索引？

1. 表记录太少的字段
2. 经常增删改的字段
3. 唯一性太差的字段，不适合单独创建索引。比如性别，民族，政治面貌

索引主要有哪几种分类？

MySQL主要的几种索引类型：1.普通索引 2.唯一索引 3.主键索引 4.组合索引 5.全文索引。

1. 普通索引: 是最基本的索引，它没有任何限制
2. 唯一索引: 索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
3. 主键索引: 是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。
4. 组合索引: 一个索引包含多个列，实际开发中推荐使用组合索引。
5. 全文索引: 全文搜索的索引。FULLTEXT 用于搜索很长一篇文章的时候，效果最好。只能用于InnoDB或MyISAM表，只能为CHAR、VARCHAR、TEXT列创建。

主键索引和唯一索引的区别：

主键必唯一，但是唯一索引不一定是主键；

一张表上只能有一个主键，但是可以有一个或多个唯一索引。

索引的数据结构有哪些？

索引的数据结构主要有B+树和哈希表，对应的索引分别为B+树索引和Hash索引。InnoDB引擎的索引类型有B+树索引和Hash索引，默认的索引类型为B+树索引。

Hash索引

哈希索引是基于哈希表实现的，当我们要给某张表某列增加索引时，存储引擎会对这列进行哈希计算得到哈希码，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是O(1)，一般多用于精确查找。所以在= in <=>(安全等于的时候）塔的效率是非常，但我们开发一般会选择Btree，因为Hash会存在如下一些缺点。

1. Hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。
2. Hash 索引无法被用来避免数据的排序操作。
3. Hash索引不能利用部分索引键查询。
4. Hash索引在任何时候都不能避免表扫描。
5. Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

B+树索引

相对于cpu和内存操作，磁盘IO开销很大，非常容易成为系统的性能瓶颈。为什么索引能提升数据库查询效率呢？根本原因就在于索引减少了查询过程中的IO次数。那么它是如何做到的呢？使用B+树。下面先简单了解一下B树和B+树。

B树

B树,这里的 B 表示 balance( 平衡的意思),B-树是一种多路自平衡的查找树，它类似普通的平衡二叉树，不同的一点是B树允许每个节点有更多的子节点。下图是 B树的简化图.

观察上图可见B树的两个特点：

1. 树内的每个节点都存储数据
   2.** 叶子节点之间无指针连接**

B+树

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

B+Tree相对于B-Tree有几点不同：

1. 非叶子节点只存储键值信息。
2. 所有叶子节点之间都有一个链指针。
3. 数据记录都存放在叶子节点中。

为什么B+树比B树更适合实现数据库索引？

1. B+ 树叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；B 树由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B+ 树的效率更高。
2. B+ 树更相比 B 树减少了 I/O 读写的次数。由于索引文件很大因此索引文件存储在磁盘上，B+ 树的非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字，即一次性读入内存的需要查找的关键字也就越多，磁盘的随机 I/O 读取次数相对就减少了。
3. B+树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

聚集索引相对于非聚集索引的区别？

聚集索引介绍

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

• 如果表设置了主键，则主键就是聚簇索引
• 如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE）的列作为聚簇索引
• 以上都没有，则会默认创建一个隐藏的row_id作为聚簇索引

聚集索引的叶子节点就是整张表的行记录。InnoDB 主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。

非聚集索引介绍

普通索引也叫二级索引，除聚簇索引外的索引，即非聚簇索引。

InnoDB的普通索引叶子节点存储的是主键（聚簇索引）的值，而MyISAM的普通索引存储的是记录指针。

示例

create table user(
    id int(10) auto_increment,
    name varchar(30),
    age tinyint(4),
    primary key (id),
    index idx_age (age)
    )engine=innodb charset=utf8mb4;

id 字段是聚簇索引，age 字段是普通索引（二级索引）

填充数据

insert into user(name,age) values('张三',30);
insert into user(name,age) values('李四',20);
insert into user(name,age) values('王五',40);
insert into user(name,age) values('赵六',10);
insert into user(name,age) values('田七',20);

mysql> select * from user;
+----+--------+------+
| id | name  | age |
+----+--------+------+
| 1 | 张三  |  30 |
| 2 | 李四  |  20 |
| 3 | 王五  |  40 |
| 4 | 赵六  |  10 |
| 5 | 田七  |  20 |
+----+--------+------+

聚簇索引存储结构

id 是主键，所以是聚簇索引，其叶子节点存储的是对应行记录的数据

非聚簇索引存储结构

age 是普通索引（二级索引），非聚簇索引，其叶子节点存储的是聚簇索引的的值

聚簇索引查询

如果查询条件为主键（聚簇索引），则只需扫描一次B+树即可通过聚簇索引定位到要查找的行记录数据。
如：select * from user where id = 3;

非聚簇索引查询

如果查询条件为普通索引（非聚簇索引），需要扫描两次B+树，第一次扫描通过普通索引定位到聚簇索引的值，然后第二次扫描通过聚簇索引的值定位到要查找的行记录数据。
如：select * from user where age = 40;

1. 先通过普通索引 age=40 定位到主键值 id=3
2. 再通过聚集索引 id=3 定位到行记录数据(就是上面这一步)

什么是回表查询？

上面的非聚簇索引查询就是回表查询。

先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。

什么是索引覆盖？

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

如果我们把上面的非聚簇索引查询的sql改下

select id,age from user where age = 30;

这个sql我们是不是就不用回表查询了，因为在非聚簇索引的叶子节点上已经有id和age的值。所以根本不需要拿着id的值再去聚簇索引定位行记录数据了。也就是在这一颗索引树上就可以完成对数据的检索，这样就实现了覆盖索引。

如果这个sql是

select id,age,name from user where age = 30;

那就不能实现索引覆盖了，因为name的值在age索引树上是没有的，还是需要拿着id的值再去聚簇索引定位行记录数据。但是如果我们对age和name做一个组合索引idx_age_name(age,name),那就又可以实现索引覆盖了。

什么是最左匹配原则？

如果我们创建了(age, name)的组合索引，那么其实相当于创建了(age)、(age, name)两个索引，这被称为最佳左前缀特性。因此我们在创建组合索引时应该将最常用作限制条件的列放在最左边，依次递减。

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

这是为什么呢？

我们这里以组合索引(age, name)来画一下索引树就明白了。

我们再仔细观察索引结构，可以看到索引key在排序上，首先按age排序，age相等的节点中，再按name排序。因此，如果查询条件是age或age和name联查时，是可以应用到索引的。如果查询条件是单独使用name，因为无法确定age的值，因此无法使用索引。

如果当我们创建好组合索引(age, name)，那么下面的sql还需要回表查询吗？

select id,age,name from user where age = 30;

答案是否定的，因为id,age,name字段，在这个索引树上已经都有了，我们也不需要拿着id的值再去聚簇索引定位行记录数据了。

所以在实际开发中如果你创建了(age, name)的组合索引，那就根本无需再去单独创建age的索引。同时也建议创建组合索引，只是在创建的时候需要考虑将最常用字段的列放在最左边，依次递减

索引失效场景有哪些？

理解了上面聚集索引相对于非聚集索引的树的结构，对于什么时候索引会失效，理解起来就不那么难了。

1. 组合索引未使用最左前缀，例如组合索引（age，name），where name='张三’不会使用索引;
2. or会使索引失效。如果查询字段相同，也可以使用索引。例如where age=20 or age=30（索引生效），where age=20 or name=‘张三’（这里就算你age和name都单独建索引，还是一样失效）;
3. 如果列类型是字符串，不使用引号。例如where name=张三(索引失效），改成where name=‘张三’(索引有效);
4. like未使用最左前缀，where A like ‘%China’;
5. 在索引列上做任何操作计算、函数，会导致索引失效而转向全表扫描;
6. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引;

索引的设计原则

• 索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差。
• 尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，查询速度更快。
• 索引不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。
• 利用最左前缀原则。

声明: 公众号如需转载该篇文章,发表文章的头部一定要 告知是转至公众号: 后端元宇宙。同时也可以问本人要markdown原稿和原图片。其它情况一律禁止转载！