Mysql索引下推

前提、组合索引最左前缀原则（最左匹配）

定义：MySQL中的组合索引（又称复合索引）遵循“最左前缀原则”。这意味着索引是按照创建时字段的顺序从左到右排列的。查询时，必须从索引的最左列开始，且不能跳过中间的列，才能充分利用索引进行检索。

假设我们有一个表 user，并创建一个组合索引 idx_name_age_city (name, age, city)。

id	name	age	city	sex
1	张三	20	北京	男
2	李四	22	上海	女
3	王五	20	广州	男
4	张六	24	深圳	女

生效的场景举例：

全列匹配（最优）：
```
SELECT * FROM user WHERE name = '张三' AND age = 20 AND city = '北京';
```
说明：索引的所有三列都被使用，查询效率最高。
匹配最左连续列：
```
SELECT * FROM user WHERE name = '张三' AND age = 20;
-- 或
SELECT * FROM user WHERE name = '张三';
```
说明：只要从最左列 name 开始，并且是连续的，索引就有效。(name, age) 或 (name) 会被使用。

不生效（或部分失效）的场景举例：

缺少最左列：
```
SELECT * FROM user WHERE age = 20 AND city = '北京';
```
说明：跳过了 name 列。这就像查电话簿没有按姓氏首字母查，而是直接翻看所有人的年龄一样，索引 idx_name_age_city 对此条件完全无效，会进行全表扫描。
跳过中间列：
```
SELECT * FROM user WHERE name = '张三' AND city = '北京';
```
说明：跳过了 age 列。此时索引只会使用 name 这一列来定位所有叫“张三”的人。对于 city = '北京' 这个条件，它无法在索引层进行检索，只能在所有叫“张三”的记录中，通过回表读取完整数据行后，再在Server层进行过滤。
对某一列使用了范围查询或函数（范围查询右边的列失效）：
```
SELECT * FROM user WHERE name = '张三' AND age > 18 AND city = '北京';
```
说明：索引会使用 name 和 age 两列（age 用于范围匹配），但 city 列将无法再被用于索引检索。因为 age 是范围查询，其后的 city 值在索引树中是无序的。

一、核心概念：什么是索引下推？

索引下推（ICP） 是 MySQL 5.6 版本引入的一项优化技术。它的核心思想是：将原本在 Server 层进行的一部分数据过滤工作，“下放”到存储引擎层去完成。

在没有 ICP 的情况下，存储引擎只负责根据索引查找和读取数据行，然后返回给 Server 层，再由 Server 层根据 WHERE 条件进行过滤。

有了 ICP 之后，如果 WHERE 条件中的一部分可以只用索引中的列来完成过滤，那么这部分过滤操作就会在存储引擎层进行。这样，只有满足这部分条件的数据行才会被读取并返回给 Server 层，从而大大减少了存储引擎必须访问的数据行（减少回表次数）和 Server 层必须处理的数据行数量。

二、通过一个例子来深入理解

让我们通过一个经典的例子来对比开启和关闭 ICP 的区别。

假设我们有一张 user 表，用于存储用户信息，并建立了一个联合索引 (age, city)。

id	name	age	city	...其它字段
1	张三	22	深圳	...
2	李四	23	上海	...
3	王五	22	深圳	...
4	赵六	24	北京	...

执行如下查询：

SELECT * FROM user 
WHERE age = 22 
AND city LIKE '%深圳%';

1. 没有索引下推（ICP关闭）的情况：

存储引擎：根据联合索引 (age, city)，找到所有 age = 22 的记录。因为 age 是索引的第一列，所以这个查询可以高效地使用这个索引。假设它找到了 3 条索引记录（id=1, id=3, 和另一个 age=22 但 city 不是深圳的用户）。
存储引擎：根据找到的这 3 条索引记录中的主键值（id），逐条进行回表，从主键索引（聚簇索引）中取出完整的行记录。
存储引擎：将这 3 条完整的行记录返回给 Server 层。
Server 层：对这三条记录应用 WHERE 条件中的 city LIKE '%深圳%’ 进行过滤。最终留下 2 条记录（id=1 和 id=3）。

