专栏首页开发技术神奇的 SQL 之 ICP → 索引条件下推

神奇的 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的索引

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 神奇的 SQL 之 WHERE 条件的提取与应用

      一条 SQL 在数据库中是如何执行的呢 ?相信很多人都会对这个问题比较感兴趣。但是,感兴趣归感兴趣,你得去追呀,还臆想着她主动到你怀里来 ?

  • 神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程(二)

    神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程(一)中,我们讲到了 JOIN 的部分内容,像:驱动表、JOIN 大致流程等。什么,还没看?...

  • virtualBox安装centos,并搭建tomcat

      本文没什么难点,只是发现自己记忆不好,特别是搭建tomcat服务的时候,总是需要去重新查阅资料,特此就写这篇博客来加强自己的记忆,同时也给大家以参考;

  • MySQL(四)|《千万级大数据查询优化》第一篇:创建高性能的索引(补充)

    本文是MySQL(三)|《千万级大数据查询优化》第一篇:创建高性能的索引的一个补充。 主要包括如下几点:

    黄小怪
  • 聊聊 MySql 索引那些事儿

    上一篇文章《一条SQL语句在MySQL中是如何执行的》我们聊到了sql语句内部的执行,包括InnoDB引擎是如何支持事务的,如何做到可以备份恢复的,那么今天我们...

    Java团长
  • 数据库之索引模块

    索引模块除了是数据库最重要的模块之一,也是面试中最经常被问到的,关于索引模块常见问题如下:

    端碗吹水
  • 吐血总结——90%程序员面试都用得上的索引优化手册

    索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,索引就相当于目录。当你在用新华字典...

    本人秃顶程序员
  • MySQL索引优化系列(1)—索引介绍

    MySQL官方对索引的定义为:索引(Index)是帮助MySQL高校获取数据的数据结构。

    公众号 IT老哥
  • 【Mysql进阶-2】图文并茂说尽Mysql索引

    在关系数据库中,索引是一种单独的、物理的数对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据...

    云深i不知处
  • 从认识索引到理解索引「索引优化」

    认识索引是什么东西非常关键,一个非常恰当的比喻就是书的目录页与书的正文内容之间的关系,为了方便查找书中的内容,通过对内容建立索引形成目录。因此,首先你要明白的一...

    民工哥

扫码关注云+社区

领取腾讯云代金券