MySQL——索引下推

1、使用前后对比

index Condition Pushdown(ICP)是MySQL5.6中新特性，是一种在存储引擎层使用索引过滤数据的优化方式。

• 如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给MySQL服务器，由MySQL服务器评估WHERE后面的条件是否保留行。
• 启用ICP后（默认启用），如果部分WHERE条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分WHERE条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。
  • 好处：ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。但是，ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。

例如以下语句

EXPLAIN SELECT * FROM `s1` WHERE key1 = 'vLuQVg' and key1 like '%v'

其中的key1 = 'vLuQVg’可以使用到索引，但是key1 like ‘%v’ 却无法使用到索引，在以前版本中的MySQL中，是按照下边步骤来执行这个查询的：

• 先根据key1 = 'vLuQVg’这个条件。从二级索引index_key1中获取到对应的二级索引记录。
• 根据上一步骤得到的二次索引记录中的主键进行回表，找到完整的用户记录在检测记录是否符合**key1 like ‘%v’**这个条件，将符合条件的记录加入最后的结果集。

但是虽然key1 like ‘%v’ 不能组成范围区间参与range访问方法的执行，但这个条件毕竟只涉及到了key1列，所以MySQL把上边的步骤改进了一下：

• 先根据key1 = 'vLuQVg’这个条件，定位到二级索引index_key1中对应的二级索引记录。
• 对于指定的二级索引记录，先不着急回表，而是先检测一下该记录是否满足key1 like ‘%v’ 这个条件，如果这个条件不满足，则该二级索引记录压根就没必要回表操作。
• 对于满足key1 like ‘%v’ 这个条件的二级索引记录进行回表操作。

我们说回表操作其实是一个随机IO，比较耗时，所以上述修改虽然只改进一点点，但是可以省去好多回表操作的成本。MySQL把它们的这个改进称之为索引条件下推（英文名：Index Condition Pushdown）。

2、ICP的使用条件

1. 如果表访问的类型为range、ref、eq_ref和ref_or_null可以使用ICP
2. ICP可以用于Innodb和MyISAM表，包括分区表Innodb和MyISAM表
3. 对于Innodb表，ICP仅用于二级索引。ICP的目标是减少全行读取次数，从而减少I/O操作
4. 当SQL使用覆盖索引时，不支持ICP，因为这种情况使用ICP不会减少I/O
5. 相关子查询的条件不能使用ICP