索引下推（ICP）通俗理解

索引下推（ICP）

1. 定义：

• MySQL5.6的新特性，核心目标能够减少回标查询次数，提高查询效率。降低服务器与存储引擎之间的数据交互开销。
• MySQL架构分为【服务层】，【存储引擎层】

• 服务层负责SQL语法解析、生成执行计划，并调用存储引擎去执行数据的存储和检索。
• 存储引擎层（InnoDB/MyISAM）负责数据存储、索引遍历、物理数据读取

• 索引下推就是将上层（服务层）负责的事情，交给下层（引擎层）去处理

2. 核心区别：
   使用ICP和不使用ICP的区别在于过滤数据的位置和从引擎层传到服务层的数据量

不使用ICP的查询过程（存储引擎只做索引定位，所有的过滤逻辑由服务器层完成，会有大量从存储引擎传输到服务器层的数据

1. 存储引擎根据索引前缀条件遍历索引，找到符合前缀条件的索引记录
2. 存储引擎通过索引记录中的主键值，执行回表操作读取完整的行记录
3. 存储引擎将完整行记录返回给Server层
4. Server层去检测该记录是否满足WHERE条件

使用ICP的查询过程（存储引擎在索引遍历阶段提前过滤，只把有效记录返回给服务器层）

1. 存储引擎根据索引前缀条件遍历索引，找到符合前缀条件的索引记录
2. 存储引擎在引擎层判断可通过索引字段验证的过滤条件

• 条件不满足，则跳过该索引记录，无需回表，处理下一行索引记录
• 条件满足，通过索引的主键值回表，读取完整行记录

• 存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分

对比维度	不使用 ICP	使用 ICP
过滤条件执行位置	仅 Server 层	引擎层+ Server 层
回表次数	多（符合索引前缀条件即回表，包含大量无效回表）	少（仅符合所有索引字段条件才回表）
引擎→Server 传输数据量	大（包含大量不满足最终条件的无效记录）	小（仅传输引擎层过滤后的有效记录）
执行计划 Extra 标识	Using where（仅 Server 层过滤）	Using index condition（引擎层参与索引条件过滤）
适用索引类型	所有索引（但无优化效果）	仅二级索引（主键索引无回表开销，ICP 无效）

3. 适用场景

• 仅针对二级索引（非聚簇索引）：InnoDB主键索引（聚簇索引）的叶子节点直接存储完整行记录，回表无开销，ICP无优化意义
• 联合索引的非前缀条件过滤idx_org_date_posted (organization_id, transaction_date, posted_flag)，查询条件 organization_id=1 AND posted_flag='Y' 中，posted_flag 是索引非前缀字段，ICP 可在引擎层过滤；
• 范围查询+索引字段过滤：如organization_id=1 AND transaction_date > '2025-01-01' AND posted_flag='Y'，ICP 可在引擎层过滤 posted_flag 条件；

4. 不适应场景

• 条件涉及非索引字段：引擎层无法通过索引判断，仍需Server层过滤
• 主键索引（聚簇索引）查询：回表无开销，ICP无优化空间
• SELECT *且条件仅过滤主键：无索引字段可下放过滤
• 子查询、存储过程、触发器中的条件：ICP仅作用于主查询的索引遍历
• MySQL版本<5.6:未实现该特性
• 使用FORCE INDEX强制索引但索引不包含过滤条件：ICP无字段可用于过滤

5. 验证ICP是否生效

1. 查看ICP全局配置（默认开启）

-- 查看 optimizer_switch 配置，包含 index_condition_pushdown=on 表示开启
SHOW VARIABLES LIKE 'optimizer_switch';

-- 临时开启/关闭（会话级别）
SET optimizer_switch = 'index_condition_pushdown=on';  -- 开启
SET optimizer_switch = 'index_condition_pushdown=off'; -- 关闭

2. 执行计划验证
   通过EXPLAIN查看执行计划，Extra列包含Using index condition表示ICP生效

-- 示例：查询库存组织1、2025年后入账状态为Y的记录
EXPLAIN
SELECT transaction_id, item_number
FROM cac_inventory_transaction
WHERE organization_id = 1
AND transaction_date > '2025-01-01 00:00:00'
AND posted_flag = 'Y';

• Extra: Using index condition → ICP 生效（引擎层过滤了 posted_flag 条件）；
• Extra: Using where → ICP 未生效（仅 Server 层过滤）；
• Extra: Using index → 索引覆盖（无需回表），ICP 无意义。

索引前缀条件

定义： 针对联合索引（复合索引），仅使用索引中【最左连续列】作为过滤条件，这些条件能够直接用于索引的快速定位（范围扫描、等值匹配），是存储引擎遍历索引的核心依据

举例说明索引前缀条件（符合最左匹配原则）

联合索引结构	过滤条件	前缀条件说明
(organization_id, item_id, posted_flag)	organization_id = 1	仅匹配最左1列（基础前缀）
(organization_id, item_id, posted_flag)	organization_id = 1 AND item_id = 100	匹配最左2列（连续前缀）
(organization_id, item_id, posted_flag)	organization_id = 1 AND item_id = 100 AND posted_flag = 'Y'	匹配全部列（完整前缀）
(organization_id, transaction_date)	organization_id = 1 AND transaction_date > '2025-01-01'	匹配最左2列（范围前缀）

举例说明非索引前缀条件（不符合最左匹配原则）

联合索引结构	过滤条件	非前缀条件说明
(organization_id, item_id, posted_flag)	item_id = 100	跳过最左列 organization_id
(organization_id, item_id, posted_flag)	item_id = 100 AND posted_flag = 'Y'	跳过最左列 organization_id
(organization_id, item_id, posted_flag)	organization_id = 1 AND posted_flag = 'Y'	跳过中间列 item_id
(organization_id, transaction_date)	transaction_date > '2025-01-01'	跳过最左列 organization_id
(organization_id, item_id, posted_flag)	posted_flag = 'Y'	仅使用最右侧列，无前缀匹配