百度后端二面有哪些内容，万字总结(一)-一一网

前言

这是最近一位老朋友去百度面试，应该是面试资深工程师岗位，他跟我讲被问到mysql索引知识点？其实面试官主要还是考察对mysql的性能调优相关，问理论知识其实也是想知道你对原理的认知，从而确认你是否有相关的调优经验。朋友说他回答的还行，然后很顺利进行了三面四面。那么本文将跟大家一起来聊一聊这个如何回答面试官的这个问题！

公众号：我是阿沐
阿沐不仅仅是一名程序员，还是一名散文软文爱好者

`以下是自己的理解，如果以下有不对的地方，请你来喷我鸭！`

聊聊索引分类

按数据结构分类可分为：B+TREE(树)索引、HASH索引、FULLTEXT索引；按索引种类可以分为：普通索引、主键索引、唯一索引、全文索引、组合索引、一级索引、二级索引。

这两个有什么区别嘛？肯定有：一个是索引实现类型；一个是创建索引用到的类型

普通索引：(INDEX)建立在普通字段上的索引被称为普通索引

ALTER TABLE `table_name` ADD INDEX idx_name ( `user_name` ) 
复制代码

主键索引：(PRIMARY KEY)建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值

ALTER TABLE `table_name` ADD PRIMARY KEY ( `user_id` ) 
复制代码

唯一索引：(UNIQUE)建立在 unique 字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突

ALTER TABLE `table_name` ADD UNIQUE (`user_name`)
复制代码

全文索引：(FULLTEXT)建立在 varchar、char、text列上的全文索引；配合 match against 使用，类似一个搜索引擎，数据大时，很占用空间且耗时

ALTER TABLE `table_name` ADD FULLTEXT ( `user_desc` )
复制代码

组合索引：建立在多列上的索引叫组合索引，遵循”最左前缀“原则

ALTER TABLE `table_name` ADD INDEX idx_name_age ( `user_name`, `user_age` )
复制代码

一级索引：索引和数据存储在一起，都存储在同一个B+tree中的叶子节点。一般主键索引都是一级索引
二级索引：二级索引树的叶子节点存储的是主键而不是数据。也就是说，在找到索引后，得到对应的主键，再回到一级索引中找主键对应的数据记录

注意点：切不可滥用索引；切不可建立太多的索引；切不可建立重复索引

① 索引虽然提高查询速度，但同时会降低更新表的速度

② 建立索引会占用磁盘空间的索引文件；尽量减少在大表上建立过多的组合索引；

上面图带上一级二级索引是为了让大家更加了解索引结构 B+ Tree 的结构图可以很清楚索引是如何存储构建的且分层。

索引覆盖

顾名思义：覆盖索引就是查询的数据列只需要从索引中就可以获取到，不用再读取数据行；再通俗易懂的讲，我们sql查询的数据要被所建的索引能覆盖。

Mysql中只能使用 B+Tree 索引做覆盖索引；想必大家都知道 B+Tree 的原理吧？这里不再赘述。说下用处：

无需回表，查询速度快
减少系统调用和数据拷贝到缓存区等待时间

看到这这里知道为啥很多大厂不建议使用 select * from xxx 查询了(千万不要听别人说 可以使用select * 我待了几个大厂从来不建议这样操作)，目的就是：尽量能避免回表和减少IO的大小

怎么确认sql触发索引覆盖

触发索引覆盖：我们可以通过 explain sql 语句 输出结果为 Using Index 时，就能够触发索引覆盖。

① 那么我们看下 Explain 关键词分析：

常见类型	描述	备注说明
Using Index	使用了索引覆盖，不需要回表查询	无
Using Index Condition	使用了索引下推(5.6+版本)	无
Using Where	使用where条件再Sever层过滤数据	并非最佳
Using Filesort	不能利用索引树而采取了额外的排序操作	需要优化
Using Temporary	使用了临时表保存了中间结果集	需要优化
Using Join Buffer	连表查询时使用了循环嵌套扫描	需要优化

② 索引覆盖例子

## 创建一张测试索引覆盖的临时表，并对昵称  user_name 创建了索引

CREATE TABLE `user` (
  `user_id`   int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `user_name` varchar(125)     NOT NULL DEFAULT ''     COMMENT '用户昵称',
  `user_pwd`  varchar(64)      NOT NULL DEFAULT ''     COMMENT '用户密码',
  `user_sex`  tinyint(1)       NOT NULL DEFAULT '0'    COMMENT '用户性别 0-保密；1-男；2-女',
  `create_at` int(10)          NOT NULL DEFAULT '0'    COMMENT '创建时间',
  PRIMARY KEY (`user_id`),
  KEY `idx_name` (`user_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';
复制代码

来看下第一个sql语句：

mysql> explain select user_id,user_name from user where user_name = '李阿沐' limit 1;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_name      | idx_name | 377     | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
复制代码

我们从上面执行的结果可以看到 Extra = 'Using index'，使用了 idx_name 普通索引项。我们知道 B+Tree 叶子节点中索引也会作为数据页，存放的是普通目录项记录；idx_name的索引树里面存储了主键ID和user_name，这样就完全不需要回表操作，查询效率比较高。

再来看一个sql语句：

## 例如有时我们会通过用户昵称查询用户的pwd
mysql> explain select user_pwd from user where user_name = '李阿沐';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_name      | idx_name | 377     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
复制代码

我们来看下流程走向：

通过 idx_name 索引树，从树上找到 user_name = '李阿沐' 对应的主键id
通过 回表操作 在主键索引树上找到满足条件的数据，返回

虽然sql语句命中了idx_name索引，尽管索引叶子节点存储了主键user_id，很遗憾并没有存储 user_pwd 字段，所以需要回表查询才可以拿到这个值；那么这种操作就不符合索引覆盖原理，因为经过了回表，从而影响了查询效率。跟索引覆盖理念完全不合。

在这里有必要顺便解释一下 explain 结果集个字段的意思，加深下印象（简单扫一眼，看看下，很少人问）：

字段	描述说明
id	SELECT识别符，每个SELECT子句的标识id
select_type	SELECT类型；例如：① simple简单select(不使用UNION或子查询)；② primary最外面的select查询；③ union中的第二个或后面的select查询语句；④ dependent union的第二个或后面的查询语句,取决于外面的查询；⑤ union result的结果集；⑥ subquery子查询中的第一个select查询；⑦ dependent subquery子查询中的第一个select查询，取决于外面的查询；⑧ derived导出表的select查询(from子句的子查询)
table	当前表名
partitions	显示查询访问的分区
type	当前表内的联接类型；例如：① system表仅有一行；② const表最多有一个匹配行；③ eq_ref对于每个来自于前面的表的行组合,从该表中读取一行；④ ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取；⑤ ref_or_null同ref，但添加了mysql可以专门搜索包含NULL值的行；⑥ index_merge使用了索引合并优化方法；⑦ range只检索给定范围的行,使用一个索引来选择行；⑧ index该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小；⑨ all对于每个来自于先前的表的行组合,进行完整的表扫描
possible_keys	显示可能使用到的索引
key	显示mysql经过优化器评估最终使用的索引，如果没有选择索引，键是null
key_len	使用到的索引长度，若键为null则为null
ref	引用到上个表的列
rows	得到结果集需要扫描的记录数
filtered	存储引擎返回数据在server层过滤后，剩下多少满足查询的记录数据比例
Extra	查询额外信息

索引下推

在介绍索引下推之前，我们对上面的数据表增加一个 user_age 字段：

增加字段 user_age：

alter table `user` add column `user_age` smallint(5) not null default '0' comment '用户年龄';

## 执行结果

mysql> alter table `user` add column `user_age` smallint(5) not null default '0' comment '用户年龄';
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1

复制代码

增加组合索引 name + age：

alter table `user` add index idx_name_age (`user_name`, `user_age`);

## 执行结果

mysql> alter table `user` add index idx_name_age (`user_name`, `user_age`);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
复制代码

查看表结构：

## 再看下表结构 已经新增了  user_age 字段 和 idx_name_age索引
mysql> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `user_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `user_name` varchar(125) NOT NULL DEFAULT '' COMMENT '用户昵称',
  `user_pwd` varchar(64) NOT NULL DEFAULT '' COMMENT '用户密码',
  `user_sex` tinyint(1) NOT NULL DEFAULT '0' COMMENT '用户性别 0-保密；1-男；2-女',
  `create_at` int NOT NULL DEFAULT '0' COMMENT '创建时间',
  `user_age` smallint NOT NULL DEFAULT '0' COMMENT '用户年龄',
  PRIMARY KEY (`user_id`),
  KEY `idx_name_age` (`user_name`,`user_age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表'
1 row in set (0.00 sec)
复制代码

索引下推介绍

索引下推（index condition pushdown ）简称ICP，在Mysql5.6+的版本上推出，用于优化查询。索引下推在 非主键索引 上做合理的优化，可以有效减少回表的次数，同时可减少mysql服务器从存储引擎接收数据的次数，大大提升了查询的效率

未出现索引下推流程

在没有索引下推之前，执行的过程是这样，例如执行以下sql：

## 根据用户昵称 + 年龄查询 相匹配的用户

select * from user where `user_name` like '李%' and `age` = 26;

因为上面已经建立了联合索引idx_name_age，所以要根据 最左匹配原则 优先匹配name索引进行查询，不然就会造成不走索引导致全表扫描
复制代码

先根据name索引从存储引擎中拉取数据，存储引擎通过索引检索到数据之后，通过不断一个个的回表到主键索引找出符合的数据记录，然后数据加载到 server 层，开始通过 user_age 条件过滤符合要求的数据。

未使用索引下推图3-1

图 3-1

从图中我们可以看出来：① 在查询数据时存储引擎会忽略age这个字段；② 直接通过 name 索引在 idx_name_age 这颗索引树上查询到3条复合要求的结果；③ 开始根据主键 user_id 回表查询age对应的值，共回表3次；④ 在server层通过age条件进行过滤，得到最终符合要求的结果集；

出现索引下推流程

跟上图进行对比发现：在通过索引树拿到数据之后，就进行了索引下推操作，索引内部条件过滤符合数据结果。

使用索引下推图3-2

图3-2

mysql5.6版本之后，增加索引下推，流程走向：① 存储引擎通过 idx_name_age 查询，索引内部直接检测判断age值是否等于26，否则直接跳过；② 通过索引树查到匹配记录，通过主键id去主键索引树中回表查询对应字段值；③并不需要从存储引擎拉到数据在server层做过滤操作

实践操作

实践前先插入几条数据：

## 插入数据

mysql> insert into `user`(`user_name`,`user_pwd`,`user_sex`,`user_age`,`create_at`) VALUES('李阿沐', '123', 1, 26, 1624182989),('李子柒', '123', 1, 31, 1624182989),('李佳琦', '123', 1, 29, 1624182989),('高火火', '123', 1, 25, 1624182989);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

## 查看下表数据 

mysql> select * from user;
+---------+-----------+----------+----------+------------+----------+
| user_id | user_name | user_pwd | user_sex | create_at  | user_age |
+---------+-----------+----------+----------+------------+----------+
|       1 | 李阿沐    | 123      |        1 | 1624182989 |       26 |
|       2 | 李子柒    | 123      |        1 | 1624182989 |       31 |
|       3 | 李佳琦    | 123      |        1 | 1624182989 |       29 |
|       4 | 高火火    | 123      |        1 | 1624182989 |       25 |
+---------+-----------+----------+----------+------------+----------+
4 rows in set (0.00 sec)
复制代码

mysql版本5.5下sql执行情况：

## 查看版本 5.5 小于5.6版本

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.19-log |
+------------+
1 row in set (0.00 sec)

## 查看执行explain结果集 图 1

mysql> explain select * from user where `user_name` like '李%' and `user_age` = 26;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | idx_name_age  | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

## 另一个执行结果  图 2
mysql> explain select user_id,user_name from user where `user_name` like '李%' and `user_age` = 26;
+----+-------------+-------+-------+---------------+--------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | range | idx_name_age  | idx_name_age | 379     | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
复制代码

不知道大家从上面是否看出来一个问题：它并不是走的索引下推而是 Using where；图1和图2存储引擎都是显示可能使用到的索引，但是图1并没有走索引并且全表扫描；而图2走了索引只扫描其中几条；所以可以得到一个结论：like查询百分号前置，并不是100%不会走索引。① 据量少直接回全表扫描；② 若只select索引字段，或者select索引字段和主键，会走索引的

mysql版本5.6下sql执行情况：

## 一样先查看下mysql版本 8.0

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)

## 查看执行explain结果集

mysql> explain select * from user where `user_name` like '李%' and `user_age` = 26;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | idx_name_age  | idx_name_age | 379     | NULL |    3 |    25.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

复制代码

小伙伴们是不是可以很清晰的看到： Extra = Using index condition 使用了索引下推。所以大家以后再写sql语句的时候，需要适当的根据 where条件 做合理的索引，尽量的使我们sql语句是最优状态；当然这也是我们公司经常要求的，而且发现不好的sql语句，会被拿出来做案例。

小小的总结下索引下推

未使用索引下推优化：先根据索引查询记录，回表再根据where条件过滤
使用索引下推优化：根据索引树获取记录的时，检测是否可以用where条件过滤数据在回表查询

最左匹配原则

面试中经常会被问到：假如表中设置了 (a, b, c)联合索引，那么你在sql查询使用 (a, c, b) 或者 (b, a, c)会不会继续走索引项呢？至少我面试中基本都会被问到，尤其是某些大厂！其实他们主要是想考察你最 最左匹配原则 是否理解原理。从而看出来你平常是否会对sql进行调优。

为什么要使用联合索引

我们先修改表的联合索引字段：

## mysql没有提供修改索引的指令，可先删除原索引，新增一个新的索引，变相实现修改索引

alter table `user` drop index idx_name_age;

alter table `user` add index idx_name_age_sex ( `user_name`, `user_age`, `user_sex` );

## 执行结果

mysql> alter table `user` drop index idx_name_age;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table `user` add index idx_name_age_sex ( `user_name`, `user_age`, `user_sex` );
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
复制代码

idx_name_age_sex联合索引，相当于三个索引：idx_name、idx_name_age、idx_age_sex，节省磁盘空间的开销和写操作开销
若出现覆盖索引，select user_name,user_age,user_sex from xxx where xxx，则直接通过索引遍历获取数据，无序回表查询数据，减少IO操作和回表次数

最左匹配原则：mysql创建联合索引时总会遵守最左匹配原则；从最左边为起点任何连续的索引都会被匹配成功；但是若查询时出现范围查询(like、>、<、between)会停止索引匹配。

全值匹配查询时

name_age_sex 索引顺序

mysql> explain select * from user where `user_name` = "李阿沐" and `user_age` = 26 and `user_sex` = 1;
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys    | key              | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_name_age_sex | idx_name_age_sex | 380     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
复制代码

name_sex_age 索引顺序

mysql> explain select * from user where `user_name` = "李阿沐" and `user_sex` = 1 and `user_age` = 26;
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys    | key              | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_name_age_sex | idx_name_age_sex | 380     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
复制代码

sex_age_name 索引顺序

mysql> explain select * from user where `user_sex` = 1 and `user_age` = 26 and `user_name` = "李阿沐";
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys    | key              | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_name_age_sex | idx_name_age_sex | 380     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

复制代码

从上面看出来：三个查询全部走了 idx_name_age_sex 联合索引。可能不能更好的看出到底走的是哪一种索引，我们可以通过观察 key_len 和 ref 这两个跟第一个是完全一致的。

可能有小伙伴会有疑惑：“卧槽，不应该走最左匹配嘛？怎么下面两个命名没有按照最左匹配却都走了索引？”

其实我们不能忽略mysql本身的查询优化器啊，我们可以不需要规规矩矩的按照顺序去写where条件，因为查询优化器会自动检测这条sql，它以哪一种方式执行效率最高，最后才生成了真正的执行计划。

匹配左边的列时

① 依次匹配 name

mysql> explain select * from user where `user_name` = "李阿沐";
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_name_age_sex | idx_name_age_sex | 377     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
复制代码