科普文：软件架构数据库系列之【MySQL查询优化器中的优化策略optimizer_switch--ICP索引下推】

本文链接：https://blog.csdn.net/Rookie_CEO/article/details/142183889

概叙

科普文：软件架构数据库系列之【MySQL查询优化器中的优化策略optimizer_switch】-CSDN博客

前面我们讲解了， MySQL查询优化器中的优化策略optimizer_switch，今天继续，结合索引下推，我们来熟悉一下optimizer_switch的调整对查询语句的影响。

1 什么是索引下推 Index Condition Pushdown (ICP)

索引下推 (Index Condition Pushdown，索引条件下推，简称 ICP)，是 MySQL5.6 版本的新特性，它可以在对联合索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，能有效的减少回表次数（目前我们使用的 mysql 版本较高，一般大家可能感觉这是正常的，但是 mysql5.6 之前都不是这样实现的，下面会细细道来）。

　 Index Condition Pushdown (ICP)是MySQL使用索引从表中检索行的一种优化。如果没有ICP，存储引擎将遍历索引以定位表中的行，并将它们返回给MySQL服务器，服务器将判断行的WHERE条件。在启用ICP的情况下，如果可以只使用索引中的列来计算WHERE条件的一部分，MySQL服务器就会将WHERE条件的这一部分推到存储引擎中。然后，存储引擎通过使用索引条目来评估推入的索引条件，只有当满足该条件时，才从表中读取行。ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。

1.1 ICP适用条件

MySQL :: MySQL 8.0 Reference Manual :: 10.2.1.6 Index Condition Pushdown Optimization

我们先来了解一下索引下推的使用条件及限制：

只支持 select。
当需要访问全表时，ICP 用于type值（join 类型）为 range，ref，eq_ref 和 ref_or_null 访问类型。
ICP 可用于 InnoDB 和 MyISAM 表，包括分区的 InnoDB 和 MyISAM 表。（5.6 版本不适用分区表查询，5.7 版本后可以用于分区表查询）。
对于 InnDB 引擎只适用于二级索引（也叫辅助索引），因为 InnDB 的聚簇索引会将整行数据读到 InnDB 的缓冲区，这样一来索引条件下推的主要目的减少 IO 次数就失去了意义。因为数据已经在内存中了，不再需要去读取了。
‌单列索引可以支持索引下推(Index Condition Pushdown, ICP)‌。索引下推是一种优化技术，它允许数据库在索引层面就过滤掉不满足查询条件的数据，从而减少需要访问的数据块数量，提高查询效率。这种技术在单列索引和多列索引（如联合索引）中都可以应用，旨在减少不必要的磁盘I/O操作，提升查询性能。通过在索引树的节点上进行条件过滤，索引下推技术避免了从磁盘读取不必要的数据，降低了IO开销，从而提高了查询速度和响应速度‌。
单列索引：如果就只有一列索引，是无所谓有没有索引下推这个功能的，所以我们只讨论存在多个where条件，这些条件中的多列索引（如联合索引）的情况。
在虚拟生成列上创建的辅助索引不支持 ICP（注：InnoDB 支持虚拟生成列的辅助索引）。
使用了子查询的条件无法下推。
使用存储过程或函数的条件无法下推（因为因为存储引擎没有调用存储过程或函数的能力）。
触发条件无法下推。（有关触发条件的信息，请参阅官方资料：Section 8.2.2.3, “Optimizing Subqueries with the EXISTS Strategy”.。MySQL :: MySQL 8.0 Reference Manual :: 10.2.2.3 Optimizing Subqueries with the EXISTS Strategy）

1.2 ICP原理介绍

首先，我们大致回顾下 mysql 的基本架构：

MySQL 基本的架构示例图

MySQL 服务层主要负责 SQL 语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和查询。
索引下推的下推其含义就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。
在 MySql 5.6 版本之前没有索引下推这个功能，从 5.6 版本后才加上了这个优化项。我们先简单对比一下使用和未使用 ICP 两种情况下，MySql 的查询过程吧。

1) 未使用 ICP 的情况下：

