执行计划里出现 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 手册说明适用边界。
继续阅读联合索引的定位与过滤和覆盖索引与回表。