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

关注

发布于: 3 小时前

前言

这是最近一位老朋友去百度面试，应该是面试资深工程师岗位，他跟我讲被问到 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 关键词分析：

② 索引覆盖例子

## 创建一张测试索引覆盖的临时表，并对昵称  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 语句：

## 例如有时我们会通过用户昵称查询用户的pwdmysql> 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 结果集个字段的意思，加深下印象（简单扫一眼，看看下，很少人问）：

索引下推

在介绍索引下推之前，我们对上面的数据表增加一个 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: userCreate 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

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

出现索引下推流程

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

使用索引下推图 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)
## 另一个执行结果  图 2mysql> 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)

复制代码

从上图我们呢可以很清楚看到是遵循最左匹配原则使用了联合索引且使用的是其中的 name 索引，没有其他索引；看下 key_len 长度为(单位字节

)：

377 = 125 * 3 + 2

刚刚好是 name 索引长度值。

② 依次匹配 name_age

mysql> explain select * from user where `user_name` = "李阿沐" 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 | 379     | const,const |    1 |   100.00 | NULL  |+----+-------------+-------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

复制代码

从上图我们呢可以很清楚看到是遵循从左往右依次匹配原则使用了联合索引且使用的是其中的 name + age 索引，没有其他索引；看下 key_len 长度为(单位字节

)：

379 = 125 * 3 + 2 + 2 (s m a l l i n t 长 度)

刚刚好是 name + age 索引长度值。

③ 依次匹配 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)

复制代码

)：

380 = 125 * 3 + 2 + 2 (s m a l l i n t 长 度) + 1 (t i n y i n t 长 度)

刚刚好是 name + age + age 索引长度值。

④ 若不是依次匹配

mysql> explain select * from user where `user_age` = 26;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

复制代码

由于上面都没有从最左边开始匹配，所以没有用到联合索引，使用的都是 index 全索引扫描。

⑤ 补充下索引长度计算方式

- 1.所有的索引字段，如果没有设置not null，则需要加一个字节 
- 2.定长字段，int占四个字节、date占三个字节、char(n)占n个字符
- 3.对于变成字段varchar(n)，则有n个字符+两个字节
- 4.字符集不同则一个字符占用的字节数也不同；    utf8mb4 - 1字符占用4字节    utf8    - 1字符占用3字节    gbk     - 1字符占用2字节    latin1  - 1字符占用1字节

复制代码

这样是不是很清楚通过索引长度看出到底使用了那几个索引：

k e y_{l} e n = 125 (v a r c h a r 类 型 3 字 节) * 3 + 2 (额 外 加 2 字 节) + 2 (s m a l l i n t 类 型 2 字 节) + 1 (t i n y i n t 类 型 1 个 字 节) = 380 (字 节)

匹配列前缀

我们先看下下面三个 sql 语句最终的执行结果：

① 前缀匹配

mysql> explain select * from user where `user_name` like '李%';+----+-------------+-------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |+----+-------------+-------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+|  1 | SIMPLE      | user  | NULL       | range | idx_name_age_sex | idx_name_age_sex | 377     | NULL |    3 |   100.00 | Using index condition |+----+-------------+-------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)

复制代码

② 后缀匹配

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

复制代码

③ 中缀匹配

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

复制代码

从上面执行结果可以看出：第一个前缀已经排好序的，所以走索引查询；第二个第三个则是全表扫描查询

注意：若列是字符串则比较规则是这样的；先比较字符串第一个字符，若相同则继续比较第二个字符，以此类推下去。

匹配范围值

## 最左查询 这里是新增了一列联合索引 删除之前的那个 便于测试mysql> explain select * from user where `user_age` > 1 and `user_age` < 30;+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+|  1 | SIMPLE      | user  | NULL       | range | idx_age_sex   | idx_age_sex | 2       | NULL |    3 |   100.00 | Using index condition |+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)
## 在user_age>1 and user_age < 30的范围中，sex是有序的，所以使用的是rang范围查询mysql> explain select * from user where `user_age` > 1 and `user_age` < 30 and `user_sex` > 0;+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+|  1 | SIMPLE      | user  | NULL       | range | idx_age_sex   | idx_age_sex | 2       | NULL |    3 |    33.33 | Using index condition |+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)

复制代码

结论：多个列进行范围查询时，只有最左边列查询时才使用了索引；若在 1<age<20 范围中 sex 是有序的，则使用的是 rang 范围查询；否则无序只能等 age 查询到记录后通过 sex > 0 逐条过滤。

精确匹配某一列并范围匹配另外一列

## 如果左边的列是精确查找的，右边的列可以进行范围查找mysql> explain select * from user where `user_name` = "李阿沐" and `user_age` < 29;+----+-------------+-------+------------+-------+------------------------------+------------------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type  | possible_keys                | key              | key_len | ref  | rows | filtered | Extra                 |+----+-------------+-------+------------+-------+------------------------------+------------------+---------+------+------+----------+-----------------------+|  1 | SIMPLE      | user  | NULL       | range | idx_name_age_sex,idx_age_sex | idx_name_age_sex | 379     | NULL |    1 |   100.00 | Using index condition |+----+-------------+-------+------------+-------+------------------------------+------------------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.01 sec)

复制代码

通过上面可以看出，左列值精确查找，左边值是有序的进行范围查找会走联合索引。当然可以通过 key_len 长度可以看出来：

k e y_{l} e n = 125 * 3 + 2 + 2 = 377 + 2 = 399

回表

什么是回表查询：简单来说就是查询时获取的列有大量的非索引列，这个时候根据主键索引树去表中知道相关列的值信息，而这个操作就叫做回表。

我们看下下面两个 sql 语句：

## 无需回表，因为select列全部是索引列
select user_id, user_name from user where `user_name` = "李阿沐";
或者
select * from user where `user_id` = 1;不回表原因：根据主键ID查询，只需要在主键b+tree上搜索数据即可。
## 需要回表，因为select列出现非索引列，需要根据主键索引到表中查询信息;实际上使用了两次索引查询
select user_id, user_name, user_pwd from user where `user_name` = "李阿沐";

复制代码

为什么要尽量减少回表操作，尤其是当表的数据量越来越大的时候？

例如：现在我们通过 idx_name_age 索引扫描到了 10w 条数据，通过索引查询到主键索引在回表去查询相关列信息；mysql 会认为每一次的回表都需要一次单独的 I/O 操作成本.

索引查询成本

CPU 操作成本

r e s u l t = 100000 * 0.2 = 20000

I/O操作成本 $ $r e s u l t = 100000 * 1 = 100000$ $
两次操作的成本数 $ $r e s u l t = 20000 + 100000 = 120000$ $

全表扫描查询成本

mysql> SELECT TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,(DATA_LENGTH+INDEX_LENGTH) as length,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,3), 'MB') as total_size FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' order by length desc;+------------+-------------+--------------+--------+------------+------------+| TABLE_NAME | DATA_LENGTH | INDEX_LENGTH | length | TABLE_ROWS | total_size |+------------+-------------+--------------+--------+------------+------------+| test       |       16384 |        16384 |  32768 |          3 | 0.031MB    || user       |       16384 |        16384 |  32768 |          4 | 0.031MB    |+------------+-------------+--------------+--------+------------+------------+2 rows in set (0.01 sec)
通过 命令 查看全表的字节数 user表 Data_length = 16384

复制代码

从上面可以计算出全表扫描需要读取多少记录页：

p a g e = 16384 / 1024 / 16 = 1

可以看出我本地的记录页太少了；假设本地全表有 16384000 个字节，表内有 300000 条记录；那么我们需要扫描 1000 个记录页；现在算下成本：

$$result = CPU 成本(3000000.2=60000) + I/O 成本(10001 = 1000) = 61000$$

这样看起来有时候会出现回表操作查询比全表扫描更消耗性能；所以我们在做业务需求是，先预估表的量，再合理的构建索引，并且通过 explain 解析看下具体性能。

查询成本组成与计算

CPU 成本
I/O 成本
要清楚 mysql 规定读取一个页面的成本是 1.0 并不是随意写的；读取和检测记录是否使用索引的成本是 0.2
注意：不管记录是否检测满足索引条件，成本都是 0.2；记住就行了

所以上面大家可以看清楚回表的计算逻辑了，如下：

回 表 成 本 = C P U 成 本 (记 录 数 * 0.2) + I / O 成 本 (记 录 页 数 * 1)

小小总结

使用聚集索引（主键或第一个唯一索引）就不会回表，普通索引就会回表。

尽量减少回表查询降低查询成本：① 能用主键索引或唯一索引的就不用辅助索引；② 可以使用覆盖索引

参考与感谢

发布于: 3 小时前阅读数: 4

原文链接:【http://xie.infoq.cn/article/09187168e6740dbdd201d703c】。文章转载请联系作者。

我是阿沐

关注

生活最重要的是开心 | 保持一个好心态 2021.05.29 加入

公众号：我是阿沐 | 思绪来得快去得也快，偶尔会在这里停留

发布

暂无评论

创作场景

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

前言

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

聊聊索引分类

索引覆盖

怎么确认 sql 触发索引覆盖

索引下推

索引下推介绍

未出现索引下推流程

未使用索引下推图 3-1

出现索引下推流程

使用索引下推图 3-2

实践操作

小小的总结下索引下推

最左匹配原则

为什么要使用联合索引

全值匹配查询时

匹配左边的列时

匹配列前缀

匹配范围值

精确匹配某一列并范围匹配另外一列

回表

索引查询成本

全表扫描查询成本

查询成本组成与计算

小小总结

参考与感谢

我是阿沐

评论

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