MySQL索引下推（Index Condition Pushdown, ICP）是一种优化技术，首次出现在MySQL 5.6版本中，旨在提高查询效率，特别是当查询涉及到非主键索引（也称为二级索引或辅助索引）时。在没有索引下推的情况下，MySQL在使用索引进行查询时，会先通过索引找到可能匹配的行ID，然后根据这些ID回表（即访问实际的数据行）获取完整数据行，最后在Server层应用WHERE条件过滤出最终结果。这个过程可能会导致大量不必要的数据被读取到Server层。
索引下推优化了这个流程，它允许MySQL将部分WHERE条件直接下推到存储引擎层，在读取索引记录时就进行过滤，这样就可以在更早的阶段排除不符合条件的记录，减少回表的次数和数据传输量，进而提升查询性能。
举例说明：
假设有这样一个表employees，包含字段id（主键）、name（姓名）、age（年龄）、department_id（部门ID），并且针对department_id和age字段建立了组合索引。
Sql
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(255),
age INT,
department_id INT,
INDEX idx_department_age(department_id, age)
);
现在，我们要执行一个查询，查找部门ID为5且年龄大于30岁的员工：
Sql
SELECT * FROM employees WHERE department_id = 5 AND age > 30;
没有索引下推的情况：
MySQL使用idx_department_age索引找到所有department_id = 5的记录。
对于每条记录，MySQL回表获取完整的行数据。
在Server层，MySQL检查每行数据的age字段是否大于30，丢弃不满足条件的记录。
启用索引下推的情况：
MySQL同样使用idx_department_age索引，但在存储引擎层直接检查department_id = 5且age > 30的记录。
只有同时满足两个条件的记录才会被标识出来，然后回表获取这些记录的完整数据。
因此，回表操作大大减少，提高了查询效率。
通过索引下推，MySQL能够在索引遍历的过程中就过滤掉大量不满足条件的记录，避免了无用的回表操作，这对于大数据集和复杂查询尤其有利。不过，索引下推的效益依赖于查询的具体条件和数据分布，不是所有情况下都能显著提升性能。
网友评论