MySQL索引优化-Index Condition Pushdown Optimization（索引下推ICP）

索引条件下推（ICP）核心原理

适用场景：针对使用索引检索数据的查询语句。

未开启ICP：

1. 存储引擎通过索引找到符合条件的数据位置。
2. 从磁盘读取完整数据行返回给MySQL服务层。
3. 服务层对所有行做where条件筛选。

开启ICP后：

1. MySQL服务层先分析where条件
   • 可以完全使用索引判断的条件（索引列）
   • 需要拿到数据行之后判断的条件
2. 把索引判断的条件下推给存储引擎
3. 存储引擎在索引扫描阶段进行预过滤，只有索引条目满足下推条件时，才读取整行数据。

对比开启前后，可以发现，ICP的作用是用来减少磁盘读取次数以提高查询性能，不过要使ICP生效也有一些条件限制：

• 适用于类型为 range,ref,eq_ref,ref_or_null 的查询并获取数据行（非覆盖索引）
• 支持InnoDB和MyISAM，也包括分区表。
• 对于InnoDB表，ICP只适用于二级索引（辅助索引），因为主键（聚簇）索引已经把数据行加载到内存缓冲区了，ICP的目标是减少行数据读取的次数，聚簇索引使用ICP并不会减少IO次数。
• 虚拟列上创建的二级索引不支持ICP，因为虚拟列的值是实时计算的，存储引擎没办法提前判断。
• 包含子查询的过滤条件不支持ICP，因为存储引擎不具备执行子查询的能力。
• 引用存储函数的条件不支持ICP。因为存储函数的执行是在服务层，引擎无法调用。
• 触发器不支持ICP。因为触发器是在服务层实现的。

假设现在有一张表：t_order_product，通过以下SQL查询给出表结构：

select
    COLUMN_NAME as '字段名',
    IS_NULLABLE as '允许为空',
    COLUMN_TYPE as '字段类型',
    COLUMN_KEY as '字段索引',
    EXTRA as '额外属性',
    COLUMN_COMMENT as '字段注释'
from
    information_schema.COLUMNS
where TABLE_NAME = 't_order_product' order by ORDINAL_POSITION

字段名	允许为空	字段类型	字段索引	额外属性	字段注释
id	NO	int	PRI	auto_increment	主键自增id
order_id	NO	int	PRI		订单主表id
code	NO	varchar(127)	MUL		商品code
name	YES	varchar(256)	MUL		商品名称
count	YES	int			商品个数
price	NO	int			商品价格

这张表上有 联合索引 idx_code_name(code, name), 执行SQL：

select * from t_order_product where code = '123602' and name like '%广告券%'

执行计划：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t_order_product		ref	idx_code_name	idx_code_name	510	const	31	11.11	Using index condition

在执行计划的 Extra 列显示了 Using index condition，则表示使用了索引下推，注意它容易和 Using index混淆，Using index 表示使用了索引覆盖。

索引下推 默认为启用，要查看是否开启，可以执行：

show variables like '%optimizer_switch%'

结果：

Variable_name	Value
optimizer_switch	index_condition_pushdown=on

同样可以用 optimizer_switch 系统变量来控制开启和关闭：

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

---

参考文档：

dev.mysql.com/doc/refman/…