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: rangepossible_keys: idx_age_name          key: idx_age_name      key_len: 1          ref: NULL         rows: 2     filtered: 33.33        Extra: Using index condition1 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