MySQL学习专题

MySQL ICP:用开关对比索引条件下推

在 MySQL 8.4 中开关 index_condition_pushdown,对比非覆盖查询的 Extra,区分索引定位、索引内过滤和覆盖访问。

简明结论

ICP 把可使用索引列判断的部分条件交给存储引擎,在读取完整记录前过滤;出现 Using index condition 不等于覆盖索引或已经最优。

执行计划里出现 Using index condition,是否意味着不需要回表?不是。ICP 的作用正是让某些不符合条件的索引项在取完整记录前被排除。下面让查询读取未包含在联合索引中的 notes,再对比开关。

准备独立实验库

在可丢弃的 MySQL 8.4 实例里首次运行以下建库脚本。不要在业务库中执行;其他文章已创建同名实验库时可以复用同结构数据,不要重复建库。

CREATE DATABASE phase2_knowledge;
USE phase2_knowledge;
CREATE TABLE employee (
  id INT PRIMARY KEY,
  tenant INT NOT NULL,
  age INT NOT NULL,
  name VARCHAR(40) NOT NULL,
  notes VARCHAR(200) NOT NULL,
  KEY idx_tenant_age_name (tenant, age, name),
  KEY idx_age_id (age, id)
) ENGINE=InnoDB;
INSERT INTO employee
WITH RECURSIVE seq AS (
  SELECT 1 AS n UNION ALL SELECT n+1 FROM seq WHERE n < 1000
)
SELECT n, MOD(n,10), MOD(n,80), CONCAT('user',n), REPEAT('x',120) FROM seq;
ANALYZE TABLE employee;

对比脚本

USE phase2_knowledge;
SET @saved_optimizer_switch=@@optimizer_switch;
SET optimizer_switch='index_condition_pushdown=on';
SELECT 'ICP_ON';
EXPLAIN SELECT notes FROM employee FORCE INDEX (idx_tenant_age_name)
WHERE tenant=3 AND age>10 AND name LIKE '%3%';
SET optimizer_switch='index_condition_pushdown=off';
SELECT 'ICP_OFF';
EXPLAIN SELECT notes FROM employee FORCE INDEX (idx_tenant_age_name)
WHERE tenant=3 AND age>10 AND name LIKE '%3%';
SET optimizer_switch=@saved_optimizer_switch;

本次 MySQL 8.4.13 验证中,ICP_ON 后的 Extra 包含 Using index condition,ICP_OFF 后不包含。使用 FORCE INDEX 固定教学访问路径;生产应先观察自然计划。

tenant 约束前导列,age 形成范围,name 的包含匹配不能直接变成同样的前缀定位,但索引里存在 name,因而可能在取 notes 前判断该条件。这个例子不支持“范围之后的列完全无用”的结论。

三件容易混淆的事

观察对象回答的问题
索引访问范围需要走过哪些索引项
索引条件下推哪些项可以在读完整记录前被过滤
覆盖访问所需列是否已经都在索引中

本例只对比计划字段,没有测量物理 I/O、回表次数和实际加速倍数。ICP 是否适用还取决于引擎、索引种类、访问方式和条件本身;单次出现或未出现 Extra 不能作为整个系统的性能结论。脚本最后恢复本连接的优化器设置。

保留历史原文地址;本站新增了同一查询的开关对比,并按 MySQL 8.4 手册说明适用边界。

继续阅读联合索引的定位与过滤和覆盖索引与回表。