MySQL 索引条件下推优化

字数统计1.2k阅读时长7分阅读次数

索引条件下推优化

概述

索引条件下推（Index Condition Pushdown，简称ICP）是针对MySQL使用索引从表中检索行的情况进行的优化。并在Mysql5.6的版本上才推出。MySQL提供的用某一个索引对一个特定的表从表中获取元组，即单表利用索引进行扫描以获取数据的一种方式，因而不适用于多表查询。

是否启用ICP的区别

在没有ICP的情况下，存储引擎遍历索引来定位基表中的行，并将它们返回给MySQL服务器，MySQL服务器评估行的WHERE条件。

启用ICP后，如果WHERE条件的一部分可以仅使用索引中的列进行计算，MySQL服务器会将WHERE条件的这一部分向下推送到存储引擎。然后，存储引擎通过使用索引项来评估推送的索引条件，并且只有在满足该条件时，才从表中读取行。可在下面实例中体会其意义。

ICP的意义

  1. 索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。
  2. ICP可以减少存储引擎必须访问基表的次数
  3. MySQL服务器必须访问存储引擎的次数。

指标条件下推优化的适用性取决于以下条件：

  1. 当需要访问整表行时，ICP用于range、ref、eq_ref和ref_or_null访问方法。

  2. ICP可以用于InnoDB和MyISAM表，包括分区的InnoDB和MyISAM表。

  3. 对于InnoDB表，ICP仅用于二级索引。ICP的目标是减少整行读取的数量，从而减少I/O操作。对于InnoDB聚集索引，完整的记录已经读入InnoDB缓冲区。在这种情况下使用ICP并不能减少I/O。（注意：Ⅰ说明减少完整记录（一条完整元组）读取的个数；Ⅱ是说明对于InnoDB聚集索引无效，只能是对SECOND INDEX这样的非聚集索引有效。）

  4. 在虚拟生成的列上创建的辅助索引不支持ICP。InnoDB支持虚拟生成列的二级索引。

  5. 引用子查询的条件不能向下推。

  6. 引用存储函数的条件不能向下推。存储引擎不能调用存储函数。

  7. 触发条件不能下推。

ICP优化原理

要了解此优化的工作原理，请首先考虑在不使用索引条件下推时索引扫描是如何进行的：

  1. 获取下一行，首先读取索引元组，然后使用索引元组定位并读取整个表行。
  2. 测试适用于此表的WHERE条件部分。根据测试结果接受或拒绝行。

下推过程

使用“索引条件”下推，扫描过程如下：

  1. 获取下一行的索引元组（而不是整个表行）。
  2. 测试应用于此表且只能使用索引列检查的WHERE条件部分。如果不满足条件，则转到下一行的索引元组。
  3. 如果满足该条件，则使用索引元组来定位并读取整个表行。
  4. 测试适用于此表的WHERE条件的其余部分。根据测试结果接受或拒绝行。

EXPLAIN输出显示在使用索引条件下推时在额外列中使用索引条件。它不显示使用索引，因为当必须读取完整表行时，这不适用。

案例

假设一个表包含有关人员及其地址的信息，并且该表有一个定义为index的索引（手机尾号、姓名、包裹号）。如果我们知道一个人的手机尾号，但不确定姓名，我们可以这样搜索：

1
2
3
4
SELECT * FROM order
  WHERE 手机尾号='9856'
  AND 姓名 LIKE '%莫同学%'
  AND 包裹号 LIKE '%211%';

MySQL可以使用索引扫描手机尾号为’9856’的人。第二部分(姓名LIKE ‘%莫同学%’)不能用于限制必须扫描的行数，所以如果没有索引条件下推，这个查询必须检索手机尾号=’9850’的所有人的全表行。

当使用下推索引条件，MySQL在读取全表行之前检查姓名LIKE ‘%莫同学%’部分。这避免读取与手机尾号条件匹配但不匹配姓名条件的索引元组对应的完整行。

ICP的开启与关闭

索引条件默认启用“下推”。可以通过设置index_condition_pushdown标志来控制optimizer_switch系统变量:

1
2
SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

大家觉得写还可以，可以点赞、收藏、关注一下吧！
也可以到我的个人博客参观一下，估计近几年都会一直更新！和我做个朋友吧！https://motongxue.cn