前言
- 大家好，我是 Lorin ，今天给大家带来 MySQL 查询性能优化的另一个重要优化技术：索引下推。
- 索引下推(Index Condition Pushdown，简称ICP)，是 MySQL5.6 版本提供的新特性，核心目标是减少回表查询次数，提高查询效率。
索引下推原理
MySQL 基础架构
- 在了解索引下推前，我们先简单回忆一下MySQL 基础架构：
- 索引下推本质上是将服务层的的工作下推到存储引擎，从而减少回表的次数，我们来看一下传统查询过程和ICP查询过程的区别：
- 假设我们有如下 TABLE 和 SQL 查询语句：
CREATE TABLE `tuser` (
`id` int(11) NOT NULL,
`id_card` varchar(32) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL
PRIMARY KEY (`id`),
KEY `id_card` (`id_card`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `tuser` (`id`, `id_card`, `name`, `age`) VALUES (1, '1', '张三', 10);
INSERT INTO `tuser` (`id`, `id_card`, `name`, `age`) VALUES (2, '2', '张三', 10);
INSERT INTO `tuser` (`id`, `id_card`, `name`, `age`) VALUES (3, '3', '张六', 30);
INSERT INTO `tuser` (`id`, `id_card`, `name`, `age`) VALUES (4, '4', '张三', 20);
INSERT INTO `tuser` (`id`, `id_card`, `name`, `age`) VALUES (5, '5', '李四', 20);
INSERT INTO `tuser` (`id`, `id_card`, `name`, `age`) VALUES (6, '6', '李五', 10);
INSERT INTO `tuser` (`id`, `id_card`, `name`, `age`) VALUES (7, '7', '马六', 34);
INSERT INTO `tuser` (`id`, `id_card`, `name`, `age`) VALUES (8, '8', '朱九', 22);
INSERT INTO `tuser` (`id`, `id_card`, `name`, `age`) VALUES (9, '9', '祝马甸', 27);
INSERT INTO `tuser` (`id`, `id_card`, `name`, `age`) VALUES (10, '10', '毛阿敏', 234);
EXPLAIN select * from tuser where name like '李%' and age=11;
传统查询过程
- 存储引擎根据 name like '李%' 查询出所有符合的数据列，根据数据列对应主键 ID 回表查询，并返回 Service 层。
- Service 层根据 age=11 筛选符合条件的数据返回客户端。
ICP 查询过程
- 存储引擎根据 name like '李%' 查询出所有符合的数据列，由于使用的是联合索引，数据列中包含 age 的值，存储引擎再根据 age=11 过滤出符合条件的数据列，根据数据列对应主键 ID 回表查询，并返回 Service 层。
- 可以看到使用 ICP 的方式减少了我们回表查询次数，但索引下推在某些情况下可能并不总是生效，具体取决于优化器的决策。
使用场景限制
- 适用于range、ref、eq_ref和ref_or_null查询
- InnoDB和MyISAM都支持，Mysql partition分表也可以使用
- 对于InndoDB而言，ICP只支持二级索引，主键不需要回表
- 子查询不支持
参数配置
索引下推开启状态查询
SHOW VARIABLES LIKE 'optimizer_switch';
// 默认开启
index_condition_pushdown=on
索引下推开启和关闭
set optimizer_switch="index_condition_pushdown=off";
set optimizer_switch="index_condition_pushdown=on";
一些问题
只有联合索引才能使用索引下推？
- 是的，需要联合索引中无法生效的索引字段列进行过滤，从而减少回表查询。
- 注意：Using index condition 不代表一定使用了索引下推，所以一定要注意，比如下面的例子：
EXPLAIN select * from tuser where name like '李%' and id_card > "1111";
- 虽然 Extra 中有 Using index condition ，但是并没有使用索引下推，只是可能使用。
下面的查询为什么不走索引下推
EXPLAIN select * from tuser where name > '李%' and age=11;
- 可以看到查询计划选择了全表扫描，所以无法运用索引下推。
参考
- MySQL 官方手册
- 索引下推，这个点你肯定不知道！
个人简介
👋 你好，我是 Lorin 洛林，一位 Java 后端技术开发者！座右铭：Technology has the power to make the world a better place.
🚀 我对技术的热情是我不断学习和分享的动力。我的博客是一个关于Java生态系统、后端开发和最新技术趋势的地方。
🧠 作为一个 Java 后端技术爱好者，我不仅热衷于探索语言的新特性和技术的深度，还热衷于分享我的见解和最佳实践。我相信知识的分享和社区合作可以帮助我们共同成长。
💡 在我的博客上，你将找到关于Java核心概念、JVM 底层技术、常用框架如Spring和Mybatis 、MySQL等数据库管理、RabbitMQ、Rocketmq等消息中间件、性能优化等内容的深入文章。我也将分享一些编程技巧和解决问题的方法，以帮助你更好地掌握Java编程。
🌐 我鼓励互动和建立社区，因此请留下你的问题、建议或主题请求，让我知道你感兴趣的内容。此外，我将分享最新的互联网和技术资讯，以确保你与技术世界的最新发展保持联系。我期待与你一起在技术之路上前进，一起探讨技术世界的无限可能性。
📖 保持关注我的博客，让我们共同追求技术卓越。
