贝壳面试:什么是回表?什么是索引下推?

2024-09-08 14:36

本文主要是介绍贝壳面试:什么是回表?什么是索引下推?,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

尼恩说在前面

在40岁老架构师 尼恩的读者交流群(50+)中,最近有小伙伴拿到了一线互联网企业如得物、阿里、滴滴、极兔、有赞、希音、百度、网易、美团的面试资格,遇到很多很重要的面试题:

1.谈谈你对MySQL 索引下推 的认识?

2.在MySQL中,索引下推 是如何实现的?请简述其工作原理。

3、说说什么是 回表,什么是 索引下推 ?

最近有小伙伴在面试 贝壳、soul,又遇到了相关的面试题。小伙伴懵了,因为没有遇到过,所以支支吾吾的说了几句,面试官不满意,面试挂了。

所以,尼恩给大家做一下系统化、体系化的梳理,使得大家内力猛增,可以充分展示一下大家雄厚的 “技术肌肉”,让面试官爱到 “不能自已、口水直流”,然后实现”offer直提”。

当然,这道面试题,以及参考答案,也会收入咱们的 《尼恩Java面试宝典PDF》V171版本,供后面的小伙伴参考,提升大家的 3高 架构、设计、开发水平。

最新《尼恩 架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》的PDF,请关注本公众号【技术自由圈】获取,回复:领电子书

1、回表查询(table lookup)是什么?

尼恩的叙事风格: 故事从最基础的地方讲起。

先简单了解一下MySQL大概的架构:

再由浅入深,一步一步理解回表查询(table lookup)

然后介绍,回表(table lookup)查询带来的 巨大的问题

1.1 MySQL大概的架构

尼恩的叙事风格: 故事从最基础的地方讲起。

先简单了解一下MySQL大概的架构:

在这里插入图片描述

  • 第1层:连接层

对来自客户端的连接进行权限验证并将相关的连接信息维护到连接池中,以便于下次连接。

  • 第2层:服务层:

提供NoSQL,SQL的API,SQL解析,SQL语句优化,SQL语句缓存等相关组件。

  • 第3层:存储引擎层:

提供了一系列可插拔的存储引擎,我们可以通过存储引擎来进行数据的写入与读取,

通过存储引擎,我们可以真正的与硬盘中的数据和日志进行交互,

我们可以根据需求来选择合适的存储引擎进行使用。

  • 第4层:文件系统层:

该层包含了具体的日志文件和数据文件以及MySQL相关的程序。

1.2 回表(table lookup) 的简单介绍

回表(table lookup)通常是指数据库查询过程中,需要从索引层查找到某条记录的主键,再通过这个主键回到数据表中获取完整的记录。

回表操作在数据库系统中的不同层次上实现,而这主要取决于数据库的架构设计。

  1. Server 层(查询层)

    在这一层,数据库接收查询请求并决定如何执行查询。

    查询优化器会决定是否需要进行回表操作。通过二级索引 查找到记录的主键后,查询层会发出回表的请求。

    此时,实际的数据读取工作还没有发生。

  2. Engine 存储层:回表的实际数据读取是在存储层完成的。

    一旦查询层决定需要回表,存储层负责根据主键从存储介质(例如硬盘或内存)中提取相应的完整记录。

    存储层负责处理物理数据读取、缓存管理、磁盘 I/O 等操作。

总结来说,回表的决策是在 Server 层(查询层)做出的,而实际的记录读取则是在 存储层 完成的。

所以,回表操作涵盖了这两个层面的协作。

在这里插入图片描述

1.3 由浅入深,一步一步理解回表查询(table lookup)

在理解回表查询之前,我们需要先了解两个重要的概念:聚集索引和非聚集索引。

基本概念:聚集索引(Clustered Index):

  • 存储方式:聚集索引决定了数据表中数据行的物理存储顺序。索引的顺序与数据行的顺序一致,实际上是直接嵌入到数据表中的一种排序结构。
  • 影响查询:由于数据行的存储顺序与聚集索引的顺序一致,当通过聚集索引进行查询时,数据库引擎可以更快地定位到所需的数据,因为它知道数据的物理存储位置。适用于范围查询和排序操作。
  • 唯一性:一个表只能有一个聚集索引,通常是主键,因为主键的值是唯一的。
  • 存储数据:整个数据行

在这里插入图片描述

