开心一刻

隔壁有一个80多岁的老大爷，昨天在小区的一棵树下发现一条黑色的蛇，冻僵了，大爷善心大发，就把蛇揣在了怀里，想给它一点温暖

今天一大早看到大爷在树上挂了一个牌子，写到：不准随地大小便！

基础回顾

回表 和 索引覆盖 可是面试中的常见面试题，我们一起来回顾下，先准备点基础数据，建表 tbl_index

CREATE TABLE tbl_index (    c1 INT,    c2 INT,    c3 CHAR(1),    PRIMARY KEY(c1),    KEY idx_c2 (c2));

是覆盖索引查询，但是这条 SQL 没有意义，如果我们在 tbl_index 表上增加索引 index idx_c2_c3 (c2,c3) ，那么 select c3 from tbl_index where c2 = 4;

走覆盖索引查询就有意义了，具体有什么意义呢 ？ 我们往下看回表 通过某个索引无法直接完成 SQL 查询 ❝ where 条件的列和 select 的列不全部存在于任何一个索引中 那么此时需要获取完整的数据记录来完成此次查询，从索引项记录到获取对应的完整数据记录的过程就叫回表；概念可能说的有些抽象，我们以 MySQL 为例来看看具体什么是回表InnoDB 回表 InnoDB 的索引结构有些特殊，非聚簇索引（二级索引）回表到聚簇索引的过程类似如下ICP优化过程-InnoDB 回表InnoDB 的聚簇索引即数据，索引和数据是存在一起的；那么直接走聚簇索引查询的 SQL 是不存在回表一说的，比如 select * from tbl_index where c1 = 10;

只有从二级索引出发，并且二级索引独自完成不了查询的时候才会回表到聚簇索引完成查询MyISAM 的回表 有这样一种说法： ❝ MyISAM 中的索引都是二级索引 其实说的是聚簇索引和二级索引的结构基本一致，只是聚簇索引多了个唯一性约束；MyISAM 聚簇索引和二级索引，以及它们的回表过程类似如下ICP优化过程-MyISAM 回表MyISAM 的回表过程指的是根据叶子节点中的数据记录的地址来获取完整记录的过程，与 InnoDB 还是有些许差别的；无论是聚簇索引还是二级索引都可能存在回表的过程上面只是以 MySQL 为例，其他数据库类似，大家不要被局限了，把思维打开 无论是聚簇索引的回表还是二级索引的回表，很有可能会造成额外的磁盘 IO，这会严重影响查询效率，覆盖索引的目的就是尽量能够一次完成 SQL 查询，避免有回表过程，从而提高效率；如何确定 SQL 是进行了覆盖索引查询，还是进行了回表查询 ？可以看执行计划，以 MySQL 为例，如果 Extra 中只有 using index 则说明使用了覆盖索引查询，如果 Extra 中出现了 using index condition 或 using index & using where 则说明进行了回表查询Index Condition PushDown

简称：ICP，主流关系型数据库都有实现，是一种查询优化，但 MySQL 在 5.6 才实现，算是比较晚的

字面翻译过来就是：索引条件下推，至于从哪下推到哪，有什么作用，我们继续往下看

❝

继续往下看之前需要先复习下：神奇的 SQL，你们的知识盲区：WHERE条件的提取与应用[1]，不然有些概念会看不懂

同样以 MySQL 为例（但大家不要局限于 MySQL），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 的执行效率

准备表：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优化过程-no_icp

没有使用 ICP 时，引擎层会将满足 Index Key 范围限制的所有数据记录（示例中一共 6 条）逐条返回给 Server 层，然后由 server 层应用 Index Filter 和 Table Filter ，最后将满足条件的数据返回给客户端

若使用 ICP，则 SQL 查询类似如下

ICP优化过程-icp

使用了 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 能提高 SQL 执行效率，但也不是任何情况下都适用的，它只适用于某些情况

当 SQL 需要全表访问时，ICP 的优化策略可用于 range, ref, eq_ref, ref_or_null 类型的数据访问方式MySQL 中只适用于 InnoDB 和 MyISAM 两种存储引擎MySQL 的 InnoDB 中，只适用于二级索引 ICP 的目的就是为了减少回表导致的磁盘 I/O，而 InnoDB 的聚簇索引的叶子节点存放的就是完整的数据记录，只要索引数据被读到内存了，那么索引项对应的完整数据记录也就读到内存了，那么通过索引项获取数据记录的过程就在内存中进行了，无需进行磁盘 I/O；也就说聚簇索引上应用 ICP，不会减少磁盘 I/O，也就没有使用的意义了不支持覆盖索引 其实和第 3 点一样，因为覆盖索引无需回表，ICP 也就没意义了不支持子查询条件的下推不支持存储过程条件、触发器条件的下推

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

总结索引覆盖与回表 这两个往往是一起来考虑的，因为覆盖索引的目的就是减少因回表产生的磁盘 I/O，从而提高执行效率；在实际应用中，我们往往也需要考虑尽可能用覆盖索引来完成我们的 SQL 查询Index Condition Pushdown 将 Index Filter 从 Server 层 Push Down 到了引擎层，减少了因回表产生的磁盘 I/O，提高了 SQL 执行效率参考

Index Condition Pushdown Optimization[2]

Index Condition Pushdown[3]

原文：

作者：青石路

#记录我的9月生活#

Reference

[1]:

[2]:

[3]: