一篇文章读懂什么是MySQL索引下推（ICP）

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)

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

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)

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";