数据库核心术语知识库

定义

索引下推 (Index Condition Pushdown, ICP) 是 MySQL 5.6 引入的一项查询优化技术。它的核心思想是将 WHERE 条件中可以利用索引进行判断的部分,"下推"到存储引擎层进行过滤,从而减少不必要的回表操作和服务器层的条件判断。

在传统模式下,存储引擎只负责根据索引键值查找记录,然后将完整行返回给服务器层,由服务器层进行 WHERE 条件过滤。而 ICP 优化后,存储引擎可以在索引遍历过程中直接对索引中包含的字段进行 WHERE 条件判断,提前过滤掉不满足条件的记录。

详细笔记

核心原理

优化前后对比

优化前 (MySQL 5.6 之前):

存储引擎: 根据索引查找符合条件的记录
  ↓
存储引擎: 回表获取完整行数据
  ↓
存储引擎: 返回完整行给服务器层
  ↓
服务器层: 对返回的行进行 WHERE 条件过滤
  ↓
服务器层: 返回最终结果给客户端

优化后 (启用 ICP):

存储引擎: 根据索引查找记录
  ↓
存储引擎: 在索引层进行 WHERE 条件判断(ICP)
  ↓
存储引擎: 只对满足条件的记录回表
  ↓
存储引擎: 返回完整行给服务器层
  ↓
服务器层: 返回最终结果给客户端

示例说明

sql

-- 表结构
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  city VARCHAR(50),
  INDEX idx_name_age (name, age)
);

-- 查询语句
SELECT * FROM users 
WHERE name LIKE '张%' AND age > 25 AND city = '北京';

未启用 ICP 的执行流程

1. 存储引擎使用 idx_name_age 索引,找到所有 name LIKE '张%' 的记录
 → 假设有 1000 条记录
 
2. 对这 1000 条记录逐一回表,获取完整行数据
 
3. 将 1000 条完整记录返回给服务器层
 
4. 服务器层对 1000 条记录进行 age > 25 AND city = '北京' 过滤
 → 假设最终只有 10 条满足
 
5. 返回 10 条记录给客户端

总回表次数: 1000 次

启用 ICP 的执行流程

1. 存储引擎使用 idx_name_age 索引遍历
 
2. 在索引层同时判断:
 - name LIKE '张%'  (索引前缀匹配)
 - age > 25   (ICP:索引中包含 age 字段)
 → 假设只有 50 条记录同时满足这两个条件
 
3. 只对这 50 条记录回表,获取完整行数据
 
4. 将 50 条完整记录返回给服务器层
 
5. 服务器层对 50 条记录进行 city = '北京' 过滤
 → 最终 10 条满足
 
6. 返回 10 条记录给客户端

总回表次数: 50 次

优化效果:回表次数从 1000 次降低到 50 次,减少了 95% 的随机 I/O。

EXPLAIN 分析

sql

EXPLAIN SELECT * FROM users 
WHERE name LIKE '张%' AND age > 25 AND city = '北京';

输出示例:

+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
|  1 | SIMPLE  | users | range | idx_name_age  | idx_name_age | 152   | NULL | 1000 | Using index condition |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+

关键标识:

Extra: Using index condition:表示使用了索引下推优化
type: range:范围扫描
如果没有 ICP,Extra 会显示 Using where

ICP 的适用条件

可以使用 ICP 的场景

二级索引查询:ICP 仅适用于二级索引,不适用于聚集索引
WHERE 条件包含索引列:

sql

-- ✅ 可以 ICP:age 在索引中
SELECT * FROM users WHERE name LIKE '张%' AND age > 25;

-- ❌ 不能 ICP:city 不在索引中
SELECT * FROM users WHERE name LIKE '张%' AND city = '北京';

符合最左前缀原则:

sql

CREATE INDEX idx_abc ON t(a, b, c);

-- ✅ 可以 ICP
SELECT * FROM t WHERE a = 1 AND b > 2 AND c = 3;

-- ✅ 可以 ICP(b 和 c 都可以下推)
SELECT * FROM t WHERE a = 1 AND b > 2;

-- ❌ 不能 ICP(不符合最左前缀)
SELECT * FROM t WHERE b > 2 AND c = 3;

不支持子查询的条件:ICP 不能下推包含子查询的条件
不支持触发器产生的条件

ICP 不适用的场景

