当前位置：首页 > news >正文

mysql＜回表，覆盖索引，最左匹配，索引下推＞

news 来源：原创 2024/5/5 4:14:44

1.两类索引

MySQL 中的索引有很多中不同的分类方式，可以按照数据结构分，可以按照逻辑角度分，也可以按照物理存储分，其中，按照物理存储方式，可以分为聚簇索引和非聚簇索引。

主键索引，其实就是聚簇索引(Clustered Index);主键索引之外，其他的都称之为非主键索引，非主键索引也被称为二级索引(Secondary Index)，或者叫作辅助索引。

对于主键索引和非主键索引，使用的数据结构都是 B+Tree，唯一的区别在于叶子结点中存储的内容不同：

主键索引的叶子结点存储的是一行完整的数据。
非主键索引的叶子结点存储的则是主键值。叶子结点不包含行记录的全部数据；非主键的叶子结点中，除了用来排序的key还包含一个bookmark；该书签存储了聚集索引的key。

这就是两者最大的区别。

查询的时候：

如果是通过主键索引来查询数据，例如 select * from user where id=100，那么此时只需要搜索主键索引的 B+Tree 就可以找到数据。
如果是通过非主键索引来查询数据，例如 select * from user where username='javaboy'，那么此时需要先搜索 username 这一列索引的 B+Tree，搜索完成后得到主键的值，然后再去搜索主键索引的 B+Tree，就可以获取到一行完整的数据。（对username创建普通索引，由于是select*查询的是全部的数据，就会在叶子节点再次走可key的b+tree）

对于第二种查询方式而言，一共搜索了两棵 B+Tree，第一次搜索 B+Tree 拿到主键值后再去搜索主键索引的 B+Tree，这个过程就是所谓的回表。

从上面的分析中我们也能看出，通过非主键索引查询要扫描两棵 B+Tree，而通过主键索引查询只需要扫描一棵 B+Tree，所以如果条件允许，还是建议在查询中优先选择通过主键索引进行搜索。

2.建立一张学生表，其中包含字段id设置主键索引、name设置普通索引、age(无处理)，并向数据库中插入4条数据：("小赵", 10)("小王", 11)("小李", 12)("小陈", 13)

 create table `student` (

`id`  int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`name` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '名称',
`age` int(3) unsigned NOT NULL DEFAULT '1' COMMENT '年龄',
primary key (`id`),
KEY `I_name` (`name`)
) ENGINE=InnoDB;
INSERT INTO student (name, age) VALUES("小赵", 10),("小王", 11),("小李", 12),("小陈", 13);

每一个索引在 InnoDB 里面对应一棵B+树，那么此时就存着两棵B+树。

可以发现区别在与叶子节点中，主键索引存储了整行数据，而非主键索引中存储的值为主键id, 在我们执行如下sql后

SELECT age FROM student WHERE name = '小李'；

sql执行流程为：

在name索引树上找到名称为小李的节点 id为 03
从id索引树上找到id为 03的节点获取所有数据
从数据中获取字段命为age的值返回 12

在流程中从非主键索引树搜索回到主键索引树搜索的过程称为：回表，在本次查询中因为查询结果只存在主键索引树中，我们必须回表才能查询到结果，那么如何优化这个过程呢？引入正文覆盖索引

2.覆盖索引

就是把单列的非主键索引修改为多字段的联合索引, 在一棵索引数上。就找到了想要的数据, 不需要去主键索引树上,再检索一遍这个现象,称之为索引覆盖.

覆盖索引(covering index ，或称为索引覆盖)即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。

如何使用是覆盖索引？

之前我们已经建立了表student，那么现在出现的业务需求中要求根据名称获取学生的年龄，并且该搜索场景非常频繁，那么先在我们删除掉之前以字段name建立的普通索引，以name和age两个字段建立联合索引，sql命令与建立后的索引树结构如下。

 # 删除之前的非主键索引
alter table student drop index I_name;
 # 添加非主键索引
alter table student add index I_name_age(name, age);

那在我们再次执行如下sql后：

select age from student where name = '小李'；

流程为：

在name,age联合索引树上找到名称为小李的节点
此时节点索引(非主键索引)里包含信息age 直接返回 12

如何确定数据库成功使用了覆盖索引呢？

当发起一个索引覆盖查询时，在explain的extra列可以看到using index的信息:

这里我们很清楚的看到Extra中using index表明我们成功使用了覆盖索引。

覆盖索引避免了回表现象的产生，从而减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是性能优化的一种手段。

3.索引下推

在新版本中，在存储引擎中就会过滤掉一部分数据

老版本中，是在存储引擎中先取出来再在server中进行过滤

询表中“名字第一个字是张，性别男，年龄为10岁的所有记录”。

select * from tuser where name like '张%' and age=10 and ismale=1;

该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录(即记录ID3)，接下来是怎么处理的呢？

当然就是从ID3开始，逐个回表，到主键索引上找出相应的记录，再比对age和ismale这两个字段的值是否符合。

但是！MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。

下面图1、图2分别展示这两种情况。

图 1 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

图 2 跟图 1 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

如果没有索引下推优化(或称ICP优化)，当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。