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

原创

我是青石路 2022-11-03 12:09:06 博主文章分类：SQL ©著作权

文章标签 回表覆盖索引 ICP mysql sql 文章分类 运维

©著作权归作者所有：来自51CTO博客作者我是青石路的原创作品，请联系作者获取转载授权，否则将追究法律责任

开心一刻

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

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

　　小伙伴们：好

　　嘿、哈、嚯

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

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

回表与覆盖索引

　　正式讲 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 的聚簇索引有点特殊，具体可以看下面的图）

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

　　回表

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

　　　　InnoDB 的回表

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

神奇的 SQL 之 ICP → 索引条件下推_回表_02

select * from tbl_index where c1 = 10;

　　　　MyISAM 的回表

MyISAM 中的索引都是二级索引

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

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

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

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

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

using index 则说明使用了覆盖索引查询，如果 Extra 中出现了 using index 或 using index & using where

ICP

　　Index Condition Pushdown，MySQL 5.6 中引入的一种优化策略

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

Index Key，Index Filter，Table

　　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');

View Code

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

神奇的 SQL 之 ICP → 索引条件下推_覆盖索引_04

Index Key 范围限制的所有数据记录（示例中一共 6 条）逐条返回给 Server 层，然后由 server 层应用 Index Filter 和 Table

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

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

Index 下推到引擎层，引擎层在对 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

　　相信到这里，大家对 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 筛选掉的数据的比例，过滤掉的数据比例大，那就性能提升大，反之则性能提升小