基本概念:非聚集索引(Non-Clustered Index):

  • 存储方式:非聚集索引维护了索引键值和指向实际数据行的指针之间的映射关系。

    索引键值与数据行的物理存储顺序无关,数据行的实际内容可能分散存储在磁盘上。

  • 影响查询:通过非聚集索引进行查询时,数据库引擎首先根据索引键值找到对应的 指针或引用,然后再根据指针或引用去检索相应的数据行。适用于频繁的搜索和查询,但可能需要额外的IO操作。

  • 唯一性:一个表可以有多个非聚集索引,不要求索引键值是唯一的。

  • 存储数据:当前字段的值和指向数据行的指针或引用(通俗的说就是当前字段的主键值 Primary Key)

在这里插入图片描述

使用案例介绍:回表查询

回表查询是数据库中常见的一个概念,指的是server层无法直接从索引中获取所需数据,而需要回到原始数据表中进行额外的查找操作。

为了更好地理解回表查询,让我们通过SQL语句的方式来演示一下。

假设我们有一个包含员工信息的表 employees,其中包括:

  • 员工的编号(employee_id)
  • 姓名(name)
  • 部门(department)
  • 薪水(salary)等字段。

这边把 employee_id 作为ID 主键,也就是 聚集索引,以加快根据员工编号进行查询的速度。

首先来一个没有回表的查询:

现在,假设我们需要查询员工编号为 1001 的员工的薪水,我们可能会编写如下的SQL查询语句:

SELECT salary FROM employees WHERE employee_id = 100;

在这个查询中,server层 通过 Engine 利用 employee_id 上的聚集索引,快速地找到对应的员工corde记录,并返回薪水信息,这时候,就不会发生回表查询。

再来一个没有回表的查询:

如果我们需要在name列查询员工姓名为 令狐冲 的薪水,并且假设 name 存在一个idx_name 的非聚集索引 :

SELECT salary  FROM employees  WHERE name= '令狐冲';

那么数据库server层 通过 Engine 利用 idx_name 非聚集索引,查到 令狐冲的id 为100。server层 再通过 Engine 利用 employee_id 上的聚集索引,快速地找到100 对应的员工recode记录,这就导致了回表查询。

在这里插入图片描述

回表通常发生在:

  • 查询语句中包含了索引无法覆盖的字段
  • 或者涉及到了复杂的查询条件时。

1.4 回表查询带来的 巨大的问题

回表查询通常出现在使用非聚簇索引或二级索引的场景中,它带来的一些问题主要集中在性能和效率上。

以下是回表查询中的常见问题:

1. 性能开销大

  • 多次随机 I/O:回表查询通常需要从索引查到主键后,再通过主键到表中查找完整数据。这意味着,数据库可能需要进行多次磁盘 I/O 操作,尤其是在表非常大、且数据不在内存中的情况下。频繁的随机磁盘访问可能导致性能瓶颈。
  • 索引覆盖不足:如果查询的字段没有完全包含在索引中,就会触发回表操作。覆盖索引(covering index)可以避免回表查询,但一旦查询涉及的数据超出了索引范围,回表不可避免。

2. 表的大小问题

当表非常大时,回表操作的效率会显著下降。原因在于索引层查找出的记录需要到大量数据中进行定位,表越大,回表的开销就越高。特别是在分布式数据库系统中,跨节点回表查询的代价会更大。

3.频繁回表增加查询延迟

当查询结果需要频繁回表时,会导致查询的整体延迟增加。

例如,在复杂的 JOIN 查询中,如果某个表上的索引无法覆盖查询需求,回表次数会随记录数量的增加而增加,导致显著的性能下降。

4. 缓存失效问题

数据库会尝试将最近访问的数据缓存在内存中,但由于回表查询涉及两步操作(先查索引,再查表),在高并发场景下,缓存命中率可能会降低。

当表数据和索引数据存储位置不一致(例如,索引在内存中,而表数据在磁盘上),回表查询更容易导致缓存失效。

5.回表查询不适合大批量查询

在大批量数据查询时,回表操作会带来非常显著的性能问题。

批量查询意味着多个记录需要回表,而这将成倍地增加查询的 I/O 开销。

对于批量查询,可以考虑使用聚簇索引,或者使用更多维度的覆盖索引来降低回表需求。

6. 业务设计影响

如果数据库设计过程中没有正确考虑回表问题,比如没有充分利用索引覆盖或是合理的索引设计,会导致查询性能低下。

因此,在设计数据库时,需要根据业务场景评估是否需要创建聚簇索引或合适的二级索引,以减少回表操作。

1.5 怎样避免回表查询?

1.创建合适的索引:

覆盖索引(Covering Index):

确保查询所需的列都包含在一个索引中。如果你的查询只涉及索引中的列,而不需要回表获取其他列的值,就可以避免回表查询。例如,在你的表中为常用的查询条件和选择列表创建合适的索引。

-- 示例:为 name 列和 employee_id 列创建覆盖索引
CREATE INDEX idx_name_employee_id ON employees (name, employee_id);

2.使用索引覆盖的查询:

在编写查询语句时,尽量使用覆盖索引来满足查询的需求。这意味着查询中的条件和选择列表中的列都应该是索引的一部分,这样数据库引擎可以直接从索引中获取所需的信息。

SELECT salary
FROM employees
WHERE name= '张三';

3.避免使用SELECT * 查询

明确列出查询中需要的列,而不是使用SELECT *。这可以防止不必要的列被检索,从而减少回表的需求。

4.理解查询计划:

-- 示例:使用 EXPLAIN 分析查询计划
EXPLAIN SELECT employee_id  FROM employees  WHERE name = '张三';

使用数据库查询优化工具或者EXPLAIN语句来分析查询计划。

确保查询计划中使用了合适的索引,并且尽量减少回表的情况。

5.使用缓存数据库

将常用的查询结果存储在缓存数据库中,这样我们查询时候就可以先走缓存,极大地提高查询性能,并减少回表查询的需求。

2 索引下推的底层原理是什么?优势是什么?

尼恩的叙事风格: 故事从最基础的地方讲起。

通俗易懂,介绍一下 索引下推的简单案例,

再由浅入深,一步一步理解索引下推

然后介绍,索引下推带来的 性能优势

2.1 通俗易懂,介绍一下 索引下推的简单案例

理论比较抽象,我们来上一个实践。

使用一张用户表tuser,表里创建联合索引(name, age)。

在这里插入图片描述

如果现在有一个需求:检索出表中 名字第一个字是a,而且年龄是10岁的所有用户。

那么,SQL语句是这么写的:

select * from tuser where name like 'a%' and age=10;

根据索引 最左匹配原则 + 前缀匹配原则,那么就知道, 这个语句在搜索索引树的时候,能用 前缀a,找到满足条件的记录, 有4个记录,id为1、2、3、4。

这里联合索引里边明明有 age,为啥不直接 判断呢?

这个和最左匹配原则 有关.

最左匹配原则 中的第4条:范围匹配规则

最左匹配原则 的4条,关于 匹配范围值,有如下规则:

多个列同时进行范围查找时,只有对索引最左边的那个列进行范围查找才用到B+树索引,

下述SQL,可以对最左边的列进行范围查询

select * from table_name where  a > 1 and a < 3这里,假设 a b 有联合索引

多个列同时进行范围查找时,只有对索引最左边的那个列进行范围查找才用到B+树索引,也就是只有a用到索引。

下面的查询,b字段没法直接在索引层进行过滤:

select * from table_name where  a > 1 and a < 3 and b > 1;

在1<a<3的范围内, b是无序的,不能用索引,找到1<a<3的记录后,只能通过回表,根据条件 b > 1继续逐条过滤。

最左匹配原则的详细内容,请参见尼恩的公号文章:

贝壳面试:MySQL联合索引,最左匹配原则是什么?

尼恩的公号,被很多小伙伴私藏为宝藏号,建议大家 多多读读, 打好自己的知识基础。

2.2 没有索引下推场景下 的联合索引范围查询 执行流程

首先,我们的表里有两个索引(一个联合索引,一个聚族索引),示意图如下:

在这里插入图片描述

在MySQL 5.6之前,下面的SQL语句,演示没有索引下推 的联合索引范围查询

select * from tuser where name like 'a%' and age=10;

server层和Engine层的执行流程,大概如下:

  • 存储引擎根据Engine层 通过联合索引找到name like 'a%' 的主键id(1、2、3、4),根据最左匹配原则,Engine层 已经没有办法对 age=10 进行过滤了,

  • 这些数据主键id(1、2、3、4) 回到了 server层, server层 还需要进行 age=10 的条件过滤。办法是, 1、2、3、4 逐一进行回表扫描,去聚簇索引找到完整的行记录,server层再对数据根据age=10进行筛选。

在这里插入图片描述

在MySQL 5.6以前 是没有索引下推的,或者MySQL 5.6以后,可以关掉了索引下推 。

没有索引下推的场景, 存储引擎根据(name,age)联合索引进行range 范围,查询找到name like 'a%',仅仅用到了 联合索引的第一个name列,并没有用到联合索引的age列。

