【mysql 5.7】存储引擎不能使用索引中范围条件右边的列 & range

概述

对于range查询，在一条组合索引里面，range字段后面的列是不生效的，不会使用索引，range列自身仍是生效的。

1 准备数据

1.1 建表

DROP TABLE IF EXISTS staff;
CREATE TABLE IF NOT EXISTS staff (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(50),
    age INT,
    pos VARCHAR(50) COMMENT '职位',
    salary DECIMAL(10,2)
);

1.2 插入数据

INSERT INTO staff(name, age, pos, salary) VALUES('Alice', 22, 'HR', 5000);

2 测试&Explain分析

2.1 创建索引

创建组合索引：

CREATE INDEX idx_nameAgePos ON staff(name, age, pos);

2.2 测试

Case#1：只有 name 且是等于

EXPLAIN SELECT * FROM staff where name = 'Alice';

注意key_len显示使用了索引中的第一列

Case#2：name & age 且是等于

EXPLAIN SELECT * FROM staff where name = 'Alice' AND age = 22;

注意key_len显示使用了索引中的第一列和第二列

Case#3：name & age & pos 且是等于

EXPLAIN SELECT * FROM staff where name = 'Alice' AND age = 22 AND pos = 'HR';

Case#4：name 是等于，age 不是等于（pos 其实已经使用不上索引了）

EXPLAIN SELECT * FROM staff where name = 'Alice' AND age > 21 AND pos = 'HR';

分析一下Case#4的结果，如下：

• type=range，
  注意上面三条语句全是 type=ref，第四条时range，已经劣了一级；

• key_len=58（这和 Case#2 的相同），说明只有 name & age 两个索引生效，而后面的 pos 没有用上索引

  疑问：用了前2列，没有用最后一列，这个索引过程是怎么走的？

• ref=Null（前面全是 const）

注意：虽然type列是range，但是Extra列是Using index condition，说明仍然使用了索引，只不过是前导列(id列不在这个组合做引中，需要回表)

3 结论

范围之后全失效——存储引擎不能使用索引中范围条件右边的列

参考：
《索引法则–范围之后全失效》