MySQL 索引下推

MySQL 架构

（图片来源于《MySQL 实战 45 讲》）

索引下推

索引下推，简称 ICP，英文全称：Index Condition Pushdown，用于优化查询。其核心点就在于把数据筛选的过程放在了存储引擎层去处理，而不是像之前一样放到Server层去做过滤。

这是 MySQL 5.6 之后新增的功能。

MySQL 5.6+ 默认是开启 ICP 的，可以通过以下命令关闭：

SET optimizer_switch = 'index_condition_pushdown=off';

从一个查询开始

如下：

create table if not exists `sakila`.`user` (  
    `id` int unsigned auto_increment not null ,  
    `age` tinyint unsigned not null default 0,  
    `name` varchar(30) not null default '',  
    `city` varchar(30) not null default '',  
    primary key (`id`),  
    key `idx_age_name`(`age`, `name`) using BTREE  
) engine=InnoDB;  
  
insert into `sakila`.`user` (`age`, `name`, `city`) values (10, 'Jack', 'Beijing'),  
                                                           (5, 'Jane', 'Shanghai'),  
                                                           (21, 'Jhon', 'ChongQing');

现在有以下查询：

select * from `sakila`.`user` where age > 9 and name = 'Jane';

通过 explain 看到以下执行计划：

MySQL [(none)]> explain select * from `sakila`.`user` where age > 9 and name = 'Jane'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: range
possible_keys: idx_age_name
          key: idx_age_name
      key_len: 1
          ref: NULL
         rows: 2
     filtered: 33.33
        Extra: Using index condition
1 row in set, 1 warning (0.01 sec)

从 Extra: Using index condition 中，我们知道这条查询可能使用了索引下推。

不开启 ICP

• Server 层调用 Engine 查询数据
• Engine 根据联合索引查询 age > 9 的数据
• 找到每一条满足条件（ age > 9 ） 的数据并根据主键 ID 回表，直到找到不符合条件的结果
• 数据返回给 Server 层，Server 层根据条件过滤（name = 'Jane'），流程结束

开启 ICP

• Server 层调用 Engine 查询数据
• Engine 根据联合索引查询 age > 9 的数据，再根据联合索引中已存在的 name 字段进行过滤，找到符合条件的数据
• 根据找到符合条件的数据，回表查询
• 返回数据给Server层，流程结束

ICP 的限制

• ICP 适用的访问方法：range、ref、eq_ref 和 ref_or_null
• ICP可以用于 InnoDB 和 MyISAM 表（MySQL 5.7 已经支持分区表）
• 对于 InnoDB，ICP 只用于二级索引
• 子查询不支持索引下推

小结

对比开启 ICP 和不开启 ICP ，开启 ICP 数据的过滤放在了引擎层，明显减少了回表的次数和返回的数据，节省了磁盘 IO。

参考

• MySQL索引与Index Condition Pushdown
• https://mp.weixin.qq.com/s/87...
• MySQL :: MySQL 5.6 Reference Manual :: 8.2.1.5 Index Condition Pushdown Optimization