前言

这是最近一位老朋友去百度面试，应该是面试资深工程师岗位，他跟我讲被问到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长度为(单位字节)： $$ 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(smallint长度) $$ 刚刚好是 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 索引，没有其他索引；看下key_len长度为(单位字节)： $$ 380 = 125*3 + 2 + 2(smallint长度) + 1(tinyint长度) $$ 刚刚好是 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字节

这样是不是很清楚通过索引长度看出到底使用了那几个索引： $$ key_len = 125(varchar类型3字节)*3 + 2(额外加2字节) + 2(smallint类型2字节) + 1(tinyint类型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长度可以看出来：

$$ key_len = 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操作成本

$$ result = 100000 * 0.2 = 20000 $$

• I/O操作成本 $$ result = 100000 * 1 = 100000 $$

• 两次操作的成本数 $$ result = 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 

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

$$ page = 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；记住就行了

所以上面大家可以看清楚回表的计算逻辑了，如下： $$ 回表成本 = CPU成本(记录数 * 0.2) + I/O成本(记录页数 * 1) $$

小小总结

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

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

参考与感谢

• mysql官方

• Mysql最左匹配原则