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

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 查询语句执行大概流程是这样的：

搜索idx_age 索引树，将磁盘块1加载到内存，由于32<43,搜索左路分支，到磁盘寻址磁盘块2 。
将磁盘块2加载到内存中，由于32<36,搜索左路分支，到磁盘寻址磁盘块4 。
将磁盘块4加载到内存中，在内存继续遍历，找到age=32的记录，取得id = 400.
拿到id=400后，回到id主键索引树。
搜索id主键索引树，将磁盘块1加载到内存，因为300<400<500,所以在选择中间分支，到磁盘寻址磁盘块3 。
虽然在磁盘块3，找到了id=400，但是它不是叶子节点，所以会继续往下找。到磁盘寻址磁盘块8 。
将磁盘块8加载内存，在内存遍历，找到id=400的记录，拿到R4这一行的数据，好的，大功告成。

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就引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。