索引下推（Index Condition Pushdown）优化是什么？

索引下推通过将WHERE条件中涉及索引列的过滤下推到存储引擎层执行，减少回表和数据传输量，从而提升查询性能。具体表现为：在复合索引查询中，存储引擎利用索引条目直接过滤非前缀列条件（如WHERE city='New York' AND age>30），仅将满足所有索引条件的行回表并传给服务器层，避免了大量无效数据的传输与处理。该优化适用于复合索引的非前缀列、LIKE前缀匹配、ENUM列等场景，但要求条件列必须包含在索引中且不使用函数或表达式。从MySQL 5.6开始支持，通过EXPLAIN执行计划中Extra列是否出现“Using index condition”可判断其是否生效。

索引下推（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

其次是降低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';

1. 存储引擎会先用

   department_id = 10

   登录后复制
   进行索引定位。
2. 在遍历

   department_id = 10

   登录后复制
   的索引条目时，它会利用索引中的

   salary

   登录后复制
   和

   hire_date

   登录后复制
   信息，直接判断

   salary > 50000

   登录后复制
   和

   hire_date < '2020-01-01'

   登录后复制
   。
3. 只有当这三个条件都满足时，存储引擎才会去读取完整的行数据，并将其传给服务器层。

这就像快递员送包裹，以前是把所有目的地在某个区域的包裹都送到分拣中心，分拣中心再根据具体门牌号筛选。现在是快递员在揽件的时候就根据门牌号把不符合的包裹筛选掉了，分拣中心收到的就是可以直接派送的包裹，效率自然高了。

哪些场景下索引下推会生效？

索引下推并不是万能的，它有自己的适用范围和条件。主要是在以下几种场景下会发挥作用：

1. 复合索引的非前缀列条件： 这是最典型的应用场景。当你的

   WHERE

   登录后复制
   子句中，除了用于索引查找（比如

   range

   登录后复制
   、

   ref

   登录后复制
   类型）的索引前缀列外，还包含了该复合索引中其他非前缀列的条件时，ICP就能介入。
   • 例如：索引

     (col1, col2, col3)

     登录后复制
     ，查询

     WHERE col1 = 'A' AND col3 > 10

     登录后复制
     。

     col1 = 'A'

     登录后复制
     用于索引定位，

     col3 > 10

     登录后复制
     就是可以被下推的条件。

2. LIKE

   登录后复制
   操作符： 当

   LIKE

   登录后复制
   模式不是以通配符开头，并且可以利用索引前缀进行范围扫描时，

   LIKE

   登录后复制
   条件中剩余的部分（如果仍然是索引列的一部分）就有可能被下推。
   • 例如：索引

     (name)

     登录后复制
     ，查询

     WHERE name LIKE 'John%' AND name LIKE '%son'

     登录后复制
     。

     'John%'

     登录后复制
     用于索引范围扫描，而

     '%son'

     登录后复制
     这个条件，如果能在索引条目上直接判断，也会被下推。不过，这种情况相对复杂，更常见的是

     LIKE 'prefix%'

     登录后复制
     本身作为索引扫描条件。

3. 多列索引中的

   OR

   登录后复制
   条件（有限制）： 虽然

   OR

   登录后复制
   条件通常会阻碍索引使用，但在某些特定情况下，如果

   OR

   登录后复制
   连接的条件都只涉及索引列，并且优化器认为下推有利，也可能发生。但这不如

   AND

   登录后复制
   条件常见和有效。
   

   纳米搜索

   纳米搜索：360推出的新一代AI搜索引擎

   30

   查看详情

4. 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

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

city = 'New York'

age > 30

SELECT *

如果没有

Using index condition

Using where; Using index

Using where

WHERE

掌握

EXPLAIN

以上就是索引下推（Index Condition Pushdown）优化是什么？的详细内容，更多请关注php中文网其它相关文章！