<返回更多

一线大厂面试的MySQL索引夺命连环15问

2023-05-08    dbaplus社群
加入收藏

我们准备了索引的15连问,相信大家看完肯定会有帮助的。

1. 索引是什么?

2. MySQL索引有哪些类型

数据结构维度

物理存储维度

逻辑维度

3. 索引什么时候会失效?

4. 哪些场景不适合建立索引?

5. 为什么要用 B+树,为什么不用二叉树?

可以从几个维度去看这个问题,查询是否够快,效率是否稳定,存储数据多少, 以及查找磁盘次数,为什么不是二叉树,为什么不是平衡二叉树,为什么不是 B 树,而偏偏是 B+树呢?

为什么不是一般二叉树?

如果二叉树特殊化为一个链表,相当于全表扫描。平衡二叉树相比于二叉查找 树来说,查找效率更稳定,总体的查找速度也更快。

为什么不是平衡二叉树呢?

我们知道,在内存比在磁盘的数据,查询效率快得多。如果树这种数据结构作 为索引,那我们每查找一次数据就需要从磁盘中读取一个节点,也就是我们说 的一个磁盘块,但是平衡二叉树可是每个节点只存储一个键值和数据的,如果 是 B 树,可以存储更多的节点数据,树的高度也会降低,因此读取磁盘的次数 就降下来啦,查询效率就快啦。

那为什么不是 B 树而是 B+树呢?

6. 一次B+树索引树查找过程

假设有以下表结构,并且初始化了这几条数据

假设有以下表结构,并且初始化了这几条数据

CREATE TABLE `employee` (

`id` int(11) NOT NULL,

`name` varchar(255) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

`date` datetime DEFAULT NULL,

`sex` int(1) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idx_age` (`age`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into employee values(100,'小伦',43,'2021-01-20','0');

insert into employee values(200,'俊杰',48,'2021-01-21','0');

insert into employee values(300,'紫琪',36,'2020-01-21','1');

insert into employee values(400,'立红',32,'2020-01-21','0');

insert into employee values(500,'易迅',37,'2020-01-21','1');

insert into employee values(600,'小军',49,'2021-01-21','0');

insert into employee values(700,'小燕',28,'2021-01-21','1');

执行这条查询SQL,需要执行几次的树搜索操作?可以画下对应的索引树结构图~

select * from Temployee where age=32;

其实这个,这个大家可以先画出idx_age普通索引的索引结构图,大概如下:

再画出id主键索引,我们先画出聚族索引结构图,如下:

这条 SQL 查询语句执行大概流程是这样的:

7. 什么是回表?如何减少回表?

当查询的数据在索引树中,找不到的时候,需要回到主键索引树中去获取,这个过程叫做回表。

比如在第6小节中,使用的查询SQL

select * from Temployee where age=32;

需要查询所有列的数据,idx_age普通索引不能满足,需要拿到主键id的值后,再回到id主键索引查找获取,这个过程就是回表。

8. 什么是覆盖索引?

如果我们查询SQL的select * 修改为 select id, age的话,其实是不需要回表的。因为id和age的值,都在idx_age索引树的叶子节点上,这就涉及到覆盖索引的知识点了。

覆盖索引是select的数据列只用从索引中就能够取得,不必回表,换句话说,查询列要被所建的索引覆盖。

9. 聊聊索引的最左前缀原则

索引的最左前缀原则,可以是联合索引的最左N个字段。比如你建立一个组合索引(a,b,c),其实可以相当于建了(a),(a,b),(a,b,c)三个索引,大大提高了索引复用能力。

当然,最左前缀也可以是字符串索引的最左M个字符。比如,你的普通索引树是酱紫:

这个SQL:

select * from employee where name like '小%' order by age desc;

也是命中索引的。

10. 索引下推了解过吗?什么是索引下推

给你这个SQL:

select * from employee where name like '小%' and age=28 and sex='0';

其中,name和age为联合索引(idx_name_age)。

如果是Mysql5.6之前,在idx_name_age索引树,找出所有名字第一个字是“小”的人,拿到它们的主键id,然后回表找出数据行,再去对比年龄和性别等其他字段。如图:

有些朋友可能觉得奇怪,idx_name_age(name,age)不是联合索引嘛?为什么选出包含“小”字后,不再顺便看下年龄age再回表呢,不是更高效嘛?所以呀,MySQL 5.6就引入了索引下推优化,可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

因此,MySQL5.6版本之后,选出包含“小”字后,顺表过滤age=28

11. 大表如何添加索引

如果一张表数据量级是千万级别以上的,那么,如何给这张表添加索引?

我们需要知道一点,给表添加索引的时候,是会对表加锁的。如果不谨慎操作,有可能出现生产事故的。可以参考以下方法:

(1)先创建一张跟原表A数据结构相同的新表B。

(2)在新表B添加需要加上的新索引。

(3)把原表A数据导到新表B

(4)rename新表B为原表的表名A,原表A换别的表名;

12. 如何知道语句是否走索引查询?

explain查看SQL的执行计划,这样就知道是否命中索引了。

当explain与SQL一起使用时,MySQL将显示来自优化器的有关语句执行计划的信息。

一般来说,我们需要重点关注type、rows、filtered、extra、key。

1.2.1 type

type表示连接类型,查看索引执行情况的一个重要指标。以下性能从好到坏依次:system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

1.2.2 rows

该列表示MySQL估算要找到我们所需的记录,需要读取的行数。对于InnoDB表,此数字是估计值,并非一定是个准确值。

1.2.3 filtered

该列是一个百分比的值,表里符合条件的记录数的百分比。简单点说,这个字段表示存储引擎返回的数据在经过过滤后,剩下满足条件的记录数量的比例。

1.2.4 extra

该字段包含有关MySQL如何解析查询的其他信息,它一般会出现这几个值:

1.2.5 key

该列表示实际用到的索引。一般配合possible_keys列一起看。

13.Hash 索引和 B+树区别是什么?你在设计索引是怎么抉择的?

14. 索引有哪些优缺点?

优点:

缺点:

15. 聚簇索引与非聚簇索引的区别

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。它表示索引结构和数据一起存放的索引。非聚集索引是索引结构和数据分开存放的索引

接下来,我们分不同存存储引擎去聊哈~

在MySQL的InnoDB存储引擎中, 聚簇索引与非聚簇索引最大的区别,在于叶节点是否存放一整行记录。聚簇索引叶子节点存储了一整行记录,而非聚簇索引叶子节点存储的是主键信息,因此,一般非聚簇索引还需要回表查询。

而在MyISM存储引擎中,它的主键索引,普通索引都是非聚簇索引,因为数据和索引是分开的,叶子节点都使用一个地址指向真正的表数据。

作者丨捡田螺的小男孩

来源丨公众号:捡田螺的小男孩(ID:gh_51e0e901a289)

声明:本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▍相关推荐
更多资讯 >>>