复合索引在范围查询中的优化策略与失效场景分析

题目

复合索引在范围查询中的优化策略与失效场景分析

信息

• 类型：问答
• 难度：⭐⭐⭐

考点

复合索引结构,索引最左前缀原则,范围查询对索引的影响,索引条件下推(ICP),索引优化策略

快速回答

在复合索引中，范围查询（如 >, <, BETWEEN）会导致索引中后续列无法被有效使用：

• 复合索引 (a, b, c) 中，对 a 列进行范围查询时，b 和 c 列仍可能被利用（通过ICP）
• 对 b 列进行范围查询时，c 列无法使用索引过滤
• 范围查询后的列无法参与索引排序（ORDER BY）优化

最佳实践：

• 将等值查询列放在复合索引左侧
• 范围查询列尽量置于索引最右侧
• 使用覆盖索引避免回表

原理说明

复合索引在数据库中按左到右顺序构建B+树结构。例如索引 (last_name, first_name, age)：

• 先按 last_name 排序
• 相同 last_name 按 first_name 排序
• 相同 first_name 按 age 排序

范围查询会破坏索引的有序性，导致后续列无法利用索引的有序结构进行快速定位。

范围查询失效场景

案例表结构及索引：

CREATE TABLE employees (
  id INT PRIMARY KEY,
  last_name VARCHAR(50),
  first_name VARCHAR(50),
  age INT,
  department VARCHAR(50),
  INDEX idx_name_age (last_name, first_name, age)
);

场景1：范围查询在中间列（索引部分失效）

SELECT * FROM employees
WHERE last_name = 'Smith'
  AND first_name > 'J'
  AND age = 30;  -- age 无法使用索引过滤

执行计划：
• 使用索引范围扫描：last_name='Smith' AND first_name>'J'
• age=30 需回表后过滤

场景2：范围查询在首列（严重失效）

SELECT * FROM employees
WHERE last_name LIKE 'Sm%'
  AND first_name = 'John'
  AND age = 30;  -- 后续列均无法使用索引

执行计划：
• 仅使用 last_name 索引范围扫描
• first_name 和 age 需回表后过滤

索引条件下推（ICP）优化

MySQL 5.6+ 引入ICP机制，可在存储引擎层提前过滤数据：

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

SELECT * FROM employees
WHERE last_name = 'Smith'
  AND first_name LIKE 'J%'
  AND age BETWEEN 25 AND 35;

执行过程：
1. 索引定位 last_name='Smith'
2. 在存储引擎层直接过滤 first_name LIKE 'J%'
3. 进一步过滤 age BETWEEN 25 AND 35
4. 仅符合条件的记录回表

最佳实践

• 索引列顺序策略：
  等值列（=） → 范围列（>, <） → ORDER BY/GROUP BY列
  INDEX (department, age, last_name)
• 覆盖索引优化：
  SELECT last_name, first_name, age
FROM employees -- 无需回表
WHERE department = 'IT' AND age > 30;
• 范围查询拆分：
  将 age BETWEEN 20 AND 30 转为
  age >= 20 AND age <= 30

常见错误

• 错误1：在范围查询列后添加非覆盖列
  INDEX (a, b) WHERE a > 10 ORDER BY b, c → c 无法利用索引排序
• 错误2：过度依赖索引合并
  INDEX(a), INDEX(b) 不如 INDEX(a,b) 高效
• 错误3：忽略列值离散度
  低基数列（如性别）放在索引最左端效率低下

扩展知识

• 索引跳跃扫描(MySQL 8.0+)：
  对 INDEX(gender, age) 执行 WHERE age > 30
  可自动拆分为 gender='M' AND age>30 + gender='F' AND age>30
• 函数索引解决方案：
  CREATE INDEX idx_month ON sales((MONTH(sale_date)));
  解决 WHERE MONTH(sale_date) = 12 的索引失效问题
• 多范围读优化(MRR)：
  对范围查询结果的主键进行排序后再回表，减少磁盘随机IO