索引下推（Index Condition Pushdown，简称ICP）优化，说白了，就是数据库在处理查询时，把一部分原本要在服务器层（Server Layer）做的条件过滤，提前下放到存储引擎层（Storage Engine Layer）去执行。这样一来，存储引擎就只把那些真正符合条件的行传给服务器层，大大减少了数据传输量，自然也就提升了查询效率。
解决方案理解索引下推，得先知道MySQL大致分两层：服务器层和存储引擎层。服务器层负责SQL解析、查询优化、缓存、日志等，而存储引擎层（比如InnoDB）则负责实际的数据存储和检索。
在没有ICP的情况下，如果一个查询使用了复合索引，但
WHERE子句中除了用于索引查找的列，还有索引中其他列的条件，那么存储引擎会先根据索引查找定位到所有符合索引查找条件的行（比如
idx_city_age索引中的
city = 'New York'），然后把这些行的所有数据（或者至少是请求的列）都加载到服务器层。服务器层再对这些数据进行二次过滤（比如
age > 30）。
而有了ICP，如果
WHERE子句中的某些条件只涉及到索引列，即使这些列不是索引的“前缀”部分，存储引擎也可以在读取索引条目时就对这些条件进行判断。它会利用索引中的信息，直接过滤掉不符合条件的行，只把那些既满足索引查找条件又满足下推条件的行传给服务器层。
举个例子，你有一个复合索引
(city, age)，查询是
SELECT * FROM users WHERE city = 'New York' AND age > 30;
-
没有ICP： 存储引擎找到所有
city = 'New York'
的索引条目，然后回表（如果不是覆盖索引）获取完整行数据，将这些行全部发送给服务器层。服务器层再逐行判断age > 30
。 -
有ICP： 存储引擎在遍历
city = 'New York'
的索引条目时，会同时检查age > 30
这个条件。只有当city = 'New York'
且age > 30
都满足时，才去回表获取完整行数据，并将这些行发送给服务器层。这样，存储引擎和服务器层之间的数据传输量就显著减少了。
在我看来，ICP最直观的价值就是减少了“无用功”。那些明明可以在数据源头就过滤掉的数据，就不必千里迢迢地传输到上层再被抛弃，这对于IO密集型的数据库操作来说，简直是雪中送炭。
索引下推是如何提升查询性能的？说起来，性能提升主要体现在两个方面：数据传输量和CPU消耗。
首先是减少数据传输量。想象一下，如果你的索引覆盖了查询所需的所有列，并且
WHERE条件中包含了这些索引列，那么在没有ICP时，存储引擎会根据索引找到所有符合主查询条件的索引条目，然后把这些条目对应的“完整”行数据（或者说，至少是那些需要进一步过滤的行）都一股脑地丢给服务器层。但如果有了ICP，存储引擎在扫描索引的时候，就能提前利用索引中的信息把不符合下推条件的行给筛掉。这意味着，从存储引擎到服务器层的数据通道上，流过的都是“精挑细选”过的有效数据，而不是一大堆可能很快就会被抛弃的“垃圾”。数据量少了，传输自然就快，尤其是当表很大，符合初步索引条件的行很多，但符合下推条件的行很少时，这个效果尤为显著。
其次是降低CPU消耗。数据传输量的减少，直接导致服务器层需要处理的行数变少。这意味着服务器层在进行最终过滤、排序、聚合等操作时，CPU的负担会轻很多。毕竟，处理几百行数据和处理几万行数据，CPU的忙碌程度是完全不同的。而且，原本在服务器层进行的条件判断，现在下放到存储引擎层，存储引擎往往对数据结构有更深入的了解，理论上可以更高效地进行这些判断。
举个例子，我们有张表
employees，上面有一个复合索引
(department_id, salary, hire_date)。 如果我们执行查询：
SELECT * FROM employees WHERE department_id = 10 AND salary > 50000 AND hire_date < '2020-01-01';在ICP的加持下：
- 存储引擎会先用
department_id = 10
进行索引定位。 - 在遍历
department_id = 10
的索引条目时，它会利用索引中的salary
和hire_date
信息，直接判断salary > 50000
和hire_date < '2020-01-01'
。 - 只有当这三个条件都满足时，存储引擎才会去读取完整的行数据，并将其传给服务器层。
这就像快递员送包裹，以前是把所有目的地在某个区域的包裹都送到分拣中心，分拣中心再根据具体门牌号筛选。现在是快递员在揽件的时候就根据门牌号把不符合的包裹筛选掉了，分拣中心收到的就是可以直接派送的包裹，效率自然高了。
哪些场景下索引下推会生效？索引下推并不是万能的，它有自己的适用范围和条件。主要是在以下几种场景下会发挥作用：
-
复合索引的非前缀列条件： 这是最典型的应用场景。当你的
WHERE
子句中，除了用于索引查找（比如range
、ref
类型）的索引前缀列外，还包含了该复合索引中其他非前缀列的条件时，ICP就能介入。- 例如：索引
(col1, col2, col3)
，查询WHERE col1 = 'A' AND col3 > 10
。col1 = 'A'
用于索引定位，col3 > 10
就是可以被下推的条件。
- 例如：索引
-
LIKE
操作符： 当LIKE
模式不是以通配符开头，并且可以利用索引前缀进行范围扫描时，LIKE
条件中剩余的部分（如果仍然是索引列的一部分）就有可能被下推。- 例如：索引
(name)
，查询WHERE name LIKE 'John%' AND name LIKE '%son'
。'John%'
用于索引范围扫描，而'%son'
这个条件，如果能在索引条目上直接判断，也会被下推。不过，这种情况相对复杂，更常见的是LIKE 'prefix%'
本身作为索引扫描条件。
- 例如：索引
-
多列索引中的
OR
条件（有限制）： 虽然OR
条件通常会阻碍索引使用，但在某些特定情况下，如果OR
连接的条件都只涉及索引列，并且优化器认为下推有利，也可能发生。但这不如AND
条件常见和有效。
PIA
全面的AI聚合平台，一站式访问所有顶级AI模型
226
查看详情
ENUM
类型列的条件： 如果索引列是ENUM
类型，并且WHERE
条件中对ENUM
值进行了过滤，ICP也能很好地工作。
限制和不生效的场景：
-
非索引列条件： 如果
WHERE
条件中包含的列不在索引中，那么这个条件就无法被下推。存储引擎无法访问这些列的信息。 -
函数或表达式： 如果
WHERE
条件中对索引列使用了函数或表达式（例如WHERE YEAR(hire_date) = 2020
），通常也无法下推，因为存储引擎需要先计算函数结果才能判断，这超出了它直接处理索引条目的能力。 -
NOT EXISTS
或NOT IN
子查询： 这些复杂查询的优化器行为比较特殊，ICP通常不适用。 - 全文索引和空间索引： ICP主要针对B-tree索引。
- MySQL版本： 索引下推是MySQL 5.6版本引入的特性，如果你还在用更老的版本，那自然是享受不到这项优化了。
理解这些适用场景，能帮助我们更好地设计索引和编写查询语句，让数据库的优化器有更多“施展拳脚”的空间。
如何判断我的查询是否使用了索引下推？要判断一个查询是否用到了索引下推，最直接、最权威的工具就是MySQL的
EXPLAIN语句。当你运行
EXPLAIN来分析你的SQL查询时，你需要关注结果中的
Extra列。
如果
Extra列中出现了
Using index condition，那么恭喜你，你的查询就利用了索引下推优化。
我们来看一个具体的例子：
假设我们有一个用户表
user_profiles，包含
id,
first_name,
last_name,
age,
city等字段。我们为
city和
age建立了一个复合索引：
CREATE TABLE user_profiles (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
age INT,
city VARCHAR(50),
INDEX idx_city_age (city, age)
);
INSERT INTO user_profiles (first_name, last_name, age, city) VALUES
('Alice', 'Smith', 30, 'New York'),
('Bob', 'Johnson', 25, 'London'),
('Charlie', 'Brown', 35, 'New York'),
('David', 'Miller', 40, 'Paris'),
('Eve', 'Davis', 28, 'London'),
('Frank', 'Wilson', 32, 'New York'),
('Grace', 'Moore', 27, 'London'); 现在，我们执行一个查询，并用
EXPLAIN分析：
EXPLAIN SELECT * FROM user_profiles WHERE city = 'New York' AND age > 30;
你可能会看到类似这样的输出（具体内容可能因MySQL版本和数据分布略有差异）：
在上面的
EXPLAIN结果中，
Extra列显示了
Using index condition。这明确告诉我们，优化器决定使用索引下推来处理
age > 30这个条件。具体来说，存储引擎在扫描
idx_city_age索引，找到所有
city = 'New York'的条目时，会同时检查这些条目中的
age值是否大于 30。只有那些同时满足
city = 'New York'和
age > 30的索引条目，才会触发回表操作（因为我们查询的是
SELECT *），将完整的行数据传给服务器层。
如果没有
Using index condition，而是显示
Using where; Using index或者仅仅是
Using where，那么就意味着虽然可能使用了索引，但
WHERE子句的过滤是在服务器层完成的，没有利用到索引下推的优势。
掌握
EXPLAIN是数据库性能优化的基础，通过它，我们不仅能判断ICP是否生效，还能深入了解查询的执行计划，从而做出更精准的优化决策。它就像是数据库的“X光片”，能让你看到内部的运行机制。
以上就是索引下推（Index Condition Pushdown）优化是什么？的详细内容，更多请关注知识资源分享宝库其它相关文章！
相关标签： mysql 工具 ai red sql mysql select enum 数据结构 堆 using 数据库 性能优化 大家都在看： MySQL内存使用过高（OOM）的诊断与优化配置 MySQL与NoSQL的融合：探索MySQL Document Store的应用 如何通过canal等工具实现MySQL到其他数据源的实时同步？ 使用Debezium进行MySQL变更数据捕获（CDC）实战 如何设计和优化MySQL中的大表分页查询方案
发表评论:
◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。