MySQL索引下推（ICP）

一、简介

ICP（Index Condition Pushdown）是在MySQL 5.6版本上推出的查询优化策略，把本来由Server层做的索引条件检查下推给存储引擎层来做，以降低回表和访问存储引擎的次数，提高查询效率。

二、原理

为了理解ICP是如何工作的，我们先了解下没有使用ICP的情况下，MySQL是如何查询的：

• 存储引擎读取索引记录；
• 根据索引中的主键值，定位并读取完整的行记录；
• 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP的情况下，查询过程如下：

• 读取索引记录（不是完整的行记录）；
• 判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
• 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
• 存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。

三、实践

先创建一张表，并插入记录

CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT COMMENT "主键",
name varchar(32)  COMMENT "姓名",
city varchar(32)  COMMENT "城市",
age int(11)  COMMENT "年龄",
primary key(id),
key idx_name_city(name, city)
)engine=InnoDB default charset=utf8;

insert into user(name, city, age) values("ZhaoDa", "BeiJing", 20),("QianEr", "ShangHai", 21),("SunSan", "GuanZhou", 22), ("LiSi", "ShenZhen", 24), ("ZhouWu", "NingBo", 25),  ("WuLiu", "HangZhou", 26), ("ZhengQi", "NanNing", 27), ("WangBa", "YinChuan", 28), ("LiSi", "TianJin", 29), ("ZhangSan", "NanJing", 30), ("CuiShi", "ZhengZhou", 65),  ("LiSi", "KunMing", 29), ("LiSi", "ZhengZhou", 30);

查看一下表记录

mysql> select * from user;
+----+----------+-----------+------+
| id | name     | city      | age  |
+----+----------+-----------+------+
|  1 | ZhaoDa   | BeiJing   |   20 |
|  2 | QianEr   | ShangHai  |   21 |
|  3 | SunSan   | GuanZhou  |   22 |
|  4 | LiSi     | ShenZhen  |   24 |
|  5 | ZhouWu   | NingBo    |   25 |
|  6 | WuLiu    | HangZhou  |   26 |
|  7 | ZhengQi  | NanNing   |   27 |
|  8 | WangBa   | YinChuan  |   28 |
|  9 | LiSi     | TianJin   |   29 |
| 10 | ZhangSan | NanJing   |   30 |
| 11 | CuiShi   | ZhengZhou |   65 |
| 12 | LiSi     | KunMing   |   29 |
| 13 | LiSi     | ZhengZhou |   30 |
+----+----------+-----------+------+
13 rows in set (0.00 sec)

注意，这张表里创建了联合索引(name, city)，假设我们想查询如下语句：

select * from user where name="LiSi" and city like "%Z%" and age > 25;

3.1 不使用索引下推

在不使用索引下推的情况下，根据联合索引“最左匹配”原则，只有name列能用到索引，city列由于是模糊匹配，是不能用到索引的，此时的执行过程是这样的：

• 存储引擎根据(name, city)联合索引，找到name值为LiSi的记录，共4条记录；
• 然后根据这4条记录中的id值，逐一进行回表扫描，去聚簇索引中取出完整的行记录，并把这些记录返回给Server层；
• Server层接收到这些记录，并按条件name="LiSi" and city like "%Z%" and age > 25进行过滤，最终留下("LiSi", "ZhengZhou", 30)这条记录。

画张图看一下：

3.2 使用索引下推

使用索引下推的情况下，执行过程是这样的：

• 存储引擎根据(name, city)联合索引，找到name='LiSi'的记录，共4条；
• 由于联合索引中包含city列，存储引擎直接在联合索引中按city like "%Z%"进行过滤，过滤后剩下2条记录；
• 根据过滤后的记录的id值，逐一进行回表扫描，去聚簇索引中取出完整的行记录，并把这些记录返回给Server层；
• Server层根据WHERE语句的其它条件age > 25，再次对行记录进行筛选，最终只留下("LiSi", "ZhengZhou", 30)这条记录。

画张图看一下：

 

另外，从执行计划里也可以看到使用了索引下推（Extra里显示Using index condition）

mysql> explain select * from user where name="LiSi" and city like "%Z%" and age > 25;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra                              |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+------------------------------------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_name_city | idx_name_city | 99      | const |    4 |     7.69 | Using index condition; Using where |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

四、使用条件

• 只能用于range、 ref、 eq_ref、ref_or_null访问方法；
• 只能用于InnoDB和 MyISAM存储引擎及其分区表；
• 对InnoDB存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;

索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于InnoDB的聚簇索引来说，完整的行记录已经加载到缓存区了，索引下推也就没什么意义了。

• 引用了子查询的条件不能下推；
• 引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

五、相关系统参数

索引条件下推默认是开启的，可以使用系统参数optimizer_switch来控制器是否开启。

查看默认状态：

mysql> select @@optimizer_switch\G;
*************************** 1. row ***************************
@@optimizer_switch: 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,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)

切换状态：

set optimizer_switch="index_condition_pushdown=off";
set optimizer_switch="index_condition_pushdown=on";

作者：牧码人zhouz
链接：https://www.jianshu.com/p/31ceadace535
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。