关于索引的最常见的十道面试题

lose_rose777

已于 2024-04-27 21:25:05 修改

阅读量3w

点赞数 27

分类专栏：面试题文章标签：面试职场和发展数据库 java

于 2024-01-21 21:53:21 首次发布

本文链接：https://blog.csdn.net/loss_rose777/article/details/135730177

版权

面试题专栏收录该内容

40 篇文章 1 订阅

订阅专栏

面试题一：索引底层如何实现的？

MySQL索引的底层实现是取决于存储引擎的，但是是大部分存储引擎底层都是通过B+树实现的，以默认的存储InnoDB为例，底层就是通过B+树实现的，如下图所示：

B+树是一种自平衡、多路搜索树，它的主要特征包含以下几点：

非叶子节点只存储键值和指向子节点的指针
所有数据都在叶子节点，并且用双向链表连接，便于查询
查询速度比较稳定，都是树的高度O（logn）

面试题二：InnoDB索引、MyISAM和MEMORY索引底层实现都一样呢？

InnoDB索引、MyISAM和MEMORY索引底层实现是不一样的，其中：

InnoDB索引底层是通过B+树实现的，并且叶子节点为整行数据
MyISAM索引底层是通过B+树实现的，但是叶子节点存储的十内存地址，要根据内存地址进行寻找才能找到行数据
MEMORY索引底层是实现并不是树，因为其主要为内存引擎，并且适合存储键值数据，所以使用的是哈希结构实现的索引

面试题三：默认引擎索引为什么使用B+树，其他数据类型不行呢？为什么？

既然作为索引那么查询效率必然是要放在第一位，而树比其他的数据结构查询效率更高。这时你可能说哈希索引查询效率更高为什么不用哈希呢，因为哈希索引不能进行范围性查找，所以不适合作为索引的底层

而普通的二叉搜索树的层级节点太少，这样意味着查找一个元素需要多次I/O操作。所以要使用多路搜索树（B树和B+树），这样层级节点会增加，查询节点的时候，I/O操作次数会减少，这样查找效率会提高

B+树比B树的优势：

I/O次数更少（查询效率高）：B+树的非叶子节点不存放实际记录数据，仅存放索引，因此数据量相同的情况下，相比又存放索引又存放数据的B树，B+树的非叶子节点可以存放更多的索引，因此I/O次数会更少，查询效率更高
范围查询效率更高：B+树叶子节点是使用链表连接起来的，有利于范围查询；而B树要实现范围查询，只能是用树的遍历来完成范围查询，这样会涉及多次I/O操作，效率不如B+树
插入和删除效率更高：B+树中有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让B+树让插入、删除的效率更高

面试题四：索引的类型有哪些？

普通索引：这个是最基本的索引，它没有任何限制。普通索引主要是以B+树和哈希索引尾椎，任务就是加快对数据的访问速度。例如：我们有一个员工表employees，我们想要根据员工的last_name来查找员工，我们可以创建一个普通索引：

CREATE INDEX idx_lastname ON employees (last_name);

唯一索引：与普通索引类似，不同的就是：索引列的值必须唯一，但是允许空值。例如：我们有一个用户表users，我们想要确保每个用户的email都是唯一的，我们可以创建一个唯一索引：

CREATE UNIQUE INDEX idx_email ON users (email);

主键索引：主键索引是MySQL中的一个特殊的索引类型，用于标识每一个表中的唯一行的索引。主键索引要求主键列中的每一个值都是唯一的并且不为空。例如：我们在创建employees表的时候，可以这样指定employee_id为主键：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(50)
);

全文索引：全文索引是MySQL中的一个特殊的索引类型，用于对文本字段进行全文的搜素，全文索引可以帮助加快对文本数据的搜索速度，并且支持全文搜索的高级功能，例如模糊搜索和关键字匹配。假设我们有一个博客文章表posts，我们想要根据文章的content进行全文搜索，我们可以创建一个全文索引：

CREATE FULLTEXT INDEX idx_content ON posts (content);

