尼恩说在前面

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

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

3、说说什么是 回表，什么是 索引下推 ？

最新《尼恩 架构笔记》《尼恩高并发三部曲》《尼恩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 其他优化参数

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

1 什么是 回表（table lookup）？

1.1 MySQL大概的架构

• 第1层：连接层

• 第2层：服务层：

• 第3层：存储引擎层：

• 第4层：文件系统层：

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

1. Server 层（查询层）：
   在这一层，数据库接收查询请求并决定如何执行查询。
   查询优化器会决定是否需要进行回表操作。通过二级索引 查找到记录的主键后，查询层会发出回表的请求。
   此时，实际的数据读取工作还没有发生。
2. Engine 存储层：回表的实际数据读取是在存储层完成的。
   一旦查询层决定需要回表，存储层负责根据主键从存储介质（例如硬盘或内存）中提取相应的完整记录。
   存储层负责处理物理数据读取、缓存管理、磁盘 I/O 等操作。

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

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

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

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

• 存储方式：非聚集索引维护了索引键值和指向实际数据行的指针之间的映射关系。
  索引键值与数据行的物理存储顺序无关，数据行的实际内容可能分散存储在磁盘上。
• 影响查询：通过非聚集索引进行查询时，数据库引擎首先根据索引键值找到对应的 指针或引用，然后再根据指针或引用去检索相应的数据行。适用于频繁的搜索和查询，但可能需要额外的IO操作。
• 唯一性：一个表可以有多个非聚集索引，不要求索引键值是唯一的。
• 存储数据：当前字段的值和指向数据行的指针或引用（通俗的说就是当前字段的主键值 Primary Key）

使用案例介绍：回表查询

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

SELECT salary FROM employees WHERE employee_id = 100;

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

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

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

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

1.5 怎样避免 回表查询 ？                     

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

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

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

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

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

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

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

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

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

这里，假设 a b 有联合索引

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

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

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

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

• 存储引擎根据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进行筛选。

• 多次随机 I/O：
  回表查询通常需要从索引查到主键后，再通过主键到 聚族索引 中查找完整数据。
  这意味着，数据库可能需要进行多次磁盘 I/O 操作，尤其是在表非常大、且数据不在内存中的情况下。
  频繁的随机磁盘访问可能导致性能瓶颈。

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

索引下推优化的原理

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

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

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

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

案例分析

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 索引下推配置   

show variables like '%optimizer_switch%';

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

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层 回表去读取完整的记录。
• 这样大大减少了不必要的回表操作，因为数据库已经在索引层面过滤掉了大量不符合条件的记录。

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. 部分索引列的过滤：当查询涉及的过滤条件包括非索引列时，索引下推可以减少不必要的回表操作，优化查询过程。

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

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

其返回值为如下的形式：

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. 表访问优化参数

2. 表关联优化参数

4.1 表访问优化参数

1. index_merge=on

• 含义: 启用索引合并优化功能。
• 使用场景: 当查询可以使用多个索引组合来提高效率时。
• 引入版本: MySQL 5.1.

• 含义: 允许使用多个索引进行UNION操作。
• 使用场景: 当查询中涉及多个条件，需要联合多个索引进行优化时。
• 引入版本: MySQL 5.1.

• 含义: 启用排序UNION的索引合并。
• 使用场景: 当查询需要对合并后的索引结果进行排序时。
• 引入版本: MySQL 5.1.

• 含义: 允许使用多个索引进行交集操作。
• 使用场景: 当查询条件需要多个索引的交集来优化时。
• 引入版本: MySQL 5.1.

• 含义: 启用存储引擎条件下推。
• 使用场景: 将WHERE条件下推到存储引擎层以减少返回的行数，提高查询性能。
• 引入版本: MySQL 5.1.

• 含义: 启用索引条件下推。
• 使用场景: 在索引扫描期间，将WHERE条件下推到存储引擎中，以减少读取的行数。
• 引入版本: MySQL 5.6.

• 含义: 启用Multi-Range Read (MRR)。
• 使用场景: 优化范围扫描以减少磁盘I/O，提高查询效率。
• 引入版本: MySQL 5.6.

• 含义: 基于成本的MRR决策。
• 使用场景: 根据成本模型决定是否启用MRR以提高查询效率。
• 引入版本: MySQL 5.6.

1. 含义: 启用索引扩展使用。
2. 使用场景: 使用索引扩展技术来优化查询。
3. 引入版本: MySQL 5.6.

