MySQL-索引下推

MySQL-索引下推

起男 698 2022-05-06

MySQL-索引下推

当explan中extra字段出现了using index conditon说明使用了索引下推

index condition pushdown是MySQL5.6中新特性，是一种在存储引擎层使用索引过滤数据的优化方式

  • 如果没有icp，存储引擎会遍历索引以定位表中的行，并将它们返回给MySQL服务器，由MySQL服务器评估where后面的条件是否保留行
  • 启用icp后，如果部分where条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分where条件放到存储引擎筛选。然后存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行

优点

icp可以减少存储引擎必须访问基表的次数和mysql服务器必须访问存储引擎的次数

但是，icp的加速效果取决于存储引擎内通过icp筛选掉的数据比例

开启和关闭

默认情况下启用索引条件下推。可以通过设置系统变量optimizer_switch控制：index_condition_pushdown

#关闭索引下推
set optimizer_switch = 'index_condition_pushdown=off';
#打开索引下推
set optimizer_switch = 'index_condition_pushdown=on';

或者可以在查询时指定不启用icp

select /*+ no_icp(tab_name)*/ * from tab_name;

使用条件

  • 如果表访问的类型为range、ref、eq_ref、和ref_of_null可以使用icp
  • icp可以用于innodb表和myisam表，包括分区表
  • 对于innodb表，icp仅用于二级索引
  • 当sql使用索引覆盖时，不能使用icp
  • 相关子查询的条件不能使用icp
mysql