MySQL优化之索引下推

本文介绍了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|
+--+-----------+----------+----------+----+-------------+-------------+-------+-----+----+--------+---------------------+

并且索引还是遵循最左匹配的。

三、总结

索引条件下推:简单来说就是把索引当中的所有数据全部用到减少回表的次数,达到优化的目的。

表现:查看执行计划时Extra会显示为Using index condition

### MySQL 索引下推的工作原理 索引下推(Index Condition Pushdown, ICP)是一种从 MySQL 5.6 版本开始引入的性能优化技术,其主要作用是减少存储引擎层与服务器层之间的交互次数,从而降低查询过程中回表操作的数量[^3]。 #### 原理概述 在传统的查询执行流程中,MySQL 的存储引擎会先根据索引定位到可能符合条件的记录范围,然后将这些记录返回给服务器层进行进一步过滤。然而,这种方式可能会导致大量不必要的数据被传输至服务器层处理。而通过启用 ICP 功能,部分原本由服务器层完成的条件筛选工作可以转移到存储引擎层面实现。这意味着,在读取索引条目的同时即可应用某些查询条件来排除不匹配的行,进而显著减少需要访问的实际数据量[^4]。 例如,在一个复合查询条件下,如果存在多个列上的限制表达式,则可以通过仅扫描那些初步满足索引定义范围内约束的部分来进行更早阶段的选择性裁剪[^2]。 ```sql SELECT * FROM customers WHERE age >= 18 AND address LIKE '%Francisco%'; ``` 在这个例子当中,假设 `age` 字段已经建立了相应的索引结构;那么借助于索引下推机制的作用之下,不仅能够依据该字段迅速缩小目标集合规模,而且还能同步考虑其他非键属性如地址字符串模式匹配这样的复杂逻辑运算规则,以此达到双重削减候选集的效果。 ### 实现方式 具体来说,MySQL 是如何做到这一点呢?实际上就是把原来属于 SQL 层面负责解析并判断是否符合最终结果集标准的一部分计算任务下沉到了更低层次即 InnoDB 存储引擎内部去执行: - 当某个特定类型的次级索引被用于辅助检索过程时; - 如果发现除了主键之外还有额外附加的信息可用于加速决策制定的话, 此时就会激活所谓的“index condition push down”行为模式——允许直接利用这些附加信息来做预筛选动作而不是简单地依赖原始物理页加载后再做二次验证。 这种做法的好处显而易见:一方面它可以有效避免过多无关紧要的数据进入内存缓冲池占用宝贵资源;另一方面也能加快整个请求响应速度因为减少了磁盘I/O次数以及CPU周期消耗等问题的发生几率。 ### 应用场景分析 考虑到上述特点之后我们可以总结出几个比较典型的适合采用索引下推策略的应用场合如下所示: 1. **多列联合索引** - 对于涉及两个或者更多不同维度限定语句组成的复杂查询而言尤为适用; ```sql SELECT * FROM orders WHERE customer_id = 100 AND product_id > 50 ORDER BY order_date DESC LIMIT 10; ``` 此处假定 `(customer_id, product_id)` 组成了一个多维组合型二级索引对象的情况下,就可以充分利用ICP特性使得只针对那些既符合客户编号又大于指定商品ID阈值的有效项目展开深入探讨研究活动去了. 2. **全文搜索引擎集成环境下的模糊查找支持** 类似之前提到过的邮政编码样式的地理区域名称搜索实例同样非常契合此类需求特征描述要求: ```sql SELECT COUNT(*) AS num_users FROM users USE INDEX (idx_age) WHERE age BETWEEN 20 AND 30 AND city REGEXP '^New.*York$'; ``` 这里强调的是即使面对较为棘手复杂的正则表达式匹配任务也同样有机会享受到来自底层硬件设施所提供的强大助力效果哦! ---
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值