MySQL索引下推

一、核心思想：

索引条件下推的核心思想是：在遍历索引时，尽早地利用索引中的列来过滤掉不满足条件的记录，从而减少需要回表查询的次数。

它的本质是将一部分过滤工作从存储引擎之上（Server层）“下推”到了存储引擎层来执行。

二、为什么需要 ICP？—— 解决性能瓶颈

要理解ICP的价值，我们需要先了解没有ICP时，数据库是如何处理一个带有WHERE条件的查询的。

假设我们有一张表 user，并建立了一个联合索引 idx_age_name (age, name)。

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age_name` (`age`,`name`)
) ENGINE=InnoDB;

现在执行一个查询：

SELECT * FROM user 
WHERE age > 20 
  AND name LIKE '%明%'
  AND score > 90;

1. 没有ICP的情况下（MySQL 5.6之前）：

存储引擎层：
- 存储引擎根据索引 idx_age_name 进行扫描，找到所有满足 age > 20 条件的索引记录（因为age是索引的最左前缀）。
- 对于每一条索引记录，不管name字段是否满足条件，存储引擎都会根据索引中的主键值（id）进行回表（Bookmark Lookup），去主键索引（聚簇索引）中读取整行数据。
- 然后将整行数据返回给上层的Server层。
Server层：
- Server层接收到存储引擎返回的整行数据。
- 然后由Server层的WHERE条件处理器来评估所有的条件：age > 20（可能已经满足）、name LIKE ‘%明%’、score > 90。
- 只有满足所有条件的行，才会作为最终结果返回。

性能问题： 这里最大的开销在于，所有 age > 20 的记录都需要回表。而 name LIKE ‘%明%’ 这个条件其实可以利用索引 idx_age_name 中的 name 列来进行初步筛选。但由于没有ICP，这个筛选工作被推迟到了回表之后，在Server层进行，导致了很多不必要的回表操作。如果age>20的数据有10万条，而name包含‘明’的只有1000条，那么就有9万9千次回表是徒劳的。

2. 有ICP的情况下（MySQL 5.6及之后）：

存储引擎层：
- 存储引擎依然根据索引 idx_age_name 进行扫描，找到所有满足 age > 20 条件的索引记录。
- 但是，ICP功能开启后，存储引擎会多做一个动作：它不会立即回表，而是会先检查这条索引记录中的name字段是否满足 name LIKE ‘%明%’ 这个条件。
  - 如果不满足，则直接丢弃这条索引记录，跳过回表。
  - 如果满足，则再执行回表操作，读取整行数据。
- 然后将满足age和name条件的行数据返回给Server层。
Server层：
- Server层接收到的是已经初步过滤（通过了age和name条件）的数据。
- Server层只需要再检查剩余的条件（本例中是 score > 90，这个条件无法用索引过滤，因为score不在索引中），然后返回最终结果。

性能提升： ICP的引入，将name条件的过滤从Server层“下推”到了存储引擎层，在索引遍历过程中就完成了过滤。这极大地减少了不必要的回表操作，降低了磁盘I/O和CPU开销，从而显著提升了查询性能。在上面的例子中，回表次数从10万次降到了1000次。

三、ICP 的工作机制与流程

总结一下ICP的工作流程，可以概括为以下几步：

解析与优化： Server层的优化器分析SQL语句，确定可以使用哪个索引。
索引遍历： 存储引擎开始遍历索引树，定位到第一条满足索引最左前缀条件的记录。
条件下推判断： 对于索引中的每一条记录，存储引擎不仅检查最左前缀条件（如age>20），还会检查WHERE子句中所有可以仅用索引列来评估的条件（如name LIKE ‘%明%’）。
选择性回表： 只有同时满足步骤3中所有条件的索引记录，存储引擎才会执行回表操作去读取完整的行数据。
返回与最终过滤： 存储引擎将完整的行数据返回给Server层，Server层再评估WHERE子句中剩下的无法用索引列评估的条件（如score>90）。

四、ICP 的适用条件与限制

不是所有情况都能使用ICP，了解其适用场景和限制至关重要。

适用条件：

需要回表的查询： ICP是针对二级索引（非聚簇索引）的优化。因为只有使用二级索引才需要回表，覆盖索引不需要回表，也就谈不上用ICP来减少回表。
WHERE条件涉及索引列： WHERE子句中必须包含有索引的列。通常是一个联合索引。
部分条件可索引过滤： WHERE条件的一部分可以使用索引中的列来过滤（即“下推条件”），另一部分则不能（需要回表后由Server层过滤）。
- 可下推的条件： 可以仅使用索引列进行评估的条件。例如：age > 20、name = ‘张三’、name LIKE ‘张%’（虽然是范围，但索引可以评估）。
- 不可下推的条件： 需要用到索引之外列的条件。例如：score > 90（score不在索引中）。

限制：

仅支持二级索引： ICP适用于InnoDB和MyISAM存储引擎的二级索引。对于InnoDB的聚簇索引（主键索引），完整的记录已经被读取，因此不需要ICP。
子查询和函数依赖： 在某些复杂情况下，如涉及子查询或某些特定函数的条件，可能无法使用ICP。
虚拟列（Generated Columns）： 如果下推条件引用了虚拟列，而该虚拟列依赖于非索引列，则无法使用ICP。

五、如何查看是否使用了 ICP

我们可以使用 EXPLAIN 命令来查看查询的执行计划。如果使用了ICP，在 Extra 字段中会看到 Using index condition。

例如，对我们之前的例子进行 EXPLAIN：

1	EXPLAIN SELECT * FROM user WHERE age > 20 AND name LIKE '%明%’ AND score > 90;

输出结果中，如果 key 字段显示使用了 idx_age_name，并且 Extra 字段包含了 Using index condition，那么就恭喜你，ICP正在发挥作用！

+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | idx_age_name  | idx_age_name | 9       | NULL | 1000 |    10.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+

六、总结与要点回顾

特性	没有 ICP	有 ICP
工作位置	`WHERE` 条件过滤全部在 Server 层完成	部分 `WHERE` 条件过滤下推到存储引擎层
回表次数	多，所有满足最左前缀的记录都回表	少，只有同时满足下推条件的记录才回表
性能	较低，磁盘 I/O 和 CPU 开销大	较高，显著减少不必要的磁盘 I/O
EXPLAIN 显示	`Extra` 字段通常为 `Using where`	`Extra` 字段为 `Using index condition`

核心价值： ICP 通过将过滤操作提前到存储引擎层，最大限度地减少了昂贵的回表操作次数，是数据库优化器中一项非常有效的“减负”优化。

在现代MySQL版本（5.6+）中，ICP默认是开启的（通过系统变量 optimizer_switch 中的 index_condition_pushdown=on 控制）。作为开发者，我们更需要做的是设计出合理的索引（尤其是联合索引），为ICP的优化提供发挥的舞台。