《彻底掌握索引》—— 要点问题（2）

技术闲聊DD

于 2023-03-15 14:20:55 发布

阅读量291

点赞数 1

分类专栏： mysql 文章标签：数据库 sql java

本文链接：https://blog.csdn.net/wu2374633583/article/details/129499093

版权

mysql 专栏收录该内容

16 篇文章 1 订阅

订阅专栏

7. 什么是回表？如何减少回表？

当查询的数据在索引树中，找不到的时候，需要回到主键索引树中去获取，这个过程叫做回表。回表查询可以理解为普通索引的查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

在这之前，我们需要了解的就是主键索引和非主键索引在数据结构上的区别，两者使用的数据结构都是 B+Tree，唯一的区别在于叶子结点中存储的内容不同：

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

所以，当我们需要查询的时候：

如果是通过主键索引来查询数据，例如 select * from user where id=100，那么此时只需要搜索主键索引的 B+Tree 就可以找到数据。
如果是通过非主键索引来查询数据，例如 select * from user where username='javaboy'，那么此时需要先搜索 username 这一列索引的 B+Tree，搜索完成后得到主键的值，然后再去搜索主键索引的 B+Tree，就可以获取到一行完整的数据。

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

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

举个例子：

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);

# 进行数据的指数增长
INSERT INTO student (name, age) SELECT `NAME`, age FROM student;

每一个索引在 InnoDB 里面对应一棵B+树，那么此时就存着两棵B+树：
在这里插入图片描述
可以发现区别在与叶子节点中，主键索引存储了整行数据，而非主键索引中存储的值为主键id，在我们执行如下sql：

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

执行流程如下：

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

在流程中从非主键索引树搜索回到主键索引树搜索的过程称为：回表。

优化的手段：
5. 尽量使用主键索引查询
6. 引入覆盖索引。
7. 索引下推

接下来会详细说一下这几种方式。

8. 什么是覆盖索引？

8.1 定义

就是把单列的非主键索引修改为多字段的联合索引。在一棵索引数上，就找到了想要的数据, 不需要去主键索引树上，再检索一遍这个现象，称之为索引覆盖。
也就是说覆盖索引（covering index ，或称为索引覆盖）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。

8.2 如何使用覆盖索引？

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

# 查询当前表中的索引
show index from student;
# 删除之前的非主键索引
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。

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

当发起一个索引覆盖查询时，在explain的extra列可以看到using index的信息:
在这里插入图片描述
这里我们很清楚的看到Extra中using index表明我们成功使用了覆盖索引。

要注意的是：不使用主键索引，不一定就需要回表，比如查询的列本身就存在于索引中，那么即使使用二级索引，一样也是不需要回表的。

9. 索引最左前缀原则

即当你创建了一个联合索引，该索引的任何最左前缀都可以用于查询。比如当你有一个联合索引 (col1, col2, col3)，该索引的所有前缀为 (col1)、(col1, col2)、(col1, col2, col3)，包含这些列的所有查询都会使用该索引进行查询。

但是要注意的是：
有时候并不一定会遵守最左前缀原则，MySQL8.0版本开始增加了索引跳跃扫描的功能，当第一列索引的唯一值较少时，即使where条件没有第一列索引，查询的时候也可以用到联合索引。

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) NOT NULL COMMENT '姓名',
  `gender` tinyint NOT NULL COMMENT '性别',
  PRIMARY KEY (`id`),
  KEY `idx_gender_name` (`gender`,`name`)
) ENGINE=InnoDB COMMENT='用户表';

在性别和姓名两个字段上(gender,name)建立联合索引，性别字段只有两个枚举值。

执行SQL查询验证一下：

explain select * from user where name='小明';

在这里插入图片描述
虽然SQL查询条件只有name字段，但是从执行计划中看到依然是用了联合索引。
并且Extra列中显示增加了Using index for skip scan，表示用到了索引跳跃扫描的优化逻辑。就是匹配的时候遇到第一列索引就跳过，直接匹配第二列索引的值，这样就可以用到联合索引了。

我们优化一下SQL，把第一列的所有枚举值加到where条件中，也可以用到联合索引：

select * from user where gender in (0,1) and name='小明';

技术闲聊DD

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
《彻底掌握索引》—— 要点问题（2）

当查询的数据在索引树中，找不到的时候，需要回到主键索引树中去获取，这个过程叫做回表。回表查询可以理解为普通索引的查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。主键索引的叶子结点存储的是一行完整的数据。非主键索引的叶子结点存储的则是主键值。叶子结点不包含行记录的全部数据；非主键的叶子结点中，除了用来排序的key还包含一个bookmark；该书签存储了聚集索引的key。所以，当我们需要查询的时候：如果是通过主键索引来查询数据，例如。
复制链接

扫一扫