MySQL Server发出读取数据的命令，通过函数指针和handle接口（接口层）调用存储引擎的索引读或全表表读。如果有可使用的索引则进行的是索引读，但是只能选择一个索引。
指令进入存储引擎，读取索引树，在索引树上查找，拿到到符合条件的主键值，回表把满足条件的记录从表记录中读出，从存储引擎返回标识的结果
从存储引擎返回查找到的多条元组给MySQL Server，MySQL Server在得到单一索引过滤后的数据
MySQL Server拿到上述数据后，按照where中其他的条件进行过滤，得到符合条件的最终结果。

2) 使用 ICP 的情况下：

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

同上，MySQL Server发出读取数据的命令，通过函数指针和handle接口（接口层）调用存储引擎的索引读或全表表读。如果有可使用的索引则进行的是索引读，但是这里会把含有索引的列的where条件都下放到存储引擎层。
指令进入存储引擎，读取某个索引树，在该索引树上查找过滤出相应数据后，并不直接去回表查询数据，而是继续通过联合索引的其他索引条件进行过滤把满足已经下推的条件的主键拿到。然后再去回表查询所有符合条件的数据（数据较少，io也就较少）。例如
```
#假test表有个联合索引包含两列 a，b。

假如语句如下：
select * from test where a like "zhang%" and b > 10;
两个条件都会下推到存储引擎，因为最左前缀，会先按照a来筛选。其次再使用b来过滤筛选过的索引值。然后再回表查询。
```
从存储引擎返回查找到的少量数据给MySQL Server，MySQL Server在根据其他的条件进行筛选。

对比

第 1 步中MySQL Server发送了额外的索引条件到存储引擎，多了一点点网络开销
第 2 步中的回表阶段：由于比第一步过滤掉了更多的表数据，所以少了很多磁盘io
第 3 步中
接收数据阶段：要接收更多的数据，增加内部开销

二次过滤数据阶段：要处理更多的数据，占用了更多的cpu和内存。

2 具体示例

上面介绍了基本原理，下面使用示例，带大家更直观的进行理解（注：以下示例基于 InnoDB 存储引擎。）
首先，我们新建一张用户表（jxc_user），设置 id 为主键索引，并创建联合索引（name, age）。

我们先看一下该表主键索引的大致结构示例：

主键索引结构示例图
然后我们再看一下该表联合索引的大致结构示例：

联合索引结构示例图

如果现在有一个需求，要求检索出表中名字第一个字是张，而且年龄等于 10 岁的所有用户。示例 SQL 语句如下：

select id,name,age,tel,addr from jxc_user where name like '张%' and age=10;

根据索引最左匹配原则，上面这个 sql 语句在查索引树的时候，只能用 “张”，查到第一个满足条件的记录：id 为 1。
那接下来我们具体看一下使用与未使用 ICP 的情况。

2.1 未使用 ICP 的情况

在 MySQL 5.6 之前，存储引擎根据联合索引先找到 name like ‘张 %’ 的主键 id（1、4），再逐一进行回表扫描，去聚簇索引找到完整的行记录，返回 server 层，server 层拿到数据后，再根据条件 age=10 对拿到的数据进行筛选。大致的示意图如下：

从上图，可以看到需要回表两次，存储引擎并不会去按照 age=10 进行过滤，相当于联合索引的另一个字段 age 在存储引擎层没有发挥作用，比较浪费。

2.2 使用 ICP 的情况

而 MySQL 5.6 以后，存储引擎会根据（name，age）联合索引，找到 name like ‘张 %’，由于联合索引中包含 age 列，所以存储引擎直接再联合索引里按照条件 age=10 进行过滤，然后根据过滤后的数据再依次进行回表扫描。大致的示意图如下：

从上图，可以看到只是 id=1 的数据，回表了一次。

除此之外我们还可以看一下执行计划，看到 Extra 一列里 Using index condition，就是用到了索引下推。备注：有Using index condition不一定就是索引下推。

2.3 Extra有Using index condition不一定就是索引下推

"Using index condition" 是 MySQL 解释执行计划中的一个状态，表示查询中使用了索引，但需要对索引中的部分数据进行条件检测（即对索引中的某些行进行过滤）。这种情况并不一定意味着发生了“索引下推”（Index Condition Pushdown, ICP）。

索引下推（ICP）是 MySQL 5.6 及以上版本的特性，当查询需要通过索引来访问数据，但该索引不覆盖查询所需的所有列时，ICP 可以在索引遍历过程中进行过滤，减少对数据行的访问。

问题解决：