1. 含义: 禁用不可见索引。
2. 使用场景: 控制查询是否使用不可见索引进行优化。
3. 引入版本: MySQL 8.0.

1. 含义: 启用跳跃扫描。
2. 使用场景: 在多列索引的情况下，通过跳过不必要的扫描来提高查询性能。
3. 引入版本: MySQL 8.0.

4.2 表关联优化参数

1. block_nested_loop=on

• 含义: 启用块嵌套循环连接。
• 使用场景: 用于提高嵌套循环连接的性能，特别是在大数据集上。
• 引入版本: MySQL 5.6.

• 含义: 批量键访问（BKA）优化。
• 使用场景: 适用于连接操作，通过批量获取键值来提高查询性能。
• 引入版本: MySQL 5.6（默认关闭）。

• 含义: 启用哈希连接。
• 使用场景: 优化大数据集的连接操作，提高查询效率。
• 引入版本: MySQL 8.0.

• 含义: 启用条件扇出过滤。
• 使用场景: 优化连接操作中的条件过滤，以减少数据扫描量。
• 引入版本: MySQL 5.7.

4.3 子查询优化参数

1. materialization=on

• 含义: 启用子查询物化。
• 使用场景: 将子查询的结果存储在临时表中以提高查询性能。
• 引入版本: MySQL 5.6.

• 含义: 启用半连接优化。
• 使用场景: 优化存在子查询（EXISTS）的性能。
• 引入版本: MySQL 5.6.

• 含义: 启用松散扫描优化。
• 使用场景: 优化IN子查询的执行，特别是在存在重复值的情况下。
• 引入版本: MySQL 5.6.

• 含义: 启用首匹配优化。
• 使用场景: 优化存在子查询，使其在找到第一个匹配项后即停止扫描。
• 引入版本: MySQL 5.6.

• 含义: 启用重复消除优化。
• 使用场景: 在连接操作中消除重复行。
• 引入版本: MySQL 5.6.

• 含义: 基于成本的子查询物化决策。
• 使用场景: 根据成本模型决定是否物化子查询以提高性能。
• 引入版本: MySQL 5.7.

• 含义: 禁用将子查询转换为派生表。
• 使用场景: 控制查询优化器是否将子查询转换为派生表。
• 引入版本: MySQL 8.0.

4.4 其他优化参数

1. derived_merge=on

• 含义: 启用派生表合并。
• 使用场景: 优化派生表查询，将其合并到主查询中执行。
• 引入版本: MySQL 5.7.

• 含义: 优先使用排序索引。
• 使用场景: 在ORDER BY操作中优先使用索引进行排序以提高性能。
• 引入版本: MySQL 8.0.

• 含义: 禁用超图优化器。
• 使用场景: 控制是否使用新的超图优化器进行查询优化。
• 引入版本: MySQL 8.0.20.

• 含义: 启用派生表条件下推。
• 使用场景: 将WHERE条件下推到派生表中以减少数据扫描量，提高查询性能。
• 引入版本: MySQL 8.0.

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

• 被裁太爽：每月大涨1W，10年CRUD小伙，靠转架构一个月上岸，年薪50W爽歪了！

• 绝地逢生：9年经验自考小伙伴，跟着尼恩狠卷3月硬核技术，面试机会爆表，2周后收3个offer ，满血复活

• 逆天改命：3年经验2本，卷3个月涨薪60%，进准大厂(得物、乐信、58集团等)，年薪36W

• 女35岁CRUD程序媛，12月被裁后，拿30个机会2个offer上岸，年底大裁员太不容易了

• 12年小伙转架构，1小时蜕变收年薪55万offer，秘诀：GO+Java双栖架构

• 不怕裁员，8年小伙去中年危机，秘诀：换架构师赛道，上得厅堂，下得厨房，越早越好

• 逆天啦：4年crud小伙收shein+银行两优质offer，狠卷1月收年薪43万大涨30%

• 太劲爆.... 被裁4个月，38岁Android转Java，2个月提架构offer

• 起死回生：8年小伙高中毕业+频繁跳槽，狠卷2月，提Java高级offer

• 爽翻了：指导3轮，5年小伙收5大offer，涨50%，领路模式太牛

• 降维攻击，37年大龄老伙喜提60W年薪offer，1个月顺利上岸

• 极速拿offer：阿里P6被裁后极速上岸，1个月内喜提2优质offer(含滴滴)

• 裁就裁，6年小伙60W极速上岸，白拿20W还游一圈拉萨，真香