MySQL 5.7 ICP索引条件下推优化

ICP索引条件下推是5.7默认的一个优化，
意思是把MySQL Server 层的过滤"下推"到存储引擎，用索引叶子节点上现成的列值提前过滤，换取回表次数的减少。它不如复合索引的"精确定位"高效，但远好于全量回表再过滤。
先要明白MySQL的架构：

┌─────────────────────────────────────────────┐
│           客户端层 (Client Layer)             │
│   mysql CLI / DBeaver / JDBC / ODBC          │
├─────────────────────────────────────────────┤
│          服务层 (MySQL Server Layer)         │
│  ┌─────────────────────────────────────┐    │
│  │   连接管理 / 线程处理 (Connection Pool) │   │
│  ├─────────────────────────────────────┤    │
│  │   查询缓存 (Query Cache) ← 5.7 有/8.0 移除│ │
│  ├─────────────────────────────────────┤    │
│  │   解析器 (Parser) → 词法/语法分析      │    │
│  ├─────────────────────────────────────┤    │
│  │   优化器 (Optimizer) → 执行计划选择    │    │
│  ├─────────────────────────────────────┤    │
│  │   执行器 (Executor) → 逐行读取/过滤/聚合│    │
│  └─────────────────────────────────────┘    │
├─────────────────────────────────────────────┤
│        存储引擎层 (Storage Engine Layer)      │
│     InnoDB (5.5+ 默认)                      │
│     数据文件 / Redo Log / Undo Log / Buffer Pool │
├─────────────────────────────────────────────┤
│            文件系统 (File System)            │
└─────────────────────────────────────────────┘

这是分层架构，Server层执行器Executor去存储引擎搂数据、返回客户端之前，会先过滤。没在索引机制用上的查询条件用在这里的过滤。
整条过滤流水线是这样的：

WHERE name LIKE 'A%' AND age = 25 AND status = 1 AND email IS NOT NULL
      └──────┬──────┘   └────┬───┘   └────┬────┘   └──────┬──────┘
             │               │             │               │
      B+Tree定位范围      ICP下推      Server层过滤      Server层过滤
      （存储引擎）      （存储引擎）    （执行器）        （执行器）
             │               │             │               │
             ▼               ▼             ▼               ▼
       只扫描 A% 的    跳过 age≠25    回表后的行上     回表后的行上
       索引区间        的索引条目      逐行判断        逐行判断

EXPLAIN 里怎么区分：

┌───────────────────────┬───────────────────────┬──────────────────────────────────────┐
│     过滤发生在哪        │      Extra 显示       │                 说明                  │
├───────────────────────┼───────────────────────┼──────────────────────────────────────┤
│ 全部在存储引擎完成       │ 无 Using where        │ 索引条件+ICP覆盖了所有 WHERE 条件        │
├───────────────────────┼───────────────────────┼──────────────────────────────────────┤
│ 有剩余条件交给 Server   │ Using where           │ 回表取到行后，执行器逐行判断剩余条件       │
├───────────────────────┼───────────────────────┼──────────────────────────────────────┤
│ ICP 生效               │ Using index condition │ 部分条件在索引叶子层面就过滤了            │
└───────────────────────┴───────────────────────┴──────────────────────────────────────┘

所以在 EXPLAIN 里看到 Using where，就知道：有部分 WHERE 条件没能在索引机制里消化掉，是执行器把行捞回来之后才过滤的。

好，我们接着讲解一下ICP的原理。

INDEX idx_name_age (name, age)

SELECT * FROM users WHERE name LIKE 'A%' AND age = 25;
--                                    ↑                ↑
--                            索引可以用       ICP 在索引层面过滤 age
--
-- 没有 ICP：name LIKE 'A%' 的全部回表，Server 再过滤 age=25
-- 有 ICP：  索引遍历时直接跳过 age≠25 的，只回表 age=25 的
--
-- EXPLAIN Extra: "Using index condition"

上面例子的复合索引，按最左前缀原则，只用到了name索引列，age失效。 但是查询条件又确实用到了2个索引列，ICP可以拉一把。

按照name LIKE 'A%'去二级索引找到匹配的所有主键，再去聚集索引找到这些主键对应的行。
然后从存储引擎返回MySQL Server，执行器 (Executor) → 逐行读取，带age=25条件进行过滤。
以上是没有ICP的时候的情况。

而有了ICP，在去二级索引按照name LIKE 'A%'找匹配的主键的时候，顺便就会用age=25把数据进行过滤。
然后回表的时候，回表条数就会少很多。 MySQL Server那里也不用过滤了。
相当于把MySQL Server 执行器的过滤，搬到了二级索引过滤，但是减少了回表条数，提高了效率和性能。

当然，这也没有复合索引生效那么高效，
区别在于，ICP情形是获取到前一个索引列对应的主键所在的叶子节点之后，对这些记录进行逐条遍历，然后进行过滤。
复合索引是找到前一个索引列对应的主键记录后，按后一个索引列找到age=25按顺序进行批量读取。
如下所示：

ICP 情形：  在【二级索引自己的叶子节点】上逐条遍历
           （每个叶子条目 = name + age + PK）
           读到一条 → 看自带的 age → 25 就记下 PK 去回表，≠25 跳过
           特点：name LIKE 'A%' 区间内的叶子条目【全都读过一遍】

复合索引情形：B+Tree 树形查找直接定位到 (name, age=25) 的起始叶子位置
           age≠25 的叶子条目【根本不会被读到】
           特点：跳过不读 vs 读过再过滤 —— 这就是差距