贝壳面试：什么是回表？什么是索引下推？

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

尼恩说在前面

在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）通常是指数据库查询过程中，需要从索引层查找到某条记录的主键，再通过这个主键回到数据表中获取完整的记录。

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

Server 层（查询层）：

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

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

此时，实际的数据读取工作还没有发生。
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表

表中包含：id、id_card、age、user_name和height、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 开销。
提高查询性能：特别是在表很大、回表代价较高的情况下，索引下推能够显著提升查询效率。

索引下推应用范围

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

索引下推适用场景

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

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

索引下推优化是 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 表访问优化参数

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

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

4.2 表关联优化参数

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

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

4.3 子查询优化参数

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

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

4.4 其他优化参数

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

derived_merge=on
- 含义: 启用派生表合并。
- 使用场景: 优化派生表查询，将其合并到主查询中执行。
- 引入版本: MySQL 5.7.
prefer_ordering_index=on
- 含义: 优先使用排序索引。
- 使用场景: 在ORDER BY操作中优先使用索引进行排序以提高性能。
- 引入版本: MySQL 8.0.
hypergraph_optimizer=off
- 含义: 禁用超图优化器。
- 使用场景: 控制是否使用新的超图优化器进行查询优化。
- 引入版本: MySQL 8.0.20.
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，请到下面公号【技术自由圈】取↓↓↓

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

贝壳面试：什么是回表？什么是索引下推？

尼恩说在前面

1、回表查询（table lookup）是什么？

1.1 MySQL大概的架构

1.2 回表（table lookup） 的简单介绍

1.3 由浅入深，一步一步理解回表查询（table lookup）

基本概念：聚集索引（Clustered Index）：

基本概念：非聚集索引（Non-Clustered Index）：

使用案例介绍：回表查询

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

1.5 怎样避免回表查询？

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

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

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

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

2.3 图解一下：什么是索引下推？

索引下推优化的原理

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

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

案例分析

2.6 索引下推配置

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

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

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

索引下推优化效果

索引下推应用范围

索引下推适用场景

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

4 mysql调优的相关系统参数

其返回值为如下的形式：

4.1 表访问优化参数

4.2 表关联优化参数

4.3 子查询优化参数

4.4 其他优化参数

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

尼恩技术圣经系列PDF

相关文章

1.2 回表（table lookup）的简单介绍

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

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

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

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

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

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

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

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