MySQL索引优化

文章信息

• 原文链接：https://jiayq.blog.csdn.net/article/details/150282163
• 发布时间：2025-08-12 19:46:15
• 阅读量：541
• 分类：mysql专栏收录该内容, 订阅专栏
• 标签：#mysql, #性能优化, #数据库, #索引优化, #ICP, #索引条件下推, #MRR

摘要

文章浏览阅读541次，点赞16次，收藏12次。MySQL索引优化技术解析：ICP与MRR ICP（索引条件下推）通过将WHERE条件下推到存储引擎层，减少回表查询次数，适用于InnoDB辅助索引和部分查询条件。实验显示，启用ICP后执行计划中的Extra显示”Using index condition”。MRR（多范围读取）通过排序键值来减少随机磁盘访问，适用于大表范围扫描。两种优化技术分别通过减少I/O操作和优化磁盘访问顺序提升查询性能，但对虚拟列索引均不支持。通过optimizer_switch参数可控制这些优化功能的开启状态。_mysql icp和索引范围

---

MySQL索引优化

• ICP 索引条件下推
• MRR 多范围读取

ICP 索引条件下推

索引条件下推(Index Condition Pushdown,ICP)是针对MySQL 通过索引从表中查询行数据的优化。在没有ICP的情况下，存储引擎遍历索引以定位表中匹配的行数据，并将这些行数据返回给MySQL服务，MySQL服务对这些行再次进行where 条件的过滤。启用ICP后，在取出索引的同时，MySQL服务将where条件下推到存储引擎。存储引擎使用索引项来评估推入的索引条件，只有满足这个条件，才从表中读取行。启用ICP可以减少存储引擎访问表的次数和MySQL服务访问存储引擎的次数。

ICP的适用范围如下：

• 当需要访问全表时，ICP适用于range,ref,eq_ref和ref_or_null访问方法
• 可以用于InnoDB表和MyISAM表，包括分区InnoDB表和MyISAM表
• 对于InnoDB表，ICP只用于辅助索引，ICP的目标是减少整行读取的次数，从而减少I/O操作，对于InnoDB聚集索引，完整记录已经被读入InnoDB缓存区中，在这种情况下使用ICP并不会减少I/O操作。
• 在虚拟列上创建的二级索引不支持ICP
• 引用子查询的条件不能使用ICP
• 引用存储过程，触发器的条件不能使用ICP

用一个例子来感受ICP

首先创建一张表

create table `test_icp` (
`id` int not null auto_increment,
`a` int not null,
`b` char(2) not null,
`c` datetime not null default current_timestamp,
primary key (`id`),
key `idx_a_b` (`a`,`b`)
) engine=innodb default charset=utf8mb4;

如果要通过字段a和字段b查询字段c的值，但是字段b的值只知道最后一个字符

select `c` from `test_icp` where `a`=1 and `b` like '%b';

查看ICP状态，有全局的，也有session的

show global variables like 'optimizer_switch';

如果关闭ICP(session)

set session optimizer_switch='index_condition_pushdown=off';

查看执行计划

explain select `c` from `test_icp` where `a`=1 and `b` like '%b';

执行结果

打开ICP

查看执行计划

很明显的一个区别 Extra从Using where 变成 Using index condition。这就是MySQL使用ICP时Extra输出的内容。

在关闭ICP的情况下，查询使用索引扫描 a=1的记录，必须检索所有满足a=1的行，同时进行b like '%j'的过滤。

在启用ICP的情况下，MySQL在读取完整行之前就会检查b like '%j'的部分，避免读取满足了a=1但是不满足b like '%j'的行。

MRR 多范围读取

MRR(Multi-Range Read, MRR) 多范围读取。

当MySQL的表很大并且没有存储在存储引擎的缓存中时，使用二级索引上的范围扫描读取行会导致对表的许多随机磁盘访问。先使用MRR优化，MySQL会尝试通过只扫描索引并收集相关行的键来减少范围扫描的磁盘访问次数，然后对键进行排序，最后按主键的顺序从表中检索数据。

MRR的目的是减少磁盘访问次数。

在虚拟列上创建的二级索引不支持MRR优化。

optimizer_switch中有两个参数用于控制MRR: mrr 和 mrr_cost_based。

mrr控制MRR是否开启

在mrr=on的情况下，mrr_cost_based表示优化器是否尝试在使用和不使用MRR或尽可能使用MRR之间做出基于成本的选择。

当使用MRR时，explain 输出中的Extra 变为 Using MRR。