MySQL 索引条件下推

索引条件下推

Index Condition Pushdown简称ICP，也称索引条件下推。

MySQL中默认开启了索引下推。

开启

set optimizer_switch='index_condition_pushdown=on';

关闭

set optimizer_switch='index_condition_pushdown=off';

ICP 使用条件

1. 如果表访问的类型为range、ref、eq_ref、ref_or_null可以使用ICP。
2. ICP可以用于InnoDB和MyISAM表，包括分区表InnoDB和MyISAM表。
3. 对于InnoDB表，ICP仅用于二级索引。ICP的目标是减少全行读取次数，从而减少I/O操作。
4. 当SQL使用覆盖索引时，不支持ICP。因为这种情况使用ICP不会减少I/O。
5. 相关子查询的条件不能使用ICP。

概念

如果部分where条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分where条件放到存储引擎中筛选。然后存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。

好处

ICP可以减少存储引擎必须访问表的次数和MySQL服务器必须访问存储引擎的次数。

坏处

ICP的加速效果取决于在存储引擎内通过ICP筛选的数据的比例。

解释

更多针对联合索引。where条件中有这个字段，但这个字段不走索引。

create table t_user(id int primary key, name varchar(25), age int, no int);
create index idx_age_name on t_user(age, name);
insert into t_user values(1, 'aaa', 10, 1), (2,'bbb', 20, 2), (3, 'ccc', 30, 3);

Using index condition：表示使用了索引条件下推。

本来是要带着age=10回表查询，再带着name like '%abc'回表查询。

使用了索引下推后：

1. 先将age=10回表查询得到数据。
2. 然后name like '%abc'再在age=10查询得到的数据中进行筛选，过滤掉不符合的行数据。
3. 再回表查询得到最终数据。

（因为索引中包含了这两个字段的值，所以第二部不用回表）

mysql> explain select * from t_user where age = 10 and name like '%abc';
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_age_name  | idx_age_name | 5       | const |    1 |    33.33 | Using index condition |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)