“范围条件右边的列全部失效”容易把索引定位、索引条件过滤和回表混在一起。下面固定数据和索引,先确认计划实际使用哪条索引,再解释各列的作用。
准备独立实验库
在可丢弃的 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;
EXPLAIN SELECT notes FROM employee FORCE INDEX (idx_tenant_age_name)
WHERE tenant=3 AND age BETWEEN 20 AND 40 AND name LIKE 'user%';
SELECT id FROM employee WHERE tenant=3 AND age BETWEEN 20 AND 40 ORDER BY id LIMIT 3;
EXPLAIN SELECT notes FROM employee WHERE age=23;
第一条查询使用 FORCE INDEX 是为了固定演示对象,计划应出现 idx_tenant_age_name;这不是生产查询应普遍添加的提示。第二条查询的前三个 ID 为:
23
33
103
按 (tenant, age, name) 顺序组织索引时,前导 tenant 等值和 age 范围可以约束访问范围;name 还能否用于索引条件过滤,要看访问方式、选择列与优化器。因为读取了 notes,本例也不是覆盖索引查询。
最后一条单独查 age 的查询保留自然选择。表上另有 idx_age_id,因此不能把“没用这条联合索引”说成“完全没有索引”。成本估计、数据分布、统计信息和版本都会影响自然计划;不要把小表上的一次计划写成绝对规则。
排查顺序
先核对真实 SQL、参数类型、表结构和数据分布;再读取 possible_keys、key、访问方式与 Extra。必要时比较实际执行信息,而不是只数 key_len。对函数、表达式和类型转换也要核对对应版本是否存在函数索引或其他访问路径。
本轮在 MySQL 8.4.13 / InnoDB 的 1,000 行合成数据上验证了脚本和结果,没有做高并发或吞吐量测试。本次重整保留历史原文链接,新增了固定实验与边界说明,不将旧版本截图作为 8.4 实测。
继续阅读EXPLAIN 与实际执行以及ICP 的过滤位置。