MySQL 索引下推

简单7月

MySQL 架构

msZxl5IjwiYK4TE
(图片来源于《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

OhEulcz5on7jgPH

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

开启 ICP

HWNpBCVsg65Ycl2

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

ICP 的限制

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

小结

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

参考

阅读 228

七月生
从零开始玩转 vagrant,并使用 vagrant 搭建一个 LNMP 环境,LNMP环境中包含以下内容: + Nginx + PHP 7...
24 声望
1 粉丝
0 条评论
24 声望
1 粉丝
文章目录
宣传栏