问题：明明 city 也在联合索引里，但存储引擎在第一步时无法利用它来过滤，因为 LIKE '%深圳%' 是一个模糊匹配（前缀模糊），它无法像等值查询一样高效地使用索引。存储引擎只能使用索引的第一列 age，然后把所有 age=22 的行都取上来，导致了一次不必要的回表（例如那个 city 不是深圳的用户）。

2. 有索引下推（ICP开启）的情况：

存储引擎：根据联合索引 (age, city)，找到所有 age = 22 的记录（同样是找到 3 条索引记录）。
存储引擎（关键步骤）：不立即回表，而是根据联合索引中已经存在的 city 字段，直接在本层执行 city LIKE '%深圳%’ 的过滤。虽然 LIKE 前缀模糊无法走索引查找，但既然数据已经在索引里了，直接进行匹配的成本远低于回表再匹配的成本。这样，3 条索引记录中只有 2 条（city 是深圳的）通过了过滤。
存储引擎：只对通过过滤的这 2 条索引记录进行回表操作，取出完整的行记录。
存储引擎：将这 2 条完整的行记录返回给 Server 层。
Server 层：再次应用 WHERE 条件进行过滤（此时数据已经过滤过，这步通常是冗余的，但为了确保一致性依然会做），最终得到 2 条记录。

对比总结：

步骤	无 ICP	有 ICP	优势
存储引擎查找	找到所有 `age=22` 的索引记录（3条）	找到所有 `age=22` 的索引记录（3条）	-
存储引擎过滤	不过滤，直接回表3次	用索引中的 `city` 过滤，只剩2条	减少回表次数
存储引擎返回	返回3条完整记录	返回2条完整记录	减少IO和网络传输
Server层过滤	过滤3条记录，得2条	过滤2条记录，得2条	减少CPU计算

三、索引下推的作用与限制

作用：

减少回表次数：这是最大的收益。回表是一个随机 IO 操作，非常耗时，减少次数能极大提升性能。
减少存储引擎和 Server 层之间的数据传输量：需要传输和处理的数据行变少了。
降低 Server 层的负载：Server 层需要过滤的数据变少了。

适用条件/限制：

仅适用于 InnoDB 和 MyISAM 存储引擎。
仅适用于二级索引（辅助索引）：聚簇索引（主键索引）本身包含完整数据，没有“下推”的意义。
适用于 range, ref, eq_ref, ref_or_null 类型的查询。
需要过滤的条件在索引中存在：就像例子中的 city，它必须是联合索引的一部分。
对于 InnoDB 表，ICP 仅用于二级索引：因为 InnoDB 的聚簇索引已经包含了完整数据。

不适用于 ICP 的情况：

子查询的条件无法下推。
存储过程的条件无法下推。
触发器的条件无法下推。
需要访问完整行记录的条件无法下推（例如，要过滤的字段不在索引中）。

四、如何查看和使用 ICP

默认开启：ICP 在 MySQL 5.6 及以上版本是默认开启的。可以通过系统变量 optimizer_switch 来控制：

-- 查看优化器开关设置，关注 index_condition_pushdown 的值
SELECT @@optimizer_switch;

-- 关闭ICP
SET optimizer_switch = 'index_condition_pushdown=off';

-- 开启ICP
SET optimizer_switch = 'index_condition_pushdown=on';

（注意：修改仅对当前会话有效，如需全局修改，使用 SET GLOBAL）。

在 EXPLAIN 中查看：使用 EXPLAIN 语句分析查询计划时，如果使用了 ICP，在 Extra 列会看到 Using index condition。
```
EXPLAIN SELECT * FROM user WHERE age = 22 AND city LIKE '%深圳%';
```
输出结果中，如果 Extra 包含了 Using index condition，就代表这个查询成功利用了索引下推优化。

总结

索引下推（ICP） 的本质是 “过滤下放”，它让存储引擎能够利用联合索引中后续的列来提前过滤数据，即使这些列不能用于高效的索引查找（比如范围查询、模糊匹配）。

它的最大贡献在于极大地减少了不必要的回表操作，从而提升了查询性能，尤其是在联合索引和复杂 WHERE 条件结合使用的场景下，效果非常显著。作为开发者，理解 ICP 有助于我们设计出更合理的索引，并写出更能发挥数据库性能的 SQL 语句。

posted @ 2025-08-29 10:58 adragon 阅读(14) 评论(0) 收藏举报

刷新页面返回顶部

adragon