一、ICP是什么？
索引条件下推是一种MySQL优化技术，它允许在存储引擎层（如InnoDB）对索引条件进行过滤，而不是将所有行都传递给Server层再进行过滤。
核心思想
将WHERE子句中可以用索引进行过滤的条件，下推到存储引擎中执行，减少需要回表的记录数。
二、ICP的工作原理对比
1. 没有ICP的情况（MySQL 5.6之前）
sql
复制
下载
-- 示例查询：有一个复合索引 (zipcode, lastname, firstname) SELECT * FROM people WHERE zipcode = '95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';
执行流程：
text
复制
下载
1. 存储引擎：使用索引找到所有 zipcode='95054' 的行 2. 存储引擎：将满足zipcode条件的所有行（包括主键）返回给Server层 3. Server层：对每一行检查 lastname LIKE '%etrunia%' 条件 4. Server层：对满足条件的行检查 address LIKE '%Main Street%' 5. Server层：返回最终结果
问题：即使lastname是索引的一部分，但因为LIKE '%etrunia%'（通配符开头）不能使用索引范围扫描，所以所有zipcode='95054'的行都会被返回给Server层。
2. 启用ICP的情况（MySQL 5.6+）
sql
复制
下载
-- 同样的查询 SELECT * FROM people WHERE zipcode = '95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';
执行流程：
text
复制
下载
1. 存储引擎：使用索引找到所有 zipcode='95054' 的行 2. 存储引擎：在引擎层检查 lastname LIKE '%etrunia%' 条件 3. 存储引擎：只将满足zipcode和lastname条件的行返回给Server层 4. Server层：检查 address LIKE '%Main Street%' 条件 5. Server层：返回最终结果
优势：减少了存储引擎和Server层之间的数据传输量。
篇幅限制下面就只能给大家展示小册部分内容了。整理了一份核心面试笔记包括了：Java面试、Spring、JVM、MyBatis、Redis、MySQL、并发编程、微服务、Linux、Springboot、SpringCloud、MQ、Kafc
需要全套面试笔记及答案
【点击此处即可/免费获取】
三、ICP的工作机制详解
1. 适用条件
sql
复制
下载
-- 示例表结构 CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), age INT, department_id INT, salary DECIMAL(10,2), hire_date DATE, INDEX idx_dept_age (department_id, age), INDEX idx_hire_salary (hire_date, salary) );
✅ ICP适用的场景
sql
复制
下载
-- 场景1：复合索引，非首列使用范围查询 SELECT * FROM employees WHERE department_id = 10 AND age > 25; -- age是索引的第二列 -- 场景2：复合索引，对非首列使用非等值条件 SELECT * FROM employees WHERE department_id = 10 AND age BETWEEN 25 AND 30; -- 场景3：索引列使用LIKE（通配符不在开头） SELECT * FROM employees WHERE department_id = 10 AND name LIKE 'John%'; -- 假设name是索引的一部分 -- 场景4：多条件混合，部分条件可下推 SELECT * FROM employees WHERE hire_date > '2020-01-01' AND salary > 50000 AND department_id = 10;
❌ ICP不适用的场景
sql
复制
下载
-- 场景1：不使用索引的查询 SELECT * FROM employees WHERE salary > 50000; -- salary没有索引 -- 场景2：索引失效的情况 SELECT * FROM employees WHERE name LIKE '%John%'; -- 通配符开头 -- 场景3：只访问索引就能满足查询（覆盖索引） SELECT department_id, age FROM employees WHERE department_id = 10 AND age > 25; -- 场景4：子查询、JOIN中的某些复杂情况
2. ICP的具体执行步骤
sql
复制
下载
-- 创建测试表 CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE, status ENUM('pending', 'shipped', 'delivered'), amount DECIMAL(10,2), INDEX idx_customer_status_date (customer_id, status, order_date) ); -- 插入测试数据 INSERT INTO orders VALUES (1, 1001, '2023-01-10', 'delivered', 150.00), (2, 1001, '2023-01-15', 'shipped', 200.00), (3, 1001, '2023-01-20', 'pending', 100.00), (4, 1002, '2023-01-05', 'delivered', 300.00), (5, 1002, '2023-01-12', 'shipped', 250.00); -- 使用ICP的查询 EXPLAIN SELECT * FROM orders WHERE customer_id = 1001 AND status = 'shipped' AND order_date > '2023-01-01';执行过程分解：
text
复制
下载
步骤1：索引扫描 - 使用idx_customer_status_date索引 - 定位到customer_id=1001的第一条记录 步骤2：ICP过滤（在存储引擎层） - 检查当前索引条目：customer_id=1001 ✓ - 检查status='shipped'（索引第二列）✓ - 检查order_date>'2023-01-01'（索引第三列）✓ - 如果以上都满足，获取主键order_id 步骤3：回表 - 使用主键获取完整行数据 步骤4：Server层过滤 - 检查其他非索引条件（如果有）
四、代码演示：ICP效果验证
1. 创建测试环境
sql
复制
下载
-- 创建大表进行测试 CREATE TABLE icp_test ( id INT AUTO_INCREMENT PRIMARY KEY, a INT, b INT, c VARCHAR(100), d VARCHAR(100), INDEX idx_ab (a, b) ); -- 插入100万条测试数据 DELIMITER $$ CREATE PROCEDURE populate_icp_test() BEGIN DECLARE i INT DEFAULT 0; WHILE i < 1000000 DO INSERT INTO icp_test (a, b, c, d) VALUES ( FLOOR(RAND() * 100), -- a: 0-99 FLOOR(RAND() * 1000), -- b: 0-999 CONCAT('data_', i), CONCAT('extra_', i) ); SET i = i + 1; END WHILE; END$$ DELIMITER ; CALL populate_icp_test();2. 验证ICP效果
sql
复制
下载
-- 关闭ICP（仅用于对比） SET optimizer_switch = 'index_condition_pushdown=off'; -- 查询1：不使用ICP EXPLAIN ANALYZE SELECT * FROM icp_test WHERE a = 10 AND b BETWEEN 100 AND 200; -- 开启ICP SET optimizer_switch = 'index_condition_pushdown=on'; -- 查询2：使用ICP EXPLAIN ANALYZE SELECT * FROM icp_test WHERE a = 10 AND b BETWEEN 100 AND 200;
3. 分析执行计划
sql
复制
下载
-- 查看优化器开关状态 SELECT @@optimizer_switch LIKE '%index_condition_pushdown%'; -- 详细的执行计划对比 EXPLAIN FORMAT=JSON SELECT * FROM icp_test WHERE a = 10 AND b BETWEEN 100 AND 200;
执行计划关键字段解读：
Using index condition: 表示使用了ICProws: 预估需要检查的行数filtered: 存储引擎过滤后剩余的比例
五、ICP的优化器决策
1. MySQL如何决定是否使用ICP
sql
复制
下载
-- 查看查询优化过程 SET optimizer_trace="enabled=on"; SELECT * FROM icp_test WHERE a = 10 AND b > 500; SELECT * FROM information_schema.optimizer_trace; SET optimizer_trace="enabled=off";
2. 成本计算模型
MySQL优化器会评估：
使用ICP的成本：索引过滤 + 回表成本
不使用ICP的成本：全部回表 + Server层过滤成本
3. 强制/禁用ICP的Hint
sql
复制
下载
-- 使用优化器提示强制ICP SELECT /*+ INDEX_CONDITION_PUSHDOWN(t1) */ * FROM icp_test t1 WHERE a = 10 AND b > 500; -- 禁用ICP SELECT /*+ NO_INDEX_CONDITION_PUSHDOWN(t1) */ * FROM icp_test t1 WHERE a = 10 AND b > 500;
六、实际案例分析
案例1：电商订单查询优化
sql
复制
下载
-- 原始表结构 CREATE TABLE orders_before ( order_id BIGINT PRIMARY KEY, user_id INT, create_time DATETIME, status TINYINT, amount DECIMAL(10,2), INDEX idx_user_create (user_id, create_time) ); -- 查询：查找用户最近7天待发货的订单 -- 没有ICP时的问题：所有最近7天的订单都会回表 SELECT * FROM orders_before WHERE user_id = 12345 AND create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND status = 2; -- 2表示待发货 -- 优化方案：添加status到索引中 CREATE TABLE orders_after ( order_id BIGINT PRIMARY KEY, user_id INT, create_time DATETIME, status TINYINT, amount DECIMAL(10,2), INDEX idx_user_status_create (user_id, status, create_time) ); -- 现在status可以参与ICP过滤 SELECT * FROM orders_after WHERE user_id = 12345 AND status = 2 AND create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY);
案例2：时间范围查询优化
sql
复制
下载
-- 日志表 CREATE TABLE access_log ( id BIGINT PRIMARY KEY, user_id INT, access_time DATETIME, endpoint VARCHAR(200), response_time INT, INDEX idx_time_user (access_time, user_id) ); -- 查询某用户特定时间段内的慢请求 SELECT * FROM access_log WHERE access_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59' AND user_id = 1001 AND response_time > 1000; -- 慢请求阈值 -- ICP作用：在存储引擎层先过滤user_id=1001
七、监控和诊断ICP
1. 查看ICP使用情况
sql
复制
下载
-- 通过performance_schema监控 SELECT * FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST_TEXT LIKE '%SELECT%icp_test%' ORDER BY SUM_TIMER_WAIT DESC; -- 通过状态变量监控 SHOW STATUS LIKE '%handler%'; -- 关注： -- Handler_read_next: 顺序读取索引的次数 -- Handler_read_rnd_next: 随机读取数据的次数
2. ICP相关的系统变量
sql
复制
下载
-- 查看ICP相关设置 SHOW VARIABLES LIKE 'optimizer_switch'; -- 输出中包含：index_condition_pushdown=on -- 临时关闭ICP（不推荐生产使用） SET SESSION optimizer_switch='index_condition_pushdown=off';
八、最佳实践和注意事项
1. 设计索引时考虑ICP
sql
复制
下载
-- 好的索引设计：考虑ICP的使用 -- 将等值条件列放在前面，范围条件列放在后面 CREATE INDEX idx_good ON table (equality_col1, equality_col2, range_col); -- 反例：范围条件放在前面会限制ICP效果 CREATE INDEX idx_bad ON table (range_col, equality_col);
篇幅限制下面就只能给大家展示小册部分内容了。整理了一份核心面试笔记包括了：Java面试、Spring、JVM、MyBatis、Redis、MySQL、并发编程、微服务、Linux、Springboot、SpringCloud、MQ、Kafc
需要全套面试笔记及答案
【点击此处即可/免费获取】
2. 编写查询时优化
sql
复制
下载
-- 优化前：范围条件在前 SELECT * FROM table WHERE date_col > '2023-01-01' -- 范围条件 AND user_id = 1001; -- 等值条件 -- 优化后：调整条件顺序（对ICP更友好） SELECT * FROM table WHERE user_id = 1001 -- 等值条件 AND date_col > '2023-01-01'; -- 范围条件 -- 注意：条件顺序不影响ICP决策，但好的索引设计应该匹配查询模式
3. 注意事项
版本要求：MySQL 5.6+ 支持，MariaDB 5.3+ 支持
存储引擎：主要针对InnoDB，MyISAM也支持
限制：
不适用于虚拟生成列
子查询中的外部表条件不能下推
对分区表的支持有限制
九、性能对比测试
测试脚本
sql
复制
下载
-- 创建对比测试 CREATE TABLE icp_perf_test ( id INT PRIMARY KEY, col1 INT, col2 INT, col3 VARCHAR(100), col4 TEXT, INDEX idx_col1_col2 (col1, col2) ); -- 批量插入数据 INSERT INTO icp_perf_test SELECT n, FLOOR(RAND() * 1000), FLOOR(RAND() * 10000), CONCAT('data_', n), REPEAT('x', 500) FROM ( SELECT ROW_NUMBER() OVER () as n FROM information_schema.columns a CROSS JOIN information_schema.columns b LIMIT 1000000 ) t; -- 性能测试查询 SET profiling = 1; -- 测试1：ICP开启 SET optimizer_switch = 'index_condition_pushdown=on'; SELECT * FROM icp_perf_test WHERE col1 = 500 AND col2 > 8000 LIMIT 1000; -- 测试2：ICP关闭 SET optimizer_switch = 'index_condition_pushdown=off'; SELECT * FROM icp_perf_test WHERE col1 = 500 AND col2 > 8000 LIMIT 1000; -- 查看性能对比 SHOW PROFILES;十、总结
ICP的核心价值：
减少IO操作：在存储引擎层过滤掉不符合条件的行，减少回表次数
降低CPU开销：减少Server层需要处理的数据量
提升缓存效率：更少的数据传输意味着更好的缓存利用率
使用建议：
识别ICP机会：复合索引 + 非首列范围查询
监控ICP效果：通过执行计划和性能监控验证
合理设计索引：考虑查询模式，将高频等值条件放在索引前列
保持版本更新：新版本的MySQL对ICP有更多优化
通过合理利用ICP，可以在不修改应用代码的情况下，显著提升包含复合索引范围查询的性能，特别是在大数据量的场景下效果更为明显。
