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

发布时间:2024年01月21日

面试题一:索引底层如何实现的?

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_namefirst_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中,有一些常见的场景可能会导致索引失效:

  1. 联合索引非最左匹配:当使用联合索引时,如果查询条件不遵循最左匹配原则(即查询条件不包含联合索引的最左边的列),那么索引可能无法被充分利用,也就是说,索引失效了。

  2. 不当模糊查询LIKE 查询的模式字符串如果以 % 开头或者前后都有 %,那么索引可能无法被使用。只有当模式字符串以确定的字符开头,如 LIKE '张%',才能使用索引。

  3. 使用列运算:如果查询条件中的索引列参与了运算,如 SELECT * FROM table WHERE age * 2 = 40;,那么索引可能无法被使用。

  4. 使用函数:如果查询条件中的索引列使用了函数,如 SELECT * FROM table WHERE UPPER(name) = 'Zhang';,那么索引可能无法被使用。

  5. 类型转换:如果查询条件中的索引列需要进行类型转换,如某列为字符串类型,而查询的时候设置了 int 类型的值,SELECT * FROM table WHERE name = 123;,那么索引可能无法被使用。

  6. 使用 IS NOT NULL:当在查询中使用了 IS NOT NULL,SELECT * FROM table WHERE name IS NOT NULL;,索引可能无法被使用,而 IS NULL 则会正常触发索引的使用。

  7. 使用 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 表,表中有 idnamesalary 三个字段,其中 idsalary 字段都有索引。现在,我们要执行以下查询:

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

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

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

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

文章来源:https://blog.csdn.net/loss_rose777/article/details/135730177
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。