单列索引和多列索引：单列索引是指索引只包含单个列，一个表中可以有多个单列索引，但是每个单列索引只能包含一个列。多列索引（也称为复合索引、联合索引）包含两个或者多个索引。例如：在employees表中，我们可以为last_name创建一个单列索引，也可以为last_name和first_name创建一个多列索引：

CREATE INDEX idx_lastname ON employees (last_name);

CREATE INDEX idx_names ON employees (last_name, first_name);

聚簇索引和非聚簇索引：在 MySQL 的 InnoDB 引擎中，每个索引都会对应一颗 B+ 树，而聚簇索引和非聚簇索引最大的区别在于叶子节点存储的数据不同，聚簇索引叶子节点存储的是行数据，因此通过聚簇索引可以直接找到真正的行数据；而非聚簇索引叶子节点存储的是主键信息，所以使用非聚簇索引还需要回表查询。例如： student 表中有一个聚簇索引（也就是主键索引）id，和一个非聚簇索引 class_id

CREATE TABLE student(
    id INT PRIMARY KEY,
    name VARCHAR(16),
    INDEX(class_id)
);

面试题五：什么是最左匹配原则？为什么要遵循最左匹配原则？

最左匹配原则，也称为最左前缀原则，是指在使用联合索引（复合索引）进行查询时，查询条件需要遵循索引中列的顺序，从左到右进行匹配123。只有当查询条件满足最左前缀原则时，才能充分利用联合索引的优势，提高查询性能。当遇见范围查询（<、>、between、like）机会停止匹配，其中范围列可以用到索引，但是范围列后就无法使用索引，即索引最多用于一个范围列

例如，假设有一个联合索引 (a, b, c)，以下查询可以利用这个联合索引进行匹配：

select * from t where a = 1 and b = 1 and c = 1;
select * from t where a = 1 and b = 1;
select * from t where a = 1;

但是，如果查询条件不是从最左边的列开始，那么联合索引可能无法被充分利用。例如，select * from t where b = 1 and c = 1; 这样的查询就无法利用到定义的联合索引 (a, b, c)

遵循最左匹配原则的原因主要有以下几点：

