>>与软件开发有关的知识:操作系统,数据库,网络通信等 书籍支持  卫琴直播  品书摘要  在线测试  资源下载  联系我们
发表一个新主题 开启一个新投票 回复文章 您是本文章第 20940 个阅读者 刷新本主题
 * 贴子主题:  回表与覆盖索引 回复文章 点赞(0)  收藏  
作者:mary    发表时间:2020-03-11 19:04:10     消息  查看  搜索  好友  邮件  复制  引用

回表与覆盖索引

  正式讲 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的聚簇索引即数据,索引和数据是存在一起的;那么直接走聚簇索引查询的 SQL 是不存在回表一说的,比如   select *  from tbl_index  where c1  =  10; ,只有从二级索引出发,并且二级索引独自完成不了查询的时候才会回表到聚簇索引完成查询

    MyISAM 的回表

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

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

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

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

  看 MySQL 的执行计划,如果 Extra 中只有  using  index 则说明使用了覆盖索引查询,如果 Extra 中出现了  using  indexcondition 或  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 时,引擎层会将满足   Index  Key 范围限制的所有数据记录(示例中一共 6 条)逐条返回给 Server 层,然后由 server 层应用   Index Filter 和  Table Filter (MySQL 5.6 之前不区分 Index Filter 和 Table Filter),最后将满足条件的数据返回给客户端;


    使用了 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 的开发者们是肿么肥事 ?

            

----------------------------
原文链接:https://blog.51cto.com/14455981/2476794

程序猿的技术大观园:www.javathinker.net



[这个贴子最后由 flybird 在 2020-03-13 12:20:13 重新编辑]
  Java面向对象编程-->接口
  JavaWeb开发-->Web运作原理(Ⅱ)
  JSP与Hibernate开发-->映射一对多关联关系
  Java网络编程-->用Spring整合CXF发布Web服务
  精通Spring-->组合(Composition)API
  Vue3开发-->绑定CSS样式
  一文读懂非关系型数据库(NoSQL)
  SSL知识简介
  SQL查询语句练习题
  解决mysql问题:The server quit without updating PID file
  谈谈网络通信中的 ACK、NACK 和 REX
  Mysql数据库的使用总结之ERROR 1146 (42S02)
  作为一个程序员,CPU的这些硬核知识你必须会!
  MySQL 事务
  MySQL 正则表达式
  MySQL的数据类型
  SQL NOT NULL 约束
  MySQL5.7慢查询日志时间与系统时间差8小时原因
  Mysql提升效率的神器: 索引
  linux系列之常用运维命令整理
  Mysql支持的数据类型(总结)
  更多...
 IPIP: 已设置保密
楼主      
1页 0条记录 当前第1
发表一个新主题 开启一个新投票 回复文章


中文版权所有: JavaThinker技术网站 Copyright 2016-2026 沪ICP备16029593号-2
荟萃Java程序员智慧的结晶,分享交流Java前沿技术。  联系我们
如有技术文章涉及侵权,请与本站管理员联系。