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

原创

我是阿沐

修改于 2021-06-22 10:02:01

5080

修改于 2021-06-22 10:02:01

前言

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

从图中我们可以看出来：① 在查询数据时存储引擎会忽略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)

## 另一个执行结果  图 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)

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

刚刚好是 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长度为(单位字节)：

长度

刚刚好是 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)

长度长度

刚刚好是 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字节

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

类型字节额外加字节类型字节类型个字节字节

匹配列前缀

我们先看下下面三个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长度可以看出来：

回表

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

我们看下下面两个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操作成本
I/O操作成本
两次操作的成本数

全表扫描查询成本

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

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

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

成本成本

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

查询成本组成与计算

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

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

回表成本成本记录数成本记录页数

小小总结

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

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

参考与感谢

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

搜索引擎

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

搜索引擎