神奇的 SQL 之 ICP → 索引条件下推

开心一刻

　　楼主：来，我们先排练一遍

　　小伙伴们：好

　　嘿、哈、嚯

　　楼主：非常好，就是这个节奏，我们开始吧

　　楼主：啊、啊、啊，疼 ！ 你们是不是故意的 ？

回表与覆盖索引

　　正式讲 icp 之前了，我们先将相关的概念捋一捋，知道的就当回顾，不知道的就当了解了，这有助于对 icp 的理解

　　建个示例表 tbl_index 

create table tbl_index (
    c1 int,
    c2 int,
    c3 char(1),
    primary key(c1),
    key idx_c2 (c2)
);

　　覆盖索引

　　　　如果 where 条件的列和 select 的列都在一个索引中，通过这个索引就可以完成查询，这就叫就叫覆盖索引；当然，覆盖索引基本针对的是组合索引（innodb 的聚簇索引有点特殊，具体可以看下面的图）

　　　　针对上面的 tbl_index， select c2 from tbl_index where c2 = 4; 是覆盖索引查询，但是这条 sql 没有意义，如果我们在 tbl_index 表上增加索引 index idx_c2_c3 (c2,c3) ，那么 select c3 from tbl_index where c2 = 4; 走覆盖索引查询还是很有意义的，那问题又来了，覆盖索引的意义何在 ？ 我们往下看

　　回表

　　　　通过某个索引无法直接完成 sql 查询（where 条件的列和 select 的列不全部存在于任何一个索引中），那么此时需要获取完整的数据记录来完成此次查询，从索引项记录到获取对应的完整数据记录的过程就叫回表；概念可能说的有些抽象，我们结合 mysql 来看看具体什么是回表

　　　　innodb 的回表

　　　　innodb 的索引结构有些特殊，非聚簇索引（二级索引）回表到聚簇索引的过程类似如下

　　　　innodb的聚簇索引即数据，索引和数据是存在一起的；那么直接走聚簇索引查询的 sql 是不存在回表一说的，比如 select * from tbl_index where c1 = 10; ，只有从二级索引出发，并且二级索引独自完成不了查询的时候才会回表到聚簇索引完成查询

　　　　myisam 的回表

　　　　有这样一种说法： myisam 中的索引都是二级索引 ，其实说的是聚簇索引和二级索引的结构基本一致，只是聚簇索引有个唯一性约束

　　　　myisam 聚簇索引和二级索引，以及它们的回表过程类似如下

　　　　myisam 的回表过程指的是根据叶子节点中的数据记录的地址来获取完整记录的过程，无论是聚簇索引还是二级索引都可能存在回表的过程；myisam 的回表与 innodb 还是有差别的

　　无论是 innodb 的回表还是 myisam 的回表，很有可能会造成额外的磁盘 io，这会严重影响查询效率，覆盖索引的目的就是尽量能够一次完成 sql 查询，避免有回表过程，从而提高效率

　　如何确认 mysql 是进行了覆盖索引查询，还是进行了回表查询 ？

　　看 mysql 的执行计划，如果 extra 中只有 using index 则说明使用了覆盖索引查询，如果 extra 中出现了 using index condition 或 using index & using where 则说明进行了回表查询

icp

　　index condition pushdown，mysql 5.6 中引入的一种优化策略

　　那么究竟是将什么从哪 push down 到哪，优化了什么？要弄清楚这 4 个问题，我们需要先弄清楚 where 条件的提取与应用，具体可查看：神奇的 sql 之 where 条件的提取与应用

　　where 条件会被提取成 3 部分： index key，index filter，table filter ，在 mysql 5.6 之前，并不区分 index filter 与 table filter，统统将 index first key 与 index last key 范围内的索引记录，回表读取完整记录，然后返回给 mysql server 层进行过滤，而在 mysql 5.6 之后，index filter 与 table filter 分离，index filter 下降到引擎层（innodb和myisam）的索引层面进行过滤，减少了回表与返回 mysql server 层的记录交互开销，提高了 sql 的执行效率

