MySQL ICP/MRR/BNL/BKA

1. Index Condition Pushdown (ICP 索引条件下推)

作用：ICP 允许 WHERE 条件中的部分过滤操作在存储引擎层进行。当查询使用索引时，MySQL 将 WHERE 子句中可以由索引覆盖的过滤条件下推到存储引擎，从而 减少存储引擎层和 Server 层之间的数据传输量，降低回表次数。

适用条件：

• 只适用于二级索引（对于 InnoDB 表）
• 查询类型为 range、ref、eq_ref 和 ref_or_null
• 不适用于覆盖索引场景

配置与使用

  -- 查看ICP当前设置
SELECT @@optimizer_switch LIKE '%index_condition_pushdown%';

  -- 开启ICP（MySQL 8.0默认开启）
SET optimizer_switch = 'index_condition_pushdown=on';

  -- 关闭ICP（用于性能对比测试）
SET optimizer_switch = 'index_condition_pushdown=off';

实例：

  -- 创建测试表
CREATE TABLE `icp_test` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `a` int NOT NULL,
  `b` varchar(20) NOT NULL,
  `c` varchar(20) NOT NULL
) ENGINE=InnoDB;


ALTER TABLE icp_test ADD INDEX idx_a_b (a, b);


INSERT INTO icp_test (a, b, c)
SELECT 
  FLOOR(RAND() * 10) + 1,
  CONCAT('b', FLOOR(RAND() * 100)),
  CONCAT('c', FLOOR(RAND() * 1000))
FROM 
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t3;


SELECT COUNT(*) FROM icp_test;

Multi-Range Read (MRR 多范围读取)

作用：MRR 通过 将随机 I/O 转换为顺序 I/O 来优化查询性能。它先将二级索引查找到的主键 ID 收集到缓冲区，排序后再按照主键顺序访问数据行，显著减少磁盘随机访问。

适用场景：

• 索引范围扫描和等值连接操作
• 需要回表的查询
• 特别是机械硬盘环境

配置与使用

  -- 查看MRR设置
SELECT @@optimizer_switch LIKE '%mrr%';

  -- 开启MRR并关闭基于成本的评估
SET optimizer_switch = 'mrr=on,mrr_cost_based=off';

  -- 调整MRR缓冲区大小（根据需要）
SET read_rnd_buffer_size = 262144; -- 256KB

  -- 创建测试表
CREATE TABLE mrr_test (
  id INT PRIMARY KEY AUTO_INCREMENT,
  i0 INT NOT NULL,
  i1 INT NOT NULL,
  c0 VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

-- 创建辅助索引
ALTER TABLE mrr_test ADD INDEX idx_i0 (i0);

-- 插入测试数据
INSERT INTO mrr_test (i0, i1, c0)
SELECT 
  FLOOR(RAND() * 100),
  FLOOR(RAND() * 1000),
  CONCAT('content_', FLOOR(RAND() * 10000))
FROM 
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t3,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t4;

SELECT COUNT(*) FROM mrr_test;

Block Nested-Loop (BNL 块嵌套循环)

作用：BNL 针对 无索引连接 场景优化，通过将驱动表数据分块放入 join_buffer，减少内表的扫描次数。相比传统的嵌套循环连接（NLJ），BNL 大幅降低了内表的访问频率。

适用条件：

• 内表的查询类型是 ALL、index 或 range 时
• 被关联表没有可用索引时的最后手段

配置与使用

  -- 查看BNL设置
SELECT @@optimizer_switch LIKE '%block_nested_loop%';

  -- 开启BNL（MySQL 8.0默认开启）
SET optimizer_switch = 'block_nested_loop=on';

  -- 调整连接缓冲区大小
SET join_buffer_size = 262144; -- 256KB

  -- 创建测试表
CREATE TABLE bnl_driver (
  id INT PRIMARY KEY AUTO_INCREMENT,
  key_col INT NOT NULL,
  data VARCHAR(50)
) ENGINE=InnoDB;

CREATE TABLE bnl_driven (
  id INT PRIMARY KEY AUTO_INCREMENT, 
  key_col INT NOT NULL,
  data VARCHAR(50)
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO bnl_driver (key_col, data)
SELECT 
  FLOOR(RAND() * 100),
  CONCAT('driver_', FLOOR(RAND() * 1000))
FROM 
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t3;

INSERT INTO bnl_driven (key_col, data)  
SELECT 
  FLOOR(RAND() * 100),
  CONCAT('driven_', FLOOR(RAND() * 1000))
FROM 
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t3;


SELECT COUNT(*) FROM bnl_driver;

  -- 观察BNL使用
EXPLAIN SELECT * FROM bnl_driver d 
LEFT JOIN bnl_driven n ON d.key_col = n.key_col;
-- Extra列显示：Using join buffer (Block Nested Loop)

  -- 添加索引后对比（BNL将不再使用）
ALTER TABLE bnl_driven ADD INDEX idx_key_col(key_col);
EXPLAIN SELECT * FROM bnl_driver d 
LEFT JOIN bnl_driven n ON d.key_col = n.key_col;
-- Extra列不再显示Block Nested Loop

Batched Key Access (BKA 批量键访问)

作用：BKA 结合了 BNL 和 MRR 的优点，对 有索引的连接 进行批量处理。它先将驱动表的连接键批量提交给 MRR 接口，排序后按顺序访问被驱动表，将随机 I/O 转换为顺序 I/O。

适用条件：

• 被驱动表能够使用索引
• 内表的查询类型是 ref 或 eq_ref
• BKA 依赖于 MRR，需要同时开启 MRR

配置与使用

  -- 开启BKA（需要同时开启MRR）
SET optimizer_switch = 'mrr=on,mrr_cost_based=off,batched_key_access=on';

-- 查看BKA设置
SELECT @@optimizer_switch LIKE '%batched_key_access%';

  CREATE TABLE bka_driver (
  id INT PRIMARY KEY AUTO_INCREMENT,
  join_key INT NOT NULL,
  data VARCHAR(50)
) ENGINE=InnoDB;

-- 插入驱动表数据
INSERT INTO bka_driver (join_key, data)
SELECT 
  FLOOR(RAND() * 100),
  CONCAT('data_', FLOOR(RAND() * 1000))
FROM 
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t3;

-- 确保mrr_test表有idx_i0索引
DROP INDEX IF EXISTS idx_i0 ON mrr_test;
ALTER TABLE mrr_test ADD INDEX idx_i0(i0);

  -- 观察BKA使用
EXPLAIN SELECT * FROM bka_driver d 
JOIN mrr_test m ON d.join_key = m.i0 
WHERE m.i0 BETWEEN 10 AND 30;
-- Extra列显示：Using join buffer (Batched Key Access)

-- 对比关闭BKA的情况
SET optimizer_switch = 'batched_key_access=off';
EXPLAIN SELECT * FROM bka_driver d 
JOIN mrr_test m ON d.join_key = m.i0 
WHERE m.i0 BETWEEN 10 AND 30;
-- Extra列不再显示Batched Key Access