关于mysql: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