　　icp 优化过程

　　　　假设我们有表： tbl_icp 

create table tbl_icp (a int primary key, b int, c int, d int, e varchar(50));
create index idx_bcd on tbl_icp(b, c, d);
insert into tbl_icp values (4,3,1,1,'a');
insert into tbl_icp values (1,1,1,2,'d');
insert into tbl_icp values (8,8,7,8,'h');
insert into tbl_icp values (2,2,1,2,'g');
insert into tbl_icp values (5,2,2,5,'e');
insert into tbl_icp values (3,3,2,1,'c');
insert into tbl_icp values (7,4,0,5,'b');
insert into tbl_icp values (6,5,2,4,'f');

　　　　若没有使用 icp，则 sql 查询类似如下

　　　　没有使用 icp 时，引擎层会将满足 index key 范围限制的所有数据记录（示例中一共 6 条）逐条返回给 server 层，然后由 server 层应用 index filter 和 table filter （mysql 5.6 之前不区分 index filter 和 table filter），最后将满足条件的数据返回给客户端；

　　　　若使用 icp，则 sql 查询类似如下

　　　　使用了 icp，server 层会将 index filter 下推到引擎层，引擎层在对 index first key 与 index last key 范围内的索引项逐条进行过滤的时候，会应用上 index filter，对不满足 index filter 条件的索引项直接过滤掉，无需回表操作，也无需返回给 server 层，从而提供执行效率；上图中的索引项： 3 1 1 、 3 2 1 不满足 index filter 中的 d != 1 ， 4 0 5 不满足 c > 0 ，所以这 3 个索引项无需进行回表操作，也不需要返回给 server 层

　　相信到这里，大家对 icp 的 4 个问题应该就比较清楚了

　　icp 的适用条件

　　　　虽说 icp 能提高 sql 执行效率，但也不是任何情况下都适用的，它只适用于某些情况

　　　　1、当 sql 需要全表访问时，icp 的优化策略可用于 range, ref, eq_ref,  ref_or_null 类型的数据访问方式

　　　　2、只适用于 innodb 和 myisam 两种存储引擎

　　　　3、在 innodb 中，icp 只适用于二级索引

　　　　　　icp 的目的就是为了减少回表导致的磁盘 i/o，而 innodb 的聚簇索引的叶子节点存放的就是完整的数据记录，只要索引数据被读到内存了，那么索引项对应的完整数据记录也就读到内存了，那么通过索引项获取数据记录的过程就在内存中进行了，无需进行磁盘 i/o；也就说聚簇索引上应用 icp，不会减少磁盘 i/o，也就没有使用的意义了

　　　　4、不支持覆盖索引

　　　　　　其实和第 3 点一样，因为覆盖索引无需回表，icp 也就没意义了

　　　　5、不支持子查询条件的下推

　　　　6、不支持存储过程条件、触发器条件的下推

　　至于 icp 的优化效果，取决于在存储引擎内通过 icp 筛选掉的数据的比例，过滤掉的数据比例大，那就性能提升大，反之则性能提升小

总结

　　1、索引覆盖与回表

　　　　这两个往往是一起来考虑的，因为覆盖索引的目的就是减少因回表产生的磁盘 i/o，从而提高执行效率

　　　　在实际应用中，我们往往也需要考虑尽可能用覆盖索引来完成我们的 sql 查询

　　2、icp的四个问题

　　　　将什么从哪 push down 到哪，优化了什么

　　　　将 index filter 从 server 层 push down 到了引擎层，减少了因回表产生的磁盘 i/o，也减少了与 server 层的交互，提高了 sql 执行效率

　　3、疑问点

　　　　为什么这么明显的优化策略到 mysql 5.6 才引入，个人感觉很容易就能考虑到呀，mysql 的开发者们是肿么肥事 ？

　　　　可能是楼主在巨人的肩膀上，站着说话不腰疼吧......

参考

　　index condition pushdown optimization

　　index condition pushdown

　　mysql的索引