存储引擎返回id给 server层,为了获得age列 的数据,server需要进行 回表的操作,去到 去聚簇索引找到完整的行记录,server层再对数据根据age=10进行筛选。

可以看到 server层 需要回表多次,相当于把我们联合索引的另一个字段age浪费了。

前面讲到了,回表查询性能开销大

  • 多次随机 I/O

    回表查询通常需要从索引查到主键后,再通过主键到 聚族索引 中查找完整数据。

    这意味着,数据库可能需要进行多次磁盘 I/O 操作,尤其是在表非常大、且数据不在内存中的情况下。

    频繁的随机磁盘访问可能导致性能瓶颈。

那么问题来了, 前面的age列,在 联合索引是存在的,能不能直接在 Engine层 进行过滤呢。

尼恩直接告诉答案: 是可以的。

这就是索引下推。

2.3 图解一下:什么是索引下推?

索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本的新特性,它能减少回表查询次数,提高查询效率。

索引下推优化的原理

MySQL 服务层 负责SQL语法解析、生成执行计划等,并调用存储引擎层去执行数据的存储和检索。

在这里插入图片描述

我们来具体看一下,在没有使用ICP(Index Condition Pushdown,简称ICP) 的情况下,MySQL的查询:

  • 存储引擎读取索引记录;
  • 根据索引中的主键值,定位并读取完整的行记录;
  • 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

索引下推(Index Condition Pushdown,简称ICP) 的下推动作

其实就是指将部分上层(服务层)负责的事情,交给了下层(引擎层)去处理。 如下图所示

在这里插入图片描述

使用ICP的情况下,查询过程:

  • Engine层存储引擎读取 索引记录(不是完整的行记录);
  • 判断WHERE条件部分能否用索引中的列来做检查,条件不满足,则处理下一行索引记录;
  • 条件满足,使用索引中的主键去定位并读取完整的行记录(就是所谓的回表);
  • 存储引擎把记录交给Server层,Server层检测该记录是否满足WHERE条件的其余部分。

尼恩给大家做个一句话总结:

索引下推 就是 Engine层 提前把能处理的过滤,进行提前处理, 而不是一定等到 server层去做回表。

2.4 索引下推 场景下的联合索引范围查询 执行流程

由于联合索引中包含age列,所以 Engine层 存储引擎 直接在 联合索引里按照age=10过滤, 过滤完了之后,满足条件的 记录id 1 和2 到了server层。

server层使用 记录id 1 和2 进行回表。

我们看一下索引下推 场景下, 联合索引范围查询 执行流程 示意图:

在这里插入图片描述

对比一下, 前面的 索引下推 场景下的联合索引范围查询 执行流程 ,回表次数是 6次, 这里的回表记录是2次,直接少了 4次回表。

可见, 索引下推(Index Condition Pushdown, ICP)可以有效减少 回表,提升性能。

索引下推(Index Condition Pushdown, ICP)是 MySQL 5.6 及以上版本中的一项查询优化技术,用来减少回表操作。

索引下推(Index Condition Pushdown, ICP) 在扫描索引的过程中,将查询条件尽可能多地推送到索引级别进行过滤,而不是在回表之后再进行过滤。这样可以减少不必要的回表操作,提高查询性能。

2.5 通过 explain 演示一下 索引下推工作原理

通常,数据库在查询时,索引只能用于查找相关的主键或部分索引列。

其他查询条件,特别是非索引列的过滤条件,往往需要在回表之后才进行处理。而索引下推则允许数据库在遍历索引时,直接将查询中的部分过滤条件应用到索引扫描阶段,从而减少回表的次数和数据读取量。

美团面试:mysql 索引失效?怎么解决? (重点知识,建议收藏,读10遍+)

案例分析

使用之前,创建一张测试表 test_user表

表中包含:idid_cardageuser_nameheight、address字段。

