vlambda博客
学习文章列表

MySQL回表、索引覆盖、索引下推

等你点关注都等的长毛了

    准备   

CREATE TABLE index_opt_test (

  id int(11) NOT NULL AUTO_INCREMENT,

  name varchar(11) DEFAULT NULL,

  title varchar(11) DEFAULT NULL,

  age int(11) DEFAULT NULL,

  sex varchar(11) DEFAULT NULL,

  content varchar(500) DEFAULT NULL,

  PRIMARY KEY (id),

  KEY idx_cb (name,title,age)

) ENGINE=InnoDB;


INSERT INTO index_opt_test (name, title, age, sex, content) VALUES ('cc', 'T7', 35, 'male', '123');

以下内容是在InnoDB存储引擎下的操作



回表

> > > > 1. 什么是回表

回表是发生在二级索引上的一种数据查询操作,简单点讲就是我们要查询的列不在二级索引的列中,那么就必须根据二级索引查到主键ID,然后再根据主键ID到聚簇索引树上去查询整行的数据,这一过程就叫作回表。

> > > > 2.为什么会回表 

写一个会回表查询的SQL:

select id, name, age, sex from index_opt_test where name='cc' and title='T7' and age=35;

解析:

    SQL需要查询的列包括id、name、age、sex,查询条件命中索引idx_cb,其中列id、name、age都在索引idx_cb中可以获取到,但是sex不能通过索引获取到,必须要获取到整行数据之后再从结果中捞出来sex列的数据,这种情况就必须要回表。

> > > > 3. 什么情况下不需要回表

当所有的列都能在二级索引树中查询到,就不需要再回表了,这种情况就是索引覆盖。




索引覆盖

> > > > 1. 什么是索引覆盖

当SQL语句中查询的列都在二级索引中时,我们就不需要回表去把整行数据都捞出来了,可以从非聚簇索引树中直接获取到我们需要的列的数据,这就叫索引覆盖。简单点来讲就是:所有不需要回表的查询操作都叫索引覆盖。

> > > > 2. 为什么会发生索引覆盖

关于为什么会发生索引覆盖这个问题,通过一条SQL来理解:

select id, name, age from index_opt_test where name='cc' and title='T7';

这条SQL要查询的列id、name、age全部都能从非聚簇索引idx_cb中直接查询出来,可能会有个疑问:我们的索引列是name、title和age,为什么id明明不在组合索引中却还能发生索引覆盖?提出这个问题的同学真的是欠我一顿小烧烤,非聚簇索引的叶子节点里存的是什么东西:主键的关键字啊,我们这里主键是id,他的关键字就是id的值啊,那我们通过非聚簇索引是不是可以直接将主键id查出来,是不是就不用再回表了,不用回表是不是就发生了索引覆盖啊,就是那么简单。


索引下推
> > > > 1. 什么是索引下推

索引下推又叫索引条件下推(Index Condition Pushdown,简称ICP),ICP默认是开启的,使用ICP可以减少存储引擎访问基础表的次数和Server访问存储引擎的次数。

  • ICP没有启用:Server层会根据索引的断桥原则将命中的索引字段推送到引擎层获取数据,并把匹配到的数据全部返回到Server层,由Server层再根据剩余的where条件进行过滤,即使where条件中有组合索引的其他未命中的字段,也会保留在Server层做筛选,然后返回给Client

    select id, name, sex from index_opt_test where name='cc' and title like '%7' and sex='male';

    执行过程:

    1. Server层把name推到引擎层

    1. 引擎层根据name去idx_cb的索引树中匹配主键

    2. 回表去捞数据返回给Server层

    3. Server层再根据title、sex筛选出最终的数据

    4. 最后返回给客户端

  • ICP启用:Server层会将where条件中在组合索引中的字段全部推送到引擎层,引擎层根据断桥原则匹配出索引数据,然后将其他索引字段带入再进行一次筛选,然后拿最终匹配的主键关键字回表查询出数据后返回给Server层,Server层再根据剩余的where条件做一次筛选,然后返回给Client

    select id, name, sex from index_opt_test where name='cc' and title like '%7' and sex='male';

    执行过程:

    1. Server把name和title都推到引擎层

    2. 引擎层根据name去idx_cb中查询出主键关键字和title、age

    3. 再由title筛选出匹配的主键关键字

    4. 回表去捞数据返回给Server层

    5. Server层再根据sex筛选出最终的数据

    6. 再返回给客户端

    MySQL回表、索引覆盖、索引下推

> > > > 2. 索引下推适用条件
  • ICP 用于访问方法是 range/ref/eq_ref/ref_or_null,且需要访问表的完整行记录。

  • ICP适用于 InnoDB 和 MyISAM 的表,包括分区的表。

  • 对于 InnoDB 表,ICP只适用于二级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。对于 InnoDB 的聚簇索引,完整的记录已经读进 InnoDB 的缓存,使用 ICP 不能减少 I/O 。

  • ICP 不支持建立在虚拟列上的二级索引(InnoDB 支持在虚拟列上建立二级索引)。

  • 引用子查询、存储函数的条件没法下推,Triggered conditions 也没法下推。

所以ICP 适用的一个隐含前提是二级索引必须是组合索引、且在使用索引进行扫描时只能采用最左前缀匹配原则。组合索引后面的列出现在 where 条件里,因此可以先过滤索引元组、从而减少回表读的数量。

> > > > 3. 使用

索引下推在5.6版本加入的,默认开启,可以通过命令SHOW VARIABLES like '%optimizer_switch%'查看当前状态

MySQL回表、索引覆盖、索引下推

  • 关闭索引下推

SET optimizer_switch = 'index_condition_pushdown=off';

执行之后查看一下状态:

MySQL回表、索引覆盖、索引下推

然后我们执行一下SQL语句:

explain
select id, name, sex from index_opt_test where name='cc' and title like '%7' and sex='male';

MySQL回表、索引覆盖、索引下推

从执行计划我们可以看出当我们关闭索引下推后,Extra中的是Using where

  • 开启索引下推

SET optimizer_switch = 'index_condition_pushdown=on';

执行之后查看一下状态:

MySQL回表、索引覆盖、索引下推

然后我们执行以下SQL语句:

explain
select id, name, sex from index_opt_test where name='cc' and title like '%7' and sex='male';

MySQL回表、索引覆盖、索引下推

从执行计划中看到使用了Using index conditionUsing whereUsing index condition说明ICP生效了,title被推到了引擎层,而Using where是因为where条件中的sex字段