MySQL优化之索引下推

最新推荐文章于 2025-06-20 16:56:29 发布

原创最新推荐文章于 2025-06-20 16:56:29 发布 · 1.1k 阅读

12 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

MySQL 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了MySQL5.6引入的索引条件下推（IndexConditionPushdown）技术，通过实例演示了如何在查询中利用该特性减少回表操作，提升查询效率。作者通过创建表和数据插入展示了开启和关闭索引下推对查询计划的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

(/≧▽≦)/~┴┴ 嗨~我叫小奥 ✨✨✨
👀👀👀 个人博客：小奥的博客
👍👍👍：个人优快云
⭐️⭐️⭐️：传送门
🍹 本人24应届生一枚，技术和水平有限，如果文章中有不正确的内容，欢迎多多指正！
📜 欢迎点赞收藏关注哟！ ❤️

MySQL优化之索引下推

一、概念

索引条件下推(Index Condition Pushdown)，简称ICP。MySQL5.6新添加，用于优化数据的查询。

比如说有这样的场景：

有些搜索条件中虽然出现了索引列，但却不能使用到索引，比如下边这个查询：

SELECT * FROM table WHERE key1 = ‘z’ AND key1 LIKE ‘%a’;

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

先根据 key1 = 'z' 这个条件，从二级索引 idx_key1 中获取到对应的二级索引记录。
根据上一步骤得到的二级索引记录中的主键值进行回表，找到完整的用户记录再检测该记录是否符合
key1 LIKE '%a' 这个条件，将符合条件的记录加入到最后的结果集。

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

先根据 key1 = 'z' 这个条件，定位到二级索引 idx_key1 中对应的二级索引记录。
对于指定的二级索引记录，先不着急回表，而是先检测一下该记录是否满足 key1 LIKE '%a' 这个条件，如果这个条件不满足，则该二级索引记录压根儿就没必要回表。
对于满足 key1 LIKE '%a' 这个条件的二级索引记录执行回表操作。

我们知道，回表操作其实是一个随机 IO ，比较耗时，所以上述修改虽然只改进了一点点，但是可以省去好多回表操作的成本。

二、验证

如果在查询语句的执行过程中将要使用索引条件下推这个特性，在 Extra 列中将会显示 Using index condition 。

2.1 建表语句

#创建表 建立name, city, age三个字段的复合索引，sex是为了避免索引覆盖
CREATE TABLE user_index (
                      id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
                      name varchar(32)  COMMENT '姓名',
                      city varchar(32)  COMMENT '城市',
                      age int(11)  COMMENT '年龄',
                      sex int(1) default 0,
                      primary key(id),
                      key idx_name_city(name, city, age)
)engine=InnoDB default charset=utf8;
 
#插入数据
insert into user_index(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);

2.2 关闭索引下推

关闭索引下推，查看执行计划：

#关闭索引下推
set optimizer_switch='index_condition_pushdown=off';
#查看执行计划
explain select * from user_index where name = 'ZhaoDa' and age = 20;

+--+-----------+----------+----------+----+-------------+-------------+-------+-----+----+--------+-----------+
|id|select_type|table     |partitions|type|possible_keys|key          |key_len|ref  |rows|filtered|Extra      |
+--+-----------+----------+----------+----+-------------+-------------+-------+-----+----+--------+-----------+
|1 |SIMPLE     |user_index|null      |ref |idx_name_city|idx_name_city|99     |const|1   |10      |Using where|
+--+-----------+----------+----------+----+-------------+-------------+-------+-----+----+--------+-----------+

2.3 打开索引下推

打开索引下推，默认是开启的，再次查看执行计划：

#打开索引下推  默认是开启
set optimizer_switch='index_condition_pushdown=on';
#查看执行计划
explain select * from user_index where name = 'ZhaoDa' and age = 20;

+--+-----------+----------+----------+----+-------------+-------------+-------+-----+----+--------+---------------------+
|id|select_type|table     |partitions|type|possible_keys|key          |key_len|ref  |rows|filtered|Extra                |
+--+-----------+----------+----------+----+-------------+-------------+-------+-----+----+--------+---------------------+
|1 |SIMPLE     |user_index|null      |ref |idx_name_city|idx_name_city|99     |const|1   |10      |Using index condition|
+--+-----------+----------+----------+----+-------------+-------------+-------+-----+----+--------+---------------------+