CREATE TABLE `test_user` (  `id` int NOT NULL AUTO_INCREMENT,  `id_card` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,  `age` int DEFAULT '0',  `user_name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,  `height` int DEFAULT '0',  `address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `idx_id_card_age_user_name` (`id_card`,`age`,`user_name`),  KEY `idx_height` (`height`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

执行结果如下

在这里插入图片描述

脚本中,创建了三个索引:

  • 聚族索引 id:数据库的聚族索引
  • 联合索引 idx_id_card_age_user_name:由id_card、age和user_name三个字段组成的联合索引。
  • 非聚族索引 idx_height:普通索引

2.6 索引下推配置

在MySQL 5.6以前 是没有索引下推的,或者MySQL 5.6以后有索引下推,默认开启了,但可以关掉了索引下推 。

查看索引下推的配置:

show variables like '%optimizer_switch%';

如果输出结果中,显示 index_condition_pushdown=on,表示开启了索引下推

也可以手动开启索引下推

set optimizer_switch="index_condition_pushdown=on";

关闭索引下推

set optimizer_switch="index_condition_pushdown=off";

在这里插入图片描述

2.7 通过 explain 演示一下:未启用索引下推 的效果

关掉 索引下推

set optimizer_switch="index_condition_pushdown=off";

来一个 没有索引下推的查询,

  explain SELECT * FROM test_user WHERE id_card like 'a%' AND height > 180 

执行步骤(无索引下推)

  • 首先,数据库会通过索引 idx_id_card_age_user_name 只使用 id_card like ‘a%’ 的条件来查找符合条件的主键。
  • 查找到的记录 主键 后,server 层会 会回表读取 height 的值,然后再应用 `height > 180 的过滤条件。
  • 这意味着,可能查找到很多 like ‘a%’ 的记录,但这些记录未必都符合 `height > 180的条件,导致了大量的回表操作和不必要的数据读取。
  explain SELECT * FROM test_user WHERE id_card like 'a%' AND height > 180 

在这里插入图片描述

执行计划中的Extra列显示了Using where,表示没有用到了索引下推的优化逻辑。

2.8 通过 explain 演示一下: 启用索引下推 的效果

启用索引下推的查询, 打开索引下推的开关

set optimizer_switch="index_condition_pushdown=on";

同样的查询,如果启用了索引下推,执行流程会有所不同:

  explain SELECT * FROM test_user WHERE id_card like 'a%' AND height > 180 

执行步骤(有索引下推)

  • 数据库在扫描索引 idx_id_card_age_user_name 时,会立即应用 height > 180 的条件,而不需要等待回表之后再进行过滤。
  • 只有当索引级别的过滤通过时,才会发送id 到 server层
  • server层 回表去读取完整的记录。
  • 这样大大减少了不必要的回表操作,因为数据库已经在索引层面过滤掉了大量不符合条件的记录。

数据库直接在索引扫描时应用 height > 180 的条件,只回表获取符合该条件的记录。

在启用索引下推后,回表操作减少,查询性能会更优。

在这里插入图片描述

执行计划中的Extra列显示了Using index condition,表示用到了索引下推的优化逻辑。

2.9 索引下推使用条件和效果

索引下推优化效果

索引下推的核心好处在于:

  • 减少回表次数:通过在索引扫描时尽可能多地应用过滤条件,数据库减少了回表的次数,从而降低了磁盘 I/O 开销。
  • 提高查询性能:特别是在表很大、回表代价较高的情况下,索引下推能够显著提升查询效率。

索引下推应用范围

  1. 适用于InnoDB 引擎和 MyISAM 引擎的查询
  2. 适用于执行计划是range, ref, eq_ref, ref_or_null的范围查询
  3. 对于InnoDB表,仅用于非聚簇索引。索引下推的目标是减少全行读取次数,从而减少 I/O 操作。对于 InnoDB聚集索引,完整的记录已经读入InnoDB 缓冲区。在这种情况下使用索引下推 不会减少 I/O。
  4. 子查询不能使用索引下推
  5. 存储过程不能使用索引下推

索引下推适用场景

索引下推主要适用于以下场景:

  1. 联合索引:如果查询中涉及多个条件,而这些条件中的部分可以在索引层过滤,索引下推能起到良好的优化效果。
  2. 部分索引列的过滤:当查询涉及的过滤条件包括非索引列时,索引下推可以减少不必要的回表操作,优化查询过程。

索引下推优化是 MySQL 针对索引查询的一种提升性能的技术,特别适用于联合索引和复杂查询条件的场景。

通过将更多的查询条件推送到索引扫描阶段执行,能够减少回表次数,优化查询效率。

3 来一张Explain执行计划详解图:

在这里插入图片描述

4 mysql调优的相关系统参数

索引条件下推默认是开启的,可以使用系统参数optimizer_switch来控制器是否开启。

optimizer_switch 是 MySQL中一个重要的系统变量,它用于控制优化器在执行查询时是否启用或禁用某些优化功能。

这个参数可以接受多个值,每个值代表一个特定的优化器开关,合理配置这些参数可以显著提高数据库的查询性能和响应时间。

可以使用以下的命令获取当前数据库优化器参数:

SELECT @@optimizer_switch;

例子:

mysql> select @@optimizer_switch;
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)
其返回值为如下的形式:

代码语言:javascript

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on

参数分类与应用

    1. 表访问优化参数
    1. 表关联优化参数

4.1 表访问优化参数

这些参数主要影响如何访问和扫描表,特别是与索引使用和条件下推相关的优化。

  1. index_merge=on
    • 含义: 启用索引合并优化功能。
    • 使用场景: 当查询可以使用多个索引组合来提高效率时。
    • 引入版本: MySQL 5.1.
  2. index_merge_union=on
    • 含义: 允许使用多个索引进行UNION操作。
    • 使用场景: 当查询中涉及多个条件,需要联合多个索引进行优化时。
    • 引入版本: MySQL 5.1.
  3. index_merge_sort_union=on
    • 含义: 启用排序UNION的索引合并。
    • 使用场景: 当查询需要对合并后的索引结果进行排序时。
    • 引入版本: MySQL 5.1.
  4. index_merge_intersection=on
    • 含义: 允许使用多个索引进行交集操作。
    • 使用场景: 当查询条件需要多个索引的交集来优化时。
    • 引入版本: MySQL 5.1.
  5. engine_condition_pushdown=on
    • 含义: 启用存储引擎条件下推。
    • 使用场景: 将WHERE条件下推到存储引擎层以减少返回的行数,提高查询性能。
    • 引入版本: MySQL 5.1.
  6. index_condition_pushdown=on
    • 含义: 启用索引条件下推。
    • 使用场景: 在索引扫描期间,将WHERE条件下推到存储引擎中,以减少读取的行数。
    • 引入版本: MySQL 5.6.
  7. mrr=on
    • 含义: 启用Multi-Range Read (MRR)。
    • 使用场景: 优化范围扫描以减少磁盘I/O,提高查询效率。
    • 引入版本: MySQL 5.6.
  8. mrr_cost_based=on
    • 含义: 基于成本的MRR决策。
    • 使用场景: 根据成本模型决定是否启用MRR以提高查询效率。
    • 引入版本: MySQL 5.6.
  9. use_index_extensions=on
    1. 含义: 启用索引扩展使用。
    2. 使用场景: 使用索引扩展技术来优化查询。
    3. 引入版本: MySQL 5.6.
  10. use_invisible_indexes=off
    1. 含义: 禁用不可见索引。
    2. 使用场景: 控制查询是否使用不可见索引进行优化。
    3. 引入版本: MySQL 8.0.
  11. skip_scan=on
    1. 含义: 启用跳跃扫描。
    2. 使用场景: 在多列索引的情况下,通过跳过不必要的扫描来提高查询性能。
    3. 引入版本: MySQL 8.0.

4.2 表关联优化参数

这些参数主要影响表与表之间的连接操作,旨在提高连接查询的效率。

  1. block_nested_loop=on
    • 含义: 启用块嵌套循环连接。
    • 使用场景: 用于提高嵌套循环连接的性能,特别是在大数据集上。
    • 引入版本: MySQL 5.6.
  2. batched_key_access=off
    • 含义: 批量键访问(BKA)优化。
    • 使用场景: 适用于连接操作,通过批量获取键值来提高查询性能。
    • 引入版本: MySQL 5.6(默认关闭)。
  3. hash_join=on
    • 含义: 启用哈希连接。
    • 使用场景: 优化大数据集的连接操作,提高查询效率。
    • 引入版本: MySQL 8.0.
  4. condition_fanout_filter=on
    • 含义: 启用条件扇出过滤。
    • 使用场景: 优化连接操作中的条件过滤,以减少数据扫描量。
    • 引入版本: MySQL 5.7.

4.3 子查询优化参数

这些参数主要影响子查询的处理方式,旨在优化子查询的执行效率。

  1. materialization=on
    • 含义: 启用子查询物化。
    • 使用场景: 将子查询的结果存储在临时表中以提高查询性能。
    • 引入版本: MySQL 5.6.
  2. semijoin=on
    • 含义: 启用半连接优化。
    • 使用场景: 优化存在子查询(EXISTS)的性能。
    • 引入版本: MySQL 5.6.
  3. loosescan=on
    • 含义: 启用松散扫描优化。
    • 使用场景: 优化IN子查询的执行,特别是在存在重复值的情况下。
    • 引入版本: MySQL 5.6.
  4. firstmatch=on
    • 含义: 启用首匹配优化。
    • 使用场景: 优化存在子查询,使其在找到第一个匹配项后即停止扫描。
    • 引入版本: MySQL 5.6.
  5. duplicateweedout=on
    • 含义: 启用重复消除优化。
    • 使用场景: 在连接操作中消除重复行。
    • 引入版本: MySQL 5.6.
  6. subquery_materialization_cost_based=on
    • 含义: 基于成本的子查询物化决策。
    • 使用场景: 根据成本模型决定是否物化子查询以提高性能。
    • 引入版本: MySQL 5.7.
  7. subquery_to_derived=off
    • 含义: 禁用将子查询转换为派生表。
    • 使用场景: 控制查询优化器是否将子查询转换为派生表。
    • 引入版本: MySQL 8.0.

4.4 其他优化参数

这些参数涉及其他类型的优化,例如排序、查询结果一致性等。

  1. derived_merge=on
    • 含义: 启用派生表合并。
    • 使用场景: 优化派生表查询,将其合并到主查询中执行。
    • 引入版本: MySQL 5.7.
  2. prefer_ordering_index=on
    • 含义: 优先使用排序索引。
    • 使用场景: 在ORDER BY操作中优先使用索引进行排序以提高性能。
    • 引入版本: MySQL 8.0.
  3. hypergraph_optimizer=off
    • 含义: 禁用超图优化器。
    • 使用场景: 控制是否使用新的超图优化器进行查询优化。
    • 引入版本: MySQL 8.0.20.
  4. derived_condition_pushdown=on
    • 含义: 启用派生表条件下推。
    • 使用场景: 将WHERE条件下推到派生表中以减少数据扫描量,提高查询性能。
    • 引入版本: MySQL 8.0.

说在最后:有问题找老架构取经‍

关于什么是回表,什么是索引下推,尼恩给大家梳理的满分答案,已经彻底出来了

通过这个问题的深度回答,可以充分展示一下大家雄厚的 “技术肌肉”,让面试官爱到 “不能自已、口水直流”,然后实现”offer直提”。

在面试之前,建议大家系统化的刷一波 5000页《尼恩Java面试宝典PDF》,里边有大量的大厂真题、面试难题、架构难题。

很多小伙伴刷完后, 吊打面试官, 大厂横着走。

在刷题过程中,如果有啥问题,大家可以来 找 40岁老架构师尼恩交流。

另外,如果没有面试机会,可以找尼恩来改简历、做帮扶。

遇到职业难题,找老架构取经, 可以省去太多的折腾,省去太多的弯路。

尼恩指导了大量的小伙伴上岸,前段时间,刚指导一个40岁+被裁小伙伴,拿到了一个年薪100W的offer。

狠狠卷,实现 “offer自由” 很容易的, 前段时间一个武汉的跟着尼恩卷了2年的小伙伴, 在极度严寒/痛苦被裁的环境下, offer拿到手软, 实现真正的 “offer自由” 。

另外,尼恩也给一线企业提供 《DDD 的架构落地》企业内部培训,目前给不少企业做过内部的咨询和培训,效果非常好。

在这里插入图片描述

尼恩技术圣经系列PDF

  • 《NIO圣经:一次穿透NIO、Selector、Epoll底层原理》
  • 《Docker圣经:大白话说Docker底层原理,6W字实现Docker自由》
  • 《K8S学习圣经:大白话说K8S底层原理,14W字实现K8S自由》
  • 《SpringCloud Alibaba 学习圣经,10万字实现SpringCloud 自由》
  • 《大数据HBase学习圣经:一本书实现HBase学习自由》
  • 《大数据Flink学习圣经:一本书实现大数据Flink自由》
  • 《响应式圣经:10W字,实现Spring响应式编程自由》
  • 《Go学习圣经:Go语言实现高并发CRUD业务开发》

……完整版尼恩技术圣经PDF集群,请找尼恩领取

《尼恩 架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》PDF,请到下面公号【技术自由圈】取↓↓↓

这篇关于贝壳面试:什么是回表?什么是索引下推?的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1148379

相关文章

字节面试 | 如何测试RocketMQ、RocketMQ?

字节面试:RocketMQ是怎么测试的呢? 答: 首先保证消息的消费正确、设计逆向用例,在验证消息内容为空等情况时的消费正确性; 推送大批量MQ,通过Admin控制台查看MQ消费的情况,是否出现消费假死、TPS是否正常等等问题。(上述都是临场发挥,但是RocketMQ真正的测试点,还真的需要探讨) 01 先了解RocketMQ 作为测试也是要简单了解RocketMQ。简单来说,就是一个分

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

秋招最新大模型算法面试,熬夜都要肝完它

💥大家在面试大模型LLM这个板块的时候,不知道面试完会不会复盘、总结,做笔记的习惯,这份大模型算法岗面试八股笔记也帮助不少人拿到过offer ✨对于面试大模型算法工程师会有一定的帮助,都附有完整答案,熬夜也要看完,祝大家一臂之力 这份《大模型算法工程师面试题》已经上传CSDN,还有完整版的大模型 AI 学习资料,朋友们如果需要可以微信扫描下方CSDN官方认证二维码免费领取【保证100%免费

java面试常见问题之Hibernate总结

1  Hibernate的检索方式 Ø  导航对象图检索(根据已经加载的对象,导航到其他对象。) Ø  OID检索(按照对象的OID来检索对象。) Ø  HQL检索(使用面向对象的HQL查询语言。) Ø  QBC检索(使用QBC(Qurey By Criteria)API来检索对象。 QBC/QBE离线/在线) Ø  本地SQL检索(使用本地数据库的SQL查询语句。) 包括Hibern

毕业前第二次面试的感慨

距面试已经过去了有几天了,我现在想起来都有说多的恨感慨。 我一直都是想找刚刚起步的企业,因为这能让我学到更多的东西,然而正好有一家企业是刚起步的,而且他还有自己的产品专利,可以说这是一家,即是创业又是刚起步的公司,这家公司回复了我投给他的简历,这家企业想进一步了解我的情况,因为简历上我符合这家企业的基本要求,所以要进一步了解。 虽然面试的过程中,他给我的面试题,我做得并不是很理想,

Mysql高级篇(中)——索引介绍

Mysql高级篇(中)——索引介绍 一、索引本质二、索引优缺点三、索引分类(1)按数据结构分类(2)按功能分类(3) 按存储引擎分类(4) 按存储方式分类(5) 按使用方式分类 四、 索引基本语法(1)创建索引(2)查看索引(3)删除索引(4)ALTER 关键字创建/删除索引 五、适合创建索引的情况思考题 六、不适合创建索引的情况 一、索引本质 索引本质 是 一种数据结构,它用

腾讯社招面试经历

前提:本人2011年毕业于一个普通本科,工作不到2年。   15号晚上7点多,正在炒菜做饭,腾讯忽然打电话来问我对他们的Linux C++的职位是否感兴趣,我表达了我感兴趣之后,就开始了一段简短的电话面试,电话面试主要内容:C++和TCP socket通信的一些基础知识。之后就问我一道算法题:10亿个整数,随机生成,可重复,求最大的前1万个。当时我一下子就蒙了,没反应过来,何况我还正在烧

完整的腾讯面试经过

从9月10号开始到现在快两个月了,两个多月中,我经历数次面试和笔试,在经历这些的同时积累了不少的经验,也学到了不少东西,在此把它记录下来,算是和一起找工作中的同学一起共勉吧。我是本校的学生,专业是机械制造及其自动化,找工作的主要目标是计算机软件类和机械制造方向的国内的企业,所以意向去外企的同学就不必浪费时间看这些面经啦,想去国内IT企业的同学可以继续看下去。本贴中我把最近的腾讯面试经过写下

仕考网:结构化面试流程介绍

(一)结构化面试 结构化面试,也叫做标准化面试,考官按照预先设定好的一套试题以问答方式与应试者当面交谈,根据应试者的言语、行为表现,对其相关能力和个性特征作出相应评价。 (二)考试流程 抵达考场——审核抽签——面试候考——进入考场——面试答题——考生退场——计分审核 (三)答题技巧 1.声音洪亮,音量可以比平时说话声音大一点。 2.语速不要过快,语速快容易卡顿,而且不便于考官听清答

嵌入式面试经典30问:二

1. 嵌入式系统中,如何选择合适的微控制器或微处理器? 在嵌入式系统中选择合适的微控制器(MCU)或微处理器(MPU)时,需要考虑多个因素以确保所选组件能够满足项目的具体需求。以下是一些关键步骤和考虑因素: 1.1 确定项目需求 性能要求:根据项目的复杂度、处理速度和数据吞吐量等要求,确定所需的处理器性能。功耗:评估系统的功耗需求,选择低功耗的MCU或MPU以延长电池寿命或减少能源消耗。成本