减少开销：建立一个联合索引 (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 操作。

效率高：索引列越多，通过索引筛选出的数据越少。有 1000W 条数据的表，有如下 SQL：select from table where col1 = 1 and col2 = 2 and col3 = 3，假设每个条件可以筛选出 10% 的数据，如果只有单值索引，那么通过该索引能筛选出 1000W * 10% = 100w 条数据，然后再回表从 100w 条数据中找到符合 col2 = 2 and col3 = 3 的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出 1000w * 10% * 10% * 10% = 1w，效率提升可想而知。

面试题六：日常工作中，如何排查某个SQL是否正确使用了索引？

在日常工作中，我们可以使用explain关键字来查看SQL中查询的执行计划，从而判断是否正确使用索引。例如，如果我们有一个名为 users 的表，并且我们想要查询 id 等于 1 的用户，我们可以执行以下查询：

EXPLAIN SELECT * FROM users WHERE id = 1;

执行上述SQL语句，数据库会返回一个查询计划的结果集，其中包含查询的执行计划、使用的索引以及其他相关信息。查询计划通常包含以下重要信息：

type：查询块的连接类型，常见的类型包括ALL、index、range等。如果type列的值为ALL，则意味着查询未使用索引，可能对整个数据表进行全表扫描。如果type列值时index或range，则意味着查询使用了索引
possible_keys：可能使用的索引列表
key：实际使用的索引
rows：预估扫描行数
Extra：额外的信息，如对查询执行的描述

面试题七：索引失效的场景有哪些？

在MySQL中，有一些常见的场景可能会导致索引失效：

联合索引非最左匹配：当使用联合索引时，如果查询条件不遵循最左匹配原则（即查询条件不包含联合索引的最左边的列），那么索引可能无法被充分利用，也就是说，索引失效了。
不当模糊查询：LIKE 查询的模式字符串如果以 % 开头或者前后都有 %，那么索引可能无法被使用。只有当模式字符串以确定的字符开头，如 LIKE '张%'，才能使用索引。
使用列运算：如果查询条件中的索引列参与了运算，如 SELECT * FROM table WHERE age * 2 = 40;，那么索引可能无法被使用。
使用函数：如果查询条件中的索引列使用了函数，如 SELECT * FROM table WHERE UPPER(name) = 'Zhang';，那么索引可能无法被使用。
类型转换：如果查询条件中的索引列需要进行类型转换，如某列为字符串类型，而查询的时候设置了 int 类型的值，SELECT * FROM table WHERE name = 123;，那么索引可能无法被使用。
使用 IS NOT NULL：当在查询中使用了 IS NOT NULL，SELECT * FROM table WHERE name IS NOT NULL;，索引可能无法被使用，而 IS NULL 则会正常触发索引的使用。
使用 OR 操作符：当查询条件包含 OR 连接的条件，SELECT * FROM table WHERE a = 1 OR c = 1;，索引可能无法被使用，除非 OR 左右的查询字段都是索引。

面试题八：MySQL中索引和的约束有什么关系？

在MySQL中，索引和约束都是用于优化数据库性能和保证数据完整性的重要工具，但是它们的作用和使用方式有所不同：

索引是一种优化技术，它可以加快数据库的查询速度。在MySQL中，可以在列上创建索引，以便在查询的时候更快查询数据

约束是一种规则，它强制表中的数据满足特定的条件。约束的目的是为了保证表中的记录完整和有效。常见的约束类型包括非空约束（NOT NULL）、唯一性约束（UNIQUE）、主键约束（PRIMARY KEY）和外键约束（FOREIGN KEY）等

在某些情况下，索引和约束可以相互转换。例如，主键约束和唯一性约束在创建的时候会自动创建对应的索引。这是因为索引可以加快对这些约束的检查速度。然而，虽然索引可以提高查询效率，但是它并不能强制数据满足任何特定条件，这就是索引和约束的主要区别

面试题九：什么是索引覆盖？它给我们提供了什么启示？

索引覆盖是指查询语句可以完全按通过索引来满足，而勿需进一步访问表中的数据。当一个查询仅需要从索引中获取所需的数据列，而不需要访问表中实际数据行时，就称为索引覆盖。通过索引覆盖，可以减少对磁盘和内存的读取，提高查询性能例如，select id from table where age between 18 and 22，其中 id 为主键，而age 为二级索引，这时的 SOL只需要查询主键 id 的值，而 id 的值已经在 age 索引树上了，因此可以直接提供查询结果，不需要回表，这就叫做覆盖索引。
索引覆盖给我们的启示是，在实际工作中，能不使用 select *就不要使用 select *，因为 select *一定会进行回表查询，降低查询的效率，并且因为其包含的信息较多，所以也会增加网络带宽的负担，传输效率被拖慢等问题。

面试题十：什么是索引下推？为什么要有索引下推？

索引下推指的是在索引遍历的过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

假设我们有一个 employees 表，表中有 id、name 和 salary 三个字段，其中 id 和 salary 字段都有索引。现在，我们要执行以下查询：

SELECT * FROM employees WHERE id > 1000 AND salary < 5000;

在不使用索引下推的情况下，MySQL 会先使用 id 的索引找到所有 id > 1000 的记录，然后将这些记录的所有字段（包括 id、name 和 salary）都取出来，返回给 MySQL 服务器。然后，MySQL 服务器再判断这些记录中哪些记录的 salary < 5000。

而在使用索引下推的情况下，MySQL 服务器会将 salary < 5000 这个条件也一并下推给存储引擎。存储引擎在利用 id 的索引找到 id > 1000 的记录的同时，也会判断这些记录的 salary 是否小于 5000。只有当 salary < 5000 的记录，才会被取出所有字段并返回给 MySQL 服务器。

通过这个例子，你可以看到，索引下推可以减少存储引擎返回给 MySQL 服务器的记录数，从而减少了不必要的 IO 操作，提高了查询效率。