写点什么

web 前端培训 -MySQL 的索引下推解析

作者:@零度
  • 2022 年 3 月 30 日
  • 本文字数:1741 字

    阅读完需:约 6 分钟

 以下文章来源于业余草,编辑作者是业余草

本文问题

  1. 什么是索引下推(Index Condition Pushdown ICP)?索引下推的过程是怎样的?

  2. 在哪些情况下可以使用索引下推,索引下推有什么限制?

  3. 如何启用或禁用索引下推优化?




Index Condition Pushdown(ICP)

当 MySQL 使用一个索引来检索表中的行时,可以使用 ICP 作为一种优化方案。不使用 ICP 时,存储引擎通过索引检索基础表中的行并将符合 WHERE 条件中的行返回给客户端。启用 ICP 后,如果只需要通过索引中的列就能够评估行是否符合 WHERE 中的一部分条件,MySQL 将这部分 WHERE 条件下推到存储引擎中,然后存储引擎评估使用索引条目来评估下推的索引条件,并只从表中读取符合条件的行。ICP 可以减少存储引擎访问基础表的次数以及 MySQL 访问存储引擎的次数_前端培训

ICP 适用情景

  • ICP 可以用于需要访问整个表中的行的 range,ref,eq_ref,ref_or_null 访问模式。

  • ICP 可以用于 InnoDB 和 MyISAM,包括分区表。

ICP 限制

  • 对于 InnoDB 表,ICP 只适用于辅助索引。因为 ICP 的目标是减少全表读的数量从而减少 I/O 操作。对于 InnoDB 聚集索引,完整的记录已经被读取到 InnoDB 缓冲区,在这种情况下使用 ICP 不能降低 I/O

  • ICP 不支持在虚拟生成列上创建的索引。InnoDB 存储引擎支持在虚拟生成列上创建索引

  • 与子查询相关的条件不能下推

  • 与存储函数相关的条件不能下推。

  • 触发条件不能下推



ICP 优化过程

要理解 ICP 优化如何工作,首先考虑一下,在不使用索引下推的情况下,索引扫描是如何进行的:

  • 获取下一行,首先读取索引元组,然后通过索引元组检索并读取整行数据。

  • 对表中的数据测试是否符合 WHERE 条件,基于测试结果接收或拒绝行

  • 使用索引下推时,扫描过程如下:

  • 获取下一行的索引元组(不需要获取整行数据)

  • 测试能否只通过索引中的行确认数据是否符合 WHERE 条件中的一部分。如果不符合条件,继续获取下一个索引元组。

  • 如果符合条件,通过索引元组来检索并读取整行数据

  • 测试数据是否符合 WHERE 条件中的其他部分。基于测试结果接收或拒绝行。

ICP 的 EXPLIAN 输出

在 Extra 列中显示 Using index condition。

示例

假设有一个表包括人员和地址数据,在表上具有一个索引 INDEX (zipcode, lastname, firstname).如果我们知道一个人的 zipcode 值但是不确定他的名称,我们可以使用以下查询:

SELECT * FROM people

WHERE zipcode='95054'

AND lastname LIKE '%etrunia%'

AND address LIKE '%Main Street%';

MySQL 可以使用索引检索 zipcode='95054'的人。第二部分不能用来限制必须扫描的行数量(lastname LIKE '%etrunia%' 因为 LIKE 条件以 %开头),当不使用索引下推时,这个查询必须检索所有 zipcode='95054'的整行数据使用索引下推,MySQL 在读取整行数据前,先检查 lastname LIKE '%etrunia%'.这样就不用读取那些符合 zipcode 条件但是不符合 lastname 条件的行数据了。

控制 ICP 是否启用

可以使用 iptimizer_switch 变量的 index_condition_pushdown 标志来控制 ICP 是否启用

SET optimizer_switch = 'index_condition_pushdown=off';

SET optimizer_switch = 'index_condition_pushdown=on';

问题答案

  1. 什么是索引下推(Index Condition Pushdown(ICP))?索引下推的过程是怎样的?索引下推就是在查询时,首先只读取索引元组(而不是整行数据),通过索引元组来判断数据是否符合 WHERE 语句中的条件,然后只对其中符合条件的行再读取整行数据来进行其他 WHERE 条件的判断。「过程」

  • 读取索引元组

  • 根据索引元组判断该行是否符合 WHERE 中的其他条件(在索引中的其他列的条件部分),如果不符合,www.atguigu.com 直接读取下一个索引元组,不读取该行数据

  • 如果符合条件,通过索引元组检索并读取整行数据_web前端培训

  • 判断数据是否符合 WHERE 中的其他条件(不在索引中的其他列的条件部分)。

  1. 在哪些情况下可以使用索引下推,索引下推有什么限制?

  • ICP 可以用于 MyISAM 和 InnoDB 表,包括分区表。对于 InnoDB 表,ICP 只适用于辅助索引。

  • ICP 可以用于 range,ref,eq_ref,ref_or_not 类型的查询。

  • 「限制」

  • 不适用于在虚拟生成列上创建的索引。

  • 不适用于与子查询/存储函数/触发条件相关的条件

  1. 如何查看一个语句是否使用了索引下推?查看 EXPLAIN 的 Extra 列是否显示 Using index condition

  2. 如何启用或禁用索引下推优化?禁用:SET optimizer_switch = 'index_condition_pushdown=off';

  3. 启用:SET optimizer_switch = 'index_condition_pushdown=on';

用户头像

@零度

关注

关注尚硅谷,轻松学IT 2021.11.23 加入

还未添加个人简介

评论

发布
暂无评论
web前端培训-MySQL的索引下推解析_MySQL_@零度_InfoQ写作平台