索引下推（ICP）-学习笔记

作用

索引下推（Index Condition Pushdown，ICP）是MySQL 5.6引入的一种优化技术，它可以减少回表的次数，提高查询效率。

定义

建立在联合索引中，如果在二级索引中拿到查询条件（查询条件需要在同一个联合索引）需要的全部数据就可以直接匹配到主键。而如果查询条件中存在不在联合索引中的字段则不会使用索引下推，所以需要适当建立联合索引。

• 索引下推是MySql5.6的新特性

索引下推的具体实践

理论比较抽象，我们来上一个实践。

使用一张用户表tuser，表里创建联合索引（name, age）。

如果现在有一个需求：检索出表中名字第一个字是张，而且年龄是10岁的所有用户。那么，SQL语句是这么写的：

select * from tuser where name like '张%' and age=10;

那接下来的步骤是什么呢？

情况一：没有使用ICP

在MySQL 5.6之前，存储引擎根据通过联合索引找到name like '张%' 的主键id（1、4），逐一进行回表扫描，去聚簇索引找到完整的行记录，server层再对数据根据age=10进行筛选。

我们看一下示意图：

可以看到需要回表两次，把我们联合索引的另一个字段age浪费了。

情况二：使用ICP

而MySQL 5.6 以后， 存储引擎根据（name，age）联合索引，找到name like '张%'，由于联合索引中包含age列，所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。

我们看一下示意图：

可以看到只回表了一次

所以索引下推和覆盖索引的区别是覆盖索引是直接从二级索引中拿数据，而索引下推是直接通过二级索引的数据进行条件判断。

除此之外我们还可以看一下执行计划，看到Extra一列里 Using index condition，这就是用到了索引下推。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tuser	NULL	range	na_index	na_index	102	NULL	2	25.00	Using index condition

索引下推使用条件

1. 只能用于range、 ref、 eq_ref、ref_or_null访问方法；
   • range：表示根据主键索引或普通索引（包含唯一索引）进行范围查找，比如where id < 100或where key2 between 10 and 20。
   • ref：表示根据普通索引等值匹配，或is null，比如where key1 = 'key112'或where key2 is null。这种方式需要先根据普通索引匹配到多个主键，然后根据主键进行回表。
   • eq_ref：表示根据主键或唯一索引等值匹配，比如where id = 2或where key2 = 10。这种查询是很快的，查询速率认为是常数级别的。
   • ref_or_null：表示根据普通索引等值匹配，或is null，但是会同时返回匹配的值和null值，比如where key1 = 'key112' or key1 is null。这种方式比ref更慢，因为它需要扫描两次索引。
2. 只能用于InnoDB和 MyISAM存储引擎及其分区表；
3. 对InnoDB存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;

索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于InnoDB的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。

失效情况

1. 索引列的数据类型不能是BLOB、TEXT或GEOMETRY。这些数据类型的索引列不能被完全存储在索引中，而是只存储一部分前缀。因此，索引下推无法准确地过滤掉不符合的记录，反而会增加回表的次数。

2. 查询条件不能包含NOT IN、<>、!=、NOT LIKE、NOT BETWEEN、NOT EXISTS等否定操作符。这些操作符会导致索引下推失效，因为它们会使得索引条件变得过于宽松，无法有效地过滤掉不符合的记录。

3. 查询条件不能包含OR或XOR逻辑操作符。这些操作符会使得索引条件变得复杂，无法被索引下推优化器识别和处理。

4. 查询条件不能包含子查询。这些子查询会使得索引条件变得动态，无法在执行计划中确定。

5. 查询条件不能包含对索引列的函数操作。这些函数操作会破坏索引列的有序性，无法利用索引进行范围查找。

6. 查询条件不能包含存储过程、触发器或自定义函数。这些对象会使得索引条件变得不确定，无法在优化阶段进行评估。

7. 查询条件不能包含全文索引或空间索引。这些索引类型不支持索引下推优化。

8. 查询条件不能包含JSON数据类型的索引列。这种数据类型的索引列不支持索引下推优化。

相关系统参数

索引条件下推默认是开启的，可以使用系统参数optimizer_switch来控制器是否开启。

查看默认状态：

mysql> select @@optimizer_switch\G;
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)

切换状态：

set optimizer_switch="index_condition_pushdown=off";
set optimizer_switch="index_condition_pushdown=on";