如果你希望启用索引下推，但当前查询没有使用，你可以通过以下方式进行优化：

确保查询中的条件能够使用索引。
如果可能，创建一个覆盖索引，它包含了查询中需要的所有列。
使用 EXPLAIN 查看查询的执行计划，确认是否使用了索引下推。
ICP适用条件

如果你的查询已经在使用索引下推，但想要进一步优化，可以考虑以下措施：

重构查询，使得能够更有效地利用索引。
调整索引，以便更好地匹配查询条件，可能包括创建多列索引或者调整索引的顺序。
考虑对数据库的结构做出更改，比如增加合适的索引，或者对表结构进行优化。

查询数据时如果使用 index condition down 索引条件下推就会在执行计划的 extra 字段中出现 "Using index condition"。

使用二级索引查找数据时，where 条件中属于索引一部分但无法使用索引的条件（比如 like '%abc' 左侧字符不确定），MySQL 也会把这部分判断条件下推到存储引擎层，筛选之后再进行回表，这样回表时需要查找的数据就更少。

索引条件下推的特点：

下推的条件涉及的字段一定要是使用到的二级索引的一部分，因为二级索引存储了这些字段的值，才能进行筛选，所以叫做“索引条件下推”；
大幅减小回表时的随机 I/O 开销。因为索引条件下推可以在查找完二级索引后利用条件筛选，减小结果集，减小接下来回表的次数，而回表做的是随机 I/O（开销大），所以能够节省大量的 I/O 开销；
大幅减小了存储引擎层到 MySQL 服务层的传输开销。条件下推给了存储引擎层，提前进行筛选，这样返回给 MySQL 服务层的数据就变少了；
剩下的不能用到索引的 where 条件还是在 MySQL 服务层生效。

示例 1

有一个组合索引：idx_a (first_name,last_name,birth_date)

SQL:

mysql> explain select * from employees where first_name='Mayuri' and last_name like '%Alpay'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: idx_a
          key: idx_a
      key_len: 58
          ref: const
         rows: 230
     filtered: 11.11
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

查询的 where 子句中 first_name='Mayuri' 条件可以使用到二级索引 idx_a，而 last_name like '%Alpay' 条件最左的字符不固定所以不能使用二级索引。索引下推的执行过程是：

通过二级索引 idx_a 查找满足 first_name='Mayuri' 条件的记录；
MySQL 把条件 last_name like '%Alpay' 下推到 InnoDB 层，对上一步的结果进行筛选（因为 last_name 是索引 idx_a 的一部分，所以可以直接筛选，否则不行）；
取出上一步结果中的主键值，进行回表。

示例 2

有一个组合索引：idx_a (first_name,last_name,birth_date)

SQL: select * from employees where first_name > 'Mayuri' and last_name = 'Alpay';

在索引树上 first_name > 'Mayuri' 的数据行，对于 last_name 的值来说是无序的。所以搜索索引树时，只能用 first_name > 'Mayuri' 来找出数据，然后再把 last_name = 'Alpay' 这个条件下推到 innodb 层筛选数据。

示例 3

有一个组合索引：idx_a (first_name,last_name,birth_date)

SQL 为：

mysql> explain select * from employees where  first_name='Mayuri' and last_name > 'Alpay'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: range
possible_keys: idx_a
          key: idx_a
      key_len: 124
          ref: NULL
         rows: 226
     filtered: 100.00
        Extra: Using index condition

虽然这里显示了 “Using index condition”，但实际上是不需要做索引条件下推的，因为索引的第一个字段固定，对于 last_name 字段值来说也是有序的。这属于一个 “bug”，特地考古找到了丁奇老师回复的一条评论：

示例 4

如果是索引覆盖不需要回表，即使发生索引条件下推，也不会出现 "Using index condition"：

mysql> explain select first_name,last_name,birth_date from employees where  
first_name > 'Mayuri' and last_name > 'Alpay'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: range
possible_keys: idx_a
          key: idx_a
      key_len: 124
          ref: NULL
         rows: 226
     filtered: 100.00
        Extra: Using where; Using index

小结

总的来说，只要是用到 index filter，就会发生索引条件下推。但不一定出现 Using index condition 就一定发生了索引条件下推。关于 index filter 的概念，详见：SQL 中的 where 条件，在数据库中提取与应用浅析。