覆盖索引查询:如果查询已经被覆盖索引优化,无需回表,ICP 意义不大
全索引扫描:当需要扫描整个索引时,ICP 优化效果有限
主键查询:主键查询直接定位,不需要 ICP

性能影响分析

测试数据

sql

-- 创建测试表
CREATE TABLE test_icp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  category VARCHAR(50),
  status TINYINT,
  create_time DATETIME,
  content TEXT,
  INDEX idx_cat_status (category, status)
) ENGINE=InnoDB;

-- 插入 100 万条测试数据
INSERT INTO test_icp (category, status, create_time, content)
SELECT 
  ELT(FLOOR(1 + RAND() * 10), 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J'),
  FLOOR(RAND() * 5),
  DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY),
  REPEAT('x', 100)
FROM 
  (SELECT @row := @row + 1 AS n FROM 
   (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
  UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
   (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
  UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
   (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
  UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c,
   (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
  UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d,
   (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
  UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) e,
   (SELECT @row := 0) r
  ) numbers;

性能对比

sql

-- 禁用 ICP
SET optimizer_switch = 'index_condition_pushdown=off';
SELECT * FROM test_icp WHERE category = 'A' AND status = 1;
-- 执行时间: 约 2.5 秒

-- 启用 ICP
SET optimizer_switch = 'index_condition_pushdown=on';
SELECT * FROM test_icp WHERE category = 'A' AND status = 1;
-- 执行时间: 约 0.3 秒

-- 性能提升: 约 8 倍

控制 ICP 开关

全局配置

sql

-- 查看 ICP 状态
SHOW VARIABLES LIKE 'optimizer_switch';

-- 启用 ICP(默认已启用)
SET GLOBAL optimizer_switch = 'index_condition_pushdown=on';

-- 禁用 ICP
SET GLOBAL optimizer_switch = 'index_condition_pushdown=off';

Session 级别配置

sql

-- 当前会话禁用 ICP
SET SESSION optimizer_switch = 'index_condition_pushdown=off';

-- 针对单个查询使用 Hint(MySQL 8.0.17+)
SELECT /*+ NO_ICP(users) */ * FROM users 
WHERE name LIKE '张%' AND age > 25;

ICP 与其他优化的关系

ICP vs 覆盖索引

覆盖索引:完全避免回表,最优方案
ICP:减少回表次数,次优方案
优先级:优先设计覆盖索引,无法覆盖时使用 ICP

sql

-- 场景对比
CREATE INDEX idx_name_age ON users(name, age);

-- 方案一:覆盖索引(最优)
SELECT name, age FROM users WHERE name LIKE '张%' AND age > 25;
-- 无需回表

-- 方案二:ICP(次优)
SELECT * FROM users WHERE name LIKE '张%' AND age > 25;
-- 减少回表次数

-- 方案三:无优化(最差)
SELECT * FROM users WHERE name LIKE '张%' AND city = '北京';
-- city 不在索引中,无法 ICP,需要全部回表后过滤

ICP vs 索引合并

ICP:单个复合索引内部优化
索引合并:多个单列索引组合使用
可以同时使用:索引合并后再应用 ICP

源码实现

ICP 的核心数据结构

MySQL 源码中,ICP 的关键结构:

// sql/handler.h
class handler {
public:
  // ICP 回调函数
  void ha_start_consistent_snapshot();
  
  // 推送索引条件
  int ha_index_cond_push(
    uint keyno,      // 索引编号
    Item *cond,      // 下推的条件
    bool *idx_cond_pushed  // 输出:是否成功下推
  );
  
  // 检查索引条件
  enum_range_flag ha_index_range_flag;
};

ICP 执行流程(简化伪代码)

// storage/innobase/handler/ha_innodb.cc

int ha_innobase::index_cond()
{
  // 1. 检查是否有下推的条件
  if (!m_prebuilt->idx_cond) {
    return HA_READ_OK;  // 没有 ICP 条件,直接返回
  }
  
  // 2. 从当前索引记录中提取字段值
  for each field in m_prebuilt->idx_cond_fields {
    extract_field_value_from_index(field);
  }
  
  // 3. 评估下推的条件
  if (m_prebuilt->idx_cond->val_int() == 0) {
    return HA_ERR_END_OF_FILE;  // 条件不满足,跳过此记录
  }
  
  // 4. 条件满足,继续回表
  return HA_READ_OK;
}

int ha_innobase::index_read_idx_map(...)
{
  while (next_index_record()) {
    // ICP 优化点:在回表前先检查条件
    if (index_cond() != HA_READ_OK) {
    continue;  // 跳过不满足条件的记录,避免回表
    }
    
    // 回表获取完整数据
    if (general_fetch(buf, ...) != 0) {
    return error;
    }
  }
}

实际应用场景

场景一:电商商品搜索

sql

-- 表结构
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  category_id INT,
  brand_id INT,
  price DECIMAL(10, 2),
  stock INT,
  product_name VARCHAR(200),
  description TEXT,
  INDEX idx_cat_brand (category_id, brand_id)
);

-- 查询:某类别某品牌的高价商品
SELECT * FROM products 
WHERE category_id = 100 
  AND brand_id = 50 
  AND price > 500;

-- 优化建议:
-- 1. 当前:price 不在索引中,无法 ICP
-- 2. 改进索引:CREATE INDEX idx_cat_brand_price ON products(category_id, brand_id, price)
-- 3. 效果:price > 500 可以 ICP,大幅减少回表

场景二:日志查询系统

sql

CREATE TABLE application_logs (
  log_id BIGINT PRIMARY KEY,
  app_id INT,
  log_level TINYINT,
  create_time DATETIME,
  message TEXT,
  INDEX idx_app_level (app_id, log_level)
);

-- 查询:某应用的错误日志
SELECT * FROM application_logs 
WHERE app_id = 123 
  AND log_level = 3  -- ERROR
  AND create_time > '2024-01-01';

-- 分析:
-- 1. app_id 和 log_level 可以走索引
-- 2. log_level = 3 可以 ICP(索引中包含)
-- 3. create_time 不能 ICP(不在索引中)
-- 
-- 优化索引:
CREATE INDEX idx_app_level_time ON application_logs(app_id, log_level, create_time);
-- 这样 create_time > '2024-01-01' 也可以 ICP

监控与诊断

查看 ICP 使用情况

sql

-- 查看执行计划
EXPLAIN FORMAT=JSON SELECT * FROM users 
WHERE name LIKE '张%' AND age > 25;

-- JSON 输出中包含:
{
  "attached_condition": "name like '张%'",
  "used_index_conditions": [
  {
  "table": "users",
  "index": "idx_name_age",
  "index_condition": "age > 25"
  }
  ]
}

Performance Schema 统计

sql

-- 查看索引使用统计
SELECT 
  OBJECT_NAME,
  INDEX_NAME,
  COUNT_READ,
  SUM_TIMER_READ
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_NAME = 'users'
ORDER BY SUM_TIMER_READ DESC;

最佳实践

合理设计复合索引:将常用于 WHERE 过滤的列放入索引,以便 ICP 使用
遵循最左前缀原则:确保查询条件能充分利用索引
权衡索引数量:ICP 可以减少回表,但过多索引会影响写入性能
结合覆盖索引:优先考虑覆盖索引,ICP 作为补充优化
监控执行计划:定期检查慢查询,确认 ICP 是否生效

关联术语

[[回表]]
[[覆盖索引]]
[[最左前缀原则]]
[[执行计划]]
[[索引选择性]]

参考资料

MySQL 官方文档: Index Condition Pushdown Optimization
MySQL 源码: storage/innobase/handler/ha_innodb.cc 中的 index_cond() 函数
《高性能 MySQL》第 5 章:索引优化
Percona Blog: Index Condition Pushdown in MySQL 5.6

定义 ​

详细笔记 ​

核心原理 ​

优化前后对比 ​

示例说明 ​

未启用 ICP 的执行流程 ​

启用 ICP 的执行流程 ​

EXPLAIN 分析 ​

ICP 的适用条件 ​

可以使用 ICP 的场景 ​

ICP 不适用的场景 ​

性能影响分析 ​

测试数据 ​

性能对比 ​

控制 ICP 开关 ​

全局配置 ​

Session 级别配置 ​

ICP 与其他优化的关系 ​

ICP vs 覆盖索引 ​

ICP vs 索引合并 ​

源码实现 ​

ICP 的核心数据结构 ​

ICP 执行流程(简化伪代码) ​

实际应用场景 ​

场景一:电商商品搜索 ​

场景二:日志查询系统 ​

监控与诊断 ​

查看 ICP 使用情况 ​

Performance Schema 统计 ​

最佳实践 ​

关联术语 ​

参考资料 ​

定义