mysql - 索引下推 （ICP Index Condition Pushdown）

mysql - 索引下推 （ICP Index Condition Pushdown）

什么是索引下推

Index Condition Pushdown （ICP）是MySQL使用索引从表中检索行数据的一种优化方式。

• 禁用ICP时，存储引擎会通过遍历索引定位基表中的行，然后返回给Server层，再去为这些数据行进行where条件的过滤。
• 启用ICP时，如果where条件可以使用索引，MySQL会把这部分过滤操作放到存储引擎层，存储引擎通过索引过滤，把满足的行从表中读取出。 ICP可以减少存储引擎必须访问基表的次数以及Server曾必须访问存储引擎的次数

条件

• 当sql需要全表访问时,ICP的优化策略可用于range, ref, eq_ref, ref_or_null 类型的访问数据方法
• ICP只能用于二级索引，不能用于主索引。
• 并非全部where条件都可以用ICP筛选,如果where条件的字段不在索引列中,还是要读取整表的记录到server端做where过滤
• ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例
• 当sql 使用覆盖索引时，不支持ICP 优化方法。

查看是否开启 ICP

基于 mysql 8.0

mysql> show VARIABLES like '%optimizer_switch%'\G;
*************************** 1. row ***************************
Variable_name: optimizer_switch Value: 
        index_merge=on,
        index_merge_union=on,
        index_merge_sort_union=on,
        index_merge_intersection=on,
        engine_condition_pushdown=on,
        index_condition_pushdown=on,
        mrr=on,
        mrr_cost_based=on,
        block_nested_loop=on,
        batched_key_access=off,
        materialization=on,
        semijoin=on,
        loosescan=on,firstmatch=on,
        duplicateweedout=on,
        subquery_materialization_cost_based=on,
        use_index_extensions=on,    ## 默认是开启 ICP
        condition_fanout_filter=on,
        derived_merge=on,
        use_invisible_indexes=off,
        skip_scan=on,
        hash_join=on,
        subquery_to_derived=off,
        prefer_ordering_index=on,
        hypergraph_optimizer=off,
        derived_condition_pushdown=on
1 row in set (0.00 sec)

开启和关闭ICP

SET optimizer_switch = 'index_condition_pushdown=off'; # 关闭
SET optimizer_switch = 'index_condition_pushdown=on';  # 开启

准备表

CREATE TABLE `t_icp_city` (
  `id` int NOT NULL AUTO_INCREMENT,
  `province` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `block` varchar(255) DEFAULT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_city` (`province`,`city`,`block`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

其中 索引为 idx_city (province,city,block)

使用ICP情况

EXPLAIN SELECT * FROM `t_icp_city` WHERE province='上海' and city like '%上海%' and block like '%江%'

mysql> EXPLAIN SELECT * FROM `t_icp_city` WHERE province='上海' 
						and city like '%上海%' and block like '%江%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_icp_city
   partitions: NULL
         type: ref
possible_keys: idx_city
          key: idx_city
      key_len: 1023
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition  ## 使用 ICP ！！
1 row in set, 1 warning (0.00 sec)

如果使用了索引覆盖，也不会使用ICP

EXPLAIN SELECT province,city,block FROM `t_icp_city` WHERE province='上海' 
									and city like '%上海%' and block like '%江%'


mysql> EXPLAIN SELECT province,city,block FROM `t_icp_city` 
				WHERE province='上海' and city like '%上海%' 
				and block like '%江%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_icp_city
   partitions: NULL
         type: ref
possible_keys: idx_city
          key: idx_city
      key_len: 1023
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index  ### 只是使用使用了 索引！！
1 row in set, 1 